When I tried to import a SQL file to MySQL database I got this error,
ERROR 1005 (HY000) at line 26: Can’t create table ‘mroltp1.delivery_curve’ (errno: -1)
This table is an innodb table. I removed the table file and re-import, the error continues. I also dropped the database and re-created it, that doesn’t work either. Then how can I resolved this issue finally?
Our case is that the table was ever existed, then it was deleted manually (not by “drop table” or “drop database”), which makes InnoDB data dictionary inconsequent. We can find log like this,
121130 4:45:38 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './mroltp1/delivery_curve.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a temporary table #sql..., InnoDB: and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. 121130 4:45:38 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them.
The solution is, stop mysql database, remove the data file (delivery_curve.* in our case) or database (mroltp1/), then start MySQL, drop that database, recreate it again.
I’m wondering rename the table should work as well, if that table was said not existed, we can create a simpler one first.