Can't export my database from mysql workbench

Question

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

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. https://bugs.mysql.com/bug.php?id=89825. — Travelling Steve, Jun 27 '18 at 20:13
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, Apr 30 '20 at 11:25

SharpC · Answer 1 · 2021-10-09T09:57:06.547

64

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

edited Oct 09 '21 at 09:57

answered Jan 30 '20 at 09:33

SharpC

6,974
4
45
40

1

mine doesnt have a `skip_column_statistics` im using MySql workbench 8.0 CE – draw134 May 28 '20 at 15:15
Great, now how would we do this on mac? Where can we find that file.... anyone know? – Christopher Smit Sep 28 '20 at 16:59
fixed my problem working on Workbench version 8.0.23 with Windows. – Michael Staples Apr 09 '21 at 08:09
worked for me. I'm using mysql workbench 8.0.26 with ubuntu 20.04. target DB mysql version 10.1.48 – M.Siri Sep 07 '21 at 18:30
This worked for me but for a different reason - adding the path also worked but I then got an error regarding a variable tz=utc that I could not work around. I switched to the default mysqld.exe in workbench and made the config change and no longer get this error – ChumKui Feb 20 '23 at 18:14

score 55 · Answer 2 · edited Apr 03 '19 at 07:59

55

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.

edited Apr 03 '19 at 07:59

rooby

721
13
23

answered Nov 14 '18 at 10:18

Artem

675
5
11

4

This is now the correct answer based on the fact that it matches the instructions that were added to the pop-up warning you get when trying to do an export, which was added in MySQL Workbench 8.0.13. – rooby Apr 03 '19 at 03:19
1

For me this was in 'C:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysqldump.exe' However, this did not fix my error. – Jeremy Taylor May 19 '20 at 21:18
this fixed the error for me, path is the location where you have installed "mysql/bin/mysqldump.exe" – bvk Dec 22 '20 at 07:05
awesome solution – Veerendar Chary Munigadapa Mar 01 '21 at 11:27
`C:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysqldump.exe` didnt work for me. Found the solution @JeremyTaylor ? – Shaho Jul 05 '22 at 12:08

score 48 · Accepted Answer · edited Oct 24 '18 at 12:01

48

In MySql Workbench version 8.0.13 do the following steps:

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

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

edited Oct 24 '18 at 12:01

TheEdge

9,291
15
67
135

answered Oct 23 '18 at 08:21

Sander Bouwhuis

647
7
8

This should be the accepted answer. It worked for me. It is also referenced on the [MySQL bug tracker](https://bugs.mysql.com/bug.php?id=91640). – ThatGuyRob Nov 12 '18 at 23:25
18

My Workbench doesn't have this option, maybe because the production database it's on 5.x version and I'm using 8.x. Is there some way to activate something like a compatibility mode between mysqldump.exe 8.x and MySQL Server 5.x? – Douglas Ferreira Mar 07 '19 at 19:05
3

It seems this is not an option in the latest versions. The bug tracker issue that ThatGuyRob mentions says that they added a pop-up warning with instructions on how to fix it, which is described in Artem's answer. – rooby Apr 03 '19 at 03:22
2

As explained in [this other MySQL bug report](https://bugs.mysql.com/bug.php?id=94294), this issue for WorkBench 8.0.14 & 8.0.15 will be fixed in version 8.0.16 – Veve Apr 24 '19 at 14:14
1

As described by @Veve it is a not fixed issue, In Mac my solution was to downgrade the WorkBench to version v.6.3.10 and it works ok, I hoe this help. – hermeslm Sep 16 '19 at 16:03
1

This is another workaround, if you don't see the "Other/column statistics" menu item: https://stackoverflow.com/a/55121256/914352 – NMrt Jan 29 '20 at 06:14
As another workaround for new versions of Workbench, you can apply this patch easily https://serverfault.com/a/1015459 – Juanan Oct 14 '20 at 17:42

score 36 · Answer 4 · edited Mar 10 '21 at 13:34

36

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.

edited Mar 10 '21 at 13:34

SharpC

6,974
4
45
40

answered Sep 20 '18 at 10:26

Amitesh Bharti

14,264
6
62
62

1

Life saver! Thanks :) – Alex Morris Mar 03 '20 at 23:24
1

This is better solution when you're using `mysqldump` to backup a database and the error pops up. – Tuhin Jan 18 '21 at 16:00

score 16 · Answer 5 · answered Jul 29 '19 at 12:05

16

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.

answered Jul 29 '19 at 12:05

Wolfram

161
1
2

This worked for me in version 8.0.22, but very surprisingly it only worked when I edited the existing line itself. When I made a duplicate and edited the duplicate (with the original one commented), Workbench wasn't displaying anything in the Navigator (left sidebar). Odd behavior, nothing helped except editing the original line. I'm suspecting that Workbench parses its own .py files for some reason and chokes when it finds its variables in an unexpected way, even if that's in a comment. – TXN Nov 27 '20 at 22:45

score 11 · Answer 6 · answered Jun 27 '18 at 12:44

11

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.

answered Jun 27 '18 at 12:44

Matt Komarnicki

5,198
7
40
92

score 10 · Answer 7 · answered Dec 18 '18 at 06:46

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:

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

Now Return and Export the Data. Thank You

score 8 · Answer 8 · answered Nov 08 '19 at 20:16

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

score 7 · Answer 9 · answered Feb 25 '19 at 15:03

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"

Nob Hokleng · Answer 10 · 2021-01-21T06:20:45.133

4

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.

edited Jan 21 '21 at 06:20

answered Nov 16 '20 at 09:16

Nob Hokleng

69
5

Running: mysqldump --defaults-file="/tmp/tmp5c5ww33n/extraparams.cnf" --host=x.x.x.x --port=3307 --default-character-set=utf8 --user=admin --protocol=tcp --delete-master-logs=TRUE --skip-triggers "fedena" mysqldump: Couldn't execute 'SELECT COLUMN_NAME, .................. Operation failed with exitcode 2 Export of b'/home/sanaulla/dumps/Dump20210331.sql' has finished with 1 errors – Sanaulla Mar 31 '21 at 08:58
This one helped me. Thanks – Archulan Rajakumaran Dec 06 '21 at 08:17
This didn't work for me since my user does not have the right privileges. Still, it is worth a try. – Joachim Rives Jul 20 '22 at 03:18

score 2 · Answer 11 · edited Nov 08 '19 at 19:31

2

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

edited Nov 08 '19 at 19:31

Michel Feinstein

13,416
16
91
173

answered Oct 23 '19 at 06:16

Mohd. Shaizad

91
1
4

can't see any `--column-statistics=0` commented on that file – Michel Feinstein Nov 08 '19 at 19:35

score 2 · Answer 12 · answered Jan 06 '21 at 11:40

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 2 · Answer 13 · answered Mar 27 '21 at 23:18

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/

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

score 1 · Answer 14 · answered Jun 19 '20 at 14:16

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

this for MacOS wb_admin_export.py found @ /Applications/MySQLWorkbench.app/Contents/Resources/plugins — chunkiat, Jun 19 '20 at 14:18

score 0 · Answer 15 · edited Aug 20 '20 at 23:33

0

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

edited Aug 20 '20 at 23:33

SAUMYA

1,508
1
13
20

answered Aug 20 '20 at 07:48

Oleksandr Hrin

757
1
10
12

score 0 · Answer 16 · answered Sep 24 '21 at 22:10

If you are using windows with XAMPP, you need to indicate the path through XAMP. Do the following:

In your MySQL Workbench: Go to edit -> preferences -> administration under "Path to mysqldump tool" enter the path: C:\xampp\mysql\bin\mysqldump.exe then click ok.

Can't export my database from mysql workbench

16 Answers16

Linked