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

Question

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?

score 820 · Accepted Answer · edited Dec 28 '21 at 09:12

820

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.

edited Dec 28 '21 at 09:12

questionto42

419

answered May 17 '18 at 13:10

cristhiank

8,324

--databases for a particular database – Mohhamad Hasham Jul 05 '18 at 06:05
3

@forthrin Answer edited to show how to make the change permanent. – Andrew Schulman Aug 15 '18 at 15:04
3

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
9

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) https://bugs.mysql.com/bug.php?id=91640#c484427 – Scott Jan 22 '19 at 17:27
10

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 01 '19 at 14:35
1

Interestingly, the link you proved above explicitly states: "This option is disabled by default..." – M Klein Jan 25 '21 at 15:27
1

as it might help someone, I had the same problem with MariaDB 10.4.12 and it also needs the same config, so I assume 10.4+ needs it – Sobhan Atar May 18 '21 at 11:58
You can also just remove mysql, and install mariadb instead. – Edward Ned Harvey Sep 07 '21 at 19:31
@MKlein Joining your comment, quote from the link of the answer: `--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.But in reality,enabled` is the default, see 8.0 mysqldump does not work against 5.7 server.
– questionto42 Dec 23 '21 at 22:05

atjoedonahue · Answer 2 · 2019-06-18T17:35:01.990

48

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.

edited Jun 18 '19 at 17:35

answered Oct 25 '18 at 22:57

atjoedonahue

581

9

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

MySQL Workbench version 8.0.14 doesn't have this option. Looks like they took it out. – Dean Or Jan 24 '19 at 00:11
3

Indeed, https://bugs.mysql.com/bug.php?id=94294, should be back in 8.0.16... – Jean-Christophe Meillaud Mar 12 '19 at 16:53
still happen at 8.0.16 – Amir Bar Jun 25 '19 at 10:35
4

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 03 '19 at 16:37
4

I am using v8.0.18 and can't find that option either – mFeinstein Nov 08 '19 at 19:13
Like @Elte156 MySQL Workbench v8.0.17 fixed the issue on Windows automatically without having to modify any settings. – Johnie Ndosh Spax Mar 25 '20 at 08:12
1

Same on v8.0.19, can't find the option. What the heck is going on? Oracle ffs! – DARKGuy Apr 22 '20 at 06:24
3

No option in v8.0.20 either. :/ – James May 22 '20 at 03:04
3

I have 8.0.19. Workaround: copy the mysqldump command from the output window, remove defaults file part, add --column-statistics=0 with other options, open a command window in the mysqldump.exe parent folder and run manually, piping to a file in my documents folder (you'll get access denied errors if you just try to dump it to same folder). Example: C:\Program Files\MySQL\MySQL Workbench 8.0 CE>mysqldump.exe --user=USER--host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --skip-triggers --column-statistics=0 "mantis" > "C:\users\moi\Documents\dumps\mantis.sql" – dartacus Jul 15 '20 at 11:03
1

@dartacus seems to be the good solution for now, but it doesn't make any sense to use GUI if you still use the CLI – Earlee Sep 14 '20 at 01:06
what I did was I rolled back to MySQL WB 8.0.13 – Earlee Sep 14 '20 at 01:32
@Earlee literally just an easy way of getting at the command – dartacus Oct 06 '20 at 15:28
4

8.0.22 this option is missing. Having problem with MariaDB. – ACV Jan 03 '21 at 02:08
1

8.0.33 still doesn't have this option. I'm also trying to work with MariaDB. – Mr. Lance E Sloan May 27 '23 at 02:36

score 30 · Answer 3 · edited Jul 06 '21 at 23:52

30

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.

edited Jul 06 '21 at 23:52

Community

1

answered May 15 '18 at 15:32

DodiX

411

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 09: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 09 '20 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 '20 at 20:37
MySQL workbench 8.0.28 is also missing this option so @DodiX 's suggestion is what worked for me. I already had wampserver installed so I just pointed the MySQL Workbench preferences to use the Wampserver's mysqldump.exe, in my case this was C:\wamp64\bin\mysql\mysql5.7.31\bin\mysqldump.exe – BlessedHIT Feb 11 '22 at 09:05

Juanan · Answer 4 · 2020-05-03T16:16:35.807

22

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)

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

edited May 03 '20 at 16:16

answered May 03 '20 at 15:58

Juanan

321

1

