1

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?!

Improve this question

1 Answer 1

Reset to default
4

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.

Improve this answer
5
  • 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, 2012 at 17:35
  • What error do you get? Can you try mysql_upgrade -s ?
    – thinice
    Mar 11, 2012 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, 2012 at 17:41
  • See my added paragraph to my answer
    – thinice
    Mar 11, 2012 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, 2012 at 18:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .