Description:
Hello MySql Team,
I'm using the logical upgrade path to upgrade from MySql 8.0.32 to MySql 8.4.4 ( see https://dev.mysql.com/doc/refman/8.4/en/upgrade-binary-package.html#upgrade-procedure-logi... ).
The old MySql 8.0.32 allows ZERO_DATE, by excluding NO_ZERO_DATE from the @@sql_mode. And has a table with '0000-00-00 00:00:00' in a timestamp column.
Restoring this dump in MySql 8.4.4 with default @@sql_mode (NO_ZERO_DATE active) does not error and happily imports '0000-00-00 00:00:00' while this is not allowed according to @@sql_mode.
Kind regards,
Mirco Babin
How to repeat:
The reproduction consists of 3 steps.
1) On MySql 8.0.32:
mysql> select @@sql_mode;
+----------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------------+
Create a dump using mysqldump cli tool:
mysqldump --result-file="touopdrlog.sql" --user root --password --events --routines --triggers --lock-tables=false --single-transaction=true --hex-blob=true --databases trip_portaalbeta --tables touopdrlog
+++ [BEGIN] contents dumped of touopdrlog.sql +++
-- MySQL dump 10.13 Distrib 8.0.32, for Win64 (x86_64)
--
-- Host: localhost Database: trip_portaalbeta
-- ------------------------------------------------------
-- Server version 8.0.32
/*!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 */;
/*!50503 SET NAMES utf8mb4 */;
/*!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 `touopdrlog`
--
DROP TABLE IF EXISTS `touopdrlog`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `touopdrlog` (
`Id` int NOT NULL AUTO_INCREMENT,
`LastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`CreationDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`UpdateBy` int DEFAULT NULL,
`Opdrachtnr` int DEFAULT NULL,
`Logboekcode` varchar(5) DEFAULT NULL,
`Logonkey` varchar(20) DEFAULT NULL,
`Omschrijving` varchar(255) DEFAULT NULL,
`AgendaId` int DEFAULT '0',
`EmailId` int DEFAULT '0',
PRIMARY KEY (`Id`),
KEY `k1` (`Opdrachtnr`,`CreationDate`,`Logboekcode`),
KEY `k2` (`CreationDate`,`Logboekcode`,`Opdrachtnr`)
) ENGINE=InnoDB AUTO_INCREMENT=4805 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `touopdrlog`
--
LOCK TABLES `touopdrlog` WRITE;
/*!40000 ALTER TABLE `touopdrlog` DISABLE KEYS */;
INSERT INTO `touopdrlog` VALUES (99999,'0000-00-00 00:00:00','2006-07-21 07:30:15',19,20,'','test','test',0,0);
/*!40000 ALTER TABLE `touopdrlog` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Dumping events for database 'trip_portaalbeta'
--
--
-- Dumping routines for database 'trip_portaalbeta'
--
/*!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 2025-02-14 13:12:44
+++ [END] contents dumped of touopdrlog.sql +++
2) On MySql 8.4.4 restore the dump via mysql cli tool.
mysql -u root -p
mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop database if exists `mytest`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database `mytest`;
Query OK, 1 row affected (0.01 sec)
mysql> use `mytest`;
Database changed
mysql> source touopdrlog.sql;
... Query OK, 0 rows affected (0.00 sec) ...
mysql> select * from touopdrlog;
+-------+---------------------+---------------------+----------+------------+-------------+----------+--------------+----------+---------+
| Id | LastUpdate | CreationDate | UpdateBy | Opdrachtnr | Logboekcode | Logonkey | Omschrijving | AgendaId | EmailId |
+-------+---------------------+---------------------+----------+------------+-------------+----------+--------------+----------+---------+
| 99999 | 0000-00-00 00:00:00 | 2006-07-21 09:30:15 | 19 | 20 | | test | test | 0 | 0 |
+-------+---------------------+---------------------+----------+------------+-------------+----------+--------------+----------+---------+
1 row in set (0.00 sec)
mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode
|
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3) Observe the '0000-00-00 00:00:00' value is imported, while @@sql_mode NO_ZERO_DATE forbids this.
