Replication failure on multi-statement INSERT DELAYED queries
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.1 |
Won't Fix
|
Undecided
|
Unassigned | |||
5.5 |
Triaged
|
Medium
|
Unassigned | |||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Invalid
|
Undecided
|
Unassigned |
Bug Description
Percona Server version 5.5.34-rel32.0 on both master and slave.
I a multi-statement INSERT DELAYED query is executed then the master is not able to parse it correctly and writes both the queries together in the same statement binlog event. Then when the slave thread reads the event, it parses it incorrectly and the replication breaks.
Structure of the table:
CREATE TABLE `log_messages` (
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`message` text NOT NULL,
`url` text NOT NULL,
`hash` char(32) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Test query:
INSERT DELAYED INTO test.log_messages (time_stamp, message, url, hash) VALUES(
When executed together through PHP using mysqli_multi_query, the query executes fine on the master:
[root@ovaistari
mysqli Object
(
[affected_rows] => 1
[client_info] => 5.1.69
[client_
[connect_errno] => 0
[connect_error] =>
[errno] => 0
[error] =>
[field_count] => 0
[host_info] => 127.0.0.1 via TCP/IP
[info] =>
[insert_id] => 0
[server_info] => 5.5.34-rel32.0-log
[server_
[sqlstate] => 00000
[protocol_
[thread_id] => 3
[warning_count] => 0
)
master [localhost] {msandbox} (test) > select * from log_messages;
+------
| time_stamp | message | url | hash |
+------
| 2013-11-15 17:39:29 | test_msg_1 | test_url_1 | 0bee89b07a248e2
| 2013-11-15 17:39:29 | test_msg_2 | test_url_2 | b6273b589df2dfd
+------
2 rows in set (0.00 sec)
But replication on slave gets broken:
slave1 [localhost] {msandbox} (test) > show slave status\G
*******
Replicate
Replicate_
Master_
Replicate_
1 row in set (0.00 sec)
tags: | added: upstream |
MySQL configuration on master:
[root@ovaistari q-test master]# cat my.sandbox.cnf datacharmer. org
# The MySQL Sandbox
# Copyright (C) 2006-2012 Giuseppe Maxia
# Contacts: http://
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
[mysql]
prompt='master [\h] {\u} (\d) > '
#
[client] sandbox23389. sock
user = msandbox
password = msandbox
port = 23389
socket = /tmp/mysql_
[mysqld] sandbox23389. sock percona- server/ 5.5.34 /rsandbox_ ps_5_5_ 34/master/ data /rsandbox_ ps_5_5_ 34/master/ tmp /rsandbox_ ps_5_5_ 34/master/ data/mysql_ sandbox23389. pid /rsandbox_ ps_5_5_ 34/master/ data/msandbox- slow.log /rsandbox_ ps_5_5_ 34/master/ data/msandbox. log msandbox. err
user = root
port = 23389
socket = /tmp/mysql_
basedir = /work/binaries/
datadir = /work/sandboxes
tmpdir = /work/sandboxes
pid-file = /work/sandboxes
#log-slow-queries = /work/sandboxes
#log = /work/sandboxes
#
# additional options passed through 'my_clause'
#
log-bin=mysql-bin
server-id=1
log-error=