[MDEV-6253] MySQL Users Break when Migrating from MySQL 5.1 to MariaDB 10.0.10 Created: 2014-05-20 Updated: 2014-06-11 Due: 2014-06-23 Resolved: 2014-06-11 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | None |
| Affects Version/s: | 10.0.10 |
| Fix Version/s: | 10.0.12 |
| Type: | Bug | Priority: | Critical |
| Reporter: | Clifford Keeney | Assignee: | Sergei Golubchik |
| Resolution: | Fixed | Votes: | 0 |
| 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 . |
| Comments |
| Comment by Axel Schwenke [ 2014-05-20 ] |
|
from #maria channel on Freenode <XL> metateck: have you run mysql_upgrade after the upgrade? Please run mysql_upgrade and check if the problem is gone. |
| Comment by Clifford Keeney [ 2014-05-20 ] |
|
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. |
| Comment by Sergei Golubchik [ 2014-05-23 ] |
|
Was it really from MySQL 5.1? There is no plugin field in the MySQL 5.1 privilege tables. |
| Comment by Clifford Keeney [ 2014-06-06 ] |
|
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. |
| Comment by Pivotal CloudFoundry Services Team (Inactive) [ 2014-06-10 ] |
|
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. |
| Comment by Clifford Keeney [ 2014-06-10 ] |
|
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. cf-services-eng did you guys run mysql_upgrade when you encountered the issue? |
| Comment by Sergei Golubchik [ 2014-06-10 ] |
|
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... |
| Comment by Pivotal CloudFoundry Services Team (Inactive) [ 2014-06-11 ] |
|
Hi metateck, We did try mysql_upgrade but that did not fix. I believe our issue is exactly what is described by serg above. |
| Comment by Clifford Keeney [ 2014-06-11 ] |
|
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. |