I tried to repeat but got the correct output. Could you please provide steps on how to reproduce the issue?
-- MySQL dump 10.17 Distrib 10.3.25-MariaDB, for Linux (x86_64)
|
--
|
-- Host: localhost Database: test
|
-- ------------------------------------------------------
|
-- Server version 10.3.25-MariaDB
|
|
|
/*!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 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 `t1`
|
--
|
|
|
DROP TABLE IF EXISTS `t1`;
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
/*!40101 SET character_set_client = utf8 */;
|
CREATE TABLE `t1` (
|
`id` text DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
--
|
-- Dumping data for table `t1`
|
--
|
|
|
LOCK TABLES `t1` WRITE;
|
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
|
INSERT INTO `t1` VALUES ('852fe626c978cd466178e4513bce16bf');
|
/*!40000 ALTER TABLE `t1` 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 = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
|
DELIMITER ;;
|
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW SET NEW.id = MD5(CONCAT(NOW(),RAND())) */;;
|
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 2020-11-12 12:59:00
|
Sure .. I run
|
|
#/usr/bin/mysqldump --opt --single-transaction --disable-keys --routines --events --triggers --socket /var/run/mysqld/mysqld.sock -u root -p secret mydb
{sql}– MySQL dump 10.17 Distrib 10.3.25-MariaDB, for debian-linux-gnu (x86_64)
–
-- Host: localhost Database: mydb
– ------------------------------------------------------
– Server version 10.3.25-MariaDB-1:10.3.25+maria~bionic
/*!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 */;
DROP TABLE IF EXISTS `LabelsUsers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `LabelsUsers` (
`LabelsUsersID` int(11) NOT NULL AUTO_INCREMENT,
`LabelsUsersID2` varchar(32) DEFAULT NULL,
`LabelsUsersCreated` timestamp NOT NULL DEFAULT current_timestamp(),
`LabelsUsersUpdated` datetime DEFAULT NULL,
`LabelsUsersDeleted` datetime DEFAULT NULL,
PRIMARY KEY (`LabelsUsersID`),
KEY `LabelsUsersID2` (`LabelsUsersID2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
–
-- Dumping data for table `LabelsUsers`
–
LOCK TABLES `LabelsUsers` WRITE;
/*!40000 ALTER TABLE `LabelsUsers` DISABLE KEYS */;
/*!40000 ALTER TABLE `LabelsUsers` 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 = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/!50003 CREATE/ /!50017 DEFINER=`mydb`@`%` */ /!50003 TRIGGER trigger_LabelsUsers_insert_before BEFORE INSERT ON LabelsUsers FOR EACH ROW SET NEW.LabelsUsersID2 = MD5(CONCAT(NOW(),RAND())); */;;
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 */ ;
/*!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,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/!50003 CREATE/ /!50017 DEFINER=`mydb`@`%` */ /!50003 TRIGGER trigger_LabelsUsers_update_before BEFORE UPDATE ON LabelsUsers FOR EACH ROW SET NEW.LabelsUsersUpdated = CURRENT_TIMESTAMP */;;
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 routines for database 'mydb'
–
/*!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 2020-11-12 5:13:38
Just noticed that I actually run mysqldump on bionic and restore on a focal but still version 10.3.25
Hi Desdic,
I run following on 10.3.25 and didn't get the same as you:
|
|
MariaDB [test]> select @@version; |
+-----------------------+ |
| @@version |
|
+-----------------------+ |
| 10.3.25-MariaDB-debug |
|
+-----------------------+ |
1 row in set (0.000 sec) |
|
|
CREATE TABLE `t1` ( |
`id` text DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
|
|
INSERT INTO `t1` VALUES ('852fe626c978cd466178e4513bce16bf'); |
|
|
CREATE DEFINER=`root`@`localhost` TRIGGER tr |
BEFORE INSERT ON t1 FOR EACH ROW SET NEW.id = MD5(CONCAT(NOW(),RAND())); |
|
|
MariaDB [test]> show create trigger tr\G |
*************************** 1. row ***************************
|
Trigger: tr |
sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER tr |
BEFORE INSERT ON t1 FOR EACH ROW SET NEW.id = MD5(CONCAT(NOW(),RAND())) |
character_set_client: utf8
|
collation_connection: utf8_general_ci
|
Database Collation: latin1_swedish_ci |
Created: 2020-11-12 15:25:18.40
|
1 row in set (0.000 sec) |
$ ./client/mysqldump --opt --single-transaction --disable-keys --routines --events --triggers -uroot test
|
-- MySQL dump 10.17 Distrib 10.3.25-MariaDB, for Linux (x86_64)
|
--
|
-- Host: localhost Database: test
|
-- ------------------------------------------------------
|
-- Server version 10.3.25-MariaDB-debug
|
|
|
/*!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 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 `t1`
|
--
|
|
|
DROP TABLE IF EXISTS `t1`;
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
/*!40101 SET character_set_client = utf8 */;
|
CREATE TABLE `t1` (
|
`id` text DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
|
--
|
-- Dumping data for table `t1`
|
--
|
|
|
LOCK TABLES `t1` WRITE;
|
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
|
INSERT INTO `t1` VALUES ('852fe626c978cd466178e4513bce16bf');
|
/*!40000 ALTER TABLE `t1` 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 = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
|
DELIMITER ;;
|
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW SET NEW.id = MD5(CONCAT(NOW(),RAND())) */;;
|
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 'test'
|
--
|
|
|
--
|
-- Dumping routines for database 'test'
|
--
|
/*!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 2020-11-12 20:41:22
|
|
What is problematic in your example is the executable comments /!50003 CREATE/ /!50017 DEFINER=`mydb`@`%` */.
Note that executable comments are created with /*! */ not with /! */ as in your example and I couldn't find similar scenario in the codebase of mysqldump.
So same example to validate this would be great to provide.
This is an old problem MDEV-6899 (or its close relative). This is how you initially create such a trigger:
create table t1 (a int); |
delimiter ;;
|
create trigger tr after insert on t1 for each row set @a=1 ; |
select 1 ;; |
delimiter ;
|
Once you've created it, it does have an extra semicolon in the description, which ends up in the dump and causes the syntax error.
MariaDB [test]> show create trigger tr; |
+---------+-------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+ |
| Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation | Created | |
+---------+-------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+ |
| tr | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` trigger tr after insert on t1 for each row set @a=1 ; | utf8 | utf8_general_ci | latin1_swedish_ci | 2020-11-13 14:02:08.25 | |
+---------+-------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+ |
1 row in set (0.001 sec) |
anel,
What is problematic in your example is the executable comments /!50003 CREATE/
It's not a problem, it's a formatting issue. Since the comment doesn't have 'noformat' or alike, special symbols like stars are misinterpreted for bold markers. If you open the comment for editing, you'll see them there.
Ah oki so the Customer in my case actually did this themselves .. is there any option for the dump that I can add to change the behavior so I can get a consistent backup/restore ?
Any option change is going to remove the trigger. The below sed should clean up all the output. Manually inspect the diff from the original to ensure it hasn't picked up addition pattens in your dump.
|
sed workaround |
sed -e 's/;\( \*\/;;\)$/\1/g'
|
Hi danblack,
can you please review patch for 10.3 (not sure can it go in 10.2. If it can go on 10.2 probably would be good to cherry-pick it to patch-6899
elenst thank you for clarification, I will take a closer look how comments are created in that manner.
comments on patch. Same applies to MDEV-6899 ' ; ; ' ending. Looks like a bug so 10.2 on top of your MDEV-6899 branch is fine. Include a test case for MDEV-6899 like you have for this bug.
Thanks danblack for review.
Added observation about ;; and I'm not sure should we take care about that elenst what do you think about ;; in the statement (not delimiter) and observation?
Added new patch e300cd78d7f7551a52 for regex check, thanks.
Also this commit is pushed on top of bb-10.2-anel-MDEV-6899-show-event-mysqldump
I think it's a very strange idea to remove semicolons in sql_show.cc.
Either they shoudn't get into the trigger definition in the first place, or the parser should be able to parse them just fine.
On the related note I suspect that these /*!50003 comments have outlived their usefulness, 5.0.3 was looong time ago.
If we can get rid of the /*!500003 comments, then the problem can be solved purely in mysqldump like:
|
dump based solution |
--- a/client/mysqldump.c
|
+++ b/client/mysqldump.c
|
@@ -3508,8 +3508,8 @@ static int dump_trigger(FILE *sql_file, MYSQL_RES *show_create_trigger_rs,
|
C_STRING_WITH_LEN("50003"),
|
C_STRING_WITH_LEN(" TRIGGER"));
|
fprintf(sql_file,
|
- "DELIMITER ;;\n"
|
- "/*!50003 %s */;;\n"
|
+ "DELIMITER +;\n"
|
+ " %s+;\n"
|
"DELIMITER ;\n",
|
(const char *) (query_str != NULL ? query_str : row[2]));
|
|
I've changed the delimiter here so that a trigger ending in ;, when it is getting restored in the dump isn't confused by a ;;; . The principle here of dump/restore being the same. I'm also hoping something here restores exactly the same trigger that was there previously so checksums, if any are applicable, match.
A follow up task on the parser for 10.6 
to warn and drop from the definition trailing ;+ in the parser. And a mysql_upgrade patch to remove the existing trailing unnecessary syntax.
Second follow up task to remove any < 5.5 comments from mysqldump?
danblack, please, I want to review it
see my review for MDEV-6899
The patch for this MDEV can be found in MDEV-6899 patch where the test case for this MDEV is added.
Will be fixed in MDEV-6899