← Back to team overview

enterprise-support team mailing list archive

  1. enterprise-support team
  2. Mailing list archive
  3. Message #03487

[Bug 1313901] Re: Binlog events with binlog_format=MIXED are unconditionally logged in ROW format in some cases

  Thread Previous • Date Previous • • Thread Next 
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$ 


** Changed in: percona-server/5.1
       Status: New => Invalid

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1313901

Title:
  Binlog events with binlog_format=MIXED are unconditionally logged in
  ROW format in some cases

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1313901/+subscriptions
  Thread Previous • Date Previous • • Thread Next