MySQL upgrade breaking things

If you’ve just updated your MySQL installation and have found this happening:
060511 22:38:11 [Warning] ‘./mysql/host’ had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed
060511 22:38:11 [Warning] ‘./mysql/user’ had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed
060511 22:38:11 [Warning] ‘./mysql/db’ had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed
060511 22:38:11 [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

Then you need to run this:
mysql_fix_privilege_tables

However, you’ve probably stopped mysql and can’t get it to start again to run that script! Well, that’s the problem I had anyway… to remedy this, do this:
mysqld_safe –skip-grant-tables –user=root &
mysql_fix_privilege_tables
pkill mysqld
/etc/init.d/mysqld restart

Good luck!

40 thoughts on “MySQL upgrade breaking things

  1. This tip saved my life!
    Anyway, as stupid as it can seem (and it is!) I copy-pasted the mysql_safe command and got myself a lot of troubles until I realized that the hyphens before ‘skip-grant-tables’ and ‘user’ are not the ones that one must enter. So if anyone wants to use it, type the whole command by hand!
    Thank you for pointing these tips.

  2. The hyphens should be double hyphens like so:

    mysqld_safe Β–-skip-grant-tables -Β–user=root &

    Then it worked for me. Great tip, thanks.

  3. Ah yes, that’s my blogging tool thinking dash dash is one long dash. Grr! Glad that I’m actually helping people though and not just wasting my time writing stuff! heh.

  4. Hey,

    Good tip – they should have had this in the manual too πŸ˜‰ – Google + friendly internet users saves the day again.

  5. Thanks very much for this blog post. Dumping the error message within my mysqld.err logfile into Google brought me straight to your page!

    Now my blog is fixed. πŸ˜€

    Keep up the good work!

  6. The command, once I used the double dashes, failed for me. Looking at the mysql.err log showed it complaining that specifying ‘root’ was the culprit, so I had to leave that off. This was on a gentoo system, so perhaps there’s a gentooism there.

    In any event, this worked fine with those corrections. Thanks for the tip!

  7. Wow, can’t believe so many people found that so useful!

    Remember, if you want to help me, just link me on your site! Cheese!

  8. oh thank you! this was the only guide that actually worked. the other guides tell me to run mysql when its mysqld that wont even start. the –skip-grant-tables is the magic to get mysqld to start. this saved my butt! thank you!

  9. Thanks… this saved our summer projects at the school where i work… there was a mysql database with all our imaging settings in it…. and poof it died when a gentoo upgrade killed the database…. THanks πŸ˜‰

    Brett

  10. I no longer recommend using Gentoo…or at least as a server platform. I find it wayy to time consuming/over-complicated to keep things up to date. I now use Debian again. Fair enough it’s probably not quite as fast and doesn’t tend to include so many modern packages, but it’s solid as fuck.

    I’m also moving towards using Xen anyway, which I may blog upon in the coming months.

  11. Thank you. Thank you. Thank you. Thank you. Thank you. Thank you. So what I’m really trying to say is… thank you. I haven’t had much time to work on my server lately so after a month of my database being down I searched through the logs and found the error message that lead me to this page and ultimately to database salvation. I am covered in relief right now. Thank you.

  12. Uh oh.. I didn’t have such good luck..

    070906 0:29:11 [Warning] Found invalid password for user: ‘(removed) @localhost ‘; Ignoring user
    070906 0:29:11 [Warning] Found invalid password for user: ‘(removed) @localhost ‘; Ignoring user
    070906 0:29:11 [Warning] Found invalid password for user: ‘(removed) @localhost ‘; Ignoring user
    070906 0:29:11 [Warning] Found invalid password for user: ‘(removed) @localhost ‘; Ignoring user
    070906 0:29:11 [Warning] Found invalid password for user: ‘(removed) @localhost ‘; Ignoring user
    070906 0:29:11 [Warning] Found invalid password for user: ‘(removed) @localhost ‘; Ignoring user
    070906 0:29:11 [Warning] Found invalid password for user: ‘(removed) @localhost ‘; Ignoring user
    070906 0:29:11 [Warning] Found invalid password for user: ‘(removed) @localhost ‘; Ignoring user
    070906 0:29:11 [Warning] Found invalid password for user: ‘(removed) @localhost ‘; Ignoring user

    “(removed) ” = me editing out the usernames, naturally.

    πŸ™

  13. Invalid password warnings are caused by change of algorithm in passwd fucntion, I guess. Start mysql with –skip-grant-tables again and update the user table records in mysql database manually. It worked for me prfectly. I hope you do not have too much records with password there.

  14. It might be just over a year since the last entry but this fix still works, you saved me (judging from the above) hours of frustration. Instead it was like fifteen seconds (after I figured out what you were all talking about with double dashes) and bam!

Leave a Reply

Your email address will not be published. Required fields are marked *