cannot restore 3.23.58 db to MySQL 4.0.22-debug

mailing.database.mysql - MySQL database issues discussed in this best MySQL group in USENET 

Subject Author Date cannot restore 3.23.58 db to MySQL 4.0.22-debug maddunr 11-29-2005  Re: cannot restore 3.23.58 db to MySQL 4.0.22-debu... Bill Karwin 11-29-2005

Posted by maddunr on November 29, 2005, 3:42 pm Please log in for more thread options Hello All, The other day my server running MySQL 3.23.58 got hacked into and we are migrating to another one. I backed up my databases using two methods a) Mysqldump and b) copy the DATA folder on my server. Now, I am trying to restore these databases on my local machine (running MySQL 4.0.22-debug and Apache 2.0 on IIS 6.0). I have tried importing the SQL files (mysqldump) and copying the .frm, .myd and .myi files into the existing DATA folder on my local machine. In both cases, the database is created and I can see the table structure. But when I click on most of the tables, I get the following error: show/hide quoted text #1016 - Can't open file: '<table_name>.InnoDB'. (errno: 1) Can anyone pls throw some light on this issue? Any suggestion will be greatly appreciated. - V Posted by Bill Karwin on November 29, 2005, 4:13 pm Please log in for more thread options maddunr@yahoo.com wrote: show/hide quoted text > I have tried > importing the SQL files (mysqldump) and copying the .frm, .myd and .myi > files into the existing DATA folder on my local machine. I don't understand -- you did both? One or the other should be the solution, but not both. Or did you mean that you tried one method of restoratin, found it didn't work, and then tried the other with the same result? show/hide quoted text > In both cases, the database is created and I can see the table > structure. But when I click on most of the tables, I get the following > error: > > #1016 - Can't open file: '<table_name>.InnoDB'. (errno: 1) show/hide quoted text > Can anyone pls throw some light on this issue? Any suggestion will be > greatly appreciated. Similar issues discussed MySQL forums seem to state that this is caused by mixing MyISAM table files (.frm, .myd, .myi) and InnoDB tables. You have .frm files for which there is no entry in the InnoDB tablespace file. I'm guessing that when you restored your mysqldump files, it created the database using InnoDB tables. Then you put your .frm files in place, which makes InnoDB think that you want to store the tables in one file per table, instead of the default configuration of all tables in one file. Thus it became confused. You can probably remove the .FRM, .MYD, and .MYI files (from the data directory, not your originals!) and then restart MySQL. The data is show/hide quoted text probably all in <datadirectory>/ibdata1 at this time. See also http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html Regards, Bill K. Similar Threads Posted mysql backup and restore for 4.1.x April 16, 2006, 12:22 am Database Restore / MySQL 4.1 -- InnoDB May 1, 2006, 12:11 pm ERROR:Using the mysql client on freebsd to restore a mysqldump?? August 15, 2005, 2:03 am Mysql Restore fails with strange error message June 10, 2006, 4:41 pm Restore db from 4.1.12 to 3.23 July 13, 2005, 11:21 am Restore Your Account August 11, 2005, 1:45 am slow restore March 7, 2006, 12:41 pm mysqldump restore db syntax April 29, 2006, 4:38 pm Quality of Backup and Restore June 2, 2006, 3:38 am backup and restore of var lib mysq February 12, 2009, 2:12 pm