Bug #97954 Using Mysqldump 8 on 5.7 server prints invalid "NO_AUTO_CREATE_USER"
Submitted: 10 Dec 2019 20:51 Modified: 20 Jul 2020 8:45
Reporter: Carlos Tutte Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:8.0.17, 8.0.18 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[10 Dec 2019 20:51] Carlos Tutte 
Description:
When taking a backup using mysqldump 8 of a 5.7 server which contains stored procedure, the dump contains:

/*!50003 SET sql_mode              = 'NO_AUTO_CREATE_USER' */ ;

which is an invalid line, as when trying to restore the backup on a MySQL 8 instance fails because that sql_mode was deprecated. Error when restoring is:

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

How to repeat:
-- On a 5.7 server execute

CREATE DATABASE test;
USE TEST;
set session sql_mode = 'NO_AUTO_CREATE_USER';

DELIMITER //
CREATE PROCEDURE migrate_rows()
BEGIN
SET @x = 500 ;
WHILE (@x < 262144) DO
  start transaction ;
    insert into joinit2 select * from joinit where i between @x and @x + 499 ;
  commit ;
  set @x = @x + 500 ;
END WHILE ;
END //
DELIMITER ;

-- now on a separate session/server with mysqldump 8 execute

mysqldump -u root -psekret  --single-transaction --routines -h <server 5.7 IP> test > mysqldump.sql

-- Check:
$ cat mysqldump.sql | grep NO_AUTO_CREATE_USER
/*!50003 SET sql_mode              = 'NO_AUTO_CREATE_USER' */ ;

Suggested fix:
As mentioned on bug report https://bugs.mysql.com/bug.php?id=90624 

either 
1) don't write "NO_AUTO_CREATE_USER" on mysqldump output
or
2) ignore "NO_AUTO_CREATE_USER" when restoring the backup
[11 Dec 2019 6:23] MySQL Verification Team 
Hello Carlos Tutte,

Thank you for the report.

regards,
Umesh
[20 Jul 2020 8:45] Erlend Dahl 
Posted by developer Bharathy X Satish
=====================================

Running 8.0 mysqldump against 5.7 server is not correct. 8.0 mysqldump can
internally run queries which might not be correct on 5.7 server. Thus it is
not a valid test scenario.

ex: When i run 8.0.20 mysqldump against 5.7.30 i get this below error:

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 = 't';': Unknown table 'COLUMN_STATISTICS' in
information_schema (1109)
 
Also if 8.0 mysqldump when run against 5.7 server and if we strip the
sql_mode from dump file, later if this dump file is run against 5.7 server
then it is not correct as 5.7 server can recognise NO_AUTO_CREATE_USER
sql_mode.
 
Thus closing this bug as not a bug.
[16 Sep 2020 1:37] Erick Franco 
You can use mysqldump 8.0 on a 5.7 database by turning off column statistics, --column-statistics=0, but even then the problem persists.