Mysql – thesqldump: Couldn’t execute. Unknown table ‘column_statistics’ in information_schema

MySQL

I want to dump my database, even after following correct syntax it thows me following error.

Syntax I used :

mysqldump -uroot -p omnichannel_store_india > omnichannel_store_india.sql

Throws errors :

mysqldump: Couldn't execute
'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'omnichannel_store_india' AND TABLE_NAME = 'consignment_items';':

Unknown table 'column_statistics' in information_schema (1109)

Best Answer

This is due to a flag column-statistics 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 <securepass>

To disable column statistics by default, you can add the following in a MySQL config file, such as /etc/my.cnf or ~/.my.cnf.

[mysqldump]
column-statistics=0

It's brilliantly working for me.

More details about column-statistics:

Add ANALYZE TABLE statements to the output to generate histogram statistics for dumped tables when the dump file is reloaded. This option is disabled by default because histogram generation for large tables can take a long time.

Ref: MySQL official documentaion link

Related Topic