LP #1240098: CTAS fails with explicit TEMPORARY tables, binlogging enabled and autocommit=0

**Reported in Launchpad by Francesco last update 29-01-2015 04:08:47

I have a cluster of 3 nodes all running Percona-XtraDB-Cluster-server.x86_64 1:5.5.33-23.7.6.495.rhel6.

When I run this SQL:

USE test;

DROP TABLE IF EXISTS test_bug_source;
CREATE TABLE test_bug_source(`id` int);

INSERT INTO
test_bug_source
VALUES
(1)
;
COMMIT;

START TRANSACTION;
DROP TEMPORARY TABLE IF EXISTS test_bug;
CREATE TEMPORARY TABLE test_bug AS
SELECT
*
FROM
test_bug_source
;
COMMIT;

I am getting the following error:

Lookup Error - MySQL Database Error: Deadlock found when trying to get lock; try restarting transaction

I don't know what's going on! This scripts works fine on a previous version (MySQL Percona 5.5.31).

Here is the mysql configuration file:

[mysql]

1. CLIENT #
   port = 3306
   socket = /data/mysql/mysql.sock
   default-character-set=utf8

[client]
socket = /data/mysql/mysql.sock
default-character-set=utf8

[mysqld]

1. GENERAL #
   user = mysql
   default_storage_engine = InnoDB
   socket = /data/mysql/mysql.sock
   pid_file = /data/mysql/mysql.pid

1. MyISAM #
   key_buffer_size = 32M
   myisam_recover = FORCE,BACKUP

1. SAFETY #
   max_allowed_packet = 16M
   max_connect_errors = 1000000
   skip_name_resolve
   sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
   sysdate_is_now = 1
   innodb = FORCE
   innodb_strict_mode = 1

1. DATA STORAGE #
   datadir = /data/mysql/

1. BINARY LOGGING #
   log_bin = /data/mysql/mysql-bin
   expire_logs_days = 14
   sync_binlog = 1

1. CACHES AND LIMITS #
   tmp_table_size = 32M
   max_heap_table_size = 32M
   query_cache_type = 0
   query_cache_size = 0
   max_connections = 500
   thread_cache_size = 50
   open_files_limit = 65535
   table_definition_cache = 1024
   table_open_cache = 100

1. INNODB #
   innodb_flush_method = O_DIRECT
   innodb_log_files_in_group = 2
   innodb_log_file_size = 64M
   innodb_flush_log_at_trx_commit = 1
   innodb_file_per_table = 1
   innodb_buffer_pool_size = 512M

1. LOGGING #
   log_error = /data/mysql/mysql-error.log
   log_queries_not_using_indexes = 1
   slow_query_log = 1
   slow_query_log_file = /data/mysql/mysql-slow.log

1. UTF8 #
   collation-server = utf8_unicode_ci
   init-connect='SET NAMES utf8'
   character-set-server = utf8

1.  

   1.  

      1.  

         1. Cluster config ####

2. Path to Galera library
   wsrep_provider = /usr/lib64/libgalera_smm.so

1. Cluster connection URL contains the IPs of all the nodes
   wsrep_cluster_address = gcomm://<cluster_ip_addresses>

1. In order for Galera to work correctly binlog format should be ROW
   binlog_format = ROW

1. This is a recommended tuning variable for performance
   innodb_locks_unsafe_for_binlog = 1

1. This changes how InnoDB auto-increment locks are managed and is a requirement for Galera
   innodb_autoinc_lock_mode = 2

1. IP address of this node
   wsrep_node_address = <node_ip_address>

1. SST method
   wsrep_sst_method = xtrabackup

1. Cluster name
   wsrep_cluster_name = data_cluster

1. Authentication for SST method
   wsrep_sst_auth = "<user>:<password>"

Any ideas?