Thesqldump Unknown table ‘COLUMN_STATISTICS’ in information_schema (1109)

MySQLmysqldump

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!

Best Answer

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:

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

Discard the tablespace of the newly created table:

ALTER TABLE database.tableName DISCARD TABLESPACE

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

I have very bad news for you.

You should not have deleted the ibdata1 file. Here is why:

ibdata1 contains four type of information:

table metadata
MVCC data
data pages (with innodb_file_per_table enabled)
index pages (with innodb_file_per_table enabled)

Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.

I also have very good news for you along with some bad news.

It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).

Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.

To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.

I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.

UPDATE 2011-10-17 06:19 EDT

Here is your original innodb configuration from your question:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000

Please notice that innodb_log_file_size is there twice. Look carefully...

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000

The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;. Mystery solved !!!

UPDATE 2011-10-17 11:07 EDT

The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).

For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.

Mysql – Corrupt MySQL log file – cannot start the database

Was able to do a mysqldump, remove everything. Do a clean reinstall of mysql and import it back. Thanks for the help !

Best Answer

Related Solutions

Mysql – Error ‘Unknown table engine ‘InnoDB” on query. after restarting thesql

I have very bad news for you.

I also have very good news for you along with some bad news.

UPDATE 2011-10-17 06:19 EDT

UPDATE 2011-10-17 11:07 EDT

Mysql – Corrupt MySQL log file – cannot start the database

Related Question