Announcement

Announcement Module
Collapse
No announcement yet.

ERROR Cannot find or open table?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • ERROR Cannot find or open table?

    - MySQL version: 5.5.24

    Due to the following problem:


    mysql> desc reportingdb.v3_zone_date_cpm7k; ERROR 1146 (42S02): Table 'reportingdb.v3_zone_date_cpm7k' doesn't exist


    /var/log/mysqld.log


    120927 16:57:04 [ERROR] Cannot find or open table reportingdb/v3_zone_date_cpm7k#P#pcurrent_2012926 from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? or, the table contains indexes that this version of the engine doesn't support. See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html how you can resolve the problem.


    (I haven't find out the reason yet)

    The table's files are still exist in the datadir:


    -rw-rw---- 1 mysql mysql 8932 Sep 26 16:50 /var/lib/mysql/reportingdb/v3_zone_date_cpm7k.frm -rw-rw---- 1 mysql mysql 84 Sep 26 16:50 /var/lib/mysql/reportingdb/v3_zone_date_cpm7k.par -rw-rw---- 1 mysql mysql 9437184 Sep 13 17:56 /var/lib/mysql/reportingdb/v3_zone_date_cpm7k#P#MERGER_2012828.ibd -rw-rw---- 1 mysql mysql 1048576 Sep 27 15:42 /var/lib/mysql/reportingdb/v3_zone_date_cpm7k#P#MERGER_2012926.ibd


    I'm going to recover this table follow this guide. But at 2c. step, I get the below errors:


    mysql> alter table v3_zone_date_cpm7k_restore discard tablespace; ERROR 1031 (HY000): Table storage engine for 'v3_zone_date_cpm7k_restore' doesn't have this option


    http://bugs.mysql.com/bug.php?id=52422

    What can I do now?

    ---

    UPDATE

    I'm restoring from the backup, what is the right procedure to get rid of this problem?

    What I've tried (on the another server):

    1. DROP TABLE --> still get the "doen't exist"
    2. Stop MySQL
    Move all the table's files to another location
    Copy the backup files to corresponding database
    Start MySQL:


    120927 19:12:07 InnoDB: Error: table 'reportingdb/v3_zone_date_cpm7k#P#MERGER_2012828'InnoDB: in InnoDB data dictionary has tablespace id 741528,InnoDB: but tablespace with that id or name does not exist. HaveInnoDB: you deleted or moved .ibd files?InnoDB: This may also be a table created with CREATE TEMPORARY TABLEInnoDB: whose .ibd and .frm files MySQL automatically removed, but theInnoDB: table still exists in the InnoDB internal data dictionary.InnoDB: Please refer toInnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.htmlInnoDB: for how to resolve the issue.InnoDB: We removed now the InnoDB internal data dictionary entryInnoDB: of table `reportingdb`.`v3_zone_date_cpm7k` /* Partition `MERGER_2012828` */.120927 19:12:07 InnoDB: error: space object of table 'reportingdb/v3_zone_date_cpm7k#P#MERGER_2012926',InnoDB: space id 921829 did not exist in memory. Retrying an open.120927 19:12:07 InnoDB: Error: table `reportingdb`.`v3_zone_date_cpm7k` /* Partition `pcurrent_2012926` */ does not exist in the InnoDB internalInnoDB: data dictionary though MySQL is trying to drop it.InnoDB: Have you copied the .frm file of the table to theInnoDB: MySQL database directory from another database?InnoDB: You can look for further help fromInnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html

  • #2
    I am willing to bet the record for that table missing from the information_schema database.
    SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'reportingdb' AND table_name = 'v3_zone_date_cpm7k';

    I had this happen to me once, when I copied database files from one machine to another, I restored from a dump file instead and all was good.

    Comment


    • #3
      ggreen wrote on Thu, 27 September 2012 08:59
      I am willing to bet the record for that table missing from the information_schema database.
      SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'reportingdb' AND table_name = 'v3_zone_date_cpm7k';
      Obviously:

      Empty set (1.01 sec).


      ggreen wrote on Thu, 27 September 2012 08:59
      I had this happen to me once, when I copied database files from one machine to another, I restored from a dump file instead and all was good.
      How did you restore if tablespace id from the backup is diffrent from the current tablespace id?

      Comment


      • #4
        This might help you:
        http://www.chriscalender.com/?tag=innodb-error-tablespace-id -in-file

        Comment


        • #5
          ggreen wrote on Thu, 27 September 2012 14:39I see that, but the problem is:


          mysql> alter table v3_zone_date_cpm7k_restore discard tablespace; ERROR 1031 (HY000): Table storage engine for 'v3_zone_date_cpm7k_restore' doesn't have this option

          Comment

          Working...
          X