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