Bug #90624 Restaure dump created with 5.7.22 on 8.0.11
Submitted: 25 Apr 10:52 Modified: 30 Oct 12:56
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:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: MySQL Server; Workbench

[25 Apr 10:52] Emmanuel CARVIN
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
[25 Apr 11:47] Miguel Solorzano
Thank you for the bug report. Which exactly WorkBench are you using (6.3.10)?.
[25 Apr 13:54] Emmanuel CARVIN
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)
[25 Apr 14:03] Peter Laursen
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
[25 Apr 14:05] Peter Laursen
.. however it could be considered to make this error a warning with the sql_modes that have now gone.
[25 Apr 14:22] Peter Laursen
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.
[25 Apr 14:32] Peter Laursen
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.
[25 Apr 15:28] Emmanuel CARVIN
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
[25 Apr 16:12] Peter Laursen
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.
[25 Apr 17:42] Peter Laursen
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.
[26 Apr 8:27] Peter Laursen
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?
[26 Apr 8:42] Emmanuel CARVIN
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.
[26 Apr 9:04] Peter Laursen
'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
[26 Apr 9:09] Emmanuel CARVIN
That's exactly the problem I had 
Thanks
[26 Apr 11:03] Chiranjeevi Battula
Hello Emmanuel,

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

Thanks,
Chiranjeevi.
[26 Apr 11:04] Chiranjeevi Battula
Screenshot

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

[27 Apr 7:10] Mike Lischke
Peter, excellent analysis! Many thanks for spending the time to take that close look - much appreciated.
[27 Apr 12:44] Peter Laursen
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 */;
..
[27 Apr 13:43] Marcin Szalowicz
Posted by developer:
 
This bug is related to mysqldump, hence must be fixed there.
[27 Apr 13:48] Peter Laursen
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.
[19 Jun 15:44] David Webb
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.
[3 Jul 15:17] Paul Dubois
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.
[26 Oct 10:08] Muhammad Nawaz Sohail
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
[30 Oct 12:56] Emmanuel CARVIN
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.