Bug #97954 Using Mysqldump 8 on 5.7 server prints invalid "NO_AUTO_CREATE_USER"
Submitted: 10 Dec 2019 20:51 Modified: 11 Dec 2019 6:23
Reporter: Carlos Tutte Email Updates:
Status: Verified 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] Umesh Shastry
Hello Carlos Tutte,

Thank you for the report.

regards,
Umesh