49
votes

I am trying to export my database from MySQL Workbench but I get this during the export progress:

Running: mysqldump.exe --defaults-file="c:\users\user\appdata\local\temp\tmp2h91wa.cnf" --user=root --host=localhost --protocol=tcp --port=3306 --default-character-set=utf8 --skip-triggers "mydb" mysqldump: Couldn't execute 'SELECT COLUMN_NAME,
JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')
FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'mydb' AND TABLE_NAME = 'courses';': Unknown table 'column_statistics' in information_schema (1109)

Operation failed with exitcode 2 20:55:09 Export of C:\Users\user\Documents\dumps\mydb.sql has finished with 1 errors

15
This seems to be a problem with the new MySQL Workbench (8.x) trying to export from earlier versions of MySQL databases (5.x). I'm having the same issue and can't see a workaround within the MySQL Workbench tool. There doesn't seem to be an option to set column_statistics to 0 in the GUI. bugs.mysql.com/bug.php?id=89825. - Travelling Steve
Looks like it's fixed in version - 8.0.17 - Scofield
This bug seems to be fixed in Workbench 8.0.20, at least updating Workbench on Ubuntu 20.04 removed the error for me. - jockef

15 Answers

47
votes

In MySql Workbench version 8.0.13 do the following steps:

  1. Go to Management/Data export
  2. Choose the schema to export in the 'Tables to export' list
  3. Click the 'Advanced Options...' button (top right)
  4. Search for the option 'Other/column-statistics'
  5. Set the value to 0
  6. Click the 'Return' button (top right)

Now it should work. Unfortunately, you'll have to do that every time you start MySql Workbench.

43
votes

Also ran into this problem. Decided as follows: In the Workbench menu, go to:

Edit - Preferences - Administration

In the field "Path to mysqldump Tool", prescribe the path to mysqldump.exe, in my case "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe", click OK.

After that, the error no longer appeared.

33
votes

It is due to a flag that is by default "enabled" in mysqldump 8.

That can be disabled by adding --column-statistics=0.

Syntax :

mysqldump --column-statistics=0 --host=<server> --user <user> --password <securepass> 

For more info please go to this link.

To disable column statistics by default, you can add:

[mysqldump]
column-statistics=0

to a MySQL config file, such as /etc/my.cnf or ~/.my.cnf.

30
votes

To summarize what I did from the helpful comments of @JustinLaureno and @Mohd.Shaizad, tested on MySQL Workbench 8.0.18:

  • Navigate to C:\Program Files\MySQL\MySQL Workbench 8.0 CE\modules
  • Edit the file wb_admin_export.py (you need admin permissions for this)
  • amend the line:
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
  • to:
skip_column_statistics = True
  • DO NOT add inline comments or it won't work!
 skip_column_statistics = True # This won't work
  • Restart MySQL Workbench
  • Perform the export
14
votes

Bug still in Workbench 8.0.16.

Fix:

You can edit wb_admin_export.py under modules in the workbench program directory. Search for "skip_column_statistics = True" (you will find a conditional, don't worry), comment that line and add a line "skip_column_statistics = True" (without a conditional).

The required parameter will now be always added.

10
votes

I had the same issue 5 minutes ago.

I fixed it by adding in my mysqldump command --column-statistics=0. Do it and it should work.

In my case it's a phing task but you should get the idea.

enter image description here

10
votes

I too had the same problem.. I am able to resolve this Issue by disabling the column-statistics in the advanced options of the MySQL Workbench Data Export.

1: Click on the advanced options: enter image description here

2: In the other section for the column-statistics remove TRUE and set it to 0 to disable it. enter image description here

Now Return and Export the Data. Thank You

7
votes

I had the same problem and I solved it like this:

edit the workbench preferences: Edit -> Preferences -> Administration

in the property "Path to mysqldump Tool" place the path of your mysqldump.exe It is usually found in "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe"

2
votes

Go to C:\Program Files\MySQL\MySQL Workbench 8.0 CE\modules and open this file wb_admin_export.py and uncomment "--column-statistics=0" then Restart the workbench

2
votes

I found this condition in wb_admin_export.py instead of a commented --column-statistics=0. you can remove the else False condition, or change it to else True.

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

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.

1
votes

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: Mysql-workbench 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.
1
votes

On MACOS, just downgrade to version 8.0.13, that's the only thing did the job for us.

The following link can help

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

MacOS MySQL Work Bench 8.0.13

If you are using SSH key to access remote database then do the following -:

Step 1

brew install putty

Step 2

puttygen id_rsa -O private-openssh -o id_rsa.pem

Step 3 - In MySQL workbench

SSH Key File: /Users/local/.ssh/id_rsa.pem

Hope it helps someone because it wasted 3 hours of our time :)

0
votes

in version 8, I modified "wb_admin_export.py" and restart workbench. works for me

def start(self):
.
.
.
    title = "Dumping " + schema
    title += " (%s)" % table
    # description, object_count, pipe_factory, extra_args, objects
    args = []
    args.append('--column-statistics=0')
class ViewsRoutinesEventsDumpData(DumpThread.TaskData):
    def __init__(self, schema, views, args, make_pipe):
        title = "Dumping " + schema + " views and/or routines and/or events"
        if not views:
           extra_args = ["--no-create-info"]
        else:
            extra_args = []
        DumpThread.TaskData.__init__(self,title, len(views), ["--skip-triggers", " --no-data" ," --no-create-db", "--column-statistics=0"] + extra_args + args, [schema] + views, None, make_pipe)```
0
votes

You can use native MySQL Workbench "Migration wizard" to migrate data without errors. It can be found in menu Database -> Migration Wizard It can transfer data "online" but I didn't found an option to create a dump file with it. It is a pretty good solution for migrations