1

For the third time in the past 2-3 weeks I've gotten the following message during an ALTER statement:

Error 2013: Lost connection to MySQL server during query SQL Statement:
ALTER TABLE `my_database`.`my_table` ...

In this case I was changing AUTO_INCREMENT to 55, that's it. This was necessary because we store JSON strings in LONGTEXT fields with references to database IDs, but during the migration script it did not insert all of the rows correctly so the IDs were mismatched from what already exists out there. AUTO_INCREMENT had to be fixed for solely a functional purpose.

The MySQL Workbench version I'm using is 6.3.3.0 build 592.

When trying to refresh my schemata, I get:

Error Code: 2013 Lost connection to MySQL server during query

When I try to recreate the table, I get this message:

Error Code: 1005. Can't create table 'my_database.my_table' (errno: -1)

It seems that the ALTER deleted the old table definition before creating the new table definition.

How can I fix this?

The database is running on AWS RDS, so I can't change anything except what Amazon allows me to. I cannot recreate any of the previous three tables by name in my schema anymore, but only for InnoDB. If I were to try creating this table in MyISAM, it would work.

I'm using Propel for the migration script, so I did explicitly specify the id column by suppressing the autoIncrement field of the XML table tags. I didn't want to dump my tables and rerun the migration script, so I fixed the IDs manually and then attempted to change the AUTO_INCREMENT to what it should be, when this all happened.

3
  • Move all tables to a temporary database
  • Drop the original database (it's empty by now)
  • Move the tables from the temporary database to the original one.

That should fix the InnoDB dictionary and you'll be able to create the table again.

1

The statement:

DROP TABLESPACE `data_feed_param`;

Would now fix my problem without having to RENAME my tables on MySQL 5.6, if my user has the CREATE TABLESPACE privilege.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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