Shawn Shawn - 2 years ago 97
SQL Question

Mysql 'Got error -1 from storage engine' error

I have a myism table 'test' which holds some out-dated data, now I want to recreate the table, all columns the same except that I changed the storage from myism to innodb. The dumped sql I used to recreate the table is like:

drop table test;
create table test ( ... )

insert into test(...) values(...)

that's where I got the error "Got Error -1 from storage engine", I have googled around, most of the results focus on corrupted innodb tables. While for my case I don't think it's broken, it's just something I missed at the drop and create statements.

Another thing is that is after a executed the above sql, all that's left for table test is a file named file.frm, I guess innodb table needs some other stuff to run on but not sure what.

How can I fix this problem? And I probably need to do more tasks of this kind, what's the correct procedure to drop myism table and recreate them as innodb ones?


Answer Source

OK. I found the solution. The issue was caused by innodb_force_recovery parameter in my.cnf, that was set to 4.

To solve the problem, set to 0 or completely remove this parameter from my.cnf

If you check error log, during query, mysql will write in human readable language that: It won't let you change anything in table until innodb recovery mode is enabled, exactly next message:

InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.

Please refer to:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download