Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 10.0.10
-
Fix Version/s: 10.0.12
-
Component/s: None
-
Labels:None
-
Environment:CentOS release 6.5 (Final)
Linux version 2.6.32-431.5.1.el6.x86_64 (mockbuild@c6b10.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-4) (GCC) ) #1 SMP Wed Feb 12 00:41:43 UTC 2014
Description
I had MySQL installed and running. I created some users using HeidiSQL, a Windows MySQL GUI and verified that they worked. I then upgraded to MariaDB.
Only my root accounts worked on MariaDB. When I checked the mysql.user table, the root accounts had empty `plugin` fields, but the non-root accounts had mysql_native_password in the field. Emptying the field and restarting the server (probably flushing privileges would have worked) resolved the issue.
I am not sure what was in the field prior to the MariaDB migration, but after checking all the other servers I have that are running vanilla MySQL, it was probably blank. I use HeidiSQL to create all of those users and none of them have a populated plugin field .
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I ran mysql_upgrade, but unfortunately I did not run this until I fixed the problem manually by emptying the plugin field. I do not have binary logging enabled so I am unable to supply that.
Was it really from MySQL 5.1? There is no plugin field in the MySQL 5.1 privilege tables.
It was definitely MySQL 5.1. However, I did not see the plugin field in the privilege table until after the migration. It is possible that this was just my fault for not running mysql_upgrade, but I don't know for sure.
FWIW - We've seen this upgrading from MySQL 5.6.13 to MariaDB 10.0.10. We've had to clear the plugin field for all rows in user table and FLUSH PRIVILEGES.
I'm feeling a little bad that this bug report is getting so much attention when I feel it was most likely just caused by my failure to run mysql_upgrade. Pivotal CloudFoundry Services Team did you guys run mysql_upgrade when you encountered the issue?
This bug report is, basically, about MariaDB not allowing a user to connect, when the corresponding row in the mysql.user table has password and plugin fields set, but authentication_string empty.
The original logic (still preserved in MariaDB) was simple: if the plugin field is not set (meaning old table), the server looks at the password field and implicitly uses mysql_native_password plugin. If the plugin field is set (new way, pluggable-auth compatible), the server uses plugin and authentication_string pair, and ignores the password field (the password is stored in the authentication_string).
Now, it looks like Oracle broke this, in MySQL 5.6, plugin field contains "mysql_native_password", password is set, authentication_string is empty. In this case, MySQL 5.6 uses old style field password and a new style field plugin, but ignored new style field authentication_string. There's no logic in that.
Still wonder, what we should do about this issue...
Hi Clifford Keeney,
We did try mysql_upgrade but that did not fix. I believe our issue is exactly what is described by Sergei Golubchik above.
Thanks for the clarification. I didn't have a full understanding of the behavior I was experiencing but it does sound like a real bug to me now.
from #maria channel on Freenode
<XL> metateck: have you run mysql_upgrade after the upgrade?
<metateck> no i didnt
<XL> metateck: see! you must always run mysql_upgrade after upgrading/downgrading/migrating a MySQL installation
<metateck> yeah that is proabbly the entire cause of my bug report
Please run mysql_upgrade and check if the problem is gone.