MySQL Bugs: #90624: Restaure dump created with 5.7.22 on 8.0.11

Bug #90624	Restaure dump created with 5.7.22 on 8.0.11
Submitted:	25 Apr 2018 10:52	Modified:	25 Oct 2019 13:06
Reporter:	Emmanuel CARVIN	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	8.0.11	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	MySQL Server; Workbench

Description:
Hello

Before going from MySQL version 5.7.22 to version 8.0.11, I dumped all my databases with the workbench. Default option, dump stored procedures, dump event, dump triggers, export to self-contained file, include create schema.

Trying to restore these dumps, I this error : 

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

How to repeat:
Dump database with last version of server and workbench 5.7.x 

Default option, dump stored procedures, dump event, dump triggers, export to self-contained file, include create schema.

Restore on last version of server and workbench 8.0.11

Thank you for the bug report. Which exactly WorkBench are you using (6.3.10)?.

I did my dump with MySQL Workbench 6.3.10
I tried to restore it with MySQL Workbench 8.0.11 (included with the msi of MySQL Community Server 8.0.11)

I think this is easy to undeerstand.

All such bacup tools will d oat the top of the script
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
.. and at the botton 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
(this is fro0m a dump created with SQLyog, but it is basically the same).

'NO_AUTO_CREATE_USER' sql_mode was included in 'strict' modes on MySQL 5.7, but is now removed in 8.0 (as it is no longer possible to create a user with a GRANT statement).  

