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

MySQL & MariaDB Other MySQL® Questions
1

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

Move away your entire data directory.

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.

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

Stop MySQL

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

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

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

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?

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;

Copy back the correct .ibd file one more time.

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

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…

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!