CTAS fails with explicit TEMPORARY tables, binlogging enabled and autocommit=0
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL patches by Codership |
New
|
Undecided
|
Unassigned | |||
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC | Status tracked in 5.6 | |||||
5.5 |
Fix Released
|
Undecided
|
Unassigned | |||
5.6 |
Fix Released
|
Undecided
|
Unassigned |
Bug Description
I have a cluster of 3 nodes all running Percona-
When I run this SQL:
USE test;
DROP TABLE IF EXISTS test_bug_source;
CREATE TABLE test_bug_
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]
# CLIENT #
port = 3306
socket = /data/mysql/
default-
[client]
socket = /data/mysql/
default-
[mysqld]
# GENERAL #
user = mysql
default_
socket = /data/mysql/
pid_file = /data/mysql/
# MyISAM #
key_buffer_size = 32M
myisam_recover = FORCE,BACKUP
# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1
# DATA STORAGE #
datadir = /data/mysql/
# BINARY LOGGING #
log_bin = /data/mysql/
expire_logs_days = 14
sync_binlog = 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_definitio
table_open_cache = 100
# INNODB #
innodb_flush_method = O_DIRECT
innodb_
innodb_
innodb_
innodb_
innodb_
# LOGGING #
log_error = /data/mysql/
log_queries_
slow_query_log = 1
slow_query_log_file = /data/mysql/
# UTF8 #
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-
#### Cluster config ####
# Path to Galera library
wsrep_provider = /usr/lib64/
# Cluster connection URL contains the IPs of all the nodes
wsrep_cluster_
# In order for Galera to work correctly binlog format should be ROW
binlog_format = ROW
# This is a recommended tuning variable for performance
innodb_
# This changes how InnoDB auto-increment locks are managed and is a requirement for Galera
innodb_
# IP address of this node
wsrep_node_address = <node_ip_address>
# SST method
wsrep_sst_method = xtrabackup
# Cluster name
wsrep_cluster_name = data_cluster
# Authentication for SST method
wsrep_sst_auth = "<user>:<password>"
Any ideas?
no longer affects: | percona-server |
summary: |
- Deadlock when dropping and creating temporary table on MySQL Percona - 5.5.33 + Deadlock when dropping and creating temporary table on Percona XtraDB + Cluster 5.5.33 |
summary: |
- CTAS fails with explicit TEMPORARY tables | STATEMENT binlog_format + CTAS fails with explicit TEMPORARY tables | binlogging enabled |
tags: | added: ctas |
Update, I have done also this test:
1) create a machine with Percona XtraDB Cluster 5.5.31 and 3 nodes.
2) run the script, no errors.
3) upgrade the same machine to Percona XtraDB Cluster 5.5.33
4) run the same script, Deadlock error.