The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Error creating a innodb table: errno 121

Hi,

I dropped a table earlier today, and I cannot create an innodb table with the same name, I can do this with a MyISAM table but not innodb. It currently throws the errno 121 when trying to rename or create it as innodb, however if I rename the table to something else and create it with and engine type of innodb it this works.

I had already tried the above as it was listed in the error with the rename. The really strange thing is that there are no enforced foreign keys. It has a few indexes and thats about it and I tried a basic 2 column table schema with the InnoDB engine and it fails with the same error.

I have also since tried repairing the non-existent table and it obviously came up stating it could not find the table. I have had a quick look through the mysql and information_schema to see if there was anything stray that may have not been removed from the drop table statement and I can find nothing from quick observation.

Thanks for your help so far, I'm really scratching my head on this one and cannot seem to find an answer. Anything you would try/check next?

the problem with innoDb that u cant make any action in its structure without taking in consideration the relation ships that u have linked it in.
for example: if u exported an innodb tables... those tables cannot be imported due to the foreignkeys relation between the tables.
so u have to import the tables structure first and make sure that inside the memory there is no forignkeys relations still in there

I haven't done a restore, I simply dropped the table, out of curiosity why would a restore make a difference exactly?
Also there are no foreign keys referencing that table I checked the information_schema and I cannot find any references to table in question.

you can't restore an innodb table by just restoring the individual file for that table. inno doesn't work that way. i see this error frequently when someone tries to restore their table in this fashion.

so can you detail what you're done so far? you asked about renaming the table, but then you say you dropped the table. which is it?

I simply dropped the table and then tried to recreate it, from that point on I have not been able to create a new InnoDB table with the same name in that database.
I have since created the table under a different name and tried renaming it as well as simplifying the table schema to the following:

"errno 121 means a duplicate key error. Probably the table already exists in the InnoDB internal data dictionary, though the .frm file for the table has been deleted. This is the most common reason for getting errno 121 in table creation. Another possible reason is a name conflict in a foreign key constraint name. Constraint names must be unique in a database, like table names are. "

Looking at some of the errors in my log the error is exactly that. So the DROP TABLE must have failed to completely remove its internal cache.

Code:

080501 0:29:51 InnoDB: Error: table `trade_engine_log/trade_messages` already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html

I'm also looking through "http://www.innodb.com/support/tips/fic-recovery/" this atm.