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

I've had this exact problem on MariaDB 11.5, macOS 14.7 after some random crash / upgrade. The individual table .frm / .ibd files were still in place and readable. Deleting ib_logfile0 just prevented mariadb from starting up at all. The following commands finally solved my problem:

1. Login as root (or any user with required permissions) in the mariadb interactive shell:

mariadb -u root -p

2. Discard (if possible) and reload tablespace for each affected table from .ibd file:

USE `affected_database`;

-- Discard tablespace for each affected table, might fail
ALTER TABLE <table_name> DISCARD TABLESPACE;

-- Reload tablespace for each affected table
ALTER TABLE <table_name> IMPORT TABLESPACE;

Depending on your table structure, you might run into the following error:

ERROR 1815 (HY000): Internal error: Drop all secondary indexes before importing table <table_name> when .cfg file is missing.

In this case, you (obviously) need to do so before importing the tablespace. You can re-create them afterwards:

-- Show all secondary indexes
-- If you get `Error 1932 (42S02): Table <table_name> doesn't exist in engine`
-- you do need to try IMPORT TABLESPACE as explained above first,
-- which does load the indexes from the .ibd.
SHOW indexes FROM <table_name> WHERE Key_name != "PRIMARY";

-- Drop all of them (by Key_name)
ALTER TABLE <table_name> DROP INDEX <index_name>;

-- Reload tablespace
ALTER TABLE <table_name> IMPORT TABLESPACE;

-- Re-add indexes, modify according to your needs
ALTER TABLE <table_name> ADD INDEX <index_name> (<column_names>);

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 <NEW-SERVER-USERNAME>@<NEW-SERVERIP>:/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

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

Answer 4

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.

Answer 5

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 6

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 7

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 8

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

Answer 9

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 10

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.