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

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.

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

Is there a authentication_string column in mysql.user?

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.

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.

Is there a authentication_string column in mysql.user?

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

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.

What version of MySql did you do this with?

Not MySQL!

MariaDB 10.1.20 - clean install!

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.

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

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....

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.

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"?

I just committed a quick fix for this issue. HeidiSQL should then use the first non-empty field of password/authentication_string. So, the next nightly build should heal that.

By the way: See https://github.com/HeidiSQL/HeidiSQL/issues/153 for a discussion about a revamp of the user manager.

I'm running 9.5.0.5278, and the password field still comes up blank. Is it supposed to show the hash from the user table? I'm running MariaDb 10.2.14 (10.3.6 RC does the same thing). I can change the password by entering the new password in the Password field, but otherwise it stays blank.

And I forgot to mention - this user table for reasons unknown to me has both password and authentication_string fields. Password is field 3, and stores the password hash. authentication_string is field 43, and is blank.

In HeidiSQL apparently in all accounts it is shown that they do not have a password, although they do.

Even in Version 9.5.0.5278 (64 Bit) updated to May 1st, it shows that some users do not have a password but they do.