Buy Percona ServicesBuy Now!

Dataset Prepare method and MySQL cluster Percona XtraDB Crash

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dataset Prepare method and MySQL cluster Percona XtraDB Crash

    Hello!
    Faced a very interesting situation ... I have a working project on Delphi XE2 in which I use the UniDAC version 6.2.8 components to access the MySQL database. To increase the fault tolerance, the database raised the cluster on the Percona engine XtraDB 5.6.34-79.1-56-log. On virtuals, the axis UBUNTU 16. And, organized through nginx proxy requests for load balancing. But that's not the point..
    In the project, I use a lot of data sets, including those that I create dynamically. If I create a dataset, then I describe the query, I call the Prepare method and I do Open, then.. You will not believe.. The cluster crashes happily. First, one node is dropped from the cluster, after the next request, the second one, etc. While the entire cluster does not shut down. There is nothing in the logs. Just the process of MySQL flies out of memory and everything. I know that this is a bit off topic. But the matter is that it is connected with a call of a method Prepare. Without it everything works.
    If the MySQL server is on the Win system, then everything is fine. There is no such problem.
    I do not understand how the Prepare method can crash the MySQL process.
    Here is an example of a simple code dynamically created by Query. Similarly, static datasets are used and the problem is the same


    Code:
    Query_some:=TUniQuery.Create(self);
    Query_some.Connection:=UniConnection1;
    Query_some.SQL.Add('select * from some_tbl where id=:id');
    Query_some.Prepare;
    Query_some.ParamByName('id').AsString:=AIncident_Id;
    Query_some.Open;
    // ...
    Query_some.Free;
    Here is the contents of the log file of the node that crashed

    Code:
    70412 09:09:57 mysqld_safe Number of processes running now: 0
    170412 09:09:57 mysqld_safe WSREP: not restarting wsrep node automatically
    170412 09:09:57 mysqld_safe mysqld from pid file /var/lib/mysql/UBUNTU-VM2.pid ended
    170412 09:11:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    170412 09:11:32 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.zkeHnA' --pid-file='/var/lib/mysql/UBUNTU-VM2-recover.pid'
    2017-04-12 09:11:45 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2017-04-12 09:11:45 0 [Note] /usr/sbin/mysqld (mysqld 5.6.34-79.1-56-log) starting as process 6618 ...
    170412 09:11:54 mysqld_safe WSREP: Recovered position 6cf5cba5-e935-11e6-b6e9-dabe80204535:3787
    Log of wsrep recovery (--wsrep-recover):
    2017-04-12 09:11:45 6618 [Note] Plugin 'FEDERATED' is disabled.
    2017-04-12 09:11:45 b70fa940 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
    2017-04-12 09:11:45 b70fa940 InnoDB: Warning: Using innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be removed in future releases. Please use READ COMMITTED transaction isolation level instead, see http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html.
    2017-04-12 09:11:45 6618 [Note] InnoDB: Using atomics to ref count buffer pool pages
    2017-04-12 09:11:45 6618 [Note] InnoDB: The InnoDB memory heap is disabled
    2017-04-12 09:11:45 6618 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2017-04-12 09:11:45 6618 [Note] InnoDB: Memory barrier is not used
    2017-04-12 09:11:45 6618 [Note] InnoDB: Compressed tables use zlib 1.2.8
    2017-04-12 09:11:45 6618 [Note] InnoDB: Using Linux native AIO
    2017-04-12 09:11:45 6618 [Note] InnoDB: Not using CPU crc32 instructions
    2017-04-12 09:11:45 6618 [Note] InnoDB: Initializing buffer pool, size = 1.5G
    2017-04-12 09:11:47 6618 [Note] InnoDB: Completed initialization of buffer pool
    2017-04-12 09:11:48 6618 [Note] InnoDB: Highest supported file format is Barracuda.
    2017-04-12 09:11:48 6618 [Note] InnoDB: The log sequence numbers 1657730537 and 1657730537 in ibdata files do not match the log sequence number 1657733148 in the ib_logfiles!
    2017-04-12 09:11:48 6618 [Note] InnoDB: Database was not shutdown normally!
    2017-04-12 09:11:48 6618 [Note] InnoDB: Starting crash recovery.
    2017-04-12 09:11:48 6618 [Note] InnoDB: Reading tablespace information from the .ibd files...
    2017-04-12 09:11:50 6618 [Note] InnoDB: Restoring possible half-written data pages
    2017-04-12 09:11:50 6618 [Note] InnoDB: from the doublewrite buffer...
    2017-04-12 09:11:51 6618 [Note] InnoDB: 128 rollback segment(s) are active.
    2017-04-12 09:11:51 6618 [Note] InnoDB: Waiting for purge to start
    2017-04-12 09:11:51 6618 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.34-79.1 started; log sequence number 1657733148
    2017-04-12 09:11:51 6618 [Warning] InnoDB: Skipping buffer pool dump/restore during wsrep recovery.
    2017-04-12 09:11:51 6618 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
    2017-04-12 09:11:51 6618 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
    2017-04-12 09:11:51 6618 [Note] Server hostname (bind-address): '*'; port: 3306
    2017-04-12 09:11:51 6618 [Note] IPv6 is available.
    2017-04-12 09:11:51 6618 [Note]   - '::' resolves to '::';
    2017-04-12 09:11:51 6618 [Note] Server socket created on IP: '::'.
    2017-04-12 09:11:51 6618 [Note] WSREP: Recovered position: 6cf5cba5-e935-11e6-b6e9-dabe80204535:3787
    2017-04-12 09:11:51 6618 [Note] Binlog end
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'partition'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_CHANGED_PAGES'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_FT_DELETED'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_METRICS'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_CMPMEM'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_CMP_RESET'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_CMP'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_LOCKS'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'INNODB_TRX'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'XTRADB_ZIP_DICT_COLS'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'XTRADB_ZIP_DICT'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'XTRADB_RSEG'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'XTRADB_INTERNAL_HASH_TABLES'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'XTRADB_READ_VIEW'
    2017-04-12 09:11:51 6618 [Note] Shutting down plugin 'InnoDB'
    2017-04-12 09:11:51 6618 [Note] InnoDB: FTS optimize thread exiting.
    2017-04-12 09:11:51 6618 [Note] InnoDB: Starting shutdown...
    2017-04-12 09:11:52 6618 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
    2017-04-12 09:11:54 6618 [Note] InnoDB: Shutdown completed; log sequence number 1657733158
    2017-04-12 09:11:54 6618 [Note] Shutting down plugin 'ARCHIVE'
    2017-04-12 09:11:54 6618 [Note] Shutting down plugin 'BLACKHOLE'
    2017-04-12 09:11:54 6618 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
    2017-04-12 09:11:54 6618 [Note] Shutting down plugin 'MRG_MYISAM'
    2017-04-12 09:11:54 6618 [Note] Shutting down plugin 'MyISAM'
    2017-04-12 09:11:54 6618 [Note] Shutting down plugin 'CSV'
    2017-04-12 09:11:54 6618 [Note] Shutting down plugin 'MEMORY'
    2017-04-12 09:11:54 6618 [Note] Shutting down plugin 'sha256_password'
    2017-04-12 09:11:54 6618 [Note] Shutting down plugin 'mysql_old_password'
    2017-04-12 09:11:54 6618 [Note] Shutting down plugin 'mysql_native_password'
    2017-04-12 09:11:54 6618 [Note] Shutting down plugin 'wsrep'
    2017-04-12 09:11:54 6618 [Note] Shutting down plugin 'binlog'
    2017-04-12 09:11:54 6618 [Note] /usr/sbin/mysqld: Shutdown complete
    
    2017-04-12 09:11:57 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2017-04-12 09:11:57 0 [Note] /usr/sbin/mysqld (mysqld 5.6.34-79.1-56-log) starting as process 7207 ...
    ...
Working...
X