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(...)
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: http://bugs.mysql.com/bug.php?id=30225