MySQL: InnoDB tables are corrupt, but relevant Data is still accessible. How to recover?

This is what occurs when I attempt to either upgrade the DB or use mysqldump.

The data I actually care about is still there. I can login with root or the user who accesses the database and select data without issue.

What can I do to export this data and start fresh?

I was thinking setting up Slave Replication but I don’t know if the innodb stuff being broken will interfere with that.

You can use the “mysqldump” command to dump the DBs that you want you to a text file that contains all the sql commands to recreate the DB and its data.

Edit: I didn’t read that is was mysqldump giving you the error. My bad.

I didn’t read that is was mysqldump giving you the error. My bad.

Both mysqldump and mysql_upgrade give the error.

There’s a stackoverflow thread:

with similar error messages and a popular solution that involved manually dropping the “non-existent” tables and then manually recreating them. It gives the exact queries to do so and links to an official bug report:

https://bugs.mysql.com/bug.php?id=67179

1 Like

Could you link to the relevant queries? I read it for like a solid 10 minutes and have yet to hit the solution you mention.

There is a .sql here: https://bugs.mysql.com/file.php?id=19725&bug_id=67179

1 Like

Be sure to take a backup before running any potentially destructive action. After taking a backup, I suggest trying

alter table tablename engine=innodb;

which will just rebuild it in-place.

If that doesn’t work because the dictionary objects don’t exist, run select into outfile on your tables to export the data into CSV files and then reimport it into a clean database.

https://dev.mysql.com/doc/refman/5.7/en/select-into.html

Even if you run the commands from stackoverflow and it appears to work, I would still mysqldump everything and then import into a clean DB. I would never trust a DB that had been corrupted in such a manner.

1 Like

How would I import a PSV file like the one generated by this script?

http://giantdorks.org/alain/shell-script-to-export-data-from-mysql-tables-using-select-into-outfile-method-with-column-names/

So it basically used SELECT INTO OUTFILE to produce files like this:

id|data|date
1|bob|<timestamp>

:thinking:

Use load data infile.

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

You will probably need to create empty tables first, use show create table to extract the DDL.

https://dev.mysql.com/doc/refman/5.7/en/show-create-table.html

1 Like

Were you able to remedy your table error?

1 Like

Yes.

I essentially ran the DB and exported each table’s CREATE TABLE to sql files then created a new MariaDB instance and ran those queries.

Essentially grabbed what I needed, nuked what was left, and then rebuilt.

It worked.

Managed to export it, but I use different database software solutions, so I thought there would be some problems - there weren’t any. Integration is still the thing.