How to disable column-statistics in MySQL 8 permanently?

Question

Since MySQL 8 the column-statistics flag is enabled by default.

So if you try to dump some tables with MySQL Workbench 8.0.12, you get this error message:

14:50:22 Dumping db (table_name) Running: mysqldump.exe --defaults-file="c:\users\username\appdata\local\temp\tmpvu0mxn.cnf" --user=db_user --host=db_host --protocol=tcp --port=1337 --default-character-set=utf8 --skip-triggers "db_name" "table_name" mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'db_name' AND TABLE_NAME = 'table_name';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

Operation failed with exitcode 2 14:50:24 Export of C:\path\to\my\dump has finished with 1 errors

Is there any way in MySQL (Workbench) 8 to disable column-statistics permanently?

Workaround 1

An annoying workaround is doing it by hand via:

mysqldump --column-statistics=0 --host=...

Workaround 2

rename mysqldump
create a shell script (or batch on Windows)
call the renamed mysqldump with the --column-statistics=0 argument within this script
save it as mysqldump

Workaround 3

download MySQL 5.7
extract mysqldump
use this mysqldump

For example in MySQL Workbench: Edit / Preferences... / Administration / Path to mysqldump Tool

Thanks in advance!

could you rename mysqldump, make a shell script in its place and call the renamed mysqldump with the --column-statistics=0 argument? — Clint, Jul 31 '18 at 13:10
Just read about this other workaround. But is there no setting / no configuration for MySQL / MySQL Workbench? — Flo Bayer, Jul 31 '18 at 13:11
i don't think so, i think the issue is that there is a compatibility issue with the mysqldump included with mysql 8, i have seen that some users are downloading mysql 5.7 and extracting the mysqldump and using that.. — Clint, Jul 31 '18 at 13:18
Yep, I've seen that as well, but I don't like this solution that much. If there is no official / no clean way, I uninstall MySQL Workbench 8.0.12 and reinstall 6.3.10 - that would be too bad! — Flo Bayer, Aug 01 '18 at 05:22

score 37 · Answer 1 · answered Mar 12 '19 at 12:16

37

Workaround for me:

Create file named mysqldump.cmd with contents:

    @echo off
    "c:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysqldump.exe" %* --column-statistics=0

(replace path to mysqldump.exe if necessary)

Open MySQL Workbench and go to Edit > Preferences > Administration, change path to mysqldump tool and point it to mysqldump.cmd

answered Mar 12 '19 at 12:16

user3175253

588
4
10

3

this worked for me while using MySQL Workbench Version 8.0.15 build 14271522 CE (64 bits) with MySQL 5.7 db instance – Muhammad Rizwan Aug 18 '19 at 10:20
2

This seems to be the only valid workaround as of 8.0.19, so thank you.. – knif3r Jan 31 '20 at 10:56
getting error 'Workbench was unable to get mysqldump version. Please verify the log for more information.' macOs – Kamboh Apr 26 '21 at 12:27
Worked for me on v8.0.26 – Ryan Simmons Aug 12 '21 at 19:29
getting error 'Workbench was unable to get mysqldump version. Please verify the log for more information.' on 8.0.25 – Mihai Vlasceanu Oct 05 '21 at 12:12
it just crashes mysql workbench – FARS Apr 15 '22 at 08:06
Worked for me on Windows 10 with MySQL Workbench 8.0 CE (v8.0.30 build 2054668 CE 64-bit). I struggled with other solutions for about 20 minutes but this one worked with no issues. – GoTeamScotch Aug 06 '22 at 04:43

score 19 · Answer 2 · answered Nov 15 '18 at 17:43

19

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 Nov 15 '18 at 17:43

Troyd

434
5
13

41

Looks like in v8.0.14 they removed this option. – ttimmons Jan 22 '19 at 00:22
3

There is a bug report for this removal in v8.0.14 and v8.0.15: https://bugs.mysql.com/bug.php?id=94294 It will be reintroduced in v8.0.16. – Bogdan Mar 05 '19 at 01:28
8

this option is still not available on v8.0.16 – Nuryagdy Mustapayev May 09 '19 at 07:51
5

Still not available in version 8.0.19 – Christopher Smit May 15 '20 at 14:37
8

