Bug #39804 Failing CREATE ... SELECT doesn't appear in binary log.
Submitted: 2 Oct 2008 10:52 Modified: 22 Mar 2011 12:40
Reporter: Sergey Vojtovich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1, 6.0 bzr OS:Any
Assigned to: Libing Song CPU Architecture:Any

[2 Oct 2008 10:52] Sergey Vojtovich
Description:
Failing CREATE ... SELECT that updates non-transactional tables via stored function doesn't appear in binary log. That means slave and master may get out of sync.

This happens because select_create::abort() pretends to be transactional statement, not taking into account stored functions.

Tested with 6.0. 5.1 and 5.0 seem to be affected as well.

See also:
BUG#6682 - CREATE TABLE .. SELECT failing but entering binary log

How to repeat:
-- source include/have_binlog_format_mixed_or_statement.inc

set @@autocommit=0;
--delimiter |
CREATE FUNCTION f1() RETURNS INT
BEGIN
  insert into t2 values(1);
  insert into t3 values(1);
  return 1;
END|
--delimiter ;

CREATE TABLE t2(a INT);
CREATE TABLE t3(a INT) ENGINE=Falcon;
--error 1062
CREATE TABLE t1(UNIQUE(a)) SELECT 1 AS a UNION ALL SELECT f1();
--error 1062
CREATE TABLE t1(UNIQUE(a)) ENGINE=Falcon SELECT 1 AS a UNION ALL SELECT f1();
SELECT * FROM t2;
SELECT * FROM t3;

source include/show_binlog_events.inc;

Note that there is new data in t2, which didn't appear in the binary log.

Suggested fix:
The best fix would probably be to write failing CREATE ... SELECT when it updates some non-transactional tables via stored function. Another option is to always write failing CREATE ... SELECT into the binary log.
[2 Oct 2008 20:34] Sveta Smirnova
Thank you for the report.

