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 it.

UPDATE: I made a tutorial on how to fix the error - https://youtu.be/014KbCYayuE

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

Answer 3

In my case, the error disappeared after I rebooted my OS and restarted MariaDB-server. Strange.

Answer 4

You may try to:

• backup your database folder from C:\xampp\mysql\data (.frm & .ibd files only corresponding your table names)
• reinstall xampp and recopy DB folder at C:\xampp\mysql\data

• modify my.ini add

  [mysqld]
  innodb_file_per_table = on
  

• then open phpmyadmin (or any other DB viewer as Navicat or MYSQL Workbench) and run

  ALTER TABLE tbl_name IMPORT TABLESPACE 
  

  for each table

• Once you can open your tables make a full mysql dump

• delete everything and make a clean install

I know, a lot of work to do that you don't need it.

Answer 5

I've just had this problem with MariaDB/InnoDB and was able to fix it by

• create the required table in the correct database in another MySQL/MariaDB instance
• stop both servers
• copy .ibd, .frm files to original server
• start both servers
• drop problem table on both servers

Answer 6

This one really sucked.

I tried all of the solutions suggested here but the only thing that worked was to

• create a new database
• run ALTER TABLE old_db.{table_name} RENAME new_db.{table_name} on all of the functioning tables
• run DROP old_db
• create old_db again
• run ALTER TABLE new_db.{table_name} RENAME old_db.{table_name} on all the tables in new_db

Once you have done that you can finally just create the table again that you previously had.

Answer 7

This theme required awhile to find results and reasons:

using MaiaDB 5.4. via SuSE-LINUX tumblweed

1. some files in the appointed directory havn't been necessary in any direct relation with mariadb. I.e: I placed some hints, a text-file, some bakup-copys somewhere in the same appointed directory for mysql mariadb and this caused endless error-messages and blocking the server from starting. Mariadb appears to be very sensible and hostile with the presence of other files not beeing database files(comments,backups,experimantal files etc) .

2. using libreoffice as client then there already this generated much problems with the creation and working on a database and caused some crashes.The crashes eventually produced bad tables.

3. May be because of that or may be because of the presence of not yet deleted but unusable tables !! the mysql mariadb server crashed and didn't want to do it' job not even start.

Error message always same : "Table 'some.table' doesn't exist in engine"

But when it started then tables appeared as normal, but it was unpossible to work on them.

So what to do without a more precise Error Message ?? The unusable tables showed up with: "CHECK TABLE" or on the command line of the system with "mysqlcheck " So I deleted by filemanager or on the system-level as root or as allowed user all the questionable files and then problem was solved.

Proposal: the Error Message could be a bit more precisely for example: "corrupted tables" (which can be found by CHECK TABLE, but only if the server is running) or by mysqlcheck even whe server is not running - but here are other disturbing files like hints/bakups a.s.o not visible.

ANYWAY it helped a lot to have backup-copies of the original database-files on a backup volume. This helped to check out and test it again and again until solution was found.

Good luck all - Herbert

Answer 8

I had old MySQL and Centos OS (ver 6 I believe) that was not supported.
One day I couldn't access Plesk.
Using Filezilla, I copied files the database files from var/lib/mysql/databasename/ I then purchased a new server with new Centos 8 OS and MariaDB. In Plesk, I created a new database with the same name as my old one.
Using Filezilla, I then pasted the old database files into the newly created database folder. I could see the data in phpmyadmin but it was giving errors such as the ones described here. I happened to have an old sql backup dump file. I imported the dump file and it overwrote those files. I then pasted the old files back into var/lib/mysql/databasename/ I then had to do a repair in Plesk. To my suprise. It worked. I had over 6 months of order data restored and I didn't lose anything.

Answer 9

In my case the db was fully working but the error popped up in workbench without any consequences when I connected to the database. I found out, that the table not existing in the engine was an old table I deleted from db (and which I did not need anymore). But somehow I accidentally copied some backup files of this table into the dictionary of the database (.frm and .ibd file) After removing them, the error disappeared.