4

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:

ERROR 1547 (HY000): Column count of mysql.user is wrong. Expected 42, found 43. The table is probably corrupted

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

mysql_upgrade --force -uroot -p

Thanks in advance for any assistance!

2

YOU ABSOLUTELY, POSITIVELY, CANNOT DO THAT. Why ???

Back on Apr 12, 2012, I wrote the answer to Cannot GRANT privileges as root

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

  • Ouch, I'm a bit surprised that you are even allowed to add columns to tables in the catalogue. Good answer. – Lennart Aug 15 '17 at 22:16
  • I have to admit that I am surprised by this answer. Isn't the point of open source that everyone can hack code however they like? If your point was "if you do that, such and such will break..." or something like that--it would make more sense. Perhaps I am mistaking your meaning-- as telling everyone that one "absolutely, positively, cannot do that" would sound like a proprietary software order under a EULA--which is designed to stiffle unapproved innovation. Anyway, I do appreciate your taking the time to answer. – Eriks Aug 17 '17 at 17:38
  • 2
    @Erik, sure you can but you can't just hack the catalogue. You will have to hack the DBMS:s engine that uses the catalogue as well. I would suggest that you add your own tables for additional properties of for example users. – Lennart Aug 17 '17 at 18:14
0

Based on your comment

I have to admit that I am surprised by this answer. Isn't the point of open source that everyone can hack code however they like? If your point was "if you do that, such and such will break..." or something like that--it would make more sense. Perhaps I am mistaking your meaning-- as telling everyone that one "absolutely, positively, cannot do that" would sound like a proprietary software order under a EULA--which is designed to stiffle unapproved innovation. Anyway, I do appreciate your taking the time to answer.

I have decided to post a different answer in the spirit of your comment.

Since MySQL is open source, YES, it is very possible to wedge in your own columns. However, I must present the facts in order for you to see the reality of such an undertaking.

As I mentioned in my other answer, column order matters with mysql.user.

The actual source code responsible for managing grants is called sql/sql_acl.cc. You are free to look around and see it.

What worries me is that the Book

Understanding MySQL Internals

will deep dive into things like InnoDB internals and binlog format and the source code involved, but is very silent about grants (only mentioning the source code filename on pages 6 and 11).

The source code in that link is for MySQL 5.5. You could just get the source code for the latest version of MySQL 5.5 and play with it. I wish you well on your endeavor. I hope you never try to upgrade to 5.6, 5.7, or 8.0 because you will have to repeat your coding adventure for those versions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.