| Bug #20614 | Create trigger breaks replication after specific delimiter used | ||
|---|---|---|---|
| Submitted: | 21 Jun 2006 19:17 | Modified: | 23 Oct 2007 15:31 |
| Reporter: | Ed Dawley | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S3 (Non-critical) |
| Version: | 5.0.21/5.0BK/5.1BK | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[21 Jun 2006 23:38]
MySQL Verification Team
Thank you for the bug report. I was able to repeat:
master>create database dbm;
Query OK, 1 row affected (0.00 sec)
master>use dbm
Database changed
master>create table child (id int unsigned not null, parent_id int unsigned not
-> null);
Query OK, 0 rows affected (0.01 sec)
master>create table parent (id int unsigned not null, number_children int
-> unsigned not null default 0);
Query OK, 0 rows affected (0.00 sec)
master>delimiter |;
master>create trigger child_insert after insert on child for each row begin
-> update parent set number_children = number_children + 1 where parent.id =
-> NEW.parent_id; end; | ;|;
Query OK, 0 rows affected (0.01 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '|' at line 1
master>delimiter ;
master>select version();
+------------------+
| version() |
+------------------+
| 5.0.23-debug-log |
+------------------+
1 row in set (0.00 sec)
master>
slave>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 226
Relay_Log_File: hegel-relay-bin.000003
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 226
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
slave>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 857
Relay_Log_File: hegel-relay-bin.000003
Relay_Log_Pos: 613
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; |' at line 2' on query. Default database: 'dbm'. Query: 'CREATE DEFINER=`root`@`localhost` trigger child_insert after insert on child for each row begin
update parent set number_children = number_children + 1 where parent.id =
NEW.parent_id; end; |'
Skip_Counter: 0
Exec_Master_Log_Pos: 604
Relay_Log_Space: 866
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
slave>select version();
+--------------+
| version() |
+--------------+
| 5.0.23-debug |
+--------------+
1 row in set (0.01 sec)
slave>
----------------------------------------------------------------------------------------
slave>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 861
Relay_Log_File: hegel-relay-bin.000002
Relay_Log_Pos: 621
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use near '; |'
at line 2' on query. Default database: 'dbm'. Query: 'CREATE DEFINER=`root`@`localhost` trigger child_insert after insert on child for each row begin
update parent set number_children = number_children + 1 where parent.id =
NEW.parent_id; end; |'
Skip_Counter: 0
Exec_Master_Log_Pos: 608
Relay_Log_Space: 874
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
slave>select version();
+-------------------+
| version() |
+-------------------+
| 5.1.12-beta-debug |
+-------------------+
1 row in set (0.04 sec)
slave>
[28 Oct 2006 10:32]
Ilia Kantor
Also for 24a
[28 Oct 2006 11:37]
Ilia Kantor
How to workaround that? It's just pain..
[1 Dec 2006 22:38]
Andrei Elkin
can not reproduce with 5.1.14 and 5.0.32. I observed the fact that the garbage symbol `|' after `end' of the trigger is regarded by master mysqld as incorrect syntax (as reported). In the corrent version the symbol has been split from the main query, so that query is binlogged having no garbage at the end.
[4 Dec 2006 17:31]
Andrei Elkin
As noted at how to repeat, ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '|' at line 1 there is a problem with mysql client.
[23 Oct 2007 14:26]
Patrick Galbraith
This bug is fixed in 5.0.45 - please try with 5.0.45 to verify. It was not a problem with the client, but with replication.
[23 Oct 2007 15:31]
Sveta Smirnova
Patrick, thank you for the comment. Re-verified with current development sources: bug is not repeatable now.
Description: When testing triggers and replication, I accidently specified the wrong delimiter. Upon running the create trigger statement, the statement was created and written to the bin log. The slave proceded to fail upon running the create trigger statement. I am not sure if this is a bug in replication or in creating a trigger. It could also be bogus as the master is at version 5.0.21 and the slave is at 5.022. As there were no listed changes to bin logging, replication, or triggers and I am unable to downgrade one revision, I am posting the bug anyway. How to repeat: Master Version: 5.0.21 mysql> create table child (id int unsigned not null, parent_id int unsigned not null); Query OK, 0 rows affected (0.00 sec) mysql> create table parent (id int unsigned not null, number_children int unsigned not null default 0); Query OK, 0 rows affected (0.02 sec) mysql> delimiter |; mysql> create trigger child_insert after insert on child for each row begin update parent set number_children = number_children + 1 where parent.id = NEW.parent_id; end; | ;|; Query OK, 0 rows affected (0.00 sec) ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '|' at line 1 mysql> delimiter ; mysql> show triggers; +--------------+--------+-------+----------------------------------------------------------------------------------------------------+--------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | +--------------+--------+-------+----------------------------------------------------------------------------------------------------+--------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+ | child_insert | INSERT | child | begin update parent set number_children = number_children + 1 where parent.id = NEW.parent_id; end | AFTER | NULL | PIPES_AS_CONCAT,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER | admin@localhost | +--------------+--------+-------+----------------------------------------------------------------------------------------------------+--------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+ 1 row in set (0.00 sec) Slave Version: 5.0.22 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.1 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql5-bin.000190 Read_Master_Log_Pos: 59643961 Relay_Log_File: host-relay-bin.000002 Relay_Log_Pos: 43847 Relay_Master_Log_File: mysql5-bin.000190 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: ed Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1064 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; |' at line 1' on query. Default database: 'ed'. Query: 'CREATE DEFINER=`admin`@`localhost` trigger child_insert after insert on child for each row begin update parent set number_children = number_children + 1 where parent.id = NEW.parent_id; end; |' Skip_Counter: 0 Exec_Master_Log_Pos: 59643573 Relay_Log_Space: 44235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL 1 row in set (0.00 sec)