3

I am getting following error in err file.

110803  6:51:26  InnoDB: Error: table `ims`.`temp_discoveryjobdetails` 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.1/en/innodb-troubleshooting.html

And when I do the same, like copying the frm file from other database to here and drop the table, i am getting following error,

InnoDB: Error: trying to load index PRIMARY for table ims/temp_discoveryjobdetails
InnoDB: but the index tree has been freed!
110803  6:50:26  InnoDB: Error: table `ims`.`temp_discoveryjobdetails` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

Please any one help me out of this. Also can any one tell me why this error is coming.

EDIT:

The issue is occurring only when disk size is full and when we use Truncate table. Also this is occurring only in 5.1 version but not in 5.0 version.

user9517
  • 115,471
  • 20
  • 215
  • 297
Phanindra
  • 203
  • 1
  • 4
  • 11
  • Might this be related to bug #59048 for version 5.5? "There are cases that we could leave an index's metadata (rootpage info) in a corrupted situation with truncate table and/or create index if we are running out of space. index->page could left as FIL_NULL, and InnoDB refuse to load any table/index with index->page as FIL_NULL." [http://bugs.mysql.com/bug.php?id=59048] A bug fix has been released and is available as commit 130857 [http://lists.mysql.com/commits/130857] – John P Mar 08 '12 at 15:39

1 Answers1

1

Tried this? http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html

A specific issue with tables is that the MySQL server keeps data dictionary information in .frm files it stores in the database directories, whereas InnoDB also stores the information into its own data dictionary inside the tablespace files. If you move .frm files around, or if the server crashes in the middle of a data dictionary operation, the locations of the .frm files may end up out of synchrony with the locations recorded in the InnoDB internal data dictionary.

mikebabcock
  • 420
  • 4
  • 12
duenni
  • 2,959
  • 1
  • 23
  • 38
  • 6
    Don't post **only** links, there's no guarantee that they'll be there in a week, month, year, whatever. Post your answer and then provide the link as reference. – MDMarra Aug 03 '11 at 14:27
  • Yeah, I had tried this as well. Even then I am getting same error. – Phanindra Aug 03 '11 at 14:42