Home / server / Questions / 912162
Accepted
manifestor
Asked: 2018-05-15 08:40:20 +0800 CST

mysqldump throws: Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

  • 772

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?

mysql

19 Answers

  1. Best Answer

    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.

    • 723

  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.

    • 40

  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.

    • 29

  4. 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.

    • 16

  5. 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!

    • 7

  6. 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 $@
    • 6

  7. I had this problem using the latest mysql workbench (8.0.23) on OSX (11.1) with mariadb. I solved it by selecting the version of mysqldump found in the mariadb package.

    /usr/local/mariadb/mariadb-10.1.37-osx10.13-x86_64/bin/mysqldump

    • 4

  8. 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.

    • 3

  9. 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.

    • 2

  10. 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

    • 2