mysqldump throws: Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
Every time I try to make a mysqldump I get the following error:
$> mysqldump --single-transaction --host host -u user -p db > db.sql
mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM,
'$."number-of-buckets-specified"') FROM
information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'db' AND
TABLE_NAME = 'Absence';':
Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
The result is a dump which is not complete. The strange thing is that the same command, executed from another host, works without throwing any errors. Did someone experienced the same problem?
I'm using mysql-client 8.0 and try to access a mysql 5-7 server - maybe that is the reason?
Solution 1:
This is due to a new flag that is enabled by default in mysqldump 8.
You can disable it by adding --column-statistics=0. The command will be something like:
mysqldump --column-statistics=0 --host=<server> --user=<user> --password=<password>
Check this link for more information. To disable column statistics by default, you can add
[mysqldump]
column-statistics=0
to a MySQL config file, go to /etc/my.cnf, ~/.my.cnf, or directly to /etc/mysql/mysql.cnf.
Solution 2:
For those using MySQL Workbench, there is an "Advanced Options" button on the Data Export screen. The option "Use Column Statistics" can be disabled by setting to 0.
I have not confirmed, but the following information has been suggested to also be true: In Version 8.0.14 it's missing. In Version 8.0.16 it's doing this by default.
Solution 3:
I spent the whole day looking for a solution, and signed up here just to share mine.
Yes, this error is due to version differences.
Just download the MySQL 5.7 ZIP Archive from here: https://dev.mysql.com/downloads/mysql/ and unzip it, then use the mysqldump.exe file from there.
If you are using MySQL Workbench, you will need to set a path to the mysqldump Tool you downloaded by going to Edit -> Preferences -> Administration (from left hand pane).
Hope this helps.