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

I can't connect to MySQL for a specific database, even from phpMyAdmin, i get the following error:

#2002 - Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) The server is not responding (or the local server's socket is not correctly configured).

In MySQL logs I see:

130404 1:18:09 InnoDB: MySQL and InnoDB data dictionaries are out of sync. InnoDB: Unable to find the AUTOINC column id in the InnoDB table strangedb/table. InnoDB: We set the next AUTOINC column value to 0, InnoDB: in effect disabling the AUTOINC next value generation. InnoDB: You can either set the next AUTOINC value explicitly using ALTER TABLE InnoDB: or fix the data dictionary by recreating the table. 130404 1:25:13 InnoDB: MySQL and InnoDB data dictionaries are out of sync. InnoDB: Unable to find the AUTOINC column id in the InnoDB table strangedb/table. InnoDB: We set the next AUTOINC column value to 0, InnoDB: in effect disabling the AUTOINC next value generation. InnoDB: You can either set the next AUTOINC value explicitly using ALTER TABLE InnoDB: or fix the data dictionary by recreating the table. 130404 1:25:13 InnoDB: Assertion failure in thread 139934538761984 in file handler/ha_innodb.cc line 8290 InnoDB: Failing assertion: auto_inc > 0 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html InnoDB: about forcing recovery.

I can confirm MySQL is working OK for all other tables.

I have gone through the link about forcing recovery but I am not an MySQL ninja, I can select * from strangedb.table; OK and the content is listed, what would be the next steps to dump the data and reimport to a new table?

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

This turned out to be corruption like the error mentioned. I was able to view the contents of the table via the MySQL utility and did the following:

  1. Created a new table from the existing schema of the corrupt table
  2. Copy data from old to new

Commands below:

USE strangedb;
CREATE TABLE table_new LIKE table;
INSERT table_new SELECT * FROM table;

Once copied across safely, I could drop the old table DROP TABLE table; and rename the lifeboat table RENAME TABLE table_new TO table;

In my case it was OK as I didn't have any association constraints, I would imagine for more complex scenarios those would need to be recreated.

share|improve this answer
add comment

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.