MariaDB (MySQL) Login Failure New Install Fix

Sometimes…

So I decided, just for grins, to install MariaDB (MySQL) on the Raspberry Pi M3 under Devuan 2.0 just as a test case for how small a system I could use for the GHCN data. I’d done the install on the XU4 and “I know all the steps” so what could go wrong?

True, before: I’d done a MySQL install and then did MariaDB over it as an upgrade. Turns out that now installing “mysql-server” actually just does an install of “mariadb-server” (I originally installed MariaDB, then ripped it out and tried the “2 step” that informed me it was just going to install MariaDB).

As MariaDB is a “drop in replacement” for MySQL, it has the unfortunate side effect of installing a lot of stuff AS “mysql” commands.

In /usr/bin you find the usual set of commands to “do stuff” including the one that lets you set the password on the database system. In the past an ncurses style panel would pop up during installation and set the password during install. This doesn’t happen with the “new” install. So I figured I’d just use the utility that sets the password to set the password…

ls /usr/bin/mar*
/usr/bin/mariabackup
/usr/bin/mariadb
/usr/bin/mariadbcheck
/usr/bin/mariadb-service-convert

root@PiM3Devuan2:/# ls /usr/bin/my*
/usr/bin/myisamchk /usr/bin/mysql_find_rows
/usr/bin/myisam_ftdump /usr/bin/mysql_fix_extensions
/usr/bin/myisamlog /usr/bin/mysqlhotcopy
/usr/bin/myisampack /usr/bin/mysqlimport
/usr/bin/my_print_defaults /usr/bin/mysql_install_db
/usr/bin/mysql /usr/bin/mysqloptimize
/usr/bin/mysqlaccess /usr/bin/mysql_plugin
/usr/bin/mysqladmin /usr/bin/mysqlrepair
/usr/bin/mysqlanalyze /usr/bin/mysqlreport
/usr/bin/mysqlbinlog /usr/bin/mysql_secure_installation
/usr/bin/mysqlcheck /usr/bin/mysql_setpermission
/usr/bin/mysql_convert_table_format /usr/bin/mysqlshow
/usr/bin/mysqld_multi /usr/bin/mysqlslap
/usr/bin/mysqld_safe /usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysqld_safe_helper /usr/bin/mysql_upgrade
/usr/bin/mysqldump /usr/bin/mysql_waitpid
/usr/bin/mysqldumpslow /usr/bin/mysql_zap
/usr/bin/mysql_embedded /usr/bin/mytop

The key one being that “mysql_secure_installation”. I ran it. It asked for the current password and accepted a blank. It claimed to change the password. Then attempting to log in with the same credential / command as before would fail:

chiefio@PiM3Devuan2:~$ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I’ve spent a few days, on and off, trying to make this go / fix the permissions issue. Today I resorted to some web searches on the “problem” eventually finding this:

https://superuser.com/questions/949496/cant-reset-mysql-mariadb-root-password

down in the text a ways:

EDIT, some months later: I’m now used to have this problem come back frequently, I think at each update of MariaDB (or something like that). So I’ve got a better comprehension of the probem ; there’s an UNIX_SOCKET plugin that can let you log in a MariaDB account without having to create a password, because it uses the shell’s credentials to trust you, without having to enter any password. In fact, this plugin is an authentication plugin and not a method of communication with the SQL server. So you can safely disable it if you don’t use unix socket as a logging-in method. The only thing I can’t explain is why the UNIX_SOCKET plugin is regularly set on each account of the database, without any action on my side.

This has the nice side effect that, when it happens, you can login to the SQL server without having to restart MariaDB with –skip-grant-tables: just log-in to the system’s root account, then just connect with mysql -u root without password, then reset the plugin field in the way it is explained above.

OK, that got me logged in to MariaDB:

root@PiM3Devuan2:/# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

Now I can proceed with the basic business of setting up login / permissions / DB Tables / etc. and try to get back to more normal operations.