still not available 8.0.21. I don't know what MySQL team is doing at this point – Earlee Sep 14 '20 at 01:12
1

I've found a workaround for adding it back to workbench. Check out this link. This is for mac, but maybe someone can figure out for windows as well. I now have that option back on 8.0.21 - https://serverfault.com/a/1015459 – Christopher Smit Sep 28 '20 at 17:14
I was still on 8.0.13. Good to know I should not upgrade! – Stijn de Witt May 21 '21 at 10:41
1

Still not available in version 8.0.23 – cooper Oct 25 '21 at 10:15
Still not available in version 8.0.33 – Anthony Walsh May 13 '23 at 00:51

score 9 · Answer 3 · edited Jun 04 '20 at 12:33

I have download the version 8.0.16, still the same issue.

At data export advanced options I didn't saw any option about "statistics"! I have add at my.ini at

[mysqldump]
quick
max_allowed_packet = 16M
column-statistics=0

Doesn't solve the issue

I have been googling but I couldn't find the solution. I find a of companions who are strugling with this but not anyone with the solution at least for me.

At the end I have changed at MySQL Workbench file c:\Program Files\MySQL\MySQL Workbench 8.0 CE\modules\wb_admin_export.py:

skip_column_statistics = True #if get_mysqldump_version() > Version(8, 0, 2) and self.owner.ctrl_be.target_version < Version(8, 0, 0) else False

I have made comment of

if get_mysqldump...

I know this is not the way, but I can't find now another better solution. For me is this at least weird and strange. If anyone has a better solution let me know!

P.S. Restart Workbench after change has been made

I am using MySQL Workbench Version 8.0.15 build 14271522 CE (64 bits) with MySQL 5.7 db instance, I could not find the lines skip_column_statistics... and if get_mysqldump... on the mentioned path of .py file — Muhammad Rizwan, Aug 18 '19 at 10:11

Mike Lischke · Answer 4 · 2018-08-09T07:41:51.250

The idea is this: each server version has a dedicated mysqldump version. Not ideal and certainly not very backwards compatible, but that's the situation. MySQL Workbench can only include one mysqldump binary, so the latest one is used. The approach to download the MySQL 5.7 zip and use mysqldump from there is a good workaround without many side effects. You only have to be careful which server you dump with which dump version.

If you like to have that column stat flag automatically applied by MySQL Workbench please file a bug report at https://bugs.mysql.com.

Update

Meanwhile a bug report has been created for this issue: https://bugs.mysql.com/bug.php?id=91640

score 5 · Answer 5 · answered Jun 25 '19 at 00:41

WINDOWS USERS

I have MySQL Workbench 8.0CE and Xampp v3.2.3 and this worked for me:

Open xampp, and open mysql config > my.ini // add: column-statistics = 0 so:

    [mysqldump]
    quick
    max_allowed_packet = 16M
    column-statistics = 0

Save and close, reboot xampp mysql server. (just in case)

in Workbench: Edit > Preferences > Administration

In Mysqldump route put your mysqldump route of xampp, in my case:

C:\xampp\mysql\bin\mysqldump.exe

This worked for me!

score 3 · Answer 6 · answered Jan 24 '19 at 10:26

There may be another workaround if you are running an OS that supports mysqldump (i.e. Linux). Set the path to the mysqldump binary in the preferences and include the --column-statistics=0 argument in the path, in: Edit >> Preferences >> Administration >> path to MysqlDump Tool

score 3 · Answer 7 · answered Jun 13 '22 at 03:36

Mac user:

/Applications/MySQLWorkbench.app/Contents/Resources/plugins Update file wb_admin_export.py to skip_column_statistics in all cases.

skip_column_statistics = True # if get_mysqldump_version() > Version(8, 0, 2) and self.owner.ctrl_be.target_version < Version(8, 0, 0) else False

score 2 · Answer 8 · answered Nov 16 '20 at 10:16

2

From Mysql-workbench version 8.0.14 you don't have the option to disable column-statistics. But you have an option to do it by enabling delete-master-logs: https://stackoverflow.com/a/64855306/10747412

answered Nov 16 '20 at 10:16

Nob Hokleng

69
5

this options works for me on 8.0.21 workbench CE version – Patrick Jul 31 '22 at 07:11

