mysqldump Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

Question

I am having some difficulty with my MySQL database.

It appears the server suffered from a power outage during the night. As a result, MySQL wouldn't start when it reboots.

I viewed the error log and saw:

[ERROR] InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT between the checkpoint 322393393 and the end 322394369.
[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[ERROR] Plugin 'InnoDB' init function returned er
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Failed to initialize builtin plugins.
[ERROR] Aborting

MySql will only start with innodb_force_recover=6

At this point I am trying to save the data by running from MySQL Workbench: mysqldump.exe --defaults-file=#### --user=#### --host=localhost --protocol=tcp --port=59452 --default-character-set=utf8 --single-transaction=TRUE --skip-triggers "BusinessManager"

Workbench shows me this error:

mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'BusinessManager' AND TABLE_NAME = 'Activities';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

Operation failed with exitcode 2

However, the MySQL log shows:

[ERROR] InnoDB: Failed to find tablespace for table BusinessManager.Activities in the cache. Attempting to load the tablespace with space id 195

The log error is repeated for each table and the SQL dump fails.

Is there any way to save the data in the database and bypass these errors?

Thanks!

Answer 1

As far as the the Workbench error is concerned what you need to do is disable the "column statistics" argument on export. So, you should write the command adding "--column-statistics=0". This should resolve the first-Workbench issue:

mysqldump.exe --defaults-file=####  --user=#### --host=localhost --protocol=tcp --port=59452 --default-character-set=utf8 --single-transaction=TRUE --skip-triggers --column-statistics=0 "BusinessManager"

Regarding tablespace error, the documentation describes it here:

1. On the new MySQL instance, recreate the table in a database of the same name using the command CREATE TABLE ...

2. Discard the tablespace of the newly created table:

   ALTER TABLE database.tableName DISCARD TABLESPACE
   

3. Copy the orphan .ibd file from your backup directory to the new database directory using shell:

   cp /backup_directory/tableName.ibd path/to/mysql-5.6/data/database/
   

4. Finally, import the orphan .ibd file. A warning is issued indicating that InnoDB will attempt to import the file without schema verification.

   ALTER TABLE database.tableName IMPORT TABLESPACE; SHOW WARNINGS;
   

This should do it. As a test, you should run the following command in mysql:

    SELECT COUNT(*) FROM database.tableName;

If this executes correctly, tablespace error should disappear.