Gecko [ 122.154.134.11 ]

Name	Size	Permission
binlog_base64_flag.result	3.74 KB	-rw-r--r--
binlog_bug23533.result	486 B	-rw-r--r--
binlog_bug36391.result	129 B	-rw-r--r--
binlog_database.result	5.28 KB	-rw-r--r--
binlog_delete_and_flush_index....	1.75 KB	-rw-r--r--
binlog_drop_if_exists.result	5.26 KB	-rw-r--r--
binlog_grant.result	1.18 KB	-rw-r--r--
binlog_incident.result	181 B	-rw-r--r--
binlog_index.result	5.2 KB	-rw-r--r--
binlog_innodb.result	6.05 KB	-rw-r--r--
binlog_innodb_row.result	2.47 KB	-rw-r--r--
binlog_killed.result	3.43 KB	-rw-r--r--
binlog_killed_simulate.result	1.25 KB	-rw-r--r--
binlog_mix_innodb_stat.result	610 B	-rw-r--r--
binlog_mixed_failure_mixing_en...	16.79 KB	-rw-r--r--
binlog_mixed_load_data.result	429 B	-rw-r--r--
binlog_multi_engine.result	4.59 KB	-rw-r--r--
binlog_mysqlbinlog-cp932.resul...	344 B	-rw-r--r--
binlog_mysqlbinlog2.result	36.63 KB	-rw-r--r--
binlog_mysqlbinlog_base64.resu...	1.69 KB	-rw-r--r--
binlog_mysqlbinlog_row.result	150.85 KB	-rw-r--r--
binlog_mysqlbinlog_row_innodb....	239.94 KB	-rw-r--r--
binlog_mysqlbinlog_row_myisam....	241.25 KB	-rw-r--r--
binlog_mysqlbinlog_row_trans.r...	16.1 KB	-rw-r--r--
binlog_old_versions.result	1.3 KB	-rw-r--r--
binlog_query_filter_rules.resu...	173 B	-rw-r--r--
binlog_reset_master.result	14 B	-rw-r--r--
binlog_row_binlog.result	76.4 KB	-rw-r--r--
binlog_row_ctype_cp932.result	87.42 KB	-rw-r--r--
binlog_row_ctype_ucs.result	1.27 KB	-rw-r--r--
binlog_row_drop_tmp_tbl.result	1.17 KB	-rw-r--r--
binlog_row_failure_mixing_engi...	18.09 KB	-rw-r--r--
binlog_row_innodb_stat.result	610 B	-rw-r--r--
binlog_row_insert_select.resul...	829 B	-rw-r--r--
binlog_row_mix_innodb_myisam.r...	32.72 KB	-rw-r--r--
binlog_row_mysqlbinlog_db_filt...	1.5 KB	-rw-r--r--
binlog_row_mysqlbinlog_verbose...	2.68 KB	-rw-r--r--
binlog_server_id.result	1.2 KB	-rw-r--r--
binlog_sf.result	1.67 KB	-rw-r--r--
binlog_sql_mode.result	3.51 KB	-rw-r--r--
binlog_start_comment.result	357 B	-rw-r--r--
binlog_statement_insert_delaye...	949 B	-rw-r--r--
binlog_stm_binlog.result	51.51 KB	-rw-r--r--
binlog_stm_blackhole.result	7.29 KB	-rw-r--r--
binlog_stm_ctype_cp932.result	87.42 KB	-rw-r--r--
binlog_stm_ctype_ucs.result	1.29 KB	-rw-r--r--
binlog_stm_do_db.result	2.08 KB	-rw-r--r--
binlog_stm_drop_tmp_tbl.result	2.3 KB	-rw-r--r--
binlog_stm_innodb_stat.result	610 B	-rw-r--r--
binlog_stm_insert_select.resul...	707 B	-rw-r--r--
binlog_stm_mix_innodb_myisam.r...	30.23 KB	-rw-r--r--
binlog_stm_ps.result	811 B	-rw-r--r--
binlog_stm_row.result	2.48 KB	-rw-r--r--
binlog_stm_unsafe_warning.resu...	1.71 KB	-rw-r--r--
binlog_tmp_table.result	877 B	-rw-r--r--
binlog_truncate_innodb.result	2.45 KB	-rw-r--r--
binlog_truncate_myisam.result	439 B	-rw-r--r--
binlog_unsafe.result	13.81 KB	-rw-r--r--
binlog_write_error.result	3.58 KB	-rw-r--r--