Verified as described.
[17 Oct 2008 10:00] Sven Sandberg
BUG#40063 is a duplicate of this one. Note that it also contains a short discussion with three suggested solutions.
[13 Aug 2010 9:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/115649

3479 Li-Bing.Song@sun.com	2010-08-13
      Bug #39804 Failing CREATE ... SELECT doesn't appear in binary log
      Bug #51501  	RBR, slave stops with HA_ERR_KEY_NOT_FOUND
      
      Failing CREATE ... SELECT that updates non-transactional tables
      via stored function(bug#39804) or the creating non-transactional
      table already exists(Rows are inserted into it, but it can not be
      dropt) is not binlogged on SBR. The cause is that the statement
      will never be binlogged on SBR if it fails. As the table will be 
      dropt automatically after an error happens.
      
      In this patch, the statement will be binlogged on SBR if a other
      non-trasactional table is modified or the creating non-transactional
      table already exists(It means it can not be dropt).
      
      It also fix Bug#55625  RBR breaks on failing 'CREATE TABLE'
      
      It will be binlogged if a nontrasactional table is modified.
      But the 'CREATE TABLE' statement will always be binlogged even
      when the creating table is dropt automatically on master. But
      it can not be dropt automatically on slave. This will cause the 
      second 'CREATE TABLE' on the same name fails on slave(It can be
      executed on master well). 
      
      Because rows for different tables are in different events. So
      after this patch, only the row events on other tables are binlogged
      with 'ROLLBACK'. The 'CREATE TABLE' event and all row events on the
      creating table are truncated. They will never be binlogged.
     @ sql/log.cc
        add binlog_truncate_stmt() to truncate a statement.
     @ sql/sql_class.h
        add variable modified_other_non_trans_table in select_create class.
        It is set on the rare occasion that SELECT clause calls a function
        in which a non-transaction table is modified.
[19 Aug 2010 13:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116253

3227 Li-Bing.Song@sun.com	2010-08-19
      Bug #39804 Failing CREATE ... SELECT doesn't appear in binary log.
      
      Failing CREATE ... SELECT that updates non-transactional tables
      via stored function is not binlogged on SBR. The cause is that the
      statement will never be binlogged on SBR if it fails. As the table
      will be dropt automatically after an error happens.
            
      In this patch, the statement will be binlogged on SBR if a other
      non-trasactional table is modified.
     @ mysql-test/include/rpl_diff_tables.inc
        Always switches to master at the end.
     @ mysql-test/suite/rpl/t/rpl_create_table.test
        Add test for bug#39804
     @ sql/sql_class.h
        Add modified_other_non_trans_table into select_create class.
     @ sql/sql_insert.cc
        Binlog the CTS statement if other non-trasactional tables is modified.
[23 Aug 2010 8:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116473

3227 Li-Bing.Song@sun.com	2010-08-23
      Bug #39804 Failing CREATE ... SELECT doesn't appear in binary log.
      
      Failing CREATE ... SELECT that updates non-transactional tables
      via stored function is not binlogged on SBR. The cause is that the
      statement will never be binlogged on SBR if it fails. As the table
      will be dropt automatically after an error happens.
            
      In this patch, the statement will be binlogged on SBR if a other
      non-trasactional table is modified.
     @ mysql-test/include/rpl_diff_tables.inc
        Always switches to master at the end.
     @ mysql-test/suite/rpl/t/rpl_create_table.test
        Add test for bug#39804
     @ sql/sql_class.h
        Add modified_other_non_trans_table into select_create class.
     @ sql/sql_insert.cc
        Binlog the CTS statement if other non-trasactional tables is modified.
        We cannot tmp_disable_binlog() and always set 
        thd->transaction.stmt.modified_non_trans_table FALSE. As we need binlog
        the statement on some occasions.
[25 Aug 2010 14:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116761

3227 Li-Bing.Song@sun.com	2010-08-25
      Bug #39804 Failing CREATE ... SELECT doesn't appear in binary log.
      
      Failing CREATE ... SELECT that updates non-transactional tables
      via stored function is not binlogged on SBR. The cause is that the
      statement will never be binlogged on SBR if it fails. As the table
      will be dropt automatically after an error happens.
            
      In this patch, the statement will be binlogged on SBR if a other
      non-trasactional table is modified.
     @ mysql-test/include/rpl_diff_tables.inc
        Always switches to master at the end.
     @ mysql-test/suite/rpl/t/rpl_create_table.test
        Add test for bug#39804
     @ sql/sql_class.h
        Add modified_other_non_trans_table into select_create class.
     @ sql/sql_insert.cc
        Binlog the CTS statement if other non-trasactional tables is modified.
        We cannot tmp_disable_binlog() and always set 
        thd->transaction.stmt.modified_non_trans_table FALSE. As we need binlog
        the statement on some occasions.
[26 Aug 2010 2:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116809

3227 Li-Bing.Song@sun.com	2010-08-26
      Bug #39804 Failing CREATE ... SELECT doesn't appear in binary log.
      
      Failing CREATE ... SELECT that updates non-transactional tables
      via stored function is not binlogged on SBR. The cause is that the
      statement will never be binlogged on SBR if it fails. As the table
      will be dropt automatically after an error happens.
            
      In this patch, the statement will be binlogged on SBR if a other
      non-trasactional table is modified.
     @ mysql-test/include/rpl_diff_tables.inc
        Always switches to master at the end.
     @ mysql-test/suite/rpl/t/rpl_create_table.test
        Add test for bug#39804
     @ sql/sql_class.h
        Add modified_other_non_trans_table into select_create class.
     @ sql/sql_insert.cc
        Binlog the CTS statement if other non-trasactional tables is modified.
        
        tmp_disable_binlog() appeared to be redundant in the context of
        create_select::abort_result_set(). It's correct to calculate value of
        thd->transaction.stmt.modified_non_trans_table before
        create_select::abort_result_set() that logs in STMT format a way
        dependent on `modified_non_trans_table'.
        The calculated value continues to control logging in ROW format same way
        as it was in the base code before the patch.
[30 Aug 2010 2:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117082

3257 Li-Bing.Song@sun.com	2010-08-30
      Bug #39804 Failing CREATE ... SELECT doesn't appear in binary log.
      
      Failing CREATE ... SELECT that updates non-transactional tables
      via stored function is not binlogged on SBR. The cause is that the
      statement will never be binlogged on SBR if it fails. As the table
      will be dropt automatically after an error happens.
            
      In this patch, the statement will be binlogged on SBR if a other
      non-trasactional table is modified.
     @ mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test
        After this patch, It will not generate an incident event, even the
        creating table is a MyIASM table. As 'CREATE TABLE ...' part and row
        events of the creating table will never be binlogged and slave will 
        always keep in consisteny with master.
     @ mysql-test/include/rpl_diff_tables.inc
        Always switches to master at the end.
     @ mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result
        After this patch, It will not generate an incident event, even the
        creating table is a MyIASM table. As 'CREATE TABLE ...' part and row
        events of the creating table will never be binlogged and slave will 
        always keep in consisteny with master.
     @ mysql-test/suite/rpl/t/rpl_create_table.result
        Rename this file. Now not only 'CREATE TABLE IF NOT EXISTS' but also
        all other 'CREATE TABLE' statemens' tests can be put in this file.
        
        Added test for bug#39804.
     @ mysql-test/suite/rpl/t/rpl_create_table.test
        Rename this file. Now not only 'CREATE TABLE IF NOT EXISTS' but also
        all other 'CREATE TABLE' statemens' tests can be put in this file.
        
        Added test for bug#39804.
     @ sql/binlog.cc
        Added function MYSQL_BIN_LOG::reset_gathered_updates() to reset trx_cache.
     @ sql/binlog.h
        Added function MYSQL_BIN_LOG::reset_gathered_updates() to reset trx_cache.
     @ sql/sql_class.h
        Add modified_other_non_trans_table into select_create class.
     @ sql/sql_insert.cc
        Binlog the CTS statement if other non-trasactional tables is modified.
        
        tmp_disable_binlog() appeared to be redundant in the context of
        create_select::abort_result_set(). It's correct to calculate value of
        thd->transaction.stmt.modified_non_trans_table before
        create_select::abort_result_set() that logs in STMT format a way
        dependent on `modified_non_trans_table'.
        The calculated value continues to control logging in ROW format same way
        as it was in the base code before the patch.
[3 Sep 2010 4:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117480

3257 Li-Bing.Song@sun.com	2010-09-03
      Bug #39804 Failing CREATE ... SELECT doesn't appear in binary log.
      
      Failing CREATE ... SELECT that updates non-transactional tables
      via stored function is not binlogged on SBR. The cause is that the
      statement will never be binlogged on SBR if it fails. As the table
      will be dropt automatically after an error happens.
            
      In this patch, the statement will be binlogged on SBR, if another
      non-transactional table is modified. On RBR, only the row events of
      the other non-transactional table are binlogged.
     @ mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test
        After this patch, It will not generate an incident event, even the
        creating table is a MyIASM table. As 'CREATE TABLE ...' part and row
        events of the creating table will never be binlogged and slave will 
        always keep in consisteny with master.
     @ mysql-test/include/rpl_diff_tables.inc
        Always switches to master at the end.
     @ mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result
        After this patch, It will not generate an incident event, even the
        creating table is a MyIASM table. As 'CREATE TABLE ...' part and row
        events of the creating table will never be binlogged and slave will 
        always keep in consisteny with master.
     @ mysql-test/suite/rpl/t/rpl_create_table.result
        Rename this file. Now not only 'CREATE TABLE IF NOT EXISTS' but also
        all other 'CREATE TABLE' statemens' tests can be put in this file.
        
        Added test for bug#39804.
     @ mysql-test/suite/rpl/t/rpl_create_table.test
        Rename this file. Now not only 'CREATE TABLE IF NOT EXISTS' but also
        all other 'CREATE TABLE' statemens' tests can be put in this file.
        
        Added test for bug#39804.
     @ sql/binlog.cc
        Trx-cache is always able to be truncated when a SINGLE statement transaction
        rolls back and is on RBR.
     @ sql/sql_class.h
        Add modified_other_non_trans_table into select_create class.
     @ sql/sql_insert.cc
        Binlog the CTS statement if other non-trasactional tables is modified.
        
        tmp_disable_binlog() appeared to be redundant in the context of
        create_select::abort_result_set(). It's correct to calculate value of
        thd->transaction.stmt.modified_non_trans_table before
        create_select::abort_result_set() that logs in STMT format a way
        dependent on `modified_non_trans_table'.
        The calculated value continues to control logging in ROW format same way
        as it was in the base code before the patch.
     @ sql/sql_parse.cc
        OPTION_KEEP_LOG should be set only when the temporary table is
        created successfully on RBR.
[7 Sep 2010 3:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117649

3257 Li-Bing.Song@sun.com	2010-09-07
      Bug #39804 Failing CREATE ... SELECT doesn't appear in binary log.
      
      Failing CREATE ... SELECT that updates non-transactional tables
      via stored function is not binlogged on SBR. The cause is that the
      statement will never be binlogged on SBR if it fails. As the table
      will be dropt automatically after an error happens.
            
      In this patch, the statement will be binlogged on SBR, if another
      non-transactional table is modified. On RBR, only the row events of
      the other non-transactional table are binlogged.
     @ mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test
        After this patch, It will not generate an incident event, even the
        creating table is a MyIASM table. As 'CREATE TABLE ...' part and row
        events of the creating table will never be binlogged and slave will 
        always keep in consisteny with master.
     @ mysql-test/include/rpl_diff_tables.inc
        Always switches to master at the end.
     @ mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result
        After this patch, It will not generate an incident event, even the
        creating table is a MyIASM table. As 'CREATE TABLE ...' part and row
        events of the creating table will never be binlogged and slave will 
        always keep in consisteny with master.
     @ mysql-test/suite/rpl/t/rpl_create_table.result
        Rename this file. Now not only 'CREATE TABLE IF NOT EXISTS' but also
        all other 'CREATE TABLE' statemens tests can be put in this file.
        
        Added test for bug#39804.
     @ mysql-test/suite/rpl/t/rpl_create_table.test
        Rename this file. Now not only 'CREATE TABLE IF NOT EXISTS' but also
        all other 'CREATE TABLE' statemens tests can be put in this file.
        
        Added test for bug#39804.
     @ sql/binlog.cc
        Trx-cache is always able to be truncated when a SINGLE statement transaction
        rolls back and is on RBR.
     @ sql/sql_class.h
        Add modified_other_non_trans_table into select_create class.
     @ sql/sql_insert.cc
        Binlog the CTS statement if other non-transactional tables is modified.
        
        tmp_disable_binlog() appeared to be redundant in the context of
        create_select::abort_result_set(). It's correct to calculate value of
        thd->transaction.stmt.modified_non_trans_table before
        create_select::abort_result_set() that logs in STMT format a way
        dependent on `modified_non_trans_table'.
        The calculated value continues to control logging in ROW format same way
        as it was in the base code before the patch.
     @ sql/sql_parse.cc
        OPTION_KEEP_LOG should be set only when the temporary table is
        created successfully on RBR.
[7 Sep 2010 13:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117707

3257 Li-Bing.Song@sun.com	2010-09-07
      Bug #39804 Failing CREATE ... SELECT doesn't appear in binary log.
      
      Failing CREATE ... SELECT that updates non-transactional tables
      via stored function is not binlogged on SBR. The cause is that the
      statement will never be binlogged on SBR if it fails. As the table
      will be dropt automatically after an error happens.
            
      In this patch, the statement will be binlogged on SBR, if another
      non-transactional table is modified. On RBR, only the row events of
      the other non-transactional table are binlogged.
     @ mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test
        After this patch, It will not generate an incident event, even the
        creating table is a MyIASM table. As 'CREATE TABLE ...' part and row
        events of the creating table will never be binlogged and slave will 
        always keep in consisteny with master.
     @ mysql-test/include/rpl_diff_tables.inc
        Always switches to master at the end.
     @ mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result
        After this patch, It will not generate an incident event, even the
        creating table is a MyIASM table. As 'CREATE TABLE ...' part and row
        events of the creating table will never be binlogged and slave will 
        always keep in consisteny with master.
     @ mysql-test/suite/rpl/t/rpl_create_table.result
        Rename this file. Now not only 'CREATE TABLE IF NOT EXISTS' but also
        all other 'CREATE TABLE' statemens tests can be put in this file.
        
        Added test for bug#39804.
     @ mysql-test/suite/rpl/t/rpl_create_table.test
        Rename this file. Now not only 'CREATE TABLE IF NOT EXISTS' but also
        all other 'CREATE TABLE' statemens tests can be put in this file.
        
        Added test for bug#39804.
     @ sql/binlog.cc
        Add THD::reset_gathered_updates() to truncate the current statement.
     @ sql/sql_class.h
        Add modified_other_non_trans_table into select_create class.
     @ sql/sql_insert.cc
        Binlog the CTS statement if other non-transactional tables is modified.
        
        tmp_disable_binlog() appeared to be redundant in the context of
        create_select::abort_result_set(). It's correct to calculate value of
        thd->transaction.stmt.modified_non_trans_table before
        create_select::abort_result_set() that logs in STMT format a way
        dependent on `modified_non_trans_table'.
        The calculated value continues to control logging in ROW format same way
        as it was in the base code before the patch.
     @ sql/sql_parse.cc
        OPTION_KEEP_LOG should be set only when the temporary table is
        created successfully on RBR.
[11 Oct 2010 3:31] Libing Song
WL#5576 will prohibit CTS modify any other tables.
It is a better resolution for this bug.
So the patch for this bug will not be pushed, even though
it has been approved.
[30 Dec 2010 7:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127695

3449 Li-Bing.Song@sun.com	2010-12-30
      WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables                                                                                                                     
      BUG#39804 and BUG#55876 are fixed by this worklog.
                                                                                                                                                                                            
      In 'CREATE TABLE ... SELECT' statement, SELECT clause could call store functions                                                                                                    
      to modify other tables. It made CTS's behavior towards complex and not                                                                                                              
      understandable. It also caused some binlogging problem. So the behavior modifying                                                                                                   
      other tables is prohibited in this worklog.                                                                                                                                         
                                                                                                                                                                                            
      In this patch, code is added to check if there is any other table owning WRITE                                                                                                      
      lock just after all tables have been locked. CREATE TABLE ... SELECT will fail                                                                                                      
      and print an error immediately if any other table has WRITE lock.                                                                                                                                                                                                                                                                                                      
           
     @ mysql-test/include/unsafe_binlog.inc
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/create.result
        Added test to verify WL#5576.
     @ mysql-test/r/func_rollback.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/mdl_sync.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/unsafe_binlog_innodb.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/suite/innodb/r/innodb.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/suite/innodb/t/innodb.test
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/t/create.test
        Added test to verify WL#5576.
     @ mysql-test/t/func_rollback.test
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/t/mdl_sync.test
        'FOR UPDATE' is disallowed in CREATE TABLE ... SELECT statement
        after WL#5576. Using a new method to lock the table.
     @ sql/share/errmsg-utf8.txt
        Added a new error for WL#5576.
     @ sql/sql_parse.cc
        Implemented WL#5576.
[30 Dec 2010 8:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127696

3449 Li-Bing.Song@sun.com	2010-12-30
      WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables
      BUG#39804 and BUG#55876 are fixed by this worklog.
                                                                                                                                                                                            
      In 'CREATE TABLE ... SELECT' statement, SELECT clause could call store functions
      to modify other tables. It made CTS's behavior towards complex and not
      understandable. It also caused some binlogging problem. So the behavior modifying
      other tables is prohibited in this worklog.
                                                                                                                                                                                            
      In this patch, code is added to check if there is any other table owning WRITE
      lock just after all tables have been locked. CREATE TABLE ... SELECT will fail
      and print an error immediately if any other table has WRITE lock.                                                                                                                                                                                                                                                                                                      
           
     @ mysql-test/include/unsafe_binlog.inc
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/create.result
        Added test to verify WL#5576.
     @ mysql-test/r/func_rollback.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/mdl_sync.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/unsafe_binlog_innodb.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/suite/innodb/r/innodb.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/suite/innodb/t/innodb.test
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/t/create.test
        Added test to verify WL#5576.
     @ mysql-test/t/func_rollback.test
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/t/mdl_sync.test
        'FOR UPDATE' is disallowed in CREATE TABLE ... SELECT statement
        after WL#5576. Using a new method to lock the table.
     @ sql/share/errmsg-utf8.txt
        Added a new error for WL#5576.
     @ sql/sql_parse.cc
        Implemented WL#5576.
[30 Dec 2010 8:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127697

3449 Li-Bing.Song@sun.com	2010-12-30
      WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables
      BUG#39804 and BUG#55876 are fixed by this worklog.
      
      In 'CREATE TABLE ... SELECT' statement, SELECT clause could call store functions
      to modify other tables. It made CTS's behavior towards complex and not
      understandable. It also caused some binlogging problem. So the behavior modifying
      other tables is prohibited in this worklog.
      
      In this patch, code is added to check if there is any other table owning WRITE
      lock just after all tables have been locked. CREATE TABLE ... SELECT will fail
      and print an error immediately if any other table has WRITE lock.                                                                                                                                                                                                                                                                                                      
           
     @ mysql-test/include/unsafe_binlog.inc
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/create.result
        Added test to verify WL#5576.
     @ mysql-test/r/func_rollback.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/mdl_sync.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/unsafe_binlog_innodb.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/suite/innodb/r/innodb.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/suite/innodb/t/innodb.test
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/t/create.test
        Added test to verify WL#5576.
     @ mysql-test/t/func_rollback.test
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/t/mdl_sync.test
        'FOR UPDATE' is disallowed in CREATE TABLE ... SELECT statement
        after WL#5576. Using a new method to lock the table.
     @ sql/share/errmsg-utf8.txt
        Added a new error for WL#5576.
     @ sql/sql_parse.cc
        Implemented WL#5576.
[30 Dec 2010 8:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127698

3449 Li-Bing.Song@sun.com	2010-12-30
      WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables
      BUG#39804 and BUG#55876 are fixed by this worklog.
      
      In 'CREATE TABLE ... SELECT' statement, SELECT clause could call store functions
      to modify other tables. It made CTS's behavior towards complex and not
      understandable. It also caused some binlogging problem. So the behavior modifying
      other tables is prohibited in this worklog.
      
      In this patch, code is added to check if there is any other table owning WRITE
      lock just after all tables have been locked. CREATE TABLE ... SELECT will fail
      and print an error immediately if any other table has WRITE lock.   
     @ mysql-test/include/unsafe_binlog.inc
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/create.result
        Added test to verify WL#5576.
     @ mysql-test/r/func_rollback.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/mdl_sync.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/r/unsafe_binlog_innodb.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/suite/innodb/r/innodb.result
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/suite/innodb/t/innodb.test
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/t/create.test
        Added test to verify WL#5576.
     @ mysql-test/t/func_rollback.test
        The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576.
     @ mysql-test/t/mdl_sync.test
        'FOR UPDATE' is disallowed in CREATE TABLE ... SELECT statement
        after WL#5576. Using a new method to lock the table.
     @ sql/share/errmsg-utf8.txt
        Added a new error for WL#5576.
     @ sql/sql_parse.cc
        Implemented WL#5576.
[21 Feb 2011 6:06] Libing Song
It was fixed by the patch for WL#5576.
Pushed to mysql-trunk.
http://lists.mysql.com/commits/131701
[22 Mar 2011 12:40] Jon Stephens
Fixed by WL#5576, q.v. for docs info. Closed.