GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Deadlock when dropping and creating temporary table on Percona XtraDB Cluster 5.5.33

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    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;
    I am getting the following error:
    Lookup Error - MySQL Database Error: Deadlock found when trying to get lock; try restarting transaction
    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:
    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>"
    Any ideas?
    Last edited by francesco19; 10-17-2013, 06:26 AM.

  • #2
    Bug URL:
    https://bugs.launchpad.net/percona-x...r/+bug/1240098

    Comment

    Working...
    X