distal-attribute
distal-attribute
distal-attribute
distal-attribute

All users show "this user has an empty password" when connected to MariaDB 10.1

nabsltd posted 11 months ago in General

Pretty much as the title says.

The User Manager dialog doesn't show the password hash for any user, even when the login being used has full privileges and can create, delete, and change users with no issues.

The "mysql.user" table has more fields than in MySQL databases, but the basic fields are still there.

hrvoj3e posted 10 months ago

Is there any news for MariaDB 10? See my screenshot.

1 attachment(s):
  • Screenshot-2016-12-15-7_50_39
ansgar posted 10 months ago

Is there a authentication_string column in mysql.user?

zootal posted 10 months ago

I have the exact same problem. When I converted from MySql to MariaDB, I had problems with user auth, and had to basically mysqldump everything, wipe the database, install MariaDB, build the user DB from scratch, and import data. MariaDB is not quite the drop in replacement for MySql that they advertise. Anyhow, yeah, same here, whatever MariaDB does to the user table is not recognized by the current version of Heidisql.

zootal posted 10 months ago

Is there a authentication_string column in mysql.user?

The short answer is that Mysql has dropped the Password field and now uses authentication_string. Mariadb has both Password and authentication_string, but still stores passwords in Password, and does not actually use the authentication_string field. This may change in future releases, but as of 10.1.20, this is how Mariadb does it. That is probably why Heidi does not show a password, it is looking in authentication_string, sees that it is empty, does not check the Password field (why should it, after all, Mysql no longer uses it) and says there is no password.

So much for Mariadb being a drop in replacement for Mysql....when I switched over, I had to wipe the DB and rebuild it from scratch because Mariadb would not work with the mysql DB. Grrrr.....

Here is the long version of the above :)

So to satisfy my own curiosity, I

1) Installed the current version of Mysql (5.7.17) and I notice that you no longer use mysql_install_db to initialize the DB. Huh, that is news to me, I switched to MariaDB before they started doing that. So anyhow, I use bin/mysqld --initialize to init the DB, and look at the user table. It does not even have a Password field, it looks like they have dropped that completely. I see auth info is in authentication_string.

Then I wiped the DB and tried the mysql_install_db script. No difference.

2) Took an install of MariaDB 10.1.20. I notice that with Mariadb, the mysql_install_db is still a script, where with mysql it is an executable. I also note that with Mariadb you can't mysqld --initialize, it is not supported. OK, fine, so I use the mysql_install_db script (you know, like we've been doing for that past thousand years? Mysql devs don't seem to have a problem changing things that break our stuff, but that is another story).

This creates a user table with both Password and authentication_string.

If I set a password, the encrypted password goes into Password, not authentication_string.

hrvoj3e posted 9 months ago

Is there a authentication_string column in mysql.user?

mysql.user has Password field populated but authentication_string is empty. See my attachement!

1 attachment(s):
  • Screenshot-2017-01-08-9_39_49
zootal posted 9 months ago

Is there a authentication_string column in mysql.user?

mysql.user has Password field populated but authentication_string is empty. See my attachement!

What version of MySql did you do this with? What version init'd the DB? the authentication_string field was added in an older version of Mysql, but not used. Then a few versions later they dropped the password field and switched to authentication_string. Which one you see being used probably has to do with the version of Mysql that created the DB, and the version you currently used, and maybe if you ran mysql_upgrade after updating to a newer version - I haven't tried that yet.

hrvoj3e posted 9 months ago

What version of MySql did you do this with?

Not MySQL!

MariaDB 10.1.20 - clean install!

zootal posted 9 months ago

What version of MySql did you do this with?

Not MySQL!

MariaDB 10.1.20 - clean install!

Ah! Yes, that is what MariaDB does. It has the authentication_string field, but does not use it. Hashed passwords are stored in the password field. I'm not sure what the MariaDB devs have planned for future releases, I expect that eventually they will catch up to Mysql and switch over to the authentication_string field, and drop the password field.

It looks like Heidisql now recognizes this as of build 5144.

ansgar posted 9 months ago

Just to create some more confusion: pma had similar bugs and they found some differences in MariaDB 5.2+, MariaDB 5.2-, MySQL 5.7.6- and MySQL 5.7.6+.

On my MariaDB 10.0.25 the authentication_string column is present but seems unused by SET PASSWORD and CREATE USER

zootal posted 9 months ago

For bloody ages Mysql has used the Password field. I don't know why they switched, maybe they are going down the Microsoft "change for the sake of change" path? Why change something that has worked for years and years? It seems to be wreaking havoc all over the place, even though it's been a while since they did it. Their own install script used to bomb because of it LOL.

Thank you Oracle for once again screwing up Mysql. This is not the first time they changed something that screwed our scripts, and is just one of many reasons we switched to MariaDB....

hrvoj3e posted 9 months ago

It looks like Heidisql now recognizes this as of build 5144.

It is better but not quite the same as on MySQL. (see attachment)

  • First password field is not populated with hashed value as on MySQL server.
  • Message in the left bottom corner in User manager is not showing "User has empty password" like before.
1 attachment(s):
  • Screenshot-2017-01-11-16_46_28
zootal posted 9 months ago

It seems to do the same with Mysql, I tested it this morning on both Mariadb and Mysql. It won't show password fields, but you can use it to change them. I thought that was a "feature by design"?

Please login to leave a reply, or register at first.