We have a MySQL database that was restarted a few hours ago
When it restarted, the views were missing. The tables all seem intact, and a check on the mysql tables seems all good.
Any ideas what could have happened?
Mysql – Views missing on MySQL database
MySQL
Related Solutions
MyISAM does not support transactions, commits, or rollbacks. You have to switch to the InnoDB.
InnoDB Architecture
InnoDB has mechanisms, log files, and associative memory structures for rollback, commit, transaction isolation, and MVCC.
MyISAM has no infrastructure like this. Each MyISAM table is its own entity.
Here is a great reason to switch to InnoDB: MyISAM does not cache data. It lets the OS do that. That's a scary thought for MySQL/Linux, and horrific nightmare for MySQL/Windows. I wrote about this difference in the past : What are the main differences between InnoDB and MyISAM?
Thus, in the event of a crash, not only do you lose any data the OS did not flush to disk, but you also may have to run CHECK TABLE/REPAIR TABLE to fix open file counts in the headers of the MyISAM tables affected.
In your case, you are playing Russian Roulette relying on data from a MyISAM tables after a server crash or a mysqld crash.
Please see my past posts on using and tuning InnoDB
- When to switch from MyISAM to InnoDB?
- Which first: upgrade mysql version or convert storage engine? (Code Provided to Convert to InnoDB)
Give it a Try !!!
UPDATE 2014-01-03 10:29 EST
In your comment, you asked
is it possible that data that was inserted 2-3 minutes before crash also lost in MyISAM?
Absolutely !!! I actually saw this happen three ago. I create a MyISAM table in /dev/shm (Shared Memory). I loaded it with a 3GB .MYD and 2GB .MYI. While loading it, I ran ls -l on the table and it kept saying 0 bytes for .MYD and 1024 bytes for .MYI. After 5 min, I killed the mass INSERT. Suddenly, data and index pages started to pour into the table files. Had mysqld actually crashed, those data and index pages would never have made it to the table. In your case, this would especially be true for all open MyISAM tables in a write-heavy database environment.
What You make right - it is backup all files from /var/lib/mysql
all other steps - wrong
Your current error (in PHPMyAdmin with phantom tables) - because You have Your database folder with .frm files for table structure, and empty (new) ibdata.
What You can do now - revert all back (restore files in it original place) Then You can: - check error log file, for error message - start change innodb_force_recovery from 1 to 6
Best Answer
A backup was restored to the server sans views. Simple really.