MySQL Bugs: #89825: 8.0 mysqldump cannot dump 5.x by default

Bug #89825	8.0 mysqldump cannot dump 5.x by default
Submitted:	27 Feb 2018 13:07	Modified:	27 Feb 2018 13:21
Reporter:	Frederic Descamps	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	8.0.4, 8.0.16, 8.0.18, 8.0.21, 8.0.25	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Trying to dump a database from 5.5 using mysqldump of 8.0.4, it fails because it tries to check for histograms. However if I check in the documentation, column statistics should be disabled by default (https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_column-statistics)

How to repeat:
mysqldump -h relational.fit.cvut.cz --column-statistics -u guest -prelational imdb_ijs actors > actors.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'imdb_ijs' AND TABLE_NAME = 'actors';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

workaround: disable column-statistics using --column-statistics=0

[root@mysql2 datas]# mysqldump -h relational.fit.cvut.cz --column-statistics=0 -u guest -prelational imdb_ijs actors > actors.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

Suggested fix:
Update the documentation according or disable column-statistics by default

Hello Frederic,

Thank you for the report!

Thanks,
Umesh

seems same issue in 8.0.11

This is still an issue for me as well.

I have the same issue

Same here with 8.0.12

Same problem here where I tried to export the date from MySql Workbench.
Is there a workaround in Workbench?

Workbench:

Select menu Edit->preferences->administration. Add mysql information:

path to mysqldump tool: /usr/bin/mysqldump
path to mysql tool: /usr/bin/mysql

I use ubuntu 18.04

Sadly enough, still an issue with 8.0.13

workaround in 8.0.13

Attachment: Screenshot 2018-10-24 at 16.44.48.png (image/png, text), 236.71 KiB.

Bug #95992 marked as duplicate of this one

Bug #100686 marked as duplicate of this one.

Bug #101332 marked as duplicate of this one

Workaround:
* use --force but this may then hide other issues

Suggested fixes (taken from my duplicate bug report):

I think that the mysqldump code should:
* be aware of the mysql server version it is talking to and only query this information if present (and needed).
* not query the column-statistics if --column-statistics is used as you do not need this informtation
* provide an explicit --no-column-statistics option to avoid mysqldump reading the statistics from the server

Also improve documentation:
* to describe using mysqldump 8.0 against a non-8.0 version, as this is likely to be used when people test prior to upgrading to a newer version
* to show how mysqldump 8.0 can be used safely against a 5.x server

An additional issue I see is that even if doing the dump (on a 5.7 db using mysqldump 8.0) with --force you get a file to load in.

That file may not load cleanly due to 5.7 to 8.0 differences, so the mysqldump 8.0 should possibly filter some of what it does, or comment it or something.

I saw this error trying to load in the dump into an 8.0 server (without using mysql -f to force the load to continue).

mysql (without -f) errors out on:

ERROR 1231 (42000) at line 962: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

This is triggered by the following part of the dump captured with
$ zgrep -2 NO_AUTO_CREATE_USER db.dmp.gz

--
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER db.some_trigger_delete
--
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 Trigger db.some_trigger_insert
--

The reason: sql_mode NO_AUTO_CREATE_USER no longer exists in 8.0 as it is now default behaviour.

Conclusion: mysqldump 8.0 should be aware of this and able to handle it.
Otherwise using mysqldump -f ..... | mysql -f .... to copy data from 5.7 to 8.0 is going to be very likely to silently go wrong.
As users we really don't want this.

Still an issue in 8.0.23.  If the intent is for this to be on by default, then that's fine but documentation should be updated to reflect that.  Otherwise, compile-time default needs updated to disable.

Still an issue in 8.0.25.

This has been an issue since forever, it's been notified many times, although devs say it was fixed, it was not.

The option to disable statistics is not present in the advanced options tab in 8.0.25.

Bug #104672 marked as duplicate of this one

I'd like to re-emphasize the suggestion that mysqldump should check the MySQL server version/features/capabilities. This way it can make sure to only use features actually supported by the MySQL server.

In the simplest case this detection would be an alternative trigger for already existing CLI flags like "--column-statistics=0" so no major code changes should be necessary.