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
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
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 ...
...