kbartlett
asked on
MySQL database restored from backup, "Table doesn't exist"
Hi there,
We had a server crash that contained an in-house Wiki server. The OS is Windows 2003 Server with SP2.
I had installed MySQL (5.0) to be used by Wiki as it's database.
The back up we had for the server was only file based, meaning that I did not have a MySQL run database backup, only the windows file level data basked up.
I retored the backup to the new server and repected the original folder structure. Now, when I try to look at the MySQL database that Wiki used (wikidb) using "MySQL Control Centre", it is able to see that there is a datbase called Wikidb and that there are table in it, but when I try to look at any of the table I get a "table doesn't exist error.
I suspect that my database naiveté led me to believe that I could just copy the files over and the database would work,,,,,,,so what am I doing wrong?
Thanks for any help,
KB
We had a server crash that contained an in-house Wiki server. The OS is Windows 2003 Server with SP2.
I had installed MySQL (5.0) to be used by Wiki as it's database.
The back up we had for the server was only file based, meaning that I did not have a MySQL run database backup, only the windows file level data basked up.
I retored the backup to the new server and repected the original folder structure. Now, when I try to look at the MySQL database that Wiki used (wikidb) using "MySQL Control Centre", it is able to see that there is a datbase called Wikidb and that there are table in it, but when I try to look at any of the table I get a "table doesn't exist error.
I suspect that my database naiveté led me to believe that I could just copy the files over and the database would work,,,,,,,so what am I doing wrong?
Thanks for any help,
KB
Rurne
What is the result of running `show table status where name = 'myWikiTable'` (using corrupted table's name in place of 'myWikiTable')?
ASKER
I'm using MySQL Control Centre to run the query. When I entered "show table status where name = 'wikidb.user', I was shown all the columns in the table. There were no fields below the column headings, just the top row with column titles.
Interesting. Can you confirm that the user.frm file exists in the wikidb folder which you copied over?
ASKER
Yes, it's there.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, but both of the links refer to .ibd files that I cannot find. I searched the entire server with no luck.
Okay, so you're set up for domain-wide InnoDb data (meaning all tablespace info for all your InnoDb tables would be saved in ibdata1). Additionally, you will need all your ib_logfile* files to rebuild the transaction log. Barring that, you will have to restore from backup.
ASKER
Thanks.
So I found the ibdata1 file and 2 versions of the ib_logfile* file: ib_logfile) and ib_logfile1. :-0
What do I do now? Please remember I am but a humble MCSE network administrator and not a DBA so please go easy on the jargon. :-)
Thanks very much,
KB
So I found the ibdata1 file and 2 versions of the ib_logfile* file: ib_logfile) and ib_logfile1. :-0
What do I do now? Please remember I am but a humble MCSE network administrator and not a DBA so please go easy on the jargon. :-)
Thanks very much,
KB
No worries.
A simple way of forcing a rebuild, if all the mentioned files are in the right place, would be to run `ALTER TABLE wikidb.user ENGINE=InnoDb`. Of course, make sure you have a backup of the data files before trying this, as this is not guaranteed to rebuild the namespace.
When you restart the server, you may see a file ending in .err in the data directory. If you see this, please post it to ee-stuff.com and provide a link so we can further troubleshoot exactly what is failing on the import.
A simple way of forcing a rebuild, if all the mentioned files are in the right place, would be to run `ALTER TABLE wikidb.user ENGINE=InnoDb`. Of course, make sure you have a backup of the data files before trying this, as this is not guaranteed to rebuild the namespace.
When you restart the server, you may see a file ending in .err in the data directory. If you see this, please post it to ee-stuff.com and provide a link so we can further troubleshoot exactly what is failing on the import.
ASKER
This looks promising.
I'm guessing that I'll need to do this for all the tables in the DB?
I'm guessing that I'll need to do this for all the tables in the DB?
No, just for any table that is kept in .frm. Anything saved in a .MYI file is a MyISAM table, which does not keep the transactional logs that we're trying to relink and rebuild. For now, let's just try the user table to see how this particular rebuild works.
ASKER
So,,, I tried the "alter table" route and got "table wikidb.user does not exist"
Hmmmm. Do you a copy of the .err file that I mentioned before?
ASKER
Here is my .err file
https://filedb.experts-exchange.com/incoming/ee-stuff/4157-zeta.zip
Please let me know if you see anything that I can do.
Thanks,
KB
https://filedb.experts-exchange.com/incoming/ee-stuff/4157-zeta.zip
Please let me know if you see anything that I can do.
Thanks,
KB
It's looking like your ibdata file does not have the necessary data, or is out of synch, since all of your InnoDb tables are throwing a "Cannot find wikidb.x from the internal data dictionary" error.
Check that the [InnoDb] section in my.cnf does not have a "chroot=" specified, or if it does, that it points to your actual data directory. Based on:
http://www.mysql.org/doc/refman/4.1/en/innodb-troubleshooting-datadict.html
It would seem that there is no data in ibdata for your actual tables, and that the .frm files are useless. You may need to take a look into attempting to extract, from the ibdata files, the actual SQL to rebuild the files:
http://www.innodb.com/support/documentation/innodb-hot-backup-manual#options
You'd need to take whatever backup you have of the crashed server and create 2 .cnf files:
my-backup.cnf
the directories point to the folder which contains your backup files.
my-new-sync.cnf
the directories point to the new database with which you're trying to synch.
With the `ibbackup` utility, you can then:
ibbackup my-backup.cnf my-new-sync.cnf
And, if recoverable, should be able to pull the data from the old files into the new database.
Check that the [InnoDb] section in my.cnf does not have a "chroot=" specified, or if it does, that it points to your actual data directory. Based on:
http://www.mysql.org/doc/refman/4.1/en/innodb-troubleshooting-datadict.html
It would seem that there is no data in ibdata for your actual tables, and that the .frm files are useless. You may need to take a look into attempting to extract, from the ibdata files, the actual SQL to rebuild the files:
http://www.innodb.com/support/documentation/innodb-hot-backup-manual#options
You'd need to take whatever backup you have of the crashed server and create 2 .cnf files:
my-backup.cnf
the directories point to the folder which contains your backup files.
my-new-sync.cnf
the directories point to the new database with which you're trying to synch.
With the `ibbackup` utility, you can then:
ibbackup my-backup.cnf my-new-sync.cnf
And, if recoverable, should be able to pull the data from the old files into the new database.
ASKER
Sorry, you've lost me. where would I find the my.cnf file?
From http://dev.mysql.com/doc/refman/5.0/en/option-files.html:
WINDIR\my.ini
C:\my.cnf
INSTALLDIR\my.ini
Those should be your actual defaults. What you actually need to do is create two new my.cnf files to feed to the ibbackup utility. One of them (my-backup.cnf from my example) will point to your old ibdata files that have the data you want, and the other (my-new-sync.cnf) will point to another folder where you want to try to rebuild those files.
WINDIR\my.ini
C:\my.cnf
INSTALLDIR\my.ini
Those should be your actual defaults. What you actually need to do is create two new my.cnf files to feed to the ibbackup utility. One of them (my-backup.cnf from my example) will point to your old ibdata files that have the data you want, and the other (my-new-sync.cnf) will point to another folder where you want to try to rebuild those files.