CODEWITHSUNDEEP
mysqlmysql-workbenchmysql-8.0
Flo Bayer
Flo Bayer

Reputation: 1260

How to disable column-statistics in MySQL 8 permanently?

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!

Upvotes: 37

Views: 54891

Answers (14)

Nguyễn Xuân Tuấn
Nguyễn Xuân Tuấn

Reputation: 31

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

Upvotes: 3

questionto42
questionto42

Reputation: 9542

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.

Upvotes: 1

shawon
shawon

Reputation: 166

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

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

Upvotes: 0

jyl_ljy_work
jyl_ljy_work

Reputation: 25

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

My workaround is as follows:

  1. Open /usr/lib/mysql-workbench/modules/wb_admin_export.py
  2. Replace
s = re.match(".*Distrib ([\d.a-z]+).*", output)

with

s = re.match(".*mysqldump  Ver ([\d.]+).*", output)
  1. Replace
cmd = subprocess.list2cmdline(args)

with

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

Upvotes: 2

Hassnain Abbas
Hassnain Abbas

Reputation: 51

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

Upvotes: 2

Nob Hokleng
Nob Hokleng

Reputation: 69

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

Upvotes: 2

user2363969
user2363969

Reputation: 115

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

Upvotes: 10

SharpC
SharpC

Reputation: 7454

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

See my answer here.

Upvotes: 0

JCT1991
JCT1991

Reputation: 51

WINDOWS USERS

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

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

Upvotes: 5

Baptiste
Baptiste

Reputation: 1785

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

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

No option to check !

Upvotes: 1

user3175253
user3175253

Reputation: 598

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)

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

Upvotes: 38

AppSol
AppSol

Reputation: 326

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

Upvotes: 3

Troyd
Troyd

Reputation: 434

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!

Upvotes: 19

Mike Lischke
Mike Lischke

Reputation: 53337

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

Upvotes: 6

Related Questions