May 7 23:17:12 RPi4 mariadbd[495]: Version: '10.5.19-MariaDB-0+deb11u2' socket: '/run/mysqld/mysqld.sock' port: 3306 Debian 11
May 7 23:17:12 RPi4 systemd[1]: Started MariaDB 10.5.19 database server.
May 7 23:17:12 RPi4 systemd[1]: Started Regular background program processing daemon.
May 7 23:17:12 RPi4 /etc/mysql/debian-start[646]: Upgrading MySQL tables if necessary.
May 7 23:17:12 RPi4 systemd[1]: Starting The PHP 7.4 FastCGI Process Manager...
May 7 23:17:12 RPi4 cron[647]: (CRON) INFO (pidfile fd = 3)
May 7 23:17:12 RPi4 cron[647]: (CRON) INFO (Running @reboot jobs)
May 7 23:17:13 RPi4 CRON[657]: (root) CMD ( PATH="$PATH:/usr/sbin:/usr/local/bin/" pihole updatechecker reboot)
May 7 23:17:13 RPi4 CRON[656]: (root) CMD (/usr/sbin/logrotate --state /var/lib/logrotate/pihole /etc/pihole/logrotate)
May 7 23:17:13 RPi4 mariadbd[495]: 2023-05-07 23:17:13 3 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
May 7 23:17:13 RPi4 /etc/mysql/debian-start[651]: Looking for 'mariadb' as: /usr/bin/mariadb
May 7 23:17:13 RPi4 /etc/mysql/debian-start[651]: Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client
May 7 23:17:13 RPi4 /etc/mysql/debian-start[651]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
May 7 23:17:13 RPi4 /etc/mysql/debian-start[651]: FATAL ERROR: Upgrade failed
May 7 23:17:13 RPi4 /etc/mysql/debian-start[673]: Checking for insecure root accounts.
May 7 23:17:13 RPi4 mariadbd[495]: 2023-05-07 23:17:13 4 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
May 7 23:17:13 RPi4 debian-start[676]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
I think because of phpmyadmin I set a password for root (which is probably not best practice, to use root with phpmyadmin?). So I could set phpmyadmin to allow login without password, or what do you think is best practice? (phpmyadmin is only reachable from inside my LAN)
Login only as non-root into phpmyadmin but also set no root password, because MariaDB just fails to upgrade?
After removing the root password it worked just fine:
May 9 19:22:08 RPi4 systemd[1]: Started MariaDB 10.5.19 database server.
May 9 19:22:08 RPi4 /etc/mysql/debian-start[607]: Upgrading MySQL tables if necessary.
May 9 19:22:08 RPi4 systemd[1]: Started Regular background program processing daemon.
Yeah, sort of. I removed the mariaDB password for root, the message about the failed upgrade disappeared.
I can no longer login as root with phpmyadmin, because of AllowNoPassword is set to false. But this is a good thing since root shouldn’t use phpmyadmin, right?
When I look into the install script, it creates a user and group phpmyadmin and gives it the global software password, also with the note:
Since “root” user cannot be used for login (unix_socket authentication), grant full admin privileges to “phpmyadmin”.
I can not remember how I got root login to work, but I think I will delete phpmyadmin anyway.
But what should I do with the root user now? I don’t really get if I should set a password for root or not?! Or maybe this “FATAL ERROR: upgrade failed” was not because of the root user? I will set a password and will have a look.
EDIT:
Now I have:
Jul 05 19:11:31 RPi4 /etc/mysql/debian-start[680]: Upgrading MySQL tables if necessary.
Jul 05 19:11:32 RPi4 mariadbd[521]: 2023-07-05 19:11:32 3 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Jul 05 19:11:32 RPi4 /etc/mysql/debian-start[708]: Checking for insecure root accounts.
Jul 05 19:11:32 RPi4 mariadbd[521]: 2023-07-05 19:11:32 4 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Jul 05 19:11:32 RPi4 debian-start[711]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Warning when no password it set but throws an ERROR when a password is set
I removed then phpmyadmin and restartet MariaDB, same as before:
Jul 05 19:17:41 RPi4 mariadbd[2459]: 2023-07-05 19:17:41 3 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Jul 05 19:17:41 RPi4 /etc/mysql/debian-start[2483]: Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client
Jul 05 19:17:41 RPi4 /etc/mysql/debian-start[2483]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Jul 05 19:17:41 RPi4 /etc/mysql/debian-start[2483]: FATAL ERROR: Upgrade failed
Jul 05 19:17:41 RPi4 mariadbd[2459]: 2023-07-05 19:17:41 4 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Jul 05 19:17:41 RPi4 debian-start[2492]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@DietPiOPi5:~# mysql -e "SHOW GRANTS FOR 'root'@'localhost';"
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
root@DietPiOPi5:~#
