| Bug #16983 | Replication breaks with syntax error when executing stored procedure | ||
|---|---|---|---|
| Submitted: | 31 Jan 2006 16:52 | Modified: | 9 Feb 2006 13:18 |
| Reporter: | Marcel Noe | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.1.5-alpha-max-log | OS: | Linux (Linux 2.6.11.1) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[31 Jan 2006 16:54]
Marcel Noe
Script that reproduces the error
Attachment: procedure.sql (text/x-sql), 1.00 KiB.
[31 Jan 2006 17:15]
Giuseppe Maxia
Verified on Linux Fedora Core 4, with MySQL 5.0.17 and 5.0.18. It should be related to bug #11931, which does something similar to the binary log. Here is my test case: # ----- CUT drop database if exists test2; create database test2; use test2; CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` varchar(50) default NULL, `c2` varchar(50) default NULL, `t` text, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=latin1 ; CREATE TABLE `t2` ( `id` int(11), `c1` varchar(50) default NULL, `c2` varchar(50) default NULL, `t` text ) ENGINE=INNODB DEFAULT CHARSET=latin1 ; insert into t1 values (1,'foo','bar','abc'); insert into t1 values (2,'bar','foobar','xyzx'); delimiter // -- procedure 1 -- CREATE PROCEDURE test (p1 VARCHAR(50)) BEGIN DELETE FROM t2; INSERT INTO t2 SELECT * FROM t1 WHERE c2 = p1; SELECT * FROM t2 WHERE c2 = p1; END // delimiter ; call test('bar'); # ------ CUT After running the test, try a SHOW SLAVE STATUS, and then have a look at the binary log in the master (which affects the relay-bin log in the slave). Here is a hexdump of my binlog after the test: 0000000: 2330 3630 3133 3120 3138 3a31 313a 3035 #060131 18:11:05 0000010: 2073 6572 7665 7220 6964 2031 3020 2065 server id 10 e 0000020: 6e64 5f6c 6f67 5f70 6f73 2032 3235 2009 nd_log_pos 225 . 0000030: 5175 6572 7909 7468 7265 6164 5f69 643d Query.thread_id= 0000040: 3309 6578 6563 5f74 696d 653d 3009 6572 3.exec_time=0.er 0000050: 726f 725f 636f 6465 3d30 0a53 4554 2054 ror_code=0.SET T 0000060: 494d 4553 5441 4d50 3d31 3133 3837 3237 IMESTAMP=1138727 0000070: 3436 353b 0a49 4e53 4552 5420 494e 544f 465;.INSERT INTO 0000080: 2074 3220 5345 4c45 4354 202a 2046 524f t2 SELECT * FRO 0000090: 4d20 7431 2057 4845 5245 2063 3220 3d20 M t1 WHERE c2 = 00000a0: 204e 414d 455f 434f 4e53 5428 2770 3127 NAME_CONST('p1' 00000b0: 2c40 0000 0060 6ec3 9e09 6814 6100 6a1b ,@...`n...h.a.j. 00000c0: 3e08 4000 0000 c280 58c3 8200 6814 6100 >.@.....X...h.a. 00000d0: c296 c3bd 1008 18c2 89c3 9f09 0000 0000 ................ 00000e0: c288 1461 003f c2a3 1708 7a15 6100 c286 ...a.?....z.a... 00000f0: 5e46 08c2 8814 6100 c296 c3bd 1008 18c2 ^F....a......... 0000100: 89c3 9f09 0000 0000 0400 0000 7800 c3a0 ............x... 0000110: 095f 6c61 7469 6e31 2762 6172 2729 3b0a ._latin1'bar');. 0000120: 2320 6174 2031 3537 300a 2330 3630 3133 # at 1570.#06013 0000130: 3120 3138 3a31 313a 3035 2073 6572 7665 1 18:11:05 serve 0000140: 7220 6964 2031 3020 2065 6e64 5f6c 6f67 r id 10 end_log 0000150: 5f70 6f73 2031 3539 3720 0958 6964 203d _pos 1597 .Xid = 0000160: 2032 360a 434f 4d4d 4954 3b0a 2320 456e 26.COMMIT;.# En 0000170: 6420 6f66 206c 6f67 2066 696c 650a 524f d of log file.RO 0000180: 4c4c 4241 434b 202f 2a20 6164 6465 6420 LLBACK /* added 0000190: 6279 206d 7973 716c 6269 6e6c 6f67 202a by mysqlbinlog * 00001a0: 2f3b 0a2f 2a21 3530 3030 3320 5345 5420 /;./*!50003 SET 00001b0: 434f 4d50 4c45 5449 4f4e 5f54 5950 453d COMPLETION_TYPE= 00001c0: 404f 4c44 5f43 4f4d 504c 4554 494f 4e5f @OLD_COMPLETION_ 00001d0: 5459 5045 2a2f 3b0a TYPE*/;.
[31 Jan 2006 18:22]
Hartmut Holzgraefe
Looks like a charset issue, when trying to use mysqlbinlog on the servers binlog
after verifying this example i get:
# at 2580
#060131 19:17:16 server id 1 end_log_pos 2971 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1138731436;
INSERT INTO personen2 (id, vorname, nachname) VALUES ( NAME_CONST('tid',1), NAME_CONST('tvorname',�2��2��/�e����2�2���/����_latin1'Marcel'),
NAME_CONST('tnachname',�2��2��/�C���`3���/���_latin1'Noe'));
this is using 5.1.5 as master on MacOS/X and 5.1bk as slave on linux,
i will attach the masters binlog file to the issue
[3 Feb 2006 13:48]
Geert Vanderkelen
MySQL 5.0.18 (on MacOSX.)
Maybe even smaller code to reproduce, you must have replication working ofcourse:
mysql> DELIMITER //
mysql> CREATE TABLE t1 ( name VARCHAR(20))//
mysql> CREATE PROCEDURE sptest (IN pvar VARCHAR(20)) BEGIN INSERT INTO t1 (name) VALUES (pvar); END; //
mysql> CALL sptest('foo')//
On slave:
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: 'replica'. Query: 'INSERT INTO t1 (name) VALUES ( NAME_CONST('pvar','
And see the binlog entries in previous messages.
[9 Feb 2006 8:02]
Beat Vontobel
I think this is a duplicate to that one, already fixed in 5.0.19: * An INSERT statement in a stored procedure corrupted the binary log. (Bug #16621)
[9 Feb 2006 13:18]
Geert Vanderkelen
Yes indeed This bug is duplicate of BUG#16621.
Description: Given the following: 2 MySQL Servers (5.0.18-max-log or 5.1.5-alpha-max-log) replicating each other over a local ethernet. (Both machines are running Gentoo Linux). I use the precompiled database server from MySQL.com. When I create a stored procedure on the master server which select data from one to another table, it is executed on the master but replication on the slave fails with the following error: mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.4.101 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 11181 Relay_Log_File: urmel-v2-relay-bin.001335 Relay_Log_Pos: 1767 Relay_Master_Log_File: mysql-bin.000004 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 1' on query. Default database: 'crashtest'. Query: 'INSERT INTO personen2 (id, vorname, nachname) VALUES ( NAME_CONST('tid',1), NAME_CONST('tvorname',@' Skip_Counter: 0 Exec_Master_Log_Pos: 10763 Relay_Log_Space: 2185 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) Note that the call to the stored procedure runs fine on the master server but fails on all slave servers. (See the attached script under 'how to repeat'). I've also posted the bug to comp.databases.mysql, see <slrndttd4e.34v.usenet-marcel@nova.hadiko.de> and the following. How to repeat: The following script should create the error: drop database if exists crashtest; create database crashtest; use crashtest; CREATE TABLE `personen` ( `id` int(11) NOT NULL AUTO_INCREMENT, `vorname` varchar(50) default NULL, `nachname` varchar(50) default NULL, `text` text, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=latin1; CREATE TABLE `personen2` ( `id` int(11), `vorname` varchar(50) default NULL, `nachname` varchar(50) default NULL, `text` text ) ENGINE=INNODB DEFAULT CHARSET=latin1; insert into personen (vorname, nachname, text) values ('Marcel', 'Noe', 'Test'); delimiter // CREATE PROCEDURE auswahl (param1 VARCHAR(50)) BEGIN DECLARE tid INT; DECLARE tvorname VARCHAR(50); DECLARE tnachname VARCHAR(50); DELETE FROM personen2; SELECT vorname, nachname, id INTO tvorname, tnachname, tid FROM personen WHERE nachname = param1; INSERT INTO personen2 (id, vorname, nachname) VALUES (tid, tvorname, tnachname); SELECT vorname, nachname, id FROM personen WHERE nachname = param1; END; // delimiter ; call auswahl('Noe');