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!
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.
The hyphens should be double hyphens like so:
mysqld_safe Β-skip-grant-tables -Βuser=root &
Then it worked for me. Great tip, thanks.
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.
Hey,
Good tip – they should have had this in the manual too π – Google + friendly internet users saves the day again.
Awesome tip!! got me up and running again.
Thanks for the tip.
THANK YOU!! Great tip! I found it after hours and it was my last hope.
Thanks, worked perfectly
John, thanks for the tip and everyone else, thanks for the info re. the double dashes.
Thanks for these few lines with so much effect :-)!!
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!
Like all of the above, thanks a million! π
great tip! thanks, it saved me.
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!
Thanks, I’m good too now. I tried everything, this was what I needed.
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!
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!
really really really hot tip!!
Winner. Thanks a heap. Didn’t need the –user=root on gentoo.
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
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.
Late to the game, but thankful all the same. Your tip helped me resolve my mysql issues.
thanks for saving me a big headache π
Thanks for the this tip, It took took me almost al day to figure out that looking in to the log file /var/log/mysql/mysld.err could give me a hint.
Congratuations John, you made it to the “I am feeling lucky” position in google for the error message I found in this log file look at this here …
http://www.google.nl/search?q=Fatal+error%3A+mysql.user+table+is+damaged+or+in+unsupported+3.20+format.
John Says: Remember, if you want to help me, just link me on your site! Cheese!
So I did: http://www.greenbaum.org/community/node/139
Thanks a lot !!!
I was downgrading when I saw your tips : It saves me some of my time π
thanks for the tip!
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.
Ok, this is getting redundant, but THANKS!
Found this via google and it fixed me right up.
Remember, if you found this useful, you can do me a favour by linking to my site (like this):
John Hunt!
Thanks!
Yessir! Thank you. I think Google has already done you the favour…
thanks a ton! now to solve other upgrade issues with php and apache. π
Thanks a lot, much appreciated.
thank you for your solution! it helps me a lot!
Yet another happy admin whom this post helped immensely! I’ve linked to it from our KB.
Thanks!
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.
π
I’ll buy you a beer, m8.
Another life saved. Thanks !
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.
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!