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

LP #1180020: update record via sp kill cluster node



      **Reported in Launchpad by Sladjan Avlijas last update 17-11-2015 07:47:16


      i have a problem with percona mysql server. I have 3 node (VM with 4 core Intel processor, 7 GB RAM, GB Ethernet), ubuntu 12.04LTS with 5.5.30-30.2 Percona Server (GPL), Release 30.2, wsrep_23.7.4.r38. On this i have few DB, and problem is that database killed by my stored procedure. In store procedure i update some collumn in (innodb) table with 700k rows, and this update crash mysql node. SP is simple, like this:

      PROCEDURE `proc_replace_characters`()
      UPDATE table1
      SET col1 = func(col1), col2 = func(col2)

      where func is simple char replace function. By the way, when this procedure update table with 200k rows, procedure work ok, but with 700k rows procedure kill percona and i must restart server (no data change on table).



      1. The MySQL database server configuration file.
        port = 3306
        socket = /var/run/mysqld/mysqld.sock
      1. Here is entries for some specific programs
      2. The following values assume you have at least 32M ram
      3. This was formally known as [safe_mysqld]. Both versions are currently parsed.

      socket = /var/run/mysqld/mysqld.sock
      nice = 0

      1. * Basic Settings
        user = mysql
        pid-file = /var/run/mysqld/mysqld.pid
        socket = /var/run/mysqld/mysqld.sock
        port = 3306
        basedir = /usr
        datadir = /mnt/data/mysql
        tmpdir = /tmp/mysqltmp
        language = /usr/share/mysql/english
      2. Instead of skip-networking the default is now to listen only on
      3. localhost which is more compatible and is not less secure.
        bind-address = node1

      binlog_format = ROW

                    1. Galera ########
      1. Full path to wsrep provider library or 'none' to disable galera
        wsrep_provider = /usr/lib/libgalera_smm.so
      1. set to "gcom://"" to reinitialise (reset) a node
        #wsrep_cluster_address = "gcomm://"
      1. once the nodes have mounted, we will set this to the floating ip
        wsrep_cluster_address = "gcomm://node2:4567,node3:4567"

      wsrep_node_name = mysql-node1

            1. BOF : State Snapshot Transfer method

      wsrep_sst_method = rsync

      1. alternative methods to-do SST
      2. experimental, wait for RC release
      3. not recommended, transfers the ENTIRE database to re-sync nodes.
      1. Set to number of cpu cores.

      wsrep_slave_threads = 1

            1. for MyISAM support

      wsrep_replicate_myisam = 1

      ####END of MyISAM

      1. to enable debug level logging, set this to 1

      wsrep_debug = 1

      1. how many times to retry deadlocked autocommits
        wsrep_retry_autocommit = 1
      1. convert locking sessions into transactions
        wsrep_convert_LOCK_to_trx = 1
      1. Generate fake primary keys for non-PK tables (required for multi-master and parallel applying operation)
        wsrep_certify_nonPK = 1
            1. Required for Galera
              innodb_locks_unsafe_for_binlog = 1
              innodb_autoinc_lock_mode = 2
              default_storage_engine = InnoDB
              query_cache_size = 0
              query_cache_type = 0
                    1. EOF : Galera ########
      1. * Fine Tuning
        key_buffer = 32M
        max_allowed_packet = 32M
        thread_stack = 192K
        thread_cache_size = 8
      2. * Query Cache Configuration
        query_cache_limit = 1M
        query_cache_size = 32M
      3. * Logging and Replication
      4. Both location gets rotated by the cronjob.
      5. Be aware that this log type is a performance killer.
      6. As of 5.1 you can enable the log at runtime!

      general_log_file = /mnt/data/mysql/mysql.log
      general_log = 0


      1. Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
      2. Here you can see queries with especially long duration
        #log_slow_queries = /var/log/mysql/mysql-slow.log
        #long_query_time = 2

      innodb_log_file_size = 64M
      innodb_buffer_pool_size = 4000M
      innodb_thread_concurrency = 4



      max_allowed_packet = 16M

      #no-auto-rehash # faster start of mysql but no tab completition

      1. * IMPORTANT: Additional settings that can override those from this file!
      2. The files must end with '.cnf', otherwise they'll be ignored.
        !includedir /etc/mysql/conf.d/


      130514 11:29:14 [Note] WSREP: TO END: 1572149
      130514 11:29:14 [Note] WSREP: TO BEGIN: 1572149, 0 : DROP FUNCTION `tmp_ems_proc_105`
      130514 11:29:14 [Note] WSREP: TO BEGIN: 1572150, 2
      130514 11:29:14 [Note] WSREP: TO END: 1572150, 2 : DROP FUNCTION `tmp_ems_proc_105`
      130514 11:29:14 [Note] WSREP: TO END: 1572150
      09:39:50 UTC - mysqld got signal 11 ;
      This could be because you hit a bug. It is also possible that this binary
      or one of the libraries it was linked against is corrupt, improperly built,
      or misconfigured. This error can also be caused by malfunctioning hardware.
      We will try our best to scrape up some info that will hopefully help
      diagnose the problem, but since we have already crashed,
      something is definitely wrong and this may fail.
      Please help us make Percona Server better by reporting any
      bugs at http://bugs.percona.com/

      It is possible that mysqld could use up to
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 367619 K bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.

      Thread pointer: 0xa41a9b0
      Attempting backtrace. You can use the following information to find out
      where mysqld died. If you see no messages after this, something went
      terribly wrong...
      stack_bottom = 7fb424112e70 thread_stack 0x30000

      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (7fb2e40213d0): is an invalid pointer
      Connection ID (thread ID): 45
      Status: NOT_KILLED

      You may download the Percona Server operations manual by visiting
      http://www.percona.com/software/percona-server/. You may find information
      in the manual which will help you identify the cause of the crash.
      130514 11:39:51 mysqld_safe Number of processes running now: 0
      130514 11:39:51 mysqld_safe WSREP: not restarting wsrep node automatically
      130514 11:39:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

        Smart Checklist




              • Assignee:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                2 Start watching this issue


                • Created: