Percona Server moved to https://jira.percona.com/projects/PS

Bug #1313901
Comment #1

Comment 1 for bug 1313901

Revision history for this message

Nilnandan Joshi (nilnandan-joshi) wrote on 2014-06-05:

Not repeatable with PS 5.1.73, its logging as a STATEMENT for all queries with binlog-format=MIXED.

nil@Dell-XPS:~/sandboxes/rsandbox_Percona-Server-5_1_73/master/data$ mysqlbinlog -v mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
....

# at 266
#140605 11:32:05 server id 1 end_log_pos 497 Query thread_id=1 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1401948125/*!*/;
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` char(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1018 DEFAULT CHARSET=latin1
/*!*/;
# at 497
#140605 11:32:10 server id 1 end_log_pos 565 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948130/*!*/;
BEGIN
/*!*/;
# at 565
#140605 11:32:10 server id 1 end_log_pos 715 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948130/*!*/;
INSERT INTO `t2` VALUES (1, 'dummy'), (2, 'foo'), (3, 'ping'), (4, 'ding'), (5, 'ring')
/*!*/;
# at 715
#140605 11:32:10 server id 1 end_log_pos 742 Xid = 16
COMMIT/*!*/;
# at 742
#140605 11:32:15 server id 1 end_log_pos 899 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948135/*!*/;
create temporary table t2_temp(`id` int(11) NOT NULL, `c` char(32) DEFAULT NULL) ENGINE=InnoDB
/*!*/;
# at 899
#140605 11:32:20 server id 1 end_log_pos 994 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948140/*!*/;
create index idx1 on t2_temp(id)
/*!*/;
# at 994
#140605 11:32:25 server id 1 end_log_pos 1062 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948145/*!*/;
BEGIN
/*!*/;
# at 1062
#140605 11:32:25 server id 1 end_log_pos 1177 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948145/*!*/;
insert into t2_temp values(4, 'hello'), (5, 'world')
/*!*/;
# at 1177
#140605 11:32:25 server id 1 end_log_pos 1246 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948145/*!*/;
COMMIT
/*!*/;
# at 1246
#140605 11:33:13 server id 1 end_log_pos 1314 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948193/*!*/;
BEGIN
/*!*/;
# at 1314
#140605 11:33:13 server id 1 end_log_pos 1446 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948193/*!*/;
update t2, t2_temp set t2.c='updated' where t2.id=t2_temp.id
/*!*/;
# at 1446
#140605 11:33:13 server id 1 end_log_pos 1473 Xid = 22
COMMIT/*!*/;
# at 1473
#140605 11:33:23 server id 1 end_log_pos 1541 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948203/*!*/;
BEGIN
/*!*/;
# at 1541
#140605 11:33:23 server id 1 end_log_pos 1645 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948203/*!*/;
insert into t2 values(6, 'simple_insert')
/*!*/;
# at 1645
#140605 11:33:23 server id 1 end_log_pos 1672 Xid = 23
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
nil@Dell-XPS:~/sandboxes/rsandbox_Percona-Server-5_1_73/master/data$

-------------------------- On slave--------------------------

nil@Dell-XPS:~/sandboxes/rsandbox_Percona-Server-5_1_73/node1/data$
nil@Dell-XPS:~/sandboxes/rsandbox_Percona-Server-5_1_73/node1/data$ mysqlbinlog -v mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;

...

# at 266
#140605 11:32:05 server id 1 end_log_pos 497 Query thread_id=1 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1401948125/*!*/;
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` char(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1018 DEFAULT CHARSET=latin1
/*!*/;
# at 497
#140605 11:32:10 server id 1 end_log_pos 556 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948130/*!*/;
BEGIN
/*!*/;
# at 556
#140605 11:32:10 server id 1 end_log_pos 706 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948130/*!*/;
INSERT INTO `t2` VALUES (1, 'dummy'), (2, 'foo'), (3, 'ping'), (4, 'ding'), (5, 'ring')
/*!*/;
# at 706
#140605 11:32:10 server id 1 end_log_pos 733 Xid = 8
COMMIT/*!*/;
# at 733
#140605 11:32:15 server id 1 end_log_pos 890 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948135/*!*/;
create temporary table t2_temp(`id` int(11) NOT NULL, `c` char(32) DEFAULT NULL) ENGINE=InnoDB
/*!*/;
# at 890
#140605 11:32:20 server id 1 end_log_pos 985 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948140/*!*/;
create index idx1 on t2_temp(id)
/*!*/;
# at 985
#140605 11:32:25 server id 1 end_log_pos 1053 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948145/*!*/;
BEGIN
/*!*/;
# at 1053
#140605 11:32:25 server id 1 end_log_pos 1168 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948145/*!*/;
insert into t2_temp values(4, 'hello'), (5, 'world')
/*!*/;
# at 1168
#140605 11:32:25 server id 1 end_log_pos 1237 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948145/*!*/;
COMMIT
/*!*/;
# at 1237
#140605 11:33:13 server id 1 end_log_pos 1296 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948193/*!*/;
BEGIN
/*!*/;
# at 1296
#140605 11:33:13 server id 1 end_log_pos 1428 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948193/*!*/;
update t2, t2_temp set t2.c='updated' where t2.id=t2_temp.id
/*!*/;
# at 1428
#140605 11:33:13 server id 1 end_log_pos 1455 Xid = 15
COMMIT/*!*/;
# at 1455
#140605 11:33:23 server id 1 end_log_pos 1514 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948203/*!*/;
BEGIN
/*!*/;
# at 1514
#140605 11:33:23 server id 1 end_log_pos 1618 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1401948203/*!*/;
insert into t2 values(6, 'simple_insert')
/*!*/;
# at 1618
#140605 11:33:23 server id 1 end_log_pos 1645 Xid = 17
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
nil@Dell-XPS:~/sandboxes/rsandbox_Percona-Server-5_1_73/node1/data$

Not repeatable with PS 5.1.73, its logging as a STATEMENT for all queries with binlog-format=MIXED.

# at 266
#140605 11:32:05 server id 1  end_log_pos 497 	Query	thread_id=1	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1401948125/*!*/;
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` char(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1018 DEFAULT CHARSET=latin1
/*!*/;
# at 497
#140605 11:32:10 server id 1  end_log_pos 565 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948130/*!*/;
BEGIN
/*!*/;
# at 565
#140605 11:32:10 server id 1  end_log_pos 715 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948130/*!*/;
INSERT INTO `t2` VALUES (1, 'dummy'), (2, 'foo'), (3, 'ping'), (4, 'ding'), (5, 'ring')
/*!*/;
# at 715
#140605 11:32:10 server id 1  end_log_pos 742 	Xid = 16
COMMIT/*!*/;
# at 742
#140605 11:32:15 server id 1  end_log_pos 899 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948135/*!*/;
create temporary table t2_temp(`id` int(11) NOT NULL, `c` char(32) DEFAULT NULL) ENGINE=InnoDB
/*!*/;
# at 899
#140605 11:32:20 server id 1  end_log_pos 994 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948140/*!*/;
create index idx1 on t2_temp(id)
/*!*/;
# at 994
#140605 11:32:25 server id 1  end_log_pos 1062 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948145/*!*/;
BEGIN
/*!*/;
# at 1062
#140605 11:32:25 server id 1  end_log_pos 1177 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948145/*!*/;
insert into t2_temp values(4, 'hello'), (5, 'world')
/*!*/;
# at 1177
#140605 11:32:25 server id 1  end_log_pos 1246 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948145/*!*/;
COMMIT
/*!*/;
# at 1246
#140605 11:33:13 server id 1  end_log_pos 1314 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948193/*!*/;
BEGIN
/*!*/;
# at 1314
#140605 11:33:13 server id 1  end_log_pos 1446 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948193/*!*/;
update t2, t2_temp set t2.c='updated' where t2.id=t2_temp.id
/*!*/;
# at 1446
#140605 11:33:13 server id 1  end_log_pos 1473 	Xid = 22
COMMIT/*!*/;
# at 1473
#140605 11:33:23 server id 1  end_log_pos 1541 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948203/*!*/;
BEGIN
/*!*/;
# at 1541
#140605 11:33:23 server id 1  end_log_pos 1645 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948203/*!*/;
insert into t2 values(6, 'simple_insert')
/*!*/;
# at 1645
#140605 11:33:23 server id 1  end_log_pos 1672 	Xid = 23
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
nil@Dell-XPS:~/sandboxes/rsandbox_Percona-Server-5_1_73/master/data$

-------------------------- On slave--------------------------

nil@Dell-XPS:~/sandboxes/rsandbox_Percona-Server-5_1_73/node1/data$ 
nil@Dell-XPS:~/sandboxes/rsandbox_Percona-Server-5_1_73/node1/data$ mysqlbinlog -v mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;

...

# at 266
#140605 11:32:05 server id 1  end_log_pos 497 	Query	thread_id=1	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1401948125/*!*/;
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` char(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1018 DEFAULT CHARSET=latin1
/*!*/;
# at 497
#140605 11:32:10 server id 1  end_log_pos 556 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948130/*!*/;
BEGIN
/*!*/;
# at 556
#140605 11:32:10 server id 1  end_log_pos 706 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948130/*!*/;
INSERT INTO `t2` VALUES (1, 'dummy'), (2, 'foo'), (3, 'ping'), (4, 'ding'), (5, 'ring')
/*!*/;
# at 706
#140605 11:32:10 server id 1  end_log_pos 733 	Xid = 8
COMMIT/*!*/;
# at 733
#140605 11:32:15 server id 1  end_log_pos 890 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948135/*!*/;
create temporary table t2_temp(`id` int(11) NOT NULL, `c` char(32) DEFAULT NULL) ENGINE=InnoDB
/*!*/;
# at 890
#140605 11:32:20 server id 1  end_log_pos 985 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948140/*!*/;
create index idx1 on t2_temp(id)
/*!*/;
# at 985
#140605 11:32:25 server id 1  end_log_pos 1053 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948145/*!*/;
BEGIN
/*!*/;
# at 1053
#140605 11:32:25 server id 1  end_log_pos 1168 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948145/*!*/;
insert into t2_temp values(4, 'hello'), (5, 'world')
/*!*/;
# at 1168
#140605 11:32:25 server id 1  end_log_pos 1237 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948145/*!*/;
COMMIT
/*!*/;
# at 1237
#140605 11:33:13 server id 1  end_log_pos 1296 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948193/*!*/;
BEGIN
/*!*/;
# at 1296
#140605 11:33:13 server id 1  end_log_pos 1428 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948193/*!*/;
update t2, t2_temp set t2.c='updated' where t2.id=t2_temp.id
/*!*/;
# at 1428
#140605 11:33:13 server id 1  end_log_pos 1455 	Xid = 15
COMMIT/*!*/;
# at 1455
#140605 11:33:23 server id 1  end_log_pos 1514 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948203/*!*/;
BEGIN
/*!*/;
# at 1514
#140605 11:33:23 server id 1  end_log_pos 1618 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1401948203/*!*/;
insert into t2 values(6, 'simple_insert')
/*!*/;
# at 1618
#140605 11:33:23 server id 1  end_log_pos 1645 	Xid = 17
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
nil@Dell-XPS:~/sandboxes/rsandbox_Percona-Server-5_1_73/node1/data$