Tables Have Gone "missing"

[stakemaster]

Hi,

I was running an “Export Database As SQL Statements…” which was interrupted by a power failure. When I restarted SQLyog my databases & tables had disappeared. The MYI/MYD/FRM files are still in the data folder but it looks like SQLyog is no longer looking in that folder. How do I find out where SQLyog is looking for my tables? Why would they suddenly go “missing” to SQLyog? I am using WAMPSERVER 2.1.

Thanks in advance.

[peterlaursen]

SQLyog is not looking in any folders itself. It simply does not access the file system. It is the MySQL server that looks in folders. SQLyog executes SQL statements and nothing else.

It looks like what happened here is (for same reason) because of a server crash due to the power failure – not because of SQLyog crash. It is a problem with MyISAM tables that they are not crash-safe. But it sounds incredible and very bad that all tables have gone (if it is the case).

When connecting please execute (from SQLyog editor ór any client)

SHOW DATABASES;

and

SHOW TABLES FROM somedatabase;

.. do you get anything returned at all? Also XAMPP has phpAmmin I think. Does phpAdmin see any databases and tables?

If not you will have to check if the 'myisamchk' program can help you (http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html). Before experimenting stop the server and make a copy of the data folder. 'myisamcheck' is in the server /bin folder and is started from command line.

Read

1)

http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html

Stage 1: Checking your tables

Run myisamchk *.MYI or myisamchk -e *.MYI if you have more time. Use the -s (silent) option to suppress unnecessary information.

If the mysqld server is stopped, you should use the –update-state option to tell myisamchk to mark the table as “checked.”

You have to repair only those tables for which myisamchk announces an error. For such tables, proceed to Stage 2.

If you get unexpected errors when checking (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

and 2)

http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html

If you are using Windows Vista or Win7 there may be a recent 'shadow volume copy' of the data folder. Right click it from Windows Explorer, select 'previous versions' tab and see how old the latest restore point is. Maybe you can live with losing data from since last restore point.

[stakemaster]

Thanks for the excellent response, Peter, SQLyog is now showing the missing tables. For some reason, I don't know if you might know why, but the MySQL Server appeared to be using a different my.ini file that didn't have the datadir variable set. I defined the datadir in the my.ini file that WAMPSERVER opens from it's taskbar menu (C:wampbinmysqlmysql5.5.8my.ini) and the tables are now back. The spooky thing is that the datadir variable has never been defined in this my.ini file but then I found the correct definition for it in another my.ini in the folder C:program FilesMySQLMySQL Server 5.5 so it looks like the MySQL Server was using the my.ini from another installation of MySQL. Could this be possible? Could the power failure have forced a recovery that found the my.ini that it should have been using (the WAMPSERVER version, that is)?

[peterlaursen]

1) “Server was using the my.ini from another installation of MySQL. Could this be possible?”

Please read: http://dev.mysql.com…tion-files.html . If you have one in C: or C:Windows for instance the answer is YES! It is very confusing and (in my opinion) reflects a 10 year old kind of Unix thinking that 'there can only be one daemon of a kind on a computer'.

2) I have no idea why it suddenly started using another my.ini after the crash/power failure.

But good that your data seem to have survived this. It was worrying me actually!

[Author]

Posts