Database corruption with MariaDB : Table doesn't exist in engine

Question

I'm in an environement setup, running OSX with MariaDB 10.0.12-MariaDB Homebrew

I've screwed up the installation so I did completely removed MySQL and MariaDB from my setup and started again.

After finishing installing MariaDB, I've reimported my databases (innoDB) via a DB Dump from the production server. It worked fine. After a reboot, the day after, I can no longer access to the databases :

Table 'my.table' doesn't exist in engine

What's causing this and what's the solution ? I do see the structure of my database, but when I try to access it, it gives me this error message.

I did try mysql-upgrade --force and deleting rm ib_logfile1 ib_logfile0

The data loss is not a problem here, the problem is that I can't spend 30 minutes on re-installing each database each time I do a reboot.

Here's some logs :

140730  9:24:13 [Note] Server socket created on IP: '127.0.0.1'. 140730  9:24:14 [Note] Event Scheduler: Loaded 0 events 140730  9:24:14 [Warning] InnoDB: Cannot open table mysql/gtid_slave_pos from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 140730  9:24:14 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine 140730  9:24:14 [Note] /usr/local/Cellar/mariadb/10.0.12/bin/mysqld: ready for connections. Version: '10.0.12-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew 140730 16:26:28 [Warning] InnoDB: Cannot open table db/site from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 

Answer 1

Something has deleted your ibdata1 file where InnoDB keeps the dictionary. Definitely it's not MySQL who does

Answer 2

Ok folks, I ran into this problem this weekend when my OpenStack environment crashed. Another post about that coming soon on how to recover.

I found a solution that worked for me with a SQL Server instance running under the Ver 15.1 Distrib 10.1.21-MariaDB with Fedora 25 Server as the host. Do not listen to all the other posts that say your database is corrupted if you completely copied your old mariadb-server's /var/lib/mysql directory and the database you are copying is not already corrupted. This process is based on a system where the OS became corrupted but its files were still accessible.

Here are the steps I followed.

1. Make sure that you have completely uninstalled any current versions of SQL only on the NEW server. Also, make sure ALL mysql-server or mariadb-server processes on the NEW AND OLD servers have been halted by running:

   service mysqld stop or service mariadb stop.

2. On the NEW SQL server go into the /var/lib/mysql directory and ensure that there are no files at all in this directory. If there are files in this directory then your process for removing the database server from the new machine did not work and is possibly corrupted. Make sure it completely uninstalled from the new machine.

3. On the OLD SQL server:

   mkdir /OLDMYSQL-DIR cd /OLDMYSQL-DIR tar cvf mysql-olddirectory.tar /var/lib/mysql gzip mysql-olddirectory.tar

4. Make sure you have sshd running on both the OLD and NEW servers. Make sure there is network connectivity between the two servers.

5. On the NEW SQL server:

   mkdir /NEWMYSQL-DIR

6. On the OLD SQL server:

   cd /OLDMYSQL-DIR scp mysql-olddirectory.tar.gz @:/NEWMYSQL-DIR

7. On the NEW SQL server:

   cd /NEWMYSQL-DIR gunzip mysql-olddirectory.tar.gz OR tar zxvf mysql-olddirectory.tar.gz (if tar zxvf doesn't work) tar xvf mysql-olddirectory.tar.gz

8. You should now have a "mysql" directory file sitting in the NEWMYSQL-DIR. Resist the urge to run a "cp" command alone with no switches. It will not work. Run the following "cp" command and ensure you use the same switches I did.

   cd mysql/ cp -rfp * /var/lib/mysql/

9. Now you should have a copy of all of your old SQL server files on the NEW server with permissions in tact. On the NEW SQL server:

   cd /var/lib/mysql/

VERY IMPORTANT STEP. DO NOT SKIP

> rm -rfp ib_logfile* 

10. Now install mariadb-server or mysql-server on the NEW SQL server. If you already have it installed and/or running then you have not followed the directions and these steps will fail.

FOR MARIADB-SERVER and DNF:

> dnf install mariadb-server > service mariadb restart 

FOR MYSQL-SERVER and YUM:

> yum install mysql-server > service mysqld restart