Comment 20 for bug 1240098

Revision history for this message
Raghavendra D Prabhu (raghavendra-prabhu) wrote : Re: CTAS fails with explicit TEMPORARY tables | binlogging enabled

I can confirm this. This happens only when binlogging is enabled.

========================================

mysql> use sbtest;
Database changed
mysql>
mysql>
mysql> DROP TABLE IF EXISTS test_bug_source;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE test_bug_source(`id` int);
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> INSERT INTO
    -> test_bug_source
    -> VALUES
    -> (1)
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TEMPORARY TABLE IF EXISTS test_bug;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TEMPORARY TABLE test_bug AS
    -> SELECT
    -> *
    -> FROM
    -> test_bug_source
    -> ;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> COMMIT;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
==========================================

This is how binlog looks:

========================================

use `sbtest`/*!*/;
SET TIMESTAMP=1384177232/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE IF EXISTS `test_bug_source` /* generated by server */
/*!*/;
# at 236
#131111 19:10:32 server id 1 end_log_pos 339 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1384177232/*!*/;
CREATE TABLE test_bug_source(`id` int)
/*!*/;
# at 339
#131111 19:10:32 server id 1 end_log_pos 409 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1384177232/*!*/;
BEGIN
/*!*/;
# at 409
# at 465
#131111 19:10:32 server id 1 end_log_pos 465 Table_map: `sbtest`.`test_bug_source` mapped to number 33
#131111 19:10:32 server id 1 end_log_pos 499 Write_rows: table id 33 flags: STMT_END_F

BINLOG '
UN6AUhMBAAAAOAAAANEBAAAAACEAAAAAAAEABnNidGVzdAAPdGVzdF9idWdfc291cmNlAAEDAAE=
UN6AUhcBAAAAIgAAAPMBAAAAACEAAAAAAAEAAf/+AQAAAA==
'/*!*/;
# at 499
#131111 19:10:32 server id 1 end_log_pos 526 Xid = 14
COMMIT/*!*/;
# at 526
#131111 19:10:39 server id 1 end_log_pos 596 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1384177239/*!*/;
BEGIN
/*!*/;
# at 596
#131111 19:10:39 server id 1 end_log_pos 737 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1384177239/*!*/;
DROP TEMPORARY TABLE IF EXISTS `sbtest`.`test_bug` /* generated by server */
/*!*/;
# at 737
#131111 19:10:39 server id 1 end_log_pos 810 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1384177239/*!*/;
ROLLBACK/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

===========================================

From the above, it looks like

a) DROP TEMPORARY TABLE IF EXISTS is written to binlog.
b) CTAS is not written

b) is due to https://bugs.launchpad.net/codership-mysql/+bug/1246257

Now, the fix may be to prevent a) as well.