Error creating a innodb table: errno 121

[Andrew-J2000]

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.

ALTER TABLE trade_messages2 RENAME TO trade_messages;
-- ERROR 1025 (HY000):
-- Error on rename of './trade_engine_log/trade_messages2' to './trade_engine_log/trade_messages' (errno: 121).

Has anyone come across this?

PS. I also run flush tables, reset master and restarted mysql, and i'm still scratching my head why I cannot create a table with the same name?

Thanks

Andrew

[r937]

have you tried the RENAME statement?

RENAME TABLE trade_messages2 TO trade_messages

actually, i bet it's propbably a foreign key that's causing the problem

see http://www.xaprb.com/blog/2006/08/22...025-explained/

[Andrew-J2000]

have you tried the RENAME statement?

RENAME TABLE trade_messages2 TO trade_messages

actually, i bet it's propbably a foreign key that's causing the problem

see http://www.xaprb.com/blog/2006/08/22...025-explained/

Hi Rudy,

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?

Thanks

Andrew

[longneck]

you didn't happen to do a restore recently on this server, did you?

[haidoura]

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

[Andrew-J2000]

Hi,

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.


Its very frustrating as I cannot think what else to check.

Thanks

Andrew

[longneck]

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?

[Andrew-J2000]

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:

CREATE TABLE `trade_messages` (
  `tmid` bigint(20) NOT NULL auto_increment,
  `appQueryCompletionTimeStamp` datetime default NULL,
  PRIMARY KEY  (`tmid`)
) ENGINE=InnoDB;

Heres a few further tests:

mysql> CREATE TABLE `trade_messages2` (
    ->   `tmid` bigint(20) NOT NULL auto_increment,
    ->   `appQueryCompletionTimeStamp` datetime default NULL,
    ->   PRIMARY KEY  (`tmid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)


mysql> ALTER TABLE trade_messages2 RENAME TO trade_messages;
ERROR 1025 (HY000): Error on rename of './trade_engine_log/trade_messages2' to './trade_engine_log/trade_messages' (errno: 121)
mysql>  SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE trade_messages2 RENAME TO trade_messages;
ERROR 1025 (HY000): Error on rename of './trade_engine_log/trade_messages2' to './trade_engine_log/trade_messages' (errno: 121)
mysql> alter table trade_messages2 disable keys;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE trade_messages;
ERROR 1051 (42S02): Unknown table 'trade_messages'

mysql> CREATE TABLE `trade_messages` (
    ->   `tmid` bigint(20) NOT NULL auto_increment,
    ->   `appQueryCompletionTimeStamp` datetime default NULL,
    ->   PRIMARY KEY  (`tmid`)
    -> ) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table './trade_engine_log/trade_messages.frm' (errno: 121)

I just found the following post:

http://forums.mysql.com/read.php?22,...6181#msg-76181

"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.

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.

[Andrew-J2000]

This is now resolved after creating the same table in another DB, then copying it to the database that I was having trouble with altering permissions and dropping & then re-creating the table.

Thanks for all your help, really appreciate it.