49

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?

70

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 <securepass> 

Check this link for more information. To disable column statistics by default, you can add

[mysqldump]
column-statistics=0

to a MySQL config file, such as /etc/my.cnf or ~/.my.cnf.

  • --databases <database name> for a particular database – Mohhamad Hasham Jul 5 '18 at 6:05
  • @forthrin Answer edited to show how to make the change permanent. – Andrew Schulman Aug 15 '18 at 15:04
  • There is also a setting in MySQL Workbench: Under "Advanced Options...", in the "other" section, there is a column-statistics that is set to "TRUE". Change this to zero as suggest to disable. – Alex Barker Dec 26 '18 at 20:40
13

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.

  • 4
    Only MySQL Workbench version 8.0.13 and after has this option to disable "Use Column Statistics". – Paul Deng Nov 7 '18 at 9:22
10

I spent the whole day looking for a solution, and singed up here just to share mine.

Yes, probably this error is due to versions difference.

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.

  • 1
    For Windows users, this .exe was located at: C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe – Jason Oct 18 '18 at 21:42
2

To make this answer easier, you can rename mysqldump, make a shell script in its place and call the renamed mysqldump with the --column-statistics=0 argument. Eg:

Rename mysqldump:

mv /usr/local/bin/mysqldump /usr/local/bin/_mysqldump

Save the following shell script in its place:

#!/bin/sh

_mysqldump --column-statistics=0 $@
  • This is a great suggestion and works in MacOSX with the Mysql Workbench, however you have to include the full path to your real mysqldump executable or it didn't seem to work. I tried ./ but still wouldn't work. Putting in the full path and the Workbench was able to run it with the stats flagged off. – Andy D Oct 20 '18 at 21:19
1

Easiest Work Around

When using Mysql Workbench 8.0

  • Open the "Data Export" Tab
  • Click Advanced Options enter image description here
  • Under the Other heading, set column statistics to 0 enter image description here
  • Export againenter image description here

Best of luck!

0

In my situation, I'm using mac-OS. By the way, there was [mysqldump] column-statistics=0 fields in my.cnf file that is placed under the /usr/local/etc directory. Deleting that field fixed the issue. (not: mysql version is 5.7 and installed via homebrew).

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.