MySQL Performance: MySQL => after ibdata1 deleteion can't get innodb tables working. help!

Members

Help

Home

Home » Performance » MySQL » after ibdata1 deleteion can't get innodb tables working. help!

Show: Today's Messages :: Show Polls :: Message Navigator

after ibdata1 deleteion can't get innodb tables working. help! [message #2686]

Tue, 04 March 2008 05:11

valqk
Messages: 3
Registered: March 2008

Junior Member

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.

Report message to a moderator

Re: after ibdata1 deleteion can't get innodb tables working. help! [message #2693 is a reply to message #2686 ]

Wed, 05 March 2008 04:59

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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.

Report message to a moderator

Re: after ibdata1 deleteion can't get innodb tables working. help! [message #2694 is a reply to message #2686 ]

Wed, 05 March 2008 09:38

valqk
Messages: 3
Registered: March 2008

Junior Member

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?

[Updated on: Wed, 05 March 2008 09:40]

Report message to a moderator

Re: after ibdata1 deleteion can't get innodb tables working. help! [message #2695 is a reply to message #2686 ]

Wed, 05 March 2008 13:07

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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;

Report message to a moderator

Re: after ibdata1 deleteion can't get innodb tables working. help! [message #2699 is a reply to message #2686 ]

Thu, 06 March 2008 09:18

valqk
Messages: 3
Registered: March 2008

Junior Member

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

Report message to a moderator

Re: after ibdata1 deleteion can't get innodb tables working. help! [message #2709 is a reply to message #2686 ]

Sun, 09 March 2008 09:59

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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!

Report message to a moderator

Previous Topic:	Very slow query on multi-million row table
Next Topic:	Query Routinely Crashing Server

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 7 09:20:38 EST 2009

Total time taken to generate the page: 0.11428 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.