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)
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
This is due to a flag
column-statistics that is enabled by default in mysqldump 8. You can disable it by adding
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
It’s brilliantly working for me.
More details about
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.
In my case, we using mariadb-server and mysql-client(8.0). The column-statistics was introduced on mysql-client 8.0.
The quick fixed way it that remove the mysql-client and install mariadb-client.
On ubuntu 20.04, the command is:
apt remove mysql-client apt install mariadb-client
Since i cannot comment the actual answer from user:Amitesh Bharti, i’m adding a slight variation of the actual answer. In my case i had to change it in
/etc/my.cnf.d/client.cnf and i had to leave it in the
[client] section so not adding a
So for me this was working