score 1 · Answer 9 · answered May 06 '19 at 10:22

1

Download the last version of Mysql Workbench 8.0.16 and no more problems.

https://dev.mysql.com/downloads/workbench/

No option to check !

answered May 06 '19 at 10:22

Baptiste

1,688
1
15
25

Incorrect - I'm on 8.0.16 and it still fails. They haven't reintroduced the field they removed in 8.0.14 – Steve Childs May 15 '19 at 13:27
1

No need to downvote. I never told that the field was here. I had the same problem and now I can export without error – Baptiste May 15 '19 at 15:19
Agreed. I updated to 8.0.16 and I could export without any issues. – gaurang847 May 20 '19 at 07:46
This worked for me. In fact when you do the export with 8.0.16, it AUTOMATICALLY specifies --column-statistics=0 in the command. BUT don't try to get 8.0.22, it's a nightmare trying to figure out the Visual C++ 2019 Redistributable prerequisites. I worked on that for an hour before I found this solution which didn't have those prerequisites. – Jeremy Goodell Nov 03 '20 at 17:29

score 1 · Answer 10 · answered Apr 07 '21 at 18:16

1

Open MySQL Workbench Preferences and choose Path to mysqldump Tool accordiing to your xampp / mysql server path

MySQL Workbench Edit > Preferences > Administration

C:\xampp\mysql\bin\mysqldump.exe

that's it

answered Apr 07 '21 at 18:16

Hassnain Abbas

41
3

score 1 · Answer 11 · answered Jun 23 '21 at 06:50

1

I am using Ubuntu 16.04, MySql Workbench 6.3.6, MySql 8.0.25, MySqlDump 8.0.25.

My workaround is as follows:

Open /usr/lib/mysql-workbench/modules/wb_admin_export.py
Replace

s = re.match(".*Distrib ([\d.a-z]+).*", output)

with

s = re.match(".*mysqldump  Ver ([\d.]+).*", output)

Replace

cmd = subprocess.list2cmdline(args)

with

args.append("--column-statistics=0")
cmd = subprocess.list2cmdline(args)

answered Jun 23 '21 at 06:50

jyl_ljy_work

15
4

in version 8.0.29, step 2 is already there with an if condition like this `if ("Distrib" in output):` So I just utilized step 3 and it's now working. – FerdousTheWebCoder May 30 '22 at 10:31
I installed it thru a snap and not even `chmod` can give me write access to this file. The APT installation fails for Workbench 8.0.x on Ubuntu 22.04. Is there a workaround? – Joachim Rives Jul 20 '22 at 02:03

questionto42 · Answer 12 · 2022-01-26T13:33:35.097

1

Linux users

Same idea as the other answer above on Windows, here is a way to globally change the mysql settings on Linux so that you do not need to write mysqldump --column-statistics=0 anymore.

Quote from Server Fault Stack Exchange at mysqldump throws: Unknown table 'COLUMN_STATISTICS' in information_schema (1109), highly upvoted there:

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.

I recommend changing it directly in the /etc/mysql/mysql.cnf.

edited Jan 26 '22 at 13:33

answered Jan 26 '22 at 09:37

questionto42

7,175
4
57
90

Sadly this does not work on Linux either if Workbench was installed thru a snap. – Joachim Rives Jul 20 '22 at 03:16
@JoachimRives It is too long ago now, I cannot say for sure whether I tested this answer, but I used DBeaver with snap installation. If it ever worked like this, perhaps change to DBeaver. No guarantees. – questionto42 Jul 20 '22 at 17:08

score 0 · Answer 13 · answered Jan 30 '20 at 09:36

0

I fixed this by amending the MySQL Workbench config file wb_admin_export.py.

See my answer here.

answered Jan 30 '20 at 09:36

SharpC

6,974
4
45
40

score 0 · Answer 14 · answered Oct 11 '21 at 06:46

0

[![Export From DBEAVER with extra command][1]][1]

For me, I just added an extra command and it worked for me! [1]: https://i.stack.imgur.com/9iBvO.png

answered Oct 11 '21 at 06:46

shawon

166
2
6

How to disable column-statistics in MySQL 8 permanently?

Workaround 1

Workaround 2

Workaround 3

14 Answers14

Linux users

Linked