Code Editor : binlog_innodb.result

SET BINLOG_FORMAT=MIXED;
RESET MASTER;
CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB;
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE t1 SET b = 2*a WHERE a > 1;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE t1 SET b = a * a WHERE a > 3;
COMMIT;
SET BINLOG_FORMAT=STATEMENT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE t1 SET b = 1*a WHERE a > 1;
ERROR HY000: Binary logging not possible. Message: Transaction level 'READ-UNCOMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE t1 SET b = 2*a WHERE a > 2;
ERROR HY000: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE t1 SET b = 3*a WHERE a > 3;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE t1 SET b = 4*a WHERE a > 4;
COMMIT;
SET BINLOG_FORMAT=MIXED;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE t1 SET b = 1*a WHERE a > 1;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE t1 SET b = 2*a WHERE a > 2;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE t1 SET b = 3*a WHERE a > 3;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE t1 SET b = 4*a WHERE a > 4;
COMMIT;
SET BINLOG_FORMAT=ROW;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE t1 SET b = 1*a WHERE a > 1;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE t1 SET b = 2*a WHERE a > 2;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE t1 SET b = 3*a WHERE a > 3;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE t1 SET b = 4*a WHERE a > 4;
COMMIT;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t1 SET b = 2*a WHERE a > 1
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Update_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t1 SET b = 3*a WHERE a > 3
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t1 SET b = 4*a WHERE a > 4
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Update_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Update_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t1 SET b = 3*a WHERE a > 3
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; UPDATE t1 SET b = 4*a WHERE a > 4
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Update_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Update_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Update_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Update_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
DROP TABLE t1;
flush status;
show status like "binlog_cache_use";
Variable_name	Value
Binlog_cache_use	0
show status like "binlog_cache_disk_use";
Variable_name	Value
Binlog_cache_disk_use	0
create table t1 (a int) engine=innodb;
show status like "binlog_cache_use";
Variable_name	Value
Binlog_cache_use	1
show status like "binlog_cache_disk_use";
Variable_name	Value
Binlog_cache_disk_use	1
begin;
delete from t1;
commit;
show status like "binlog_cache_use";
Variable_name	Value
Binlog_cache_use	2
show status like "binlog_cache_disk_use";
Variable_name	Value
Binlog_cache_disk_use	1
drop table t1;
CREATE TABLE `t1` (
`a` int(11) NOT NULL auto_increment,
`b` int(11) default NULL,
PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
CREATE TABLE `t2` (
`a` int(11) NOT NULL auto_increment,
`b` int(11) default NULL,
PRIMARY KEY  (`a`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 ;
insert into t1 values (1,1),(2,2);
insert into t2 values (1,1),(4,4);
reset master;
UPDATE t2,t1 SET t2.a=t1.a+2;
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
select * from t2 /* must be (3,1), (4,4) */;
a	b
1	1
4	4
# There must no UPDATE in binlog;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
delete from t1;
delete from t2;
insert into t1 values (1,2),(3,4),(4,4);
insert into t2 values (1,2),(3,4),(4,4);
reset master;
UPDATE t2,t1  SET t2.a=t2.b where t2.a=t1.a;
ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
# There must be no UPDATE query event;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
drop table t1, t2;
End of tests

${this.title}

Code Editor : binlog_innodb.result