Alfranio Correia wrote:
> Hi all,
>
> Daogang Qu wrote:
> > Libing Song wrote:
> >> Hi Daogang,
> >>
> >>
> >> Nice Work. Please find my review comments below.
> >>
> >> STATUS
> >> ------
> >> Not approved.
> >>
> >> REQUIRED CHANGES
> >> ----------------
> >>
> >> RC1. I think the following patch is better.
> >>
> >> === modified file 'sql/sql_class.h'
> >> --- sql/sql_class.h 2009-12-02 13:49:21 +0000
> >> +++ sql/sql_class.h 2009-12-29 09:29:10 +0000
> >> @@ -2213,7 +2213,8 @@
> >> ("temporary_tables: %s, in_sub_stmt: %s, system_thread:
> >> %s",
> >> YESNO(temporary_tables), YESNO(in_sub_stmt),
> >> show_system_thread(system_thread)));
> >> - if ((temporary_tables == NULL) && (in_sub_stmt == 0)
> &&
> >> + if ((temporary_tables == NULL || !current_stmt_binlog_row_based)
> >>
> > Good. Maybe the following is better:
> >
> > - if ((temporary_tables == NULL) && (in_sub_stmt == 0) &&
> > + if ((temporary_tables == NULL || (!current_stmt_binlog_row_based
> &&
> > + variables.binlog_format == BINLOG_FORMAT_ROW)) && (in_sub_stmt
> > == 0) &&
> > (system_thread != SYSTEM_THREAD_NDBCLUSTER_BINLOG))
>
>
> Although I said in the IRC that it was possible to restore the correct value in the
> reset_current_stmt_binlog_row_based, I was wrong. It is not possible.
> Consider for instance the following example:
>
> --source include/master-slave.inc
> --source include/have_binlog_format_mixed.inc
>
> CREATE TABLE t (a int);
>
> INSERT INTO t VALUES(1), (2);
> CREATE TEMPORARY TABLE tt (a int, b int) SELECT *, UUID() from t;
> INSERT INTO t VALUES(3), (4);
>
> DELIMITER |;
> CREATE PROCEDURE p1() BEGIN SELECT 1 ; END |
> DELIMITER ;|
>
> INSERT INTO tt(a) VALUES(1), (2);
> INSERT INTO t VALUES(5), (6);
>
> DROP TABLE t;
>
> SHOW BINLOG EVENTS;
>
> sync_slave_with_master;
>
> exit;
>
>
> WRONG RESULT:
> -------------
>
> Log_name Pos Event_type Server_id End_log_pos Info
> master-bin.000001 4 Format_desc 1 106 Server ver:
> 5.1.42-debug-log, Binlog ver: 4
> master-bin.000001 106 Query 1 191 use `test`; CREATE TABLE t (a
> int)
> master-bin.000001 191 Query 1 282 use `test`; INSERT INTO t
> VALUES(1), (2)
> master-bin.000001 282 Query 1 350 BEGIN
> master-bin.000001 350 Table_map 1 390 table_id: 23
> (test.t)
> master-bin.000001 390 Write_rows 1 429 table_id: 23 flags:
> STMT_END_F
> master-bin.000001 429 Query 1 498 COMMIT
> master-bin.000001 498 Query 1 633 use `test`; CREATE
> DEFINER=`root`@`localhost` PROCEDURE `p1`()
> BEGIN SELECT 1 ; END
> master-bin.000001 633 Query 1 728 use `test`; INSERT INTO tt(a)
> VALUES(1), (2)
> master-bin.000001 728 Query 1 819 use `test`; INSERT INTO t
> VALUES(5), (6)
> master-bin.000001 819 Query 1 894 use `test`; DROP TABLE t
> master-bin.000001 894 Query 1 1004 use `test`; DROP /*!40005
> TEMPORARY */ TABLE IF EXISTS `tt`
>
>
> RIGHT RESULT:
> -------------
>
> master-bin.000001 4 Format_desc 1 106 Server ver:
> 5.1.42-debug-log, Binlog ver: 4
> master-bin.000001 106 Query 1 191 use `test`; CREATE TABLE t (a
> int)
> master-bin.000001 191 Query 1 282 use `test`; INSERT INTO t
> VALUES(1), (2)
> master-bin.000001 282 Query 1 350 BEGIN
> master-bin.000001 350 Table_map 1 390 table_id: 23
> (test.t)
> master-bin.000001 390 Write_rows 1 429 table_id: 23 flags:
> STMT_END_F
> master-bin.000001 429 Query 1 498 COMMIT
> master-bin.000001 498 Query 1 633 use `test`; CREATE
> DEFINER=`root`@`localhost` PROCEDURE `p1`()
> BEGIN SELECT 1 ; END
> master-bin.000001 282 Query 1 350 BEGIN
> master-bin.000001 350 Table_map 1 390 table_id: 23
> (test.t)
> master-bin.000001 390 Write_rows 1 429 table_id: 23 flags:
> STMT_END_F
> master-bin.000001 429 Query 1 498 COMMIT
> master-bin.000001 894 Query 1 1004 use `test`; DROP /*!40005
> TEMPORARY */ TABLE IF EXISTS `tt`
>
>
>
> It is not possible to figure out what is the current value.
> In this case, after creating the procedure current_stmt_binlog_row_based
> should be TRUE.
>
> So, either
>
> 1 - we create a "global" variable to save the current value and restore it
> when necessary.
>
> 2 - we create a "local" variable to save the current value and restore it.
>
I think it's a good principle to do save/change/restore within the same
function, so that we are sure that the function will not have unexpected
side effects. Unexpected side effects of functions are hard to
understand and usually are the source of bugs.
> 3 - we force the current_stmt_binlog_row_based to be TRUE if there is
> a temporary table and the format is MIXED.
>
>
> Note that the current patch and several other places in the code are wrong
> as the value should be saved and restored.
>
Agree, and please also test the problem within stored
functions/procuders (in_sub_stmt), and NDB engine
(SYSTEM_THREAD_NDBCLUSTER_BINLOG). And make sure the problem is fixed
for these cases too.
>
> Cheers.
>
>
>
> >
> >
> > What's your opinion?
> >
> >
> > Best Regards,
> >
> > Daogang
> >> + && (in_sub_stmt == 0) &&
> >> (system_thread != SYSTEM_THREAD_NDBCLUSTER_BINLOG))
> >> {
> >> current_stmt_binlog_row_based=
> >>
> >> REQUESTS
> >> --------
> >> n/a
> >>
> >> SUGGESTIONS
> >> -----------
> >> n/a
> >>
> >> DETAILS -------
> >> On Tue, 2009-12-29 at 08:55 +0000, Dao-Gang.Qu@stripped wrote:
> >>
> >>> #At file:///home/daogangqu/mysql/bzrwork/bug49132/mysql-5.1-bugteam/
> >>> based on revid:alexey.kopytov@stripped
> >>>
> >>> 3271 Dao-Gang.Qu@stripped 2009-12-29
> >>> Bug #49132 Replication failure on temporary table + DDL
> >>> In RBR, DDL statement will change binlog format to non
> >>> row-based format, and then manipulating a temporary table can
> >>> not reset binlog format to row-based format rightly. So that
> >>> the manipulated statement is binlogged with statement-based
> >>> format.
> >>> To fix the problem, restore the state of binlog format
> >>> after the DDL statement is binlogged.
> >>> @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
> >>> Correct the test result, all the above binlog event
> >>> should be row-based after the bug49132 is fixed IN RBR.
> >>> @ mysql-test/suite/rpl/r/rpl_failure_on_tmp_table_and_DDL.result
> >>> Test result of the bug#49132.
> >>> @ mysql-test/suite/rpl/t/rpl_failure_on_tmp_table_and_DDL.test
> >>> Added the test file to verify if executing DDL statement
> >>> before trying to manipulate a temporary table causes
> >>> row-based replication to break with error 'table does not
> >>> exist'.
> >>>
> >>> added:
> >>> mysql-test/suite/rpl/r/rpl_failure_on_tmp_table_and_DDL.result
> >>> mysql-test/suite/rpl/t/rpl_failure_on_tmp_table_and_DDL.test
> >>> modified:
> >>> mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
> >>> sql/events.cc
> >>> sql/sp.cc
> >>> sql/sql_acl.cc
> >>> === modified file
> >>> 'mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result'
> >>> ---
> >>> a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
> >>> 2009-10-06 10:25:36 +0000
> >>> +++
> >>> b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
> >>> 2009-12-29 08:54:57 +0000
> >>> @@ -772,8 +772,11 @@ insert into t2 values (bug27417(2));
> >>> ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
> >>> show binlog events from <binlog_start>;
> >>> Log_name Pos Event_type Server_id End_log_pos Info
> >>> -master-bin.000001 # Intvar # # INSERT_ID=3
> >>> -master-bin.000001 # Query # # use `test`; insert into
> >>> t2 values (bug27417(2))
> >>> +master-bin.000001 # Query # # BEGIN
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t2)
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t1)
> >>> +master-bin.000001 # Write_rows # # table_id: # flags:
> >>> STMT_END_F
> >>> +master-bin.000001 # Query # # ROLLBACK
> >>> select count(*) from t1 /* must be 3 */;
> >>> count(*)
> >>> 3
> >>> @@ -787,8 +790,11 @@ count(*)
> >>> 2
> >>> show binlog events from <binlog_start>;
> >>> Log_name Pos Event_type Server_id End_log_pos Info
> >>> -master-bin.000001 # Intvar # # INSERT_ID=4
> >>> -master-bin.000001 # Query # # use `test`; delete from
> >>> t2 where a=bug27417(3)
> >>> +master-bin.000001 # Query # # BEGIN
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t2)
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t1)
> >>> +master-bin.000001 # Write_rows # # table_id: # flags:
> >>> STMT_END_F
> >>> +master-bin.000001 # Query # # COMMIT
> >>> select count(*) from t1 /* must be 5 */;
> >>> count(*)
> >>> 5
> >>> @@ -810,8 +816,9 @@ ERROR 23000: Duplicate entry '1' for key
> >>> show binlog events from <binlog_start>;
> >>> Log_name Pos Event_type Server_id End_log_pos Info
> >>> master-bin.000001 # Query # # BEGIN
> >>> -master-bin.000001 # Intvar # # INSERT_ID=1
> >>> -master-bin.000001 # Query # # use `test`; insert into
> >>> t2 values (bug27417(1))
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t2)
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t1)
> >>> +master-bin.000001 # Write_rows # # table_id: # flags:
> >>> STMT_END_F
> >>> master-bin.000001 # Query # # ROLLBACK
> >>> select count(*) from t1 /* must be 1 */;
> >>> count(*)
> >>> @@ -825,8 +832,10 @@ ERROR 23000: Duplicate entry '2' for key
> >>> show binlog events from <binlog_start>;
> >>> Log_name Pos Event_type Server_id End_log_pos Info
> >>> master-bin.000001 # Query # # BEGIN
> >>> -master-bin.000001 # Intvar # # INSERT_ID=2
> >>> -master-bin.000001 # Query # # use `test`; insert into
> >>> t2 select bug27417(1) union select bug27417(2)
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t2)
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t1)
> >>> +master-bin.000001 # Write_rows # # table_id: #
> >>> +master-bin.000001 # Write_rows # # table_id: # flags:
> >>> STMT_END_F
> >>> master-bin.000001 # Query # # ROLLBACK
> >>> select count(*) from t1 /* must be 2 */;
> >>> count(*)
> >>> @@ -838,8 +847,13 @@ update t3 set b=b+bug27417(1);
> >>> ERROR 23000: Duplicate entry '4' for key 'b'
> >>> show binlog events from <binlog_start>;
> >>> Log_name Pos Event_type Server_id End_log_pos Info
> >>> -master-bin.000001 # Intvar # # INSERT_ID=4
> >>> -master-bin.000001 # Query # # use `test`; update t3
> >>> set b=b+bug27417(1)
> >>> +master-bin.000001 # Query # # BEGIN
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t3)
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t1)
> >>> +master-bin.000001 # Write_rows # # table_id: #
> >>> +master-bin.000001 # Update_rows # # table_id: #
> >>> +master-bin.000001 # Write_rows # # table_id: # flags:
> >>> STMT_END_F
> >>> +master-bin.000001 # Query # # ROLLBACK
> >>> select count(*) from t1 /* must be 2 */;
> >>> count(*)
> >>> 2
> >>> @@ -853,8 +867,9 @@ ERROR 23000: Duplicate entry '2' for key
> >>> show binlog events from <binlog_start>;
> >>> Log_name Pos Event_type Server_id End_log_pos Info
> >>> master-bin.000001 # Query # # BEGIN
> >>> -master-bin.000001 # Intvar # # INSERT_ID=6
> >>> -master-bin.000001 # Query # # use `test`; UPDATE
> >>> t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t4)
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t1)
> >>> +master-bin.000001 # Write_rows # # table_id: # flags:
> >>> STMT_END_F
> >>> master-bin.000001 # Query # # ROLLBACK
> >>> select count(*) from t1 /* must be 4 */;
> >>> count(*)
> >>> @@ -869,7 +884,7 @@ UPDATE t3,t4 SET t3.a=t4.a + bug27417(1)
> >>> ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
> >>> select count(*) from t1 /* must be 1 */;
> >>> count(*)
> >>> -1
> >>> +2
> >>> drop table t4;
> >>> delete from t1;
> >>> delete from t2;
> >>> @@ -884,8 +899,10 @@ ERROR 23000: Duplicate entry '1' for key
> >>> show binlog events from <binlog_start>;
> >>> Log_name Pos Event_type Server_id End_log_pos Info
> >>> master-bin.000001 # Query # # BEGIN
> >>> -master-bin.000001 # Intvar # # INSERT_ID=9
> >>> -master-bin.000001 # Query # # use `test`; delete from
> t2
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t2)
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t3)
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t1)
> >>> +master-bin.000001 # Write_rows # # table_id: # flags:
> >>> STMT_END_F
> >>> master-bin.000001 # Query # # ROLLBACK
> >>> select count(*) from t1 /* must be 1 */;
> >>> count(*)
> >>> @@ -904,7 +921,11 @@ ERROR 23000: Duplicate entry '1' for key
> >>> show binlog events from <binlog_start>;
> >>> Log_name Pos Event_type Server_id End_log_pos Info
> >>> master-bin.000001 # Query # # BEGIN
> >>> -master-bin.000001 # Query # # use `test`; delete t2.*
> >>> from t2,t5 where t2.a=t5.a + 1
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t2)
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t1)
> >>> +master-bin.000001 # Delete_rows # # table_id: #
> >>> +master-bin.000001 # Write_rows # # table_id: #
> >>> +master-bin.000001 # Delete_rows # # table_id: #
> >>> flags: STMT_END_F
> >>> master-bin.000001 # Query # # ROLLBACK
> >>> select count(*) from t1 /* must be 1 */;
> >>> count(*)
> >>> @@ -924,12 +945,11 @@ count(*)
> >>> show binlog events from <binlog_start>;
> >>> Log_name Pos Event_type Server_id End_log_pos Info
> >>> master-bin.000001 # Query # # BEGIN
> >>> -master-bin.000001 # Intvar # # INSERT_ID=10
> >>> -master-bin.000001 # User var # # @`b`=_latin1 0x3135
> >>> COLLATE latin1_swedish_ci
> >>> -master-bin.000001 # Begin_load_query # #
> >>> ;file_id=#;block_len=#
> >>> -master-bin.000001 # Intvar # # INSERT_ID=10
> >>> -master-bin.000001 # User var # # @`b`=_latin1 0x3135
> >>> COLLATE latin1_swedish_ci
> >>> -master-bin.000001 # Execute_load_query # # use
> >>> `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE
> >>> `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES
> >>> TERMINATED BY '\n' (a, @b) SET b=((@b) + `bug27417`(2)) ;file_id=#
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t4)
> >>> +master-bin.000001 # Table_map # # table_id: # (test.t1)
> >>> +master-bin.000001 # Write_rows # # table_id: #
> >>> +master-bin.000001 # Write_rows # # table_id: #
> >>> +master-bin.000001 # Write_rows # # table_id: # flags:
> >>> STMT_END_F
> >>> master-bin.000001 # Query # # ROLLBACK
> >>> drop trigger trg_del_t2;
> >>> drop table t1,t2,t3,t4,t5;
> >>>
> >>> === added file
> >>> 'mysql-test/suite/rpl/r/rpl_failure_on_tmp_table_and_DDL.result'
> >>> ---
> >>> a/mysql-test/suite/rpl/r/rpl_failure_on_tmp_table_and_DDL.result
> >>> 1970-01-01 00:00:00 +0000
> >>> +++
> >>> b/mysql-test/suite/rpl/r/rpl_failure_on_tmp_table_and_DDL.result
> >>> 2009-12-29 08:54:57 +0000
> >>> @@ -0,0 +1,109 @@
> >>> +stop slave;
> >>> +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
> >>> +reset master;
> >>> +reset slave;
> >>> +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
> >>> +start slave;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing";
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +ALTER EVENT e1 ON SCHEDULE EVERY 20 HOUR DO SELECT "nothing";
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DROP EVENT IF EXISTS e1;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE PROCEDURE p1() BEGIN SELECT 1 ; END |
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +ALTER PROCEDURE p1 SQL SECURITY INVOKER;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DROP PROCEDURE p1;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE FUNCTION f1() RETURNS INT RETURN 123;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +ALTER FUNCTION f1 SQL SECURITY INVOKER;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DROP FUNCTION f1;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE TABLE t2 (b int);
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DROP TABLE t2;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE DATABASE mysqltest1;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DROP DATABASE mysqltest1;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE TABLE t2 (a int);
> >>> +CREATE TABLE t3 (a int);
> >>> +CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2
> >>> VALUES(22);
> >>> +insert into t3 values (1);
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +drop table t2, t3;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE TABLE t2 (a int);
> >>> +CREATE VIEW v2 AS SELECT * FROM t2;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DROP VIEW v2;
> >>> +drop table t2;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE USER test_1@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +create table t2 (a int, b int);
> >>> +grant select on t2 to test_1@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +create procedure stamp() insert into t2 values (1,2);
> >>> +grant execute on procedure stamp to test_1@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +DROP PROCEDURE stamp;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +grant usage on *.* to test_1@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +revoke all privileges on t2 from test_1@localhost;
> >>> +revoke usage on *.* from test_1@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +RENAME USER test_1@localhost TO test_2@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +drop user test_2@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +drop table t2;
> >>>
> >>> === added file
> >>> 'mysql-test/suite/rpl/t/rpl_failure_on_tmp_table_and_DDL.test'
> >>> --- a/mysql-test/suite/rpl/t/rpl_failure_on_tmp_table_and_DDL.test
> >>> 1970-01-01 00:00:00 +0000
> >>> +++ b/mysql-test/suite/rpl/t/rpl_failure_on_tmp_table_and_DDL.test
> >>> 2009-12-29 08:54:57 +0000
> >>> @@ -0,0 +1,164 @@
> >>> +#
> >>> +# Bug#49132
> >>> +# This test verify if executing DDL statement before trying to +#
> >>> manipulate a temporary table causes row-based replication to +# break
> >>> with error 'table does not exist'.
> >>> +#
> >>> +--source include/master-slave.inc
> >>> +--source include/have_binlog_format_row.inc
> >>> +
> >>> +# CREATE EVENT when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing";
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# ALTER EVENT when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +ALTER EVENT e1 ON SCHEDULE EVERY 20 HOUR DO SELECT "nothing";
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# DROP EVENT when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DROP EVENT IF EXISTS e1;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# CREATE PROCEDURE when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DELIMITER |;
> >>> +CREATE PROCEDURE p1() BEGIN SELECT 1 ; END |
> >>> +DELIMITER ;|
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# Alter PROCEDURE when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +ALTER PROCEDURE p1 SQL SECURITY INVOKER;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +
> >>> +# DROP PROCEDURE when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DROP PROCEDURE p1;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# CREATE FUNCTION when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE FUNCTION f1() RETURNS INT RETURN 123;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# ALTER FUNCTION when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +ALTER FUNCTION f1 SQL SECURITY INVOKER;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# DROP FUNCTION when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DROP FUNCTION f1;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# CREATE TABLE when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE TABLE t2 (b int);
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# DROP TABLE when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DROP TABLE t2;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# CREATE DATABASE when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE DATABASE mysqltest1;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# DROP DATABASE when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DROP DATABASE mysqltest1;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# CREATE TRIGGER when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE TABLE t2 (a int);
> >>> +CREATE TABLE t3 (a int);
> >>> +CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2
> >>> VALUES(22);
> >>> +insert into t3 values (1);
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +drop table t2, t3;
> >>> +
> >>> +# CREATE VIEW when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE TABLE t2 (a int);
> >>> +CREATE VIEW v2 AS SELECT * FROM t2;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# DROP VIEW when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +DROP VIEW v2;
> >>> +drop table t2;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# CREATE USER when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +CREATE USER test_1@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# TABLE GRANT when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +create table t2 (a int, b int);
> >>> +grant select on t2 to test_1@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# ROUTINE GRANT when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +create procedure stamp() insert into t2 values (1,2);
> >>> +grant execute on procedure stamp to test_1@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +DROP PROCEDURE stamp;
> >>> +
> >>> +# MYSQL GRANT when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +grant usage on *.* to test_1@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# REVOKE PRIVILEGES when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +revoke all privileges on t2 from test_1@localhost;
> >>> +revoke usage on *.* from test_1@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# RENAME USER when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +RENAME USER test_1@localhost TO test_2@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +# DROP USER when a temporary table is open.
> >>> +CREATE TEMPORARY TABLE t1 (a int);
> >>> +drop user test_2@localhost;
> >>> +insert into t1 values (1);
> >>> +DROP TEMPORARY TABLE t1;
> >>> +
> >>> +drop table t2;
> >>> +
> >>> +--sync_slave_with_master
> >>> +
> >>>
> >>> === modified file 'sql/events.cc'
> >>> --- a/sql/events.cc 2009-10-16 10:29:42 +0000
> >>> +++ b/sql/events.cc 2009-12-29 08:54:57 +0000
> >>> @@ -479,6 +479,9 @@ Events::create_event(THD *thd, Event_par
> >>> }
> >>> }
> >>> pthread_mutex_unlock(&LOCK_event_metadata);
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>>
> >>> DBUG_RETURN(ret);
> >>> }
> >>> @@ -600,6 +603,9 @@ Events::update_event(THD *thd, Event_par
> >>> }
> >>> }
> >>> pthread_mutex_unlock(&LOCK_event_metadata);
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>>
> >>> DBUG_RETURN(ret);
> >>> }
> >>> @@ -674,6 +680,9 @@ Events::drop_event(THD *thd, LEX_STRING
> >>> write_bin_log(thd, TRUE, thd->query(), thd->query_length());
> >>> }
> >>> pthread_mutex_unlock(&LOCK_event_metadata);
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE; DBUG_RETURN(ret);
> >>> }
> >>>
> >>>
> >>> === modified file 'sql/sp.cc'
> >>> --- a/sql/sp.cc 2009-12-02 11:17:08 +0000
> >>> +++ b/sql/sp.cc 2009-12-29 08:54:57 +0000
> >>> @@ -1118,6 +1118,9 @@ done:
> >>> thd->variables.sql_mode= saved_mode;
> >>>
> >>> close_thread_tables(thd);
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(ret);
> >>> }
> >>>
> >>> @@ -1171,6 +1174,9 @@ sp_drop_routine(THD *thd, int type, sp_n
> >>> }
> >>>
> >>> close_thread_tables(thd);
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(ret);
> >>> }
> >>>
> >>> @@ -1241,6 +1247,9 @@ sp_update_routine(THD *thd, int type, sp
> >>> }
> >>>
> >>> close_thread_tables(thd);
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(ret);
> >>> }
> >>>
> >>>
> >>> === modified file 'sql/sql_acl.cc'
> >>> --- a/sql/sql_acl.cc 2009-12-06 23:12:11 +0000
> >>> +++ b/sql/sql_acl.cc 2009-12-29 08:54:57 +0000
> >>> @@ -3083,7 +3083,12 @@ int mysql_table_grant(THD *thd, TABLE_LI
> >>> */
> >>> tables[0].updating= tables[1].updating= tables[2].updating= 1;
> >>> if (!(thd->spcont || rpl_filter->tables_ok(0, tables)))
> >>> + {
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(FALSE);
> >>> + }
> >>> }
> >>> #endif
> >>>
> >>> @@ -3096,6 +3101,9 @@ int mysql_table_grant(THD *thd, TABLE_LI
> >>> if (simple_open_n_lock_tables(thd,tables))
> >>> { // Should never happen
> >>> close_thread_tables(thd); /* purecov: deadcode */
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(TRUE); /* purecov: deadcode */
> >>> }
> >>>
> >>> @@ -3222,6 +3230,9 @@ int mysql_table_grant(THD *thd, TABLE_LI
> >>>
> >>> /* Tables are automatically closed */
> >>> thd->lex->restore_backup_query_tables_list(&backup);
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(result);
> >>> }
> >>>
> >>> @@ -3300,13 +3311,21 @@ bool mysql_routine_grant(THD *thd, TABLE
> >>> */
> >>> tables[0].updating= tables[1].updating= 1;
> >>> if (!(thd->spcont || rpl_filter->tables_ok(0, tables)))
> >>> + {
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(FALSE);
> >>> + }
> >>> }
> >>> #endif
> >>>
> >>> if (simple_open_n_lock_tables(thd,tables))
> >>> { // Should never happen
> >>> close_thread_tables(thd);
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(TRUE);
> >>> }
> >>>
> >>> @@ -3382,6 +3401,9 @@ bool mysql_routine_grant(THD *thd, TABLE
> >>> }
> >>>
> >>> rw_unlock(&LOCK_grant);
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>>
> >>> /* Tables are automatically closed */
> >>> DBUG_RETURN(result);
> >>> @@ -3439,13 +3461,21 @@ bool mysql_grant(THD *thd, const char *d
> >>> */
> >>> tables[0].updating= tables[1].updating= 1;
> >>> if (!(thd->spcont || rpl_filter->tables_ok(0, tables)))
> >>> + {
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(FALSE);
> >>> + }
> >>> }
> >>> #endif
> >>>
> >>> if (simple_open_n_lock_tables(thd,tables))
> >>> { // This should never happen
> >>> close_thread_tables(thd); /* purecov: deadcode */
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(TRUE); /* purecov: deadcode */
> >>> }
> >>>
> >>> @@ -3505,6 +3535,9 @@ bool mysql_grant(THD *thd, const char *d
> >>>
> >>> if (!result)
> >>> my_ok(thd);
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>>
> >>> DBUG_RETURN(result);
> >>> }
> >>> @@ -5672,7 +5705,12 @@ bool mysql_create_user(THD *thd, List <L
> >>>
> >>> /* CREATE USER may be skipped on replication client. */
> >>> if ((result= open_grant_tables(thd, tables)))
> >>> + {
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(result != 1);
> >>> + }
> >>>
> >>> rw_wrlock(&LOCK_grant);
> >>> VOID(pthread_mutex_lock(&acl_cache->lock));
> >>> @@ -5715,6 +5753,9 @@ bool mysql_create_user(THD *thd, List <L
> >>>
> >>> rw_unlock(&LOCK_grant);
> >>> close_thread_tables(thd);
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(result);
> >>> }
> >>>
> >>> @@ -5752,7 +5793,12 @@ bool mysql_drop_user(THD *thd, List <LEX
> >>>
> >>> /* DROP USER may be skipped on replication client. */
> >>> if ((result= open_grant_tables(thd, tables)))
> >>> + {
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(result != 1);
> >>> + }
> >>>
> >>> thd->variables.sql_mode&= ~MODE_PAD_CHAR_TO_FULL_LENGTH;
> >>>
> >>> @@ -5789,6 +5835,9 @@ bool mysql_drop_user(THD *thd, List <LEX
> >>> rw_unlock(&LOCK_grant);
> >>> close_thread_tables(thd);
> >>> thd->variables.sql_mode= old_sql_mode;
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(result);
> >>> }
> >>>
> >>> @@ -5826,7 +5875,12 @@ bool mysql_rename_user(THD *thd, List <L
> >>>
> >>> /* RENAME USER may be skipped on replication client. */
> >>> if ((result= open_grant_tables(thd, tables)))
> >>> + {
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(result != 1);
> >>> + }
> >>>
> >>> rw_wrlock(&LOCK_grant);
> >>> VOID(pthread_mutex_lock(&acl_cache->lock));
> >>> @@ -5873,6 +5927,9 @@ bool mysql_rename_user(THD *thd, List <L
> >>>
> >>> rw_unlock(&LOCK_grant);
> >>> close_thread_tables(thd);
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(result);
> >>> }
> >>>
> >>> @@ -5907,7 +5964,12 @@ bool mysql_revoke_all(THD *thd, List <L
> >>> thd->clear_current_stmt_binlog_row_based();
> >>>
> >>> if ((result= open_grant_tables(thd, tables)))
> >>> + {
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>> DBUG_RETURN(result != 1);
> >>> + }
> >>>
> >>> rw_wrlock(&LOCK_grant);
> >>> VOID(pthread_mutex_lock(&acl_cache->lock));
> >>> @@ -6058,6 +6120,9 @@ bool mysql_revoke_all(THD *thd, List <L
> >>>
> >>> if (result)
> >>> my_message(ER_REVOKE_GRANTS, ER(ER_REVOKE_GRANTS), MYF(0));
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>>
> >>> DBUG_RETURN(result);
> >>> }
> >>> @@ -6193,6 +6258,9 @@ bool sp_revoke_privileges(THD *thd, cons
> >>> close_thread_tables(thd);
> >>>
> >>> thd->pop_internal_handler();
> >>> + /* Restore the state of binlog format */
> >>> + if (thd->variables.binlog_format == BINLOG_FORMAT_ROW)
> >>> + thd->current_stmt_binlog_row_based= TRUE;
> >>>
> >>> DBUG_RETURN(error_handler.has_errors());
> >>> }
> >>>
> >>>
> >>
> >>
> >>
> >
> >
>