This patch worked for me. Does anyone know why this happened? – Cletrix May 19 '20 at 13:24
Worked like a charm on Windows 7. Thanks. – JayJay123 Aug 20 '20 at 03:40
Worked for me as well. The file ist located '/usr/lib/mysql-workbench/modules/wb_admin_export_options.py' on Ubuntu 18.04. – Kai Mar 01 '21 at 11:56
Works on windows 10 as well! – pleasega Sep 27 '21 at 16:20
Yes! This is the correct answer for 2022 using version 8.0.28. Better than having to use phpMyAdmin. Thanks. – vr_driver Apr 21 '22 at 00:59
this saved tons of time and only this is correct for 2022! – Sithys Jun 13 '22 at 13:30
Works fine, but I'm wondering why "TRUE", "BOOL" doesn't work here? I might be misunderstanding the arguments. I'd prefer a checkbox. Also, is there some way to define the default, as I'd prefer it to be disabled by default. – Halvor Holsten Strand Dec 12 '22 at 08:54
Works with MySQL WB 8.0.31. Windows 10 path is c:\Program Files\MySQL\MySQL Workbench 8.0 CE\modules\wb_admin_export_options.py – Jeffrey Feb 06 '24 at 14:13

score 8 · Answer 5 · answered Dec 13 '18 at 07:10

8

Easiest Work Around

When using Mysql Workbench 8.0

Open the "Data Export" Tab
Click Advanced Options
Under the Other heading, set column statistics to 0
Export again

Best of luck!

answered Dec 13 '18 at 07:10

anson

181

13

I don't have this option in the "Other" section – JoRouss Feb 02 '19 at 21:22
@JoRouss Download 8.0.13 https://downloads.mysql.com/archives/workbench/ maybe .16 version will have it again ! Otherway we use command line :-) – François Breton Feb 06 '19 at 17:10
I ended up using @DodiX suggestion. Thanks @Francois! – JoRouss Feb 08 '19 at 07: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 05 '19 at 22:10
@Bostone, this solution doesn't ommit the error. – Casper Mar 25 '19 at 08:30

score 7 · Answer 6 · answered Jul 04 '18 at 05:25

7

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 $@

answered Jul 04 '18 at 05:25

pierlo

79

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
1

for Windows, you can have the script like this @echo OFF _mysqldump --column-statistics=0 %* – Earlee Oct 07 '20 at 07:04
This is a great suggestion. I'm using v8.0.30 and somehow they still don't have a column-statistics option. Seemingly it was removed? – rachelderp Aug 04 '22 at 03:49

score 5 · Answer 7 · answered Aug 04 '20 at 11:40

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.

Thank you. This is what worked for me with MySql Workbench 8.0.31 and Bitnami WAMP 7.4.33 which uses MariaDB and mysqldump 10.4.27. Two things that didn't work: 1. Pointing MySql Workbench prefs to WAMP's mysqldump. 2. Setting column-statistics=0 in WAMP's my.ini — Nick Hope, Dec 26 '22 at 07:29

score 5 · Answer 8 · answered Feb 01 '21 at 20:40

5

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

answered Feb 01 '21 at 20:40

Paul Collister

51

That works. On linux just make sure MariaDB version of mysqldump is in your PATH, or edit the path in Workbench's Preferences -> Administration – Chris Koston Mar 15 '21 at 18:18
On Windows I found the file in the mariadb-10.5.5-winx64.zip\mariadb-10.5.5-winx64\bin folder of the ZIP file download – Nick Graham Apr 08 '21 at 19:52

score 4 · Answer 9 · answered Sep 20 '21 at 13:17

There a couple of answers above here that refer to renaming the mysqldump binary and creating a wrapper script. This is a terrible approach.

The correct method (in bash) is to alias the command in your .bashrc

alias mysqldump="mysqldump --column-statistics=0"

score 4 · Answer 10 · answered Jan 23 '19 at 23:04

4

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.

answered Jan 23 '19 at 23:04

Dean Or

222

1

Easy and Clean. It works with WampServer too and on Windows. – BlackPearl Apr 07 '20 at 05: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 08 '20 at 04:07
The Xampp version worked great for me too! – Steve Stilson Aug 24 '22 at 23:17
Using the xampp version will also get rid of the "mysqldump version mismatch" error I was getting. – Steve Stilson Aug 24 '22 at 23:24

score 2 · Answer 11 · answered Feb 25 '19 at 20:39

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

score 1 · Answer 12 · edited Nov 18 '20 at 06:15

From MySQL Workbench version 8.0.14 you don't have the option to disable column-statistics

You can do that in version 8.0.13

But you have an option to do it by enabling delete-master-logs in version 8.0.22

--delete-master-logs has the same effect as the RESET MASTER SQL command.
RESET MASTER deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file. This statement is intended to be used only when the master is started for the first time.

score 1 · Answer 13 · edited Dec 18 '20 at 12:50

1

