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
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`/*!*/; 1384177232/ *!*/; pseudo_ thread_ id=4/*! */; foreign_ key_checks= 1, @@session. sql_auto_ is_null= 0, @@session. unique_ checks= 1, @@session. autocommit= 1/*!*/; sql_mode= 0/*!*/; auto_increment_ increment= 1, @@session. auto_increment_ offset= 1/*!*/; character_ set_client= 33,@@session. collation_ connection= 33,@@session. collation_ server= 8/*!*/; lc_time_ names=0/ *!*/; collation_ database= DEFAULT/ *!*/; 1384177232/ *!*/; source( `id` int) 1384177232/ *!*/; .`test_ bug_source` mapped to number 33
SET TIMESTAMP=
SET @@session.
SET @@session.
SET @@session.
SET @@session.
/*!\C utf8 *//*!*/;
SET @@session.
SET @@session.
SET @@session.
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=
CREATE TABLE test_bug_
/*!*/;
# 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=
BEGIN
/*!*/;
# at 409
# at 465
#131111 19:10:32 server id 1 end_log_pos 465 Table_map: `sbtest`
#131111 19:10:32 server id 1 end_log_pos 499 Write_rows: table id 33 flags: STMT_END_F
BINLOG ' AANEBAAAAACEAAA AAAAEABnNidGVzd AAPdGVzdF9idWdf c291cmNlAAEDAAE = AAPMBAAAAACEAAA AAAAEAAf/ +AQAAAA= = 1384177239/ *!*/; 1384177239/ *!*/; 1384177239/ *!*/; TYPE=@OLD_ COMPLETION_ TYPE*/; PSEUDO_ SLAVE_MODE= 0*/;
UN6AUhMBAAAAOAA
UN6AUhcBAAAAIgA
'/*!*/;
# 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=
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=
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=
ROLLBACK/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_
/*!50530 SET @@SESSION.
======= ======= ======= ======= ======= ======= =
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.