So a dump created from a pre-8.0 server running a strict sql_mode will raise this error. However it happens after everything is restored (doesn't it?), it is more a cosmetical than a practical issue.

-- Peter
-- not a MySQL/Oracle person

.. however it could be considered to make this error a warning with the sql_modes that have now gone.

Correction: if teh SQL-script has on top
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
.. and at the botton 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
.. it will import. 

But if current sql_mode is not saved to user variable, but rather to memory on client machine and executed as 
/*!40101 SET SQL_MODE='some_specific_mode,some-specific_mode'/;

I don't know WB too well. I think it has an option to use 'mysqldump' but there may be other interfaces for dumping. I am 99.99% sure that dumps with mysqldump (of any recent version) are not afected. as dum ps created with SQLyog are not. But some other interface in WB may be.

Anyway, are you able to check the last 20 lines of the dump how it restores the sql_mode (it may be too big for most Windows text editors)?. Just look for a "SET SQL_MODE ..." statement near the bottom of the script.

One more minor correction: This "NO_AUTO_CREATE_USER' sql_mode was included in 'strict' modes on MySQL 5.7" may only apply if server was installed using the "MySQL Installer for Windows". 

I don't even think that recent versions of the Installer has an option to choose between 'strict' or 'non-stritc' modes any more, but will always use 'strict' or - more precisely - set the sql_mode to "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" (in the my.ini). This is the configuration on my 5.7 instance and I haven't changed anything in configuration in this respect.

This error does not appear in the start and end lines, but in the triggers.

I report it as a bug because it should be a warning especially since it is impossible to update 5.7.22 to 8.0.11 with MySQLCommunity (Windows).

There may be a lot of people who, to go quickly or for lack of knowledge, will do their dump with the workbench 6.3.10 before installing version 8.0.11

Personally I actually modify some of my dumps (ConText), but others were more than 40GB so impossible to open them.

But thank for your answer and your tips

OK .. see this, what I believe is related.

https://dev.mysql.com/doc/refman/5.7/en/create-trigger.html "MySQL stores the sql_mode system variable setting in effect when a trigger is created, and always executes the trigger body with this setting in force, regardless of the current server SQL mode when the trigger begins executing.".

But except for this I will not comment further, as I am close to ignorant about WB. Let the MySQL people handle this.

Minimail testcase: On a 5.7 server with NO_AUTO_CREATE_USER sql_mode do this

CREATE DATABASE ttest;
USE ttest;
CREATE TABLE test (id INT);
DELIMITER $$
CREATE TRIGGER `ttest`.`trg` BEFORE INSERT ON `ttest`.`test`
    FOR EACH ROW BEGIN
	SET @a = 'a';
    END$$
DELIMITER ;

Dump (possibly with a variety of tools/settings/versions/interfaces) the database and check if the CREATE TRIGGER statement gets wrapped in SET SQL_MODE statements. It looks like it is this case (and would in my understanding also be nessessary in order to preserve the SQL_mode for the Trigger after import) . 

But in this case the problem that the SQL_mode for the Trigger is invalid on MySQL 8.

I think that the statement "SET SQL_MODE = 'NO_AUTO_CREATE_USER';" should be allowed to execute but having no effect.

I also wonder what would happen with an upgrade from 5.7 to 8.0? Will the upgrade succeed and will the server start at all if user has triggers affected?

On windows it is not possible to upgrade from 5.7 to 8
You must uninstall the 5.7 server and install version 8

For the moment, all my triggers created in 5.7 are functional on version 8.

'mysqldump' here from a 5.7 server running 'NO_AUTO_CREATE_USER' sql_mode.

-- MySQL dump 10.13  Distrib 5.7.22, for Win64 (x86_64)
--
-- Host: localhost    Database: ttest
-- ------------------------------------------------------
-- Server version	5.7.22-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test`
--

LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_AUTO_CREATE_USER' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `ttest`.`trg` BEFORE INSERT ON `ttest`.`test`

    FOR EACH ROW BEGIN

	SET @a = 'a';

    END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-04-26 13:16:13

.. so indeed CREATE TRIGGR is wrapped in SET SQL_MODE statements. And it fails on MySQL 8.0

That's exactly the problem I had 
Thanks

Hello Emmanuel,

Thank you for the bug report.
Verified this behavior on MySQL Workbench in 8.0.11 version.

Thanks,
Chiranjeevi.

Screenshot

Attachment: Bug_90624.PNG (image/png, text), 31.96 KiB.

Peter, excellent analysis! Many thanks for spending the time to take that close look - much appreciated.

Thanks, Mike, but sometimes you get interested!

This is not a Workbench bug. The same script/dump will fail with any tool - including commandline.

This is a (bad) oversight by the server team when they decided to remove the NO_AUTO_CREATE_USER sql_mode. It prevents upgrading from 5.7 to 8.0 for users using this sql_mode (what is practically all Windows users, but not only) and who also has triggers. And as such it should be marked as a server bug.

If the old 5.7 instance has been taken down, then it may with a large dump (we are talking of dozens of GBs much too big for a common text editor, possibly) be tricky to remove the offending SET SQL_MODE statements from the script, so that the script can run.  

Also note that though table DDL and data will be imported when the error occurs, but routines will not (for 2 reasons 1) routines they come after triggers in a 'mysqldump' and 2) Also a routine definition is wrapped in SET SQL_MODE statements as show below.).

A simple stored procedure as it looks in a 'mysqldump --routines'
..
-- Dumping routines for database 'pltest'
--
/*!50003 DROP PROCEDURE IF EXISTS `ssp` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; - < -- offending statement again here
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ssp`()
BEGIN
        -- do nothing
        END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
..

Posted by developer:
 
This bug is related to mysqldump, hence must be fixed there.

I believe that the best solution would be that setting sql_mode to NO_AUTO_CREATE_USER should either be ignored by the server or raise a warning only.

I had the same problem, on a Windows machine. Incidentally, the sql_mode problem also appears around exported procedures and functions. I devised this workaround which worked for me:

1. Use Workbench to export the database structure from 5.7 without data, but including triggers, routines and events. This produces a small, editable file.
2. Edit the file in Notepad to replace all instances of ",NO_AUTO_CREATE_USER" with nothing. That takes it out of the SET sql_mode... statements while leaving the rest intact.
3. Using Workbench, import the structure to the new 8.0 instance.
4. Using Workbench or mysqldump directly, export data only from 5.7.
5. Import the data to 8.0.

I hope that helps.

Posted by developer:
 
Fixed in 5.7.24, 8.0.13.

Stored program definitions in mysqldump dump files sometimes included
the NO_AUTO_CREATE_USER SQL mode. Because that mode has been removed
in MySQL 8.0, loading such a dump file into a MySQL 8.0 server
failed. mysqldump now removes NO_AUTO_CREATE_USER from the definition
of dumped stored programs.

It is still not fixed in MySQL 8.0.13, performed following steps to re-produce

mysqldump -uuser -p dbname --routines --events --triggers --single-transaction dbname --column_statistics=0 >backupfile.sql

and while re-loading this file on to MySQL 8.0.13 with below command faced " Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' "

mysql -uuser -p dbname <backupfile.sql

Fixed.

Create a dump with Workbench 8.0.13 on 5.7.24 MySQL's server (database with trigger, stored function etc ...).
Uninstall 5.7.24 MySQL's server.
Reboot.
Install Workbench 8.0.13 and 8.0.13 MySQL's server.
Restore dump on 8.0.13 MySQL's server.

I am using mysql work bench 8.0.16 in mac os to export data of mysql community sever 5.7.23 and trying import it to mysql community server 8.0.16 using mysql work bench 8.0.16 . I am still facing the error Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

I am moving from a MySQL 5.7.18 to a MySql 8.0.16 and I use mysqldump to extract the data and then re-import it on the new database. Unfortunately I get the error: "ERROR 1231 (42000) at line 108: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'". As the dump is very larde editing the file with data is not an option. I may be able to divide the dump into one with no data, and manually modify this, and then one with only data, but it is too bad it was not considered so one does not have to do this. So, I would say the issue has not been fixed yet.

Hi,

With the latest version of Workbench 8.0.17 with 5.7.27 MySQL's version, the dump process does not work anymore...  

while with the Workbench 8.0.13 with 5.7.24 MySQL's version worked as I said the 30 Oct 2018 12:56...

But work with command line : 

mysqldump -u root -h 127.0.0.1 -P 50003 -p --default-character-set=utf8 --routines --events --triggers --single-transaction --skip-lock-tables --databases ilap_v6_atf ilap_v6 > "C:\Partage\Dump_ilap_v6.sql"

Please see 

Bug#97954 Using Mysqldump 8 on 5.7 server prints invalid "NO_AUTO_CREATE_USER"

for the follow-up bug report.

The problem still persists on the latest version. Trying to go from 5.7.27 to 8.0.20, and I get the same error.

Posted by developer:
 
The following documentation changes were made:

1) 5.7 reference manual under mysqldump's restrictions now includes:

mysqldump always strips the NO_AUTO_CREATE_USER SQL mode as
NO_AUTO_CREATE_USER is not compatible with MySQL 8.0. It remains stripped
even when importing back into MySQL 5.7, which means that stored routines
could behave differently after restoring a dump if they rely upon this
particular sql_mode. It is stripped as of mysqldump 5.7.24.

2) The 8.0.13 release note was removed, only 5.7.24 remains

3) The 5.7.24 release note was updated to include:

  MySQL 5.7 limitation: it remains stripped even when importing
  back into MySQL 5.7, which means that stored routines could
  behave differently after restoring a dump if they rely upon
  this particular sql_mode.

Thanks to Scott for providing all of this information.