The article linked states to remove the socket based connection, but a further comment says that breaks some other bits, so I’m not sure exactly what the final “fix” will be. Hopefully I’ll figure out how to just let me do the regular old connection by “mysql -u {whoever} -p” and not have to take this “be root” path or fiddle with the socket connector; but at this point the end game is unknown.

This breaks the maintenance cron job on Ubuntu 16.04 (at least) because that script is expecting to log in without a password using the socket plug-in. See superuser.com/questions/957708/… for details. – colan Jul 21 ’16 at 20:24
Okay, interesting, but this does not happen anymore (my legacy passwords setup does not break randomly now). Maybe the Unix socket plugin is set on the root user only, now? Couldnt’ check that right now. But I don’t like the idea that the OS decides for me what authentication setup to use. – Morgan Touverey Quilling Jul 21 ’16 at 20:45
This problem sucks, it happened on Debian 9, no root password was asked when I installed MariaDB and I could not even reset it. I hope it will not happen again after an upgrade. Should we replace MariaDB with MySQL to avoid such problems? – baptx Nov 30 ’17 at 16:03
Not sure it still happens, it may have been fixed now (I can edit the post if anyone knows). Maybe it happens only at the installation, which would be a good default setup. – Morgan Touverey Quilling Nov 30 ’17 at 22:20
1
I just noticed that if we don’t use the unix socket plugin for the root user, it will fail to install phpmyadmin package on latest Debian: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO). So the best solution is to keep using the default unix socket authentication with command sudo mysql -u root, which I think is also more secure and performant. – baptx Dec 1 ’17 at 12:31

So “somebody” decided to “fix what isn’t broken” and broke all the prior documentation of how to set up MySQL / MariaDB while causing the usual password reseting process to be broken too. Sigh.

In Conclusion

So, OK, I’ve got a way in, even if it is ass-backwards. I can move forward with recreating the temperature database and adding users and all.

With some luck, I’ll figure out how to turn off this “improvement” to my “security” that mostly just means I must have a root shell running all the time (NOT a security improvement…) and figure out why in hell I must use a “Socket” to get from my own terminal to my own database on my own system.

In my opinion, many of the present “Oh Bother” changes in Linux have been to cater to the major industrial users (“cloud” providers, Red Hat Enterprise on VMs) at the expense of the “plain old desktop guy at home”. I don’t need a full on industrial installation to fiddle with a database in my own office on a Raspberry Pi. Setting things up to expect a staff including a Network Engineer, Security Officer, Kerberos Administrator, Systems Programmer, etc. etc. is just not helping. In the past, those shops, who have that staff, did the set up for their needs and left the rest of us alone. That seems to be inverting.

Sigh.

I really hope some Distribution rises that just dumps all the Red Hat Corporate pandering changes in Linux and reverts to a “Home Gamer Friendly” process.

We’ve reached the point where constantly layering on ever more complexity in the name of simplification is killing the product. Networking has at least 3 layers over administration that I know of all to “hide” the old way of “edit this file and add this text”. Just stupid complication creep.

If that simpler way doesn’t show up in a year or so, I’ll be slowly moving solutions back onto a BSD base. Most likely FreeBSD, but maybe NetBSD for some. Why? The only reason NOT to use them is the “user hostile” old way of doing things. BUT, as Linux is becoming even MORE “user hostile” with layers on layers of kludge and SystemD crapping on every system possible: We are nearly at parity of “Oh Bother” between the two. At that point, I’ll take “Simple, proven, and familiar” and just suck it up and deal with getting X-windows going. (I’ve configured X before, I just don’t like it…)

There are already some releases on the PC that come with windows already installed. That will show up on the Arm Arch eventually. At that time, I’m gone. If I want to deal with the complexity of an “industrial strength” installation, I’ll do it in BSD not in SystemD crapped up complexified Linux.

With that, I’m going to dive back into figuring out the end game of getting MariaDB to once again be “user friendly”… and getting the NCDC / NOAA / GHCN stuff reloaded and some performance testing done on the R.PiM3.

Subscribe to feed