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 OS:Linux
Assigned to: CPU Architecture:Any

[27 Feb 2018 13:07] Frederic Descamps
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
[27 Feb 2018 13:21] MySQL Verification Team
Hello Frederic,

Thank you for the report!

Thanks,
Umesh
[30 May 2018 11:47] Den ____
seems same issue in 8.0.11
[31 May 2018 10:47] Michael Law
This is still an issue for me as well.
[1 Jul 2018 17:32] Reinaldo Rei
I have the same issue
[31 Jul 2018 12:07] Flo Bayer
Same here with 8.0.12
[23 Aug 2018 2:34] Pierre Caron
Same problem here where I tried to export the date from MySql Workbench.
Is there a workaround in Workbench?
[23 Aug 2018 15:17] LEANDRO lcferreira
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
[24 Oct 2018 14:34] Frederic MEYER
Sadly enough, still an issue with 8.0.13
[24 Oct 2018 14:46] Frederic MEYER
workaround in 8.0.13

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

[26 Jun 2019 7:51] MySQL Verification Team
Bug #95992 marked as duplicate of this one
[29 Aug 2020 6:14] MySQL Verification Team
Bug #100686 marked as duplicate of this one.
[27 Oct 2020 7:43] MySQL Verification Team
Bug #101332 marked as duplicate of this one
[27 Oct 2020 10:32] Simon Mudd
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
[28 Oct 2020 19:18] Simon Mudd
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.
[15 Feb 22:11] Erik Kratzenberg
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.