How to disable column-statistics in MySQL 8 perman

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

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

---

Workaround 3

1. download MySQL 5.7
2. extract mysqldump
3. use this mysqldump

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

---

Thanks in advance!

Answer 1

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!

Answer 2

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

Answer 3

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

Answer 4

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

See my answer here.

Answer 5

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

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

No option to check !

Answer 6

Workaround for me:

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

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