MySQL Bugs: #92960: Backup Restoration on MySQL 8.0.13 using mysqldump & mysql

Bug #92960	Backup Restoration on MySQL 8.0.13 using mysqldump & mysql
Submitted:	26 Oct 2018 10:21	Modified:	29 Oct 2018 6:27
Reporter:	Muhammad Nawaz Sohail	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: mysqlpump Command-line Client	Severity:	S2 (Serious)
Version:	8.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	MySQL, MySQL 8.0.13, mysqldump

Description:
With reference to https://bugs.mysql.com/bug.php?id=90624 , it was mentioned that this will be fixed in MySQL 8.0.13, but this is still not fixed, 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

How to repeat:
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

Hello Muhammad,

Thank you for the report.
I quickly tried this with the test case from the base bug#90624 and confirmed that logical dump taken from 5.7.24 environment don't have "NO_AUTO_CREATE_USER" in it.  If you are using mysqldump from < MySQL 5.7.24 version then I'm afraid issue will be there as it is fixed in 5.7.24/8.0.13. Please confirm!

-
cat backupfile.sql
-- MySQL dump 10.13  Distrib 5.7.24, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: ttest
-- ------------------------------------------------------
-- Server version       5.7.24

/*!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              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
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 */ ;

--
-- Dumping events for database 'ttest'
--

--
-- Dumping routines for database 'ttest'
--
/*!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              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ssp`()
BEGIN

        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-10-26 12:35:41

thanks,
Umesh

We took mysqldump using the binaries of MySQL 8.0.13 and MySQL version on source is MySQL 5.7.23.
Is there any work around or configuration change which can help restoring a mysqldump from <5.7.24 and what about versions in MySQL 5.6 and MySQL 5.5? do they have to be upgraded first to MySQL 5.7.24 in that case? is there any switch in mysqldump which could exclude "NO_AUTO_CREATE_USER" in resultant backup?

Thank you for the feedback.
I discussed internally with the Developer who fixed base bug#90624, confirmed that  if dump is taken on older version less than 5.7.24 and try to restore on 8.0.13 restore will fail for sure. It is recommended that you have to upgrade to 5.7.24/8.0.13 to have that fix. Workaround is to remove occurrences of NO_AUTO_CREATE_USER from the dump file and then restore.

thanks,
Umesh