MySQL Bugs: #14554: bad dump of triggers by mysqldump

Bug #14554	bad dump of triggers by mysqldump
Submitted:	1 Nov 2005 23:21	Modified:	22 Nov 2005 17:17
Reporter:	Roland Volkmann	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.16-BK, 5.0.15-nt	OS:	Linux (Linux, Windows)
Assigned to:	Alexander Ivanov	CPU Architecture:	Any

Description:
if you have innodb table with trigger, mysqldump produces script where the two words "ROW" and "BEGIN" are not separated by whitespace.

Example:

Original DDL of Trigger:

CREATE TRIGGER "mydb"."anrede_before_ins_tr" BEFORE INSERT ON "mydb"."anrede"
  FOR EACH ROW BEGIN
  set new."Land" = upper(new."Land");
END;

Part of script produced by mysqldump:

DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI" */;;
/*!50003 CREATE TRIGGER `anrede_before_ins_tr` BEFORE INSERT ON `anrede` FOR EACH ROWBEGIN
  set new."Land" = upper(new."Land");
END  */;;
DELIMITER ; 

How to repeat:
mysqldump.exe --default-character-set=latin1 --databases --opt --hex-blob --user=root --password=blabla --result-file=mydb.sql mydb

some variables of the server:

character_set_client = latin1                           
character_set_connection = latin1                           
character_set_database = utf8                             
character_set_results = latin1                           
character_set_server = utf8                             
character_set_system = utf8                             
collation_connection = latin1_swedish_ci                
collation_database = utf8_unicode_ci                  
collation_server = utf8_unicode_ci                  
sql_mode = ANSI
storage_engine = InnoDB
table_type = InnoDB
version = 5.0.15-max
version_compile_os = Win32

Thank you for a bug report. Verified using the following steps on both 5.0.15-nt on Windows and 5.0.16-BK (ChangeSet@1.1957, 2005-10-29 13:11:34+04:00, konstantin@mysql.com) on Linux:

mysql> set sql_mode = ANSI;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (c1 int);
Query OK, 0 rows affected (0.07 sec)

mysql> delimiter //
mysql> create trigger test_bi_tr before insert on test
    -> for each row
    -> begin
    ->   set new.c1 = 0;
    -> end//
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

C:\Documents and Settings\openxs>mysqldump --databases --opt --hex-blob -uroot -proot -P3307 test > test.sql

("mysqldump --opt -uroot test" is enough, other options are not relevant).

Then one can easily found in test.sql the following rows:

/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI" */;;
/*!50003 CREATE TRIGGER `test_bi_tr` BEFORE INSERT ON `test` FOR EACH ROWbegin
  set new.c1 = 0;
end */;;

DELIMITER ;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;

The critical step here is SQL_MODE=ANSI. If trigger is created in default (empty) SQL_MODE, mysqldump produces correct results!

I can confirm that the setting of SQL_MODE is responsible for the bug. The critical element is "IGNORE_SPACE", so you can use "sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES" instead of "ANSI", and mysqldump works as expected.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32448

ChangeSet
  1.1981 05/11/21 13:36:48 aivanov@mysql.com +3 -0
  Fixed BUG #14554: mysqldump does not separate "ROW" and trigger
   statement for tables created in the IGNORE_SPACE sql mode.

  mysql-test/t/mysqldump.test
    1.76 05/11/21 13:36:39 aivanov@mysql.com +24 -0
    Added test case for bug 14554.

  mysql-test/r/mysqldump.result
    1.83 05/11/21 13:36:39 aivanov@mysql.com +56 -0
    Fixed test case result for bug 14554.

  client/mysqldump.c
    1.213 05/11/21 13:36:39 aivanov@mysql.com +2 -1
    Modified dump_triggers_for_table(): if trigger statement returned
     by SHOW TRIGGERS query does not contain a leading white space,
     additional space is inserted between "ROW" and the statement.
     The leading white spaces are removed by yylex() in the
     IGNORE_SPACE sql mode.

The fix will appear in 5.0.17.

ChangeSet
  1.1981 05/11/21 13:36:48 aivanov@mysql.com +3 -0
  Fixed BUG #14554: mysqldump does not separate "ROW" and trigger
   statement for tables created in the IGNORE_SPACE sql mode.

  mysql-test/t/mysqldump.test
    1.76 05/11/21 13:36:39 aivanov@mysql.com +24 -0
    Added test case for bug 14554.

  mysql-test/r/mysqldump.result
    1.83 05/11/21 13:36:39 aivanov@mysql.com +56 -0
    Fixed test case result for bug 14554.

  client/mysqldump.c
    1.213 05/11/21 13:36:39 aivanov@mysql.com +2 -1
    Modified dump_triggers_for_table(): if trigger statement returned
     by SHOW TRIGGERS query does not contain a leading white space,
     additional space is inserted between "ROW" and the statement.
     The leading white spaces are removed by yylex() in the
     IGNORE_SPACE sql mode.

The fix will appear in 5.0.17.

Noted in 5.0.17 changelog.