MDEV-6899:Database Dump has Syntax Error When Importingbb-10.2-anel-MDEV-6899-show-event-mysqldump-v2

- Adding the test case for MDEV-24200: Syntax error in mysqldump

3 files changed, 161 insertions, 0 deletions

diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index da80ff09db9..608a567cf04 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -5981,4 +5981,94 @@ CREATE TABLE IF NOT EXISTS `slow_log` (
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
+#
+# MDEV-6899:Database Dump has Syntax Error When Importing
+#
+create database td;
+CREATE EVENT td.fooEvent
+ON SCHEDULE EVERY 1 DAY
+DO
+DELETE FROM Token WHERE DATE(time_end) < CURRENT_DATE  ;
+SELECT 1 //
+1
+1
+# SHOW CREATE EVENTS before upgrade
+SHOW CREATE EVENT td.fooEvent;
+Event	fooEvent
+sql_mode	
+time_zone	SYSTEM
+Create Event	CREATE DEFINER=`root`@`localhost` EVENT `fooEvent` ON SCHEDULE EVERY 1 DAY STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM Token WHERE DATE(time_end) < CURRENT_DATE
+character_set_client	utf8
+collation_connection	utf8_general_ci
+Database Collation	latin1_swedish_ci
+SELECT EVENT_DEFINITION FROM information_schema.events;
+EVENT_DEFINITION	DELETE FROM Token WHERE DATE(time_end) < CURRENT_DATE
+# SHOW CREATE EVENTS after upgrade
+SHOW CREATE EVENT td.fooEvent;
+Event	fooEvent
+sql_mode	
+time_zone	SYSTEM
+Create Event	CREATE DEFINER=`root`@`localhost` EVENT `fooEvent` ON SCHEDULE EVERY 1 DAY STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM Token WHERE DATE(time_end) < CURRENT_DATE
+character_set_client	utf8
+collation_connection	utf8_general_ci
+Database Collation	latin1_swedish_ci
+DROP DATABASE td;
+#
+# MDEV-24200: Syntax error in mysqldump
+#
+create database td;
+create table td.t1 (a int);
+CREATE TRIGGER td.tr BEFORE INSERT ON td.t1 FOR EACH ROW BEGIN
+set @a=1;
+END//
+insert into td.t1 (a) values (1)//
+select * from td.t1//
+a	1
+create trigger td.tr2 after insert on td.t1 for each row set @a=1 ;
+select 1 //
+1	1
+# SHOW CREATE TRIGGER before upgrade
+SHOW CREATE TRIGGER td.tr;
+Trigger	tr
+sql_mode	
+SQL Original Statement	CREATE DEFINER=`root`@`localhost` TRIGGER td.tr BEFORE INSERT ON td.t1 FOR EACH ROW BEGIN
+set @a=1;
+END
+character_set_client	utf8
+collation_connection	utf8_general_ci
+Database Collation	latin1_swedish_ci
+Created	Created at date # time #
+SHOW CREATE TRIGGER td.tr2;
+Trigger	tr2
+sql_mode	
+SQL Original Statement	CREATE DEFINER=`root`@`localhost` trigger td.tr2 after insert on td.t1 for each row set @a=1
+character_set_client	utf8
+collation_connection	utf8_general_ci
+Database Collation	latin1_swedish_ci
+Created	Created at date # time #
+SELECT action_statement FROM information_schema.triggers WHERE TRIGGER_NAME='tr';
+action_statement	BEGIN
+set @a=1;
+END
+# Run mysql_upgrade
+# Show create trigger after upgrade
+SHOW CREATE TRIGGER td.tr;
+Trigger	tr
+sql_mode	
+SQL Original Statement	CREATE DEFINER=`root`@`localhost` TRIGGER td.tr BEFORE INSERT ON td.t1 FOR EACH ROW BEGIN
+set @a=1;
+END
+character_set_client	utf8
+collation_connection	utf8_general_ci
+Database Collation	latin1_swedish_ci
+Created	Created at date # time #
+SHOW CREATE TRIGGER td.tr2;
+Trigger	tr2
+sql_mode	
+SQL Original Statement	CREATE DEFINER=`root`@`localhost` trigger td.tr2 after insert on td.t1 for each row set @a=1
+character_set_client	utf8
+collation_connection	utf8_general_ci
+Database Collation	latin1_swedish_ci
+Created	Created at date # time #
+DROP DATABASE td;
 # End of 10.2 tests
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 8249810d1e5..dacacfc7919 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -2758,4 +2758,71 @@ DROP TABLE t1;
 --echo #
 --exec $MYSQL_DUMP $ignore $skip_opts --insert-ignore mysql
 
+--echo #
+--echo # MDEV-6899:Database Dump has Syntax Error When Importing
+--echo #
+create database td;
+
+DELIMITER //;
+CREATE EVENT td.fooEvent
+ON SCHEDULE EVERY 1 DAY
+DO
+    DELETE FROM Token WHERE DATE(time_end) < CURRENT_DATE  ;
+SELECT 1 //
+DELIMITER ;//
+--echo # SHOW CREATE EVENTS before upgrade
+--vertical_results
+--replace_regex /STARTS '[^']+'/STARTS '#'/
+SHOW CREATE EVENT td.fooEvent;
+# Note that `event_definition` column still holds semi-colon at the end
+SELECT EVENT_DEFINITION FROM information_schema.events;
+--exec $MYSQL_DUMP --events td>$MYSQLTEST_VARDIR/tmp/MDEV-6899.sql
+--exec $MYSQL td<$MYSQLTEST_VARDIR/tmp/MDEV-6899.sql
+--echo # SHOW CREATE EVENTS after upgrade
+--vertical_results
+--replace_regex /STARTS '[^']+'/STARTS '#'/
+SHOW CREATE EVENT td.fooEvent;
+DROP DATABASE td;
+
+--echo #
+--echo # MDEV-24200: Syntax error in mysqldump
+--echo #
+create database td;
+
+create table td.t1 (a int);
+delimiter //;
+CREATE TRIGGER td.tr BEFORE INSERT ON td.t1 FOR EACH ROW BEGIN
+set @a=1;
+END//
+insert into td.t1 (a) values (1)//
+select * from td.t1//
+DELIMITER ;//
+
+delimiter //;
+create trigger td.tr2 after insert on td.t1 for each row set @a=1 ;
+select 1 //
+delimiter ;//
+
+--echo # SHOW CREATE TRIGGER before upgrade
+--vertical_results
+--replace_regex /[1-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]\s{1}[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9]/Created at date # time #/
+SHOW CREATE TRIGGER td.tr;
+--vertical_results
+--replace_regex /[1-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]\s{1}[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9]/Created at date # time #/
+SHOW CREATE TRIGGER td.tr2;
+--vertical_results
+--replace_regex /[1-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]\s{1}[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9]/Created at date # time #/
+SELECT action_statement FROM information_schema.triggers WHERE TRIGGER_NAME='tr';
+--echo # Run mysql_upgrade
+--exec $MYSQL_DUMP td >$MYSQLTEST_VARDIR/tmp/MDEV-24200.sql
+--exec $MYSQL td<$MYSQLTEST_VARDIR/tmp/MDEV-24200.sql
+--echo # Show create trigger after upgrade
+--vertical_results
+--replace_regex /[1-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]\s{1}[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9]/Created at date # time #/
+SHOW CREATE TRIGGER td.tr;
+--vertical_results
+--replace_regex /[1-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]\s{1}[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9]/Created at date # time #/
+SHOW CREATE TRIGGER td.tr2;
+DROP DATABASE td;
+
 --echo # End of 10.2 tests
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index b205d253733..d20bc4c8b3d 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -711,6 +711,10 @@ sp_head::set_stmt_end(THD *thd)
     m_params.str= thd->strmake(m_param_begin, m_params.length);
   }
 
+  if (!strncmp(end_ptr -1, ";", 1))
+  {
+    --end_ptr;
+  }
   /* Remember end pointer for further dumping of whole statement. */
 
   thd->lex->stmt_definition_end= end_ptr;