Announcement

Announcement Module
Collapse
No announcement yet.

after ibdata1 deleteion can't get innodb tables working. help!

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

  • after ibdata1 deleteion can't get innodb tables working. help!

    Hi,
    I have a big problem,
    I have created a db with these opts in my.cnf:
    innodb_buffer_pool_size = 1480MB
    innodb_log_file_size = 300M
    innodb_log_buffer_size = 4M
    innodb_thread_concurrency = 8
    innodb_flush_method = O_DIRECT
    innodb_file_per_table = 1

    after that for testing purposes I've changed the log_file_size and deleted the ib* files in main mysql dir so they can be recreated upon size change...
    but forgot to make mysqldump.
    now I have all the innodb tables saying that :

    Cannot find table pl/vots from the internal data dictionary of InnoDB though the .frm file for the table exists.

    is there any way to rebuild the indexes or dump out the data directly from the bin files, I need the data, so I can reinsert it in a new db if fixing this one is not possible...

    please help.

  • #2
    Your problem is that you deleted ibdata1 that contains information about the database instance.
    You should only delete ib_logfile* instead.


    But my suggestion (although I'm not entirely sure that it will work):

    1.
    Move away your entire data directory.

    2.
    Start your MySQL with a data directory from scratch so that new ibdata and logfiles etc will be created and that no old .frm or table files are left.

    3.
    Create tables with identical names as the tables that you can access right now.

    4.
    Stop MySQL

    5.
    Copy in .frm and the table data files into the data directory and overwrite the empty ones you just created.

    6.
    Start MySQL and check if you can access the tables now.


    I think it could work but I haven't tested it myself.

    Comment


    • #3
      Thanks a lot for the answer,
      now I get:

      Mar 5 16:19:22 temp mysqld[6663]: 080305 16:19:22 InnoDB: Error: tablespace id in file './kn_pl/act.ibd' is 51, but in the InnoDB
      Mar 5 16:19:22 templar mysqld[6663]: InnoDB: data dictionary it is 86.

      if I don't put:

      innodb_force_recovery = 4

      or higher than 4 the mysql will give error when trying use kn_pl;
      that's because of names caching (autocompletition) of course but anywayz the tabels are not accessible at all then,
      if this option is set, then the tables status says not found for the tables...

      maybe it won't work.
      any other ideas?

      Comment


      • #4
        New try for you:

        # 1
        Test this on one of the tables:

        ALTER TABLE tbl_name IMPORT TABLESPACE;

        And see if you can access it then.



        # 2
        Otherwise take the long route by:

        1. Remove the table .ibd file again by:
        ALTER TABLE tbl_name DISCARD TABLESPACE;

        2.
        Copy back the correct .ibd file one more time.

        3.
        Run the ALTER TABLE statement:
        ALTER TABLE tbl_name IMPORT TABLESPACE;

        Comment


        • #5
          mysql> alter table titles import tablespace;
          ERROR 1030 (HY000): Got error -1 from storage engine

          after the discard sql and copy the new .ibd file...

          Comment


          • #6
            I am terribly sorry, but I don't have any more good ideas for you.

            I hope you manage to solve it.
            And please post the solution here so that we all can learn.

            Good Luck!

            Comment

            Working...
            X