Description:
See How to repeat.
When using the innobackup.pl perl script, and replication, I have a problem.
The slave chugs along just fine, then spits out:
Query caused different errors on master and slave. Error on master: 'Can't execute the query because you have a conflicting read lock' (1223), Error on slave: 'no error' (0). Default database: 'mysql'. Query: 'BEGIN'
I check the master binlog position, and discover this:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 35294588
#040929 2:25:51 server id 1 log_pos 35294588 Query thread_id=7830089
exec_time=0 error_code=1223
use mysql;
SET TIMESTAMP=1096449951;
BEGIN;
# at 35294629
#040929 2:25:44 server id 1 log_pos 35282293 Query thread_id=7830089
exec_time=0 error_code=0
SET TIMESTAMP=1096449944
INSERT INTO ibbackup_binlog_marker VALUES (1);
# at 35294710
#040929 2:25:51 server id 1 log_pos 35294710 Query thread_id=7830089
exec_time=0 error_code=1223
SET TIMESTAMP=1096449951;
COMMIT;
I didn't see this prior to 4.0.21 (I was on 4.0.20), but it may or may not be related.
This is using the Innobackup Perl script.
How to repeat:
USE mysql;
DROP TABLE IF EXISTS ibbackup_binlog_marker;
CREATE TABLE ibbackup_binlog_marker(a INT) TYPE=INNODB;
SET AUTOCOMMIT=0;
INSERT INTO ibbackup_binlog_marker VALUES (1);
FLUSH TABLES WITH READ LOCK;
COMMIT;
Both BEGIN and COMMIT are labeled with error code 1223 in the binlog, though
they execute without any errors in the mysql client!
For the current innobackup Perl script to work properly, the thread issuing
the FLUSH TABLES WITH READ LOCK must be able to execute also the COMMIT.
heikki@hundin:~/mysql-4.0/client> ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.21-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> USE mysql;
Database changed
mysql> DROP TABLE IF EXISTS ibbackup_binlog_marker;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE ibbackup_binlog_marker(a INT) TYPE=INNODB;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO ibbackup_binlog_marker VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
heikki@hundin:~/mysql-4.0/client> ./mysqladmin shutdown
heikki@hundin:~/mysql-standard-4.0.21-pc-linux-i686/bin> ./mysqlbinlog
~/data/bi
nlog.005
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#041004 18:00:58 server id 1 log_pos 4 Start: binlog v 3, server v
4.0.
21-standard-log created 041004 18:00:58 at startup
# at 79
#041004 18:01:24 server id 1 log_pos 79 Query thread_id=1
exec_tim
e=0 error_code=0
use mysql;
SET TIMESTAMP=1096902084;
DROP TABLE IF EXISTS ibbackup_binlog_marker;
# at 158
#041004 18:01:24 server id 1 log_pos 158 Query thread_id=1
exec_tim
e=0 error_code=0
SET TIMESTAMP=1096902084;
CREATE TABLE ibbackup_binlog_marker(a INT) TYPE=INNODB;
# at 248
#041004 18:01:24 server id 1 log_pos 248 Query thread_id=1
exec_tim
e=0 error_code=1223
SET TIMESTAMP=1096902084;
BEGIN;
# at 289
#041004 18:01:24 server id 1 log_pos 248 Query thread_id=1
exec_tim
e=0 error_code=0
SET TIMESTAMP=1096902084;
INSERT INTO ibbackup_binlog_marker VALUES (1);
# at 370
#041004 18:01:24 server id 1 log_pos 370 Query thread_id=1
exec_tim
e=0 error_code=1223
SET TIMESTAMP=1096902084;
COMMIT;
