[MDEV-6253] MySQL Users Break when Migrating from MySQL 5.1 to MariaDB 10.0.10 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.0.10
Fix Version/s: 10.0.12
Component/s: None
Labels:
None
Environment:

Hide
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

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

Attachments

Activity

Ascending order - Click to sort in descending order

View 4 older comments

Pivotal CloudFoundry Services Team (Inactive) added a comment - 2014-06-10 00:38

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.

Pivotal CloudFoundry Services Team (Inactive) added a comment - 2014-06-10 00:38 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.

Clifford Keeney added a comment - 2014-06-10 10:45 - edited

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?

Clifford Keeney added a comment - 2014-06-10 10:45 - edited 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?

Sergei Golubchik added a comment - 2014-06-10 15:59

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

Sergei Golubchik added a comment - 2014-06-10 15:59 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...

Pivotal CloudFoundry Services Team (Inactive) added a comment - 2014-06-11 00:27

Hi metateck,

We did try mysql_upgrade but that did not fix. I believe our issue is exactly what is described by serg above.

Pivotal CloudFoundry Services Team (Inactive) added a comment - 2014-06-11 00:27 Hi metateck , We did try mysql_upgrade but that did not fix. I believe our issue is exactly what is described by serg above.

Clifford Keeney added a comment - 2014-06-11 02:24

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.

Clifford Keeney added a comment - 2014-06-11 02:24 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.

People

Assignee:: Sergei Golubchik

Reporter:: Clifford Keeney

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Due:: 2014-06-23

Created:: 2014-05-20 10:25

Updated:: 2014-06-11 16:05

Resolved:: 2014-06-11 16:05

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server