252

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?

11 Answers 11

385

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, such as /etc/my.cnf or ~/.my.cnf.

| improve this answer | |
  • --databases <database name> for a particular database – Mohhamad Hasham Jul 5 '18 at 6:05
  • 1
    @forthrin Answer edited to show how to make the change permanent. – Andrew Schulman Aug 15 '18 at 15:04
  • 2
    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
  • 7
    MySQLWorkbench v8.0.14 does not have that setting. What I did (on a Mac) was download an older version of MySQLWorkbench, opened up that package (Show Package contents) and pulled out its bundled version of 'mysqldump'. I then edited MySQLWorkbench preferences to look at this version of 'mysqldump' instead of its own. Export works just fine without having to set any flags. ref) bugs.mysql.com/bug.php?id=91640#c484427 – Scott Jan 22 '19 at 17:27
  • 3
    For anyone using DataGrip, you can right-click on the schema in the Database window, go to "Dump with 'mysqldump'", and add the --column-statistics=0 argument to the generated argument list in the box at the bottom of the popup window. – Travesty3 May 1 '19 at 14:35
32

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.

| improve this answer | |
  • 8
    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
  • 7
    MySQL Workbench version 8.0.14 doesn't have this option. Looks like they took it out. – Dean Or Jan 24 '19 at 0:11
  • 3
    Indeed, bugs.mysql.com/bug.php?id=94294, should be back in 8.0.16... – Jean-Christophe Meillaud Mar 12 '19 at 16:53
  • 3
    I can confirm that MySQLWorkbench v8.0.17 fixed the issue (MacOs). It was throwing this error on MySQLWorkbench v8.0.12 and upgraded. I noticed it didn't warn me about exporting data from a 5.X DB anymore and automatically appended the --column-statistics=0 in the export command. – Elte156 Sep 3 '19 at 16:37
  • 3
    I am using v8.0.18 and can't find that option either – mFeinstein Nov 8 '19 at 19:13
23

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.

| improve this answer | |
  • 3
    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
    MySQLWorkbench v8.0.16 is still missing the option to set the column-statistics flag. This solution is arguably the best answer as it allows proper matching of MySQL versions in case of future issues. – Kiksy Apr 25 '19 at 9:15
  • 1
    I tried your solution with mysql-5.7.28-macos10.14-x86_64. It showed "Workbench was unable to get mysqldump version. Please verify the log for more information." – emeraldhieu Apr 9 at 16:46
  • For MySQL Workbench CE 8.0.19 on Windows, the path is: C:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysqldump.exe – groovenectar Apr 14 at 20:37
7

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!

| improve this answer | |
  • 8
    I don't have this option in the "Other" section – JoRouss Feb 2 '19 at 21:22
  • @JoRouss Download 8.0.13 downloads.mysql.com/archives/workbench maybe .16 version will have it again ! Otherway we use command line :-) – François Breton Feb 6 '19 at 17:10
  • I ended up using @DodiX suggestion. Thanks @Francois! – JoRouss Feb 8 '19 at 7:05
  • Stupid workaround until 8.0.16 is out: in Advanced options check "force" so the error will be ignored. Of course this will ignore any other error – Bostone Mar 5 '19 at 22:10
  • @Bostone, this solution doesn't ommit the error. – Casper Mar 25 '19 at 8:30
5

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 $@
| improve this answer | |
  • 1
    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
  • for Windows, you can have the script like this @echo OFF _mysqldump --column-statistics=0 %* – Earlee Oct 7 at 7:04
2

I use XAMPP and MySQL Workbench does warn about a version mismatch. I set MySQL Workbench to point to the XAMPP's mysql.exe and mysqldump.exe.

Go to Edit -> Preferences -> Administration and set the path for each.

This works at least for version 8.0.14. So for others you may want to avoid using the bundled version of mysql and mysqldump.

| improve this answer | |
  • 1
    Easy and Clean. It works with WampServer too and on Windows. – BlackPearl Apr 7 at 5:34
  • and if the path is something like /usr/local/bin/mysqldump which is hidden in Finder, use cmd+shitt+g to open that location – CHAN May 8 at 4:07
2

To macOS you need the older version (8.0.13) to see the "column-statistics", because I test the version 8.0.14 and 8.0.15 and both not showing the "column-statistics".

So, to adjust the "column-statistics" use the version 8.0.13 https://downloads.mysql.com/archives/get/file/mysql-workbench-community-8.0.13-macos-x86_64.dmg

| improve this answer | |
2

I know that I am late to the party but this was getting me crazy. If you want to use a recent MySQL Workbench (tried with the newest one as of today, MySQL Workbench 8.0.20) you can patch this file:

plugins/wb_admin_export_options.py

in macOS: (/Applications/MySQLWorkbench.app/Contents/Resources/plugins/wb_admin_export_options.py) Replacing this line:

  "column-statistics":["Writing ANALYZE TABLE statements to generate statistics histograms.", "FALSE", "BOOL", ("8.0.2", None)]

with this one:

"column-statistics":["Writing ANALYZE TABLE statements to generate statistics histograms (set 0 to disable).", "1", "INT", (None, None)]

Then remove the .pyo:

rm /Applications/MySQLWorkbench.app/Contents/Resources/plugins/wb_admin_export_options.pyo

Finally, reload Workbench again and in the Data Export page, click on "Advanced options..." and you will see the column-statistics option again (set 0 to disable and click the Return button)

enter image description here

Note: you can download the patched file from this Gist.

| improve this answer | |
  • This patch worked for me. Does anyone know why this happened? – Cletrix May 19 at 13:24
  • Worked like a charm on Windows 7. Thanks. – JayJay123 Aug 20 at 3:40
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).

| improve this answer | |
0

I also had the same issue, it occurs when I merge multiple data tables to the existing schema from other schema and export merged data to self contained script file. I did try to change the column-statistics=0,but result was following,

C:\xampp\mysql\bin>mysqldump --column-statistics=0 --host=loalhost --user root --passwod
mysqldump: unknown variable 'column-statistics=0'

So I hadn't help. I analyze the MySQL log I found that

2019-01-21 11:31:30 1050 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2019-01-21 11:31:30 4176 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

its complaining about the size of the innodb_buffer_pool_size. I did make it to 24MB. Then it works.

| improve this answer | |
0

In addittion to pierlo https://serverfault.com/a/919403/586669

From within MySQL Workbench there is an option to set the path of the mysqldump executable. (Edit - Preferences - Administration)

So you can create a .cmd (on Windows) or a .sh file (on Linux or mac) as follows:

mysqldump_nostatistics.cmd:

 @ECHO OFF
 "C:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysqldump.exe" %*  --column-statistics=0

mysqldump_nostatistics.sh:

#!/bin/sh

_mysqldump $@ --column-statistics=0

Note the order of the parameters (it is different from pierlo's) : the dump command executed includes (or may include) a --defaults-file= option, and this has to be the first parameter.

Also The echo off is needed otherwise the workbench is unable to parse the command output correctly.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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