MariaDB crash: Table doesn't exist in engine && can't recover tables

Question

I'm running:

MariaDB 10.0.5
Ubuntu 12.04 LTS
Both up to the latest patch level as per last Friday

I've gotten into quite a pickle with a previous issue:

http://stackoverflow.com/questions/19910996/mysql-connect-headers-and-client-library-minor-version-mismatch-library10000

This has resulted in massive corruption because I copied & removed my ibdata1, ib_logfile0 and ib_logfile1 in order to solve a symptom of the above problem. This issue began, I think, when I next started the mariadb service. When I try to select one of the affected tables or run a mysqldump I get the following:

mysqldump -u backupusr -p'somepass' --all-databases > dump.sql

mysqldump: Got error: 1932: "Table 'some_db.dw_commentmeta' doesn't exist in engine" when using LOCK TABLES

Luckily (EXTREMELY luckily) the data of my big production client website is still intact and the site is online. Though there is at least one corrupt table in that DB too. Now to recover all of my other websites' databases.

I've still got the original ibdata1, ib_logfile0 and ib_logfile1 files. When I move these files back to /var/lib/mysql/ the mariaDB service fails to start.

One of the things I've tried is forcing an InnoDB recovery as per:

http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

But that has no effect. After that I tried force-using the Mysql upgrade tool to see if it could repair the tables:

mysql_upgrade --password --force
FATAL ERROR: Upgrade failed

So that doesn't tell me anything. Next I've used the REPAIR TABLE & ALTER TABLE ENGINE commands:

http://dev.mysql.com/doc/refman/5.0/en/rebuilding-tables.html

Alter engine:

MariaDB [sictnl_db]> ALTER TABLE si_users ENGINE = MyISAM;
ERROR 1932 (42S02): Table 'sictnl_db.si_users' doesn't exist in engine

repair table:

MariaDB [sictnl_db]> REPAIR TABLE si_users;
+--------------------+--------+----------+----------------------------------------------------+
| Table              | Op     | Msg_type | Msg_text                                           |
+--------------------+--------+----------+----------------------------------------------------+
| sictnl_db.si_users | repair | Error    | Table 'sictnl_db.si_users' doesn't exist in engine |
| sictnl_db.si_users | repair | status   | Operation failed                                   |
+--------------------+--------+----------+----------------------------------------------------+
2 rows in set (0.00 sec)

So this all boils down to getting the table information back into the engine's tables. The documentation at https://mariadb.com/kb/en/mariadb-storage-engines/ states that there are two storage engines either of which I'm using per default:

Aria
MyISAM

When performaning the SHOW CREATE TABLE function on one of the uncorrupted tables I get the following:

ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |

So MyISAM it is, googling 'mysql repair myisam' leads me to the following site:

Note: Would Aria have protected me against this issue?

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

This yields me the myisamchk command, which needs to be pointed to the local storage of the tables like so:

myisamchk /var/lib/mysql/sictnl_db/*


myisamchk: error: '/var/lib/mysql/sictnl_db/db.opt' is not a MyISAM-table
---------
myisamchk: error: '/var/lib/mysql/sictnl_db/si_commentmeta.frm' is not a MyISAM-table
---------
myisamchk: error: '/var/lib/mysql/sictnl_db/si_commentmeta.ibd' is not a MyISAM-table

So the tool seems to be missing the .MYI files, but these files don't exist in the shell.

I'm posting this now to see whether you've got any helpful comment, Ill continue my analysis in the mean time and update the post should I get any further.

Thanks in advance!

Answer 1

In the end I wasn't able to recover the above server. I tried disabling one site at a time to prevent DB-access of the corrupted tables. In the end one DB contained the issue and by disabling the site mariadb didn't crash anymore.

To be sure I extracted the database data and started over on a new server. That was about 5 hours of work but after that I'm confident in my setup once again.

I've started work on restoring an old backup of the corrupted data, I've also switched back from MariaDB to Mysql as I suspect MariaDB of being the culprit.

So backups saved my day here...