snippetsqlMinor
How do I configure MySQL to accept a custom column in mysql.users?
Viewed 0 times
columnacceptcustommysqlhowusersconfigure
Problem
I am running MySQL Ver 14.14 Distrib 5.5.57 on Debian Jessie (x86_64). In my mysql.users table, I have a custom column added for database owner id--which is for my server management system's use. Somewhere along the way, MySQL started to complain when I try to modify a user's privileges:
The first 42 columns are exactly as MySQL expects. The 43rd about which it is complaining is my added column. How can I tell MySQL that it's "OK" for there to be 43 columns?
And yes, I have run the appropriate upgrade scripts with each upgrade to MySQL, including one extra time "just to make sure":
Thanks in advance for any assistance!
ERROR 1547 (HY000): Column count of mysql.user is wrong. Expected 42, found 43. The table is probably corruptedThe first 42 columns are exactly as MySQL expects. The 43rd about which it is complaining is my added column. How can I tell MySQL that it's "OK" for there to be 43 columns?
And yes, I have run the appropriate upgrade scripts with each upgrade to MySQL, including one extra time "just to make sure":
mysql_upgrade --force -uroot -pThanks in advance for any assistance!
Solution
YOU ABSOLUTELY, POSITIVELY, CANNOT DO THAT. Why ???
Back on
I carefully explained how the grant columns existed from MySQL 4.x to 5.6. I have explained this many times (See my others).
The columns in the
On
I highly recommend you remove your custom column today !!! Otherwise, you will NEVER get all your grants back. Please go manage your database user identification in another database.
Back on
Apr 12, 2012, I wrote the answer to Cannot GRANT privileges as rootI carefully explained how the grant columns existed from MySQL 4.x to 5.6. I have explained this many times (See my others).
The columns in the
mysql.user table need to be in the exact position they are published along with the exact column names.On
Oct 10, 2014, I answered MySQL service stops after trying to grant privileges to a user where I gave an example of how you can hack repairs into mysql.user to comply with the next version.I highly recommend you remove your custom column today !!! Otherwise, you will NEVER get all your grants back. Please go manage your database user identification in another database.
Context
StackExchange Database Administrators Q#183570, answer score: 2
Revisions (0)
No revisions yet.