SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    have you tried the RENAME statement?
    Code:
    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/
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    have you tried the RENAME statement?
    Code:
    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

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you didn't happen to do a restore recently on this server, did you?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    lebanon real estate www.REmonitor.com beirut apartment for sale

  6. #6
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  8. #8
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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:

    Code:
    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.

    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.

  9. #9
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •