Access denied warnings with MariaDB 10.3 on Ubuntu 20.04

[dawi]

After installing MariaDB 10.3 on Ubuntu 20.04 via this ansible role, I am getting the following warnings in /var/log/mysql/error.log:

2021-01-20 13:20:41 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.25-MariaDB-0ubuntu0.20.04.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 20.04
2021-01-20 13:20:41 8 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
2021-01-20 13:20:41 9 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
2021-01-20 13:20:41 10 [Warning] Access denied for user 'root'@'localhost' (using password: NO)

This warnings are caused during execution of /etc/mysql/debian-start:

…
MYSQL="/usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf"
…
trap "" SIGHUP
(
  upgrade_system_tables_if_necessary;
  check_root_accounts;
  check_for_crashed_tables;
) >&2 &
…

The issue could be that passwords configured in /etc/mysql/debian.cnf and /root/.my.cnf don't match.

The errors do not occur if I set the mysql_root_password to "" like this:

mysql_root_password:
mysql_packages:
  - mariadb-server-10.3
  - mariadb-client-10.3

But that's nothing I would want to use in production.

[PierreRambaud]

I also have the same problem with debian 10. I was wondering if adding the -p {{ mysql_root_password  }} is a good idea. But, it should be easier and better to have a MySQL user which can run MySQL commands without using a password.

[1977er]

Commenting out the empty password = lines in debian.cnf resolves any permission problems. (Ie. this role was unable to pass the Remove MySQL test database step due to allegedly missing permissions (ignoring correct /root/.my.cnf).)

[MassiveHiggsField]

I hope i am not hijacking a different problem, but i had a similar problem as described in the last comment here:

TASK [geerlingguy.mysql : Remove MySQL test database.] *************************
fatal: [localhost]: FAILED! => {"changed": false, "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, u\"Access denied for user 'root'@'localhost'\")"}

I tried setting a new root password and also tried it with an empty password. My specs are:

• Debian/Stretch
• Ansible 2.7.13 installed via pip 2.7
• mysql Ver 15.1 Distrib 10.1.48-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

My configuration looks like this:

- role: geerlingguy.mysql
  mysql_root_password_update:     true
  mysql_root_username:            root
  mysql_root_password:            test
  # also tried with:
  # mysql_root_password: ""
  # and:
  # mysql_root_password: 

The contents of the file /etc/mysql/debian.cnf look like this:

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = root
password = 
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = root
password = 
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

And the file /root/.my.cnf looks like this:

#
# Ansible managed
#

[client]
user="root"
password="test"

I tried removing/commenting the empty password lines in /etc/mysql/debian.cnf like the previous poster suggested and then i used "vagrant reload --provision", but the same error remained (i also verified that the lines would not get added back by reloading).

What i have noticed is, that if i try to connect from the shell (logged in as vagrant) with "mysql -u root -ptest" or "mysql -u root" it wont work, but if i "sudo su" first, it will work with just "mysql".

[stale[bot]]

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

[vurral]

I can confirm that this issue still persists. At least with this combination:

• Ubuntu 20.04.3 LTS
• MariaDB 10.3.31-0ubuntu0.20.04.1

I haven't found the reason yet, but it appears that MariaDB started blocking connections for the root-user over the host localhost. In my case I solved this with providing login_unix_socket to the mysql_user command.

Here is a config, which worked for me:

- name: Set MariaDB root password for the first time
  mysql_user:
    name: "{{ mariadb_root_user }}"
    password: "{{ mariadb_root_password }}"
    host_all: yes
    state: present
    login_unix_socket: "/var/run/mysqld/mysqld.sock"

[stale[bot]]

This issue is no longer marked for closure.

[stale[bot]]

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

[daraul]

Manually setting login_unix_socket seems to have solved this for me. Might there be some variable I could set to do something similar?

- name: Remove MySQL test database.
  mysql_db: "name='test' state=absent login_unix_socket='/var/run/mysqld/mysqld.sock'"

[stale[bot]]

This issue is no longer marked for closure.

[stale[bot]]

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

[GuillaumeDuveau]

Still active and also blocking the mysql upgrade:

Mar 30 11:01:36 vps /etc/mysql/debian-start[757]: Reading datadir from the MariaDB server failed. Got the following error w>
Mar 30 11:01:36 vps /etc/mysql/debian-start[757]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using pass>
Mar 30 11:01:36 vps /etc/mysql/debian-start[757]: FATAL ERROR: Upgrade failed

MariaDB 10.5.15 on Debian Bullseye

[stale[bot]]

This issue is no longer marked for closure.

[stale[bot]]

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

[stale[bot]]

This issue has been closed due to inactivity. If you feel this is in error, please reopen the issue or file a new issue with the relevant details.