MySQL Bugs: #91640: Workbench 8 cannot export from MySQL 5.7

Bug #91640	Workbench 8 cannot export from MySQL 5.7
Submitted:	14 Jul 2018 21:49	Modified:	19 Sep 2018 21:44
Reporter:	Nicolai Cornelis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Administration	Severity:	S3 (Non-critical)
Version:	8.0.11, 8.0.12	OS:	MacOS
Assigned to:		CPU Architecture:	Any

Description:
Hello

I had some issues with exporting emojis when using utf8mb4, so I tried downloading the latest RC of Workbench because a different bug report stated this had been fixed.

However, Workbench 8 can't export from MySQL 5.7 at all, which I am under the impression that it should be able to.

The problem is that Workbench does not disable COLUMN_STATISTICS which don't seem to exist in 5.7, and there is not option to disable it under Advanced Options when exporting.

The error is:

Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'databasebame' AND TABLE_NAME = 'firsttablename';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

How to repeat:
Install MySQL 5.7 and Workbench 8.0

Use the Data Export tool to export any database.

Suggested fix:
Add an option to disable column statistics, such that mysqldump can be called with --column-statistics=0 which should fix this problem. Or make Workbench detect MySQL versions that don't have this option and add it automatically.

Suggesting that I install MySQL 8 is not an option, since the data that needs exporting and has emojis (the reason for using Workbench 8) resides in 5.7 server.

Hello Nick Cornelis,

Thank you for the bug report.
I could not repeat the issue at our end using with MySQL workbench 8.0.11 version.
Could you please provide repeatable steps (exact steps, dump file, screenshots etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

Hello

Install MySQL 5.7.22.

Create any database with any content.

Connect to the instance with MySQL Workbench 8.0.11 for Mac.

Use Data Export tool and select the database you've created.

Observe the error as originally posted. I just tried this on a clean install of MySQL 5.7.22:

mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'new_table';': Unknown table 'column_statistics' in information_schema (1109)

Operation failed with exitcode 2

Same problem for me, database server which are lower than 8.0 can't export schema with error (see capture attached)

mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'alphavisa' AND TABLE_NAME = 'abstract';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

Tested with WB 8.0.12

Error returned while dumping mysql 5.5 server database

Attachment: Sélection_469.jpg (image/jpeg, text), 67.85 KiB.

Mysql version used

Attachment: Sélection_470.jpg (image/jpeg, text), 20.44 KiB.

Mysql 5.5 Server status

Attachment: Sélection_471.jpg (image/jpeg, text), 80.59 KiB.

Thank you for the feedback.
Verified as described with WB 8.0.12.

13:36:33 Dumping test (keyvalue)
Running: /Applications/MySQLWorkbench.app/Contents/MacOS/mysqldump --defaults-file="/var/folders/y6/c311gthd0h9277vr1b__t6x40000gn/T/tmpR8Z3PU/extraparams.cnf"  --user=ushastry --host=hod03.no.oracle.com --protocol=tcp --port=3333 --default-character-set=utf8 --skip-triggers "test" "keyvalue"
mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'keyvalue';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

Operation failed with exitcode 2
13:36:43 Export of /Users/umshastr/dumps/Dump20180808 (1) has finished with 1 errors

Posted by developer:
 
See also: https://stackoverflow.com/questions/51614140/how-to-disable-column-statistics-in-mysql-8-p..., where a number of workarounds are presented.

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 8.0.13 release, and here's the changelog entry:

An error with the following message was generated when the export
operation was executed on tables from an earlier server version: Unknown
table 'COLUMN_STATISTICS' in information_schema (1109). 

Because the version of mysqldump used with the operation can differ from that of the
target server, some features may not be exported as expected when the
versions are mismatched. A warning message now provides a description of
the condition, along with instructions to resolve the version mismatch, or
the option to continue anyway.

Thank you for the bug report.

I'm using 8.0.13 and still get this error.

Actually we can use an advanced option in Workbench to disable column statistics as per https://stackoverflow.com/a/52944315/1694902 - see below:

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)

According to the author of this post, "Unfortunately, you'll have to do that every time you start MySQL Workbench."

Not the best solution, but it works.
 Donwgrade Workbench to v.6.3.10 which is available in https://downloads.mysql.com/archives/workbench/

Now the backup works with no problem

Hope it can helps to all with the same problem.

Thanks Carlos Lopez. I used your link and downloaded that version of MySQLWorkbench.  Then, when I mounted the .dmg file, I right-clicked on that and selected Show Package contents.  I then navigated to Contents/MacOS and copied the 'mysqldump` to my home folder (where I have a 'bin' folder).

Then, in MySQLWorkbench, i edited the Preferences and pointed it at my version of mysqldump.  

Export worked perfectly.  I didn't have to make an alias and set flags, etc.

This bug needs to be reopened, because the option has disappeared in Workbench 8.0.14.

Workbench 8.0.13 added an advanced option to disable the column-statistics behavior, which is fine. Even though it's weird that one has to enter "0" to disable. It should be a checkbox, not a text field, because the option is either on, or skipped (corresponding to the --skip-column-statistics flag of mysqldump).

Althought this thread specifies MacOS, I had the issue on a windows machine running 8.0.12.  Tried downgrading to Workbench 6.3 and this works fine, just not an ideal solution.  Noted today that Workbench 8.0.15 is on general release which I have installed and can report is now working correctly.

Update to last post:  Sadly 8.0.15 does have the same problem after all.

Confirmed the problem persists can not  export and  the option  has gone
mysql:5.7  Workbench 8.0.15
Regards

I have downgraded to version 8.0.13 ,and  now the  option "columns statistic.." appears. I was able to  backup  my  database 5.7
Regards

https://bugs.mysql.com/bug.php?id=94294 marked as duplicate of this one.

How do we get this re-opened for 8.0.15? It has regressed.

This bug was reported again in https://bugs.mysql.com/bug.php?id=94294 and it sounds like it will be attended to.

Yep, I reported 94294. :)