Deadlock when dropping and creating temporary table on Percona XtraDB Cluster 5.5.33
Hi all, I have already posted this on launchpad as a bug and I have just found this forum, so sorry for cross posting. Hope someone can help:
I have a cluster of 3 nodes all running Percona-XtraDB-Cluster-server.x86_64 1:5.5.33-23.7.6.495.rhel6.
Machine info:
When I run this SQL:
I am getting the following error:
This scripts works fine on a previous version (Percona XtraDB Cluster 5.5.31).
I did also this test:
1) create a 3 nodes cluster with Percona XtraDB Cluster 5.5.31.
2) run the script, no errors.
3) upgrade all the nodes to Percona XtraDB Cluster 5.5.33
4) run the same script, Deadlock error.
Here is the mysql configuration file:
Any ideas?
I have a cluster of 3 nodes all running Percona-XtraDB-Cluster-server.x86_64 1:5.5.33-23.7.6.495.rhel6.
Machine info:
Code:
# uname -a Linux 3.8.7joyent-centos-6-opt #2 SMP Wed Apr 17 15:18:57 PDT 2013 x86_64 x86_64 x86_64 GNU/Linux
When I run this SQL:
Code:
USE test;
DROP TABLE IF EXISTS test_bug_source;
CREATE TABLE test_bug_source(`id` int);
START TRANSACTION;
DROP TEMPORARY TABLE IF EXISTS test_bug;
CREATE TEMPORARY TABLE test_bug AS
SELECT * FROM test_bug_source;
COMMIT;
Lookup Error - MySQL Database Error: Deadlock found when trying to get lock; try restarting transaction
I did also this test:
1) create a 3 nodes cluster with Percona XtraDB Cluster 5.5.31.
2) run the script, no errors.
3) upgrade all the nodes to Percona XtraDB Cluster 5.5.33
4) run the same script, Deadlock error.
Here is the mysql configuration file:
Code:
[mysql] # CLIENT # port = 3306 socket = /data/mysql/mysql.sock default-character-set=utf8 [client] socket = /data/mysql/mysql.sock default-character-set=utf8 [mysqld] # GENERAL # user = mysql default_storage_engine = InnoDB socket = /data/mysql/mysql.sock pid_file = /data/mysql/mysql.pid # MyISAM # key_buffer_size = 32M myisam_recover = FORCE,BACKUP # 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_S UBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL _GROUP_BY sysdate_is_now = 1 innodb = FORCE innodb_strict_mode = 1 # DATA STORAGE # datadir = /data/mysql/ # BINARY LOGGING # log_bin = /data/mysql/mysql-bin 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_definition_cache = 1024 table_open_cache = 100 # 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 # 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 # UTF8 # collation-server = utf8_unicode_ci init-connect='SET NAMES utf8' character-set-server = utf8 #### Cluster config #### # Path to Galera library wsrep_provider = /usr/lib64/libgalera_smm.so # Cluster connection URL contains the IPs of all the nodes wsrep_cluster_address = gcomm://<cluster_ip_addresses> # In order for Galera to work correctly binlog format should be ROW binlog_format = ROW # This is a recommended tuning variable for performance innodb_locks_unsafe_for_binlog = 1 # This changes how InnoDB auto-increment locks are managed and is a requirement for Galera innodb_autoinc_lock_mode = 2 # 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>"
Comment