• Davi Arnaut's avatar
    Bug#34306: Can't make copy of log tables when server binary log is enabled · 0406d409
    Davi Arnaut authored
    The problem is that when statement-based replication was enabled,
    statements such as INSERT INTO .. SELECT FROM .. and CREATE TABLE
    .. SELECT FROM need to grab a read lock on the source table that
    does not permit concurrent inserts, which would in turn be denied
    if the source table is a log table because log tables can't be
    locked exclusively.
    
    The solution is to not take such a lock when the source table is
    a log table as it is unsafe to replicate log tables under statement
    based replication. Furthermore, the read lock that does not permits
    concurrent inserts is now only taken if statement-based replication
    is enabled and if the source table is not a log table.
    
    include/thr_lock.h:
      Introduce yet another lock type that my get upgraded depending
      on the binary log format. This is not a optimal solution but
      can be easily improved later.
    mysql-test/r/log_tables.result:
      Add test case result for Bug#34306
    mysql-test/suite/binlog/r/binlog_stm_row.result:
      Add test case result for Bug#34306
    mysql-test/suite/binlog/t/binlog_stm_row.test:
      Add test case for Bug#34306
    mysql-test/t/log_tables.test:
      Add test case for Bug#34306
    sql/lock.cc:
      Assert that TL_READ_DEFAULT is not a real lock type.
    sql/mysql_priv.h:
      Export new function.
    sql/mysqld.cc:
      Remove using_update_log.
    sql/sql_base.cc:
      Introduce function that returns the appropriate read lock type
      depending on how the statement is going to be replicated. It will
      only take a TL_READ_NO_INSERT log if the binary is enabled and the
      binary log format is statement-based and the table is not a log table.
    sql/sql_parse.cc:
      Remove using_update_log.
    sql/sql_update.cc:
      Use new function to choose read lock type.
    sql/sql_yacc.yy:
      The lock type is now decided at open_tables time. This old behavior was
      actually misleading as the binary log format can be dynamically switched
      and this would not change for statements that have already been parsed
      when the binary log format is changed (ie: prepared statements).
    0406d409
binlog_stm_row.result 2.13 KB
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
SET GLOBAL BINLOG_FORMAT = STATEMENT;
SET SESSION BINLOG_FORMAT = STATEMENT;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 LIKE t1;
select @@SESSION.BINLOG_FORMAT;
@@SESSION.BINLOG_FORMAT
STATEMENT
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(2);
#
# Ensure that INSERT INTO .. SELECT FROM under SBR takes a read
# lock that will prevent the source table from being modified.
#
# con1
SELECT GET_LOCK('Bug#34306', 120);
GET_LOCK('Bug#34306', 120)
1
# con2
PREPARE stmt FROM "INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)";
EXECUTE stmt;;
# default
INSERT INTO t2 VALUES (3);;
# con1
SELECT RELEASE_LOCK('Bug#34306');
RELEASE_LOCK('Bug#34306')
1
# con2
SELECT RELEASE_LOCK('Bug#34306');
RELEASE_LOCK('Bug#34306')
1
# default
#
# Ensure that INSERT INTO .. SELECT FROM prepared under SBR does
# not prevent the source table from being modified if under RBR.
#
# con2
SET SESSION BINLOG_FORMAT = ROW;
# con1
SELECT GET_LOCK('Bug#34306', 120);
GET_LOCK('Bug#34306', 120)
1
# con2
EXECUTE stmt;;
# default
# con1
INSERT INTO t2 VALUES (4);
SELECT RELEASE_LOCK('Bug#34306');
RELEASE_LOCK('Bug#34306')
1
# con2
# default
# Show binlog events
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS t1
master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS t2
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT)
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2 LIKE t1
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES(1)
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES(2)
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (3)
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (4)
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Query	#	#	use `test`; COMMIT
DROP TABLE t1;
DROP TABLE t2;