For those using DBeaver check the Local Client is set to create the dump. See next images for reference:

Access to local client selection:

Access to local client selection

Local clients available:

Local clients available

edited Dec 18 '20 at 12:50

Uwe Keim

2,440

answered Dec 18 '20 at 10:15

Dario Herrero

11

score 1 · Answer 14 · answered Jan 06 '21 at 11:11

I faced the same issue with MySQL workbench latest edition, I resolved it using the mysqldump command line

C:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysqldump --column-statistics=0  --user=USERNAME --host=REMOTE_HOST --protocol=tcp --port=3306 --default-character-set=utf8 DATABASE_NAME > c:\temp\dump.sql --password

Replace USERNAME, REMOTE_HOST, DATABASE_NAME with your names.

score 1 · Answer 15 · answered Sep 07 '21 at 19:01

1

Depending on your situation, you can get rid of mysql and install mariadb instead. This eliminates the new feature that was introduced in mysql 8.

answered Sep 07 '21 at 19:01

Edward Ned Harvey

532

This helped me, thank you. I working with AWS Aurora and it looks like it more complaint with the MariaDB than with the original MySQL – Sergey Ponomarev Nov 04 '22 at 12:39

score 1 · Answer 16 · answered Dec 06 '22 at 19:44

If using a MariaDB backend with MySQL Workbench 8.0.31 CE use need to download the native MariaDB binaries for your system from https://mariadb.com/downloads/community/ and specify them in MySQL Workbench.

For example, if you are on 64-bit Microsoft Windows OS perform the following:

Goto https://mariadb.com/downloads/community/
On the above page, select your Operating System
Community Server --> Verison (latest GA) --> OS (MS Windows 64-bit) and click on download
Run the Install Shield just downloaded, and make sure "MariaDB Server -> Client Programs" is enabled.
Run up MySQL Workbench
Select the menu: Edit -> Preferences...
Select "Administration" in the left hand column
Update "Path to mysqldump Tool:" to be C:\Program Files\MariaDB 10.10\bin\mariadb-dump.exe
Update "Path to mysql Tool:" to be C:\Program Files\MariaDB 10.10\bin\mariadb.exe

No more performance statistic errors or any other divergent issues down the line between MySQL and MariaDB.

This has been posted for future reference.

score 0 · Answer 17 · answered Oct 24 '21 at 21:31

On macOS I fixed this by overriding the bundled mysqldump:

Install mariadb, e.g. brew install mariadb
Navigate to MySQLWorkbench > Preferences > Administration
Set the mysqldump path to the one you just installed, e.g. /opt/homebrew/bin/mysqldump

Note: to find the full path to mysqldump, you can run which mysqldump in your terminal.

trainin99 · Answer 18 · 2022-05-18T15:32:46.947

0

Since i cannot comment the actual answer from user:cristhiank, 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 [mysqldump] section.

So for me this was working /etc/my.cnf.d/client.cnf

[client]
column-statistics=0

edited May 18 '22 at 15:32

answered May 18 '22 at 15:29

trainin99

1

score 0 · Answer 19 · answered Nov 08 '22 at 12:27

0

In MySQL Workbench 8.0.31 on Advanced Options tab now exists new option "Force", use it to skip errors like mentioned above.

answered Nov 08 '22 at 12:27

Roman

1

score 0 · Answer 20 · answered Dec 02 '22 at 14:28

0

For Ansible users use:

mysql_db:
 ...
 dump_extra_args: --column-statistics=0

In my case it ignores ~/.my.cnf and /etc/my.cnf

answered Dec 02 '22 at 14:28

Ricky Levi

133

score 0 · Answer 21 · answered Mar 21 '23 at 10:16

Copy the mysqldump command from the output window, remove the defaults file part, and add --column-statistics=0 with other option

Open a command window in the mysqldump.exe parent folder and run the command manually, piping to a file in my documents folder (you'll get access denied errors if you just try to dump it to same folder).

Example:

C:\Program Files\MySQL\MySQL Workbench 8.0 CE>mysqldump.exe --user=USER--host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --skip-triggers --column-statistics=0 "mantis" > "C:\users\moi\Documents\dumps\mantis.sql"

This solution was written by @dartacus several years ago. I'm converting it into a formatted answer since it worked for me and was buried underneath a bunch of other comments. — Pikamander2, Mar 21 '23 at 10:17

score 0 · Answer 22 · edited Dec 11 '18 at 16:59

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

edited Dec 11 '18 at 16:59

Cory Knutson

1,876
13
20

answered Dec 11 '18 at 13:04

Hatip Aksunger

1

score 0 · Answer 23 · edited Jan 21 '19 at 10:47

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.

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

23 Answers23