Reputation: 721

Can't export my database from mysql workbench

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

Upvotes: 68

Answers (17)

Genki

Reputation: 489

I just upgraded MySql Workbench to version 8.0.32 (Community Edition) and it solves the problem. It issues a warning, but performs the backup.

Upvotes: 0

SharpC

Reputation: 7454

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

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

Upvotes: 70

Pierre Vieira

Reputation: 3300

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.

Upvotes: 0

user63323

Reputation: 61

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

Upvotes: 2

Amitesh Bharti

Reputation: 15713

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.

Upvotes: 36

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

Upvotes: 4

Hany Sakr

Reputation: 2929

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.

Upvotes: 2

Oleksandr Hrin

Reputation: 886

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

Upvotes: 0

chunkiat

Reputation: 11

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

Upvotes: 1

Justin Laureano

Reputation: 91

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

Upvotes: 9

Mohd. Shaizad

Reputation: 99

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

Upvotes: 2

Wolfram

Reputation: 161

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.

Upvotes: 16

Artem

Reputation: 715

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.

Upvotes: 59

Yosbel Santana

Reputation: 71

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"

Upvotes: 7

Sunil Valmiki

Reputation: 616

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: