Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-1474

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

    Details

      Description

      **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. Cluster config ####
      1. 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?

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                krunal.bauskar Krunal Bauskar
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: