Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a Mysql database running on Ubuntu that is suddenly falling over. I have tried cleaning the installation by removing:-

apt-get remove mysql-server 
apt-get remove mysql-client 
apt-get remove mysql-common

from the system and then reinstalling them with aptitude.

I have also reinstated all the files in /var/lib/mysql from a backup that I know was working for the database.

I can get onto the database and switch to the database where I suspect there is a corrupt file, but as soon as I do 'show tables', I get a crash:-

mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: DBNAME

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR: 
Can't connect to the server

The last lines of the error log are displaying:-

120311 17:12:29 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. The table is probably corrupted
120311 17:12:29 [ERROR] mysql.user has no `Event_priv` column at position 29
120311 17:12:29 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
120311 17:12:29 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

I was hoping that a fresh install would eradicate the 'Column count of mysql.db is wrong' error, but it doesn't seem to have done so.

Is anyone able to suggest a way to recover my DB?!

share|improve this question

1 Answer 1

up vote 4 down vote accepted

Run mysql_upgrade manually - chances are your MySQL database doesn't meet the schema requirements for the updated version of MySQL you're using.

Do you have the privileges backed up for the server? If so it might be quicker to back up your mysql data dir (just in case) - shut down MySQL, remove the 'mysql' directory/db from the data dir and run 'mysql_install_db' to have it re-generate the database, start MySQL and reload your privileges.

share|improve this answer
    
That's also crashing with mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... FOR UPGRADE'. –  Purpletoucan Mar 11 '12 at 17:35
    
What error do you get? Can you try mysql_upgrade -s ? –  thinice Mar 11 '12 at 17:36
    
mysql_upgarde doesn't appear to have '-s' flag. As well as the error stated above, the error log is showing these three errors: 120311 17:39:45 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. The table is probably corrupted 120311 17:39:45 [ERROR] mysql.user has no Event_priv column at position 29 120311 17:39:45 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. –  Purpletoucan Mar 11 '12 at 17:41
    
See my added paragraph to my answer –  thinice Mar 11 '12 at 17:45
    
Thanks so much for your help thinice - no joy with that server but I have managed to create a new server (I'm on AWS) and resurrect the DB from a dump. I am now confident that I can rebuild the server around that DB, but I've got quite a long night ahead! I'm beginning to suspect that perhaps there's a disk or other h/w error on my existing server :( –  Purpletoucan Mar 11 '12 at 18:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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