Roel Van de Paar added a comment - 2021-12-12 04:19 - edited Attached MDEV-27233_short.txt and MDEV-27233_full.txt which contain thread apply all bt and thread apply all bt full stack traces, respectively, from a mariadbd gdb --pid=x breakin whilst hanging. A core dump is available if needed also.

Roel Van de Paar added a comment - 2021-12-21 09:57 nayuta-yanagisawa This issue remains top priority for testing imho. When you have a moment in your busy schedule, this would be the one to look at from testing priority POV. Thanks!

Nayuta Yanagisawa (Inactive) added a comment - 2021-12-21 11:12 The bug might be related to MDEV-22979 .

Nayuta Yanagisawa (Inactive) added a comment - 2021-12-21 13:53 The result of git-bisect: b428b09997d172f29fc201b9ab05c160ef4cbc39 is the first bad commit commit b428b09997d172f29fc201b9ab05c160ef4cbc39 Author: Kentoku SHIBA <kentokushiba@gmail.com> Date: Thu Aug 1 08:59:53 2019 +0900   MDEV-20179 Server hangs on shutdown during installation of Spider (#1369) Use LOCK_server_started, COND_server_started and mysqld_server_started for waiting starting the server   sql/mysqld.cc | 2 +- storage/spider/spd_include.h | 3 +- storage/spider/spd_table.cc | 119 +++++++++++++++++++------------------------ 3 files changed, 54 insertions(+), 70 deletions(-)

Nayuta Yanagisawa (Inactive) added a comment - 2021-12-22 03:38 The following part of the code seems to lead to the dead lock: https://github.com/MariaDB/server/blob/mariadb-10.6.5/storage/spider/spd_table.cc#L10501-L10522

Nayuta Yanagisawa (Inactive) added a comment - 2021-12-22 06:57 At first glance, spider_create_handler() tries to take spd_LOCK_server_started and then hangs because spider_table_bg_sts_action() owns the mutex by calling pthread_cond_wait() . However, this looks strange. I will look at it in detals.

Nayuta Yanagisawa (Inactive) added a comment - 2021-12-22 14:03 - edited I misunderstood. The main thread executing mysqld_main() hangs at spider_create_handler() because it tries to lock thread->mutex , which is not spd_LOCK_server_started and is locked by another thread, for sts sync, executing spider_table_bg_sts_action() . The sts sync thread is waiting for spd_COND_server_started is signaled, but it is to be done by the main thread (dead lock!).

Nayuta Yanagisawa (Inactive) added a comment - 2021-12-22 15:43 What I need to make it clear is that why spider_create_handler() needs to wait for finishing init_command. Here, thread->init_command == FALSE means that the first sts sync thread has executed spider_init_queries , which create Spider-related table in the mysql database. The constraint such that "mysql.spider_XX tables must be created before the first ha_spider is created" itself looks reasonable, while there seems to be no need that the table creation must be done by the sts sync thread. handler* spider_create_handler( ... ) { .... SPIDER_THREAD * thread = &spider_table_sts_threads[0]; /* wait for finishing init_command */ pthread_mutex_lock(& thread ->mutex); if (unlikely( thread ->init_command)) { thd->mysys_var->current_cond = & thread ->sync_cond; thd->mysys_var->current_mutex = & thread ->mutex; pthread_cond_wait(& thread ->sync_cond, & thread ->mutex); } pthread_mutex_unlock(& thread ->mutex); thd->mysys_var->current_cond = cond; thd->mysys_var->current_mutex = mutex; .... DBUG_RETURN( new (mem_root) ha_spider(hton, table)); }

Nayuta Yanagisawa (Inactive) added a comment - 2021-12-24 12:04 holyfoot Please review: https://github.com/MariaDB/server/commit/5f4debe08dac13c0a3675ed84e224b3b9e65c8f8 I tried to move the system table creation to spider_db_init() but it results in another deadlock. I tried to solve it, but it seemed to take a long time, so I just removed the unnecessary synchronization process for now.

Nayuta Yanagisawa (Inactive) added a comment - 2021-12-24 14:59 holyfoot Let me withdraw the patch. I tested it with MDEV-22979 's test case and result in crash.

Nayuta Yanagisawa (Inactive) added a comment - 2022-01-06 11:14 The current status: I tried the SQL service, which is backported by holyfoot to 10.4, but it did not work well. holyhoot will check my patches and help me debug. https://github.com/MariaDB/server/tree/bb-10.4-MDEV-27233

Roel Van de Paar added a comment - 2022-01-19 10:01 Thank you for your continued work and interest on this issue nayuta-yanagisawa and holyfoot . Having this available will greatly aid Spider testing.

Nayuta Yanagisawa (Inactive) added a comment - 2022-01-19 10:51 The set of the patches in https://github.com/MariaDB/server/tree/bb-10.4-MDEV-27233 partially works but there is some problem related to replication. holyfoot will take a look at it.

Nayuta Yanagisawa (Inactive) added a comment - 2022-02-07 07:15 > ./mysql-test/mtr spider/bugfix.delete_with_float_column_mariadb --rr Logging: /home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233/mysql-test/mysql-test-run.pl spider/bugfix.delete_with_float_column_mariadb --rr VS config: vardir: /home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233/bld/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233/bld/mysql-test/var'... Checking supported features... MariaDB Version 10.4.23-MariaDB-debug - SSL connections supported - binaries are debug compiled - binaries built with wsrep patch Collecting tests... Installing system database...   ==============================================================================   TEST RESULT TIME (ms) or COMMENT --------------------------------------------------------------------------   worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 spider/bugfix.delete_with_float_column_mariadb [ fail ] Test ended at 2022-02-07 16:12:15   CURRENT_TEST: spider/bugfix.delete_with_float_column_mariadb analyze: sync_with_master mysqltest: In included file "/home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column.inc": included from /home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mariadb.test at line 3: At line 35: sync_with_master failed: 'select master_pos_wait('master-bin.000001', 29175, 300, '')' returned NULL indicating slave SQL thread failure   The result from queries just before the failure was: < snip >   drop and create databases connection master_1; CREATE DATABASE auto_test_local; USE auto_test_local; connection slave1_1; CREATE DATABASE auto_test_local; USE auto_test_local; connection child2_1; SET @old_log_output = @@global.log_output; SET GLOBAL log_output = 'TABLE,FILE'; CREATE DATABASE auto_test_remote; USE auto_test_remote;   create table and insert connection child2_1; CHILD2_1_CREATE_TABLES TRUNCATE TABLE mysql.general_log; connection master_1; connection slave1_1;   More results from queries before failure can be found in /home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233/bld/mysql-test/var/log/delete_with_float_column_mariadb.log     == /home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233/bld/mysql-test/var/tmp/analyze-sync_with_master-mysqld.4.1.err ==   ############################## default ##############################   **** SHOW WARNINGS on default **** SHOW WARNINGS; Level Code Message   **** SELECT replication-related variables on default **** SELECT NOW(), @@SERVER_ID; NOW() @@SERVER_ID 2022-02-07 16:12:15 3   **** SHOW SLAVE STATUS on default **** SHOW SLAVE STATUS; Slave_IO_State Waiting for master to send event Master_Host 127.0.0.1 Master_User root Master_Port 16000 Connect_Retry 60 Master_Log_File master-bin.000001 Read_Master_Log_Pos 29175 Relay_Log_File mysqld-relay-bin.000002 Relay_Log_Pos 7618 Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1959 Last_Error Error 'Invalid role specification ``' on query. Default database: ''. Query: 'CREATE DEFINER=`` PROCEDURE `mysql`.`spider_fix_one_table`(tab_name char(255) charset utf8 collate utf8_bin, test_col_name char(255) charset utf8 collate utf8_bin, _sql text charset utf8 collate utf8_bin) begin set @col_exists := 0; select 1 into @col_exists from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = tab_name AND COLUMN_NAME = test_col_name; if @col_exists = 0 then select @stmt := _sql; prepare sp_stmt1 from @stmt; execute sp_stmt1; end if;end' Skip_Counter 0 Exec_Master_Log_Pos 7318 Relay_Log_Space 29785 Until_Condition None Until_Log_File Until_Log_Pos 0 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master NULL Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error Last_SQL_Errno 1959 Last_SQL_Error Error 'Invalid role specification ``' on query. Default database: ''. Query: 'CREATE DEFINER=`` PROCEDURE `mysql`.`spider_fix_one_table`(tab_name char(255) charset utf8 collate utf8_bin, test_col_name char(255) charset utf8 collate utf8_bin, _sql text charset utf8 collate utf8_bin) begin set @col_exists := 0; select 1 into @col_exists from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = tab_name AND COLUMN_NAME = test_col_name; if @col_exists = 0 then select @stmt := _sql; prepare sp_stmt1 from @stmt; execute sp_stmt1; end if;end' Replicate_Ignore_Server_Ids Master_Server_Id 1 Master_SSL_Crl Master_SSL_Crlpath Using_Gtid No Gtid_IO_Pos Replicate_Do_Domain_Ids Replicate_Ignore_Domain_Ids Parallel_Mode conservative SQL_Delay 0 SQL_Remaining_Delay NULL Slave_SQL_Running_State Slave_DDL_Groups 12 Slave_Non_Transactional_Groups 1 Slave_Transactional_Groups 0   **** SHOW MASTER STATUS on default **** SHOW MASTER STATUS;   **** SHOW SLAVE HOSTS on default **** SHOW SLAVE HOSTS;   **** SHOW PROCESSLIST on default **** SHOW PROCESSLIST; Id User Host db Command Time State Info Progress 1 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000 2 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000 10 system user NULL Slave_IO 0 Waiting for master to send event NULL 0.000 12 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 13 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 14 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 15 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 16 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 17 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 18 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 19 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 20 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 21 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 22 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 23 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 24 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 25 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 26 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 27 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 28 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 29 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 30 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 31 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 32 root localhost NULL Query 0 Init SHOW PROCESSLIST 0.000   **** SHOW BINARY LOGS on default **** SHOW BINARY LOGS; ERROR HY000: You are not using binary logging   **** SHOW BINLOG EVENTS on default **** binlog_name = 'No such row' SHOW BINLOG EVENTS IN 'No such row'; Log_name Pos Event_type Server_id End_log_pos Info   **** SHOW RELAYLOG EVENTS on default **** relaylog_name = 'mysqld-relay-bin.000002' SHOW RELAYLOG EVENTS IN 'mysqld-relay-bin.000002'; Log_name Pos Event_type Server_id End_log_pos Info mysqld-relay-bin.000002 4 Format_desc 3 256 Server ver: 10.4.23-MariaDB-debug-log, Binlog ver: 4 mysqld-relay-bin.000002 256 Rotate 1 0 master-bin.000001;pos=4 mysqld-relay-bin.000002 304 Format_desc 1 256 Server ver: 10.4.23-MariaDB-debug-log, Binlog ver: 4 mysqld-relay-bin.000002 556 Gtid_list 1 285 [] mysqld-relay-bin.000002 585 Binlog_checkpoint 1 329 master-bin.000001 mysqld-relay-bin.000002 629 Gtid 1 371 BEGIN GTID 0-1-1 mysqld-relay-bin.000002 671 Query 1 562 use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'unknown variable' COLLATE 'latin1_swedish_ci')) mysqld-relay-bin.000002 862 Query 1 634 COMMIT mysqld-relay-bin.000002 934 Gtid 1 676 GTID 0-1-2 mysqld-relay-bin.000002 976 Query 1 1100 create table if not exists mysql.spider_xa( format_id int not null default 0, gtrid_length int not null default 0, bqual_length int not null default 0, data char(128) charset binary not null default '', status char(8) not null default '', primary key (data, format_id, gtrid_length), key idx1 (status)) engine=MyISAM default charset=utf8 collate=utf8_bin mysqld-relay-bin.000002 1400 Gtid 1 1142 GTID 0-1-3 mysqld-relay-bin.000002 1442 Query 1 1939 create table if not exists mysql.spider_xa_member( format_id int not null default 0, gtrid_length int not null default 0, bqual_length int not null default 0, data char(128) charset binary not null default '', scheme char(64) not null default '', host char(64) not null default '', port char(5) not null default '', socket text not null, username char(64) not null default '', password char(64) not null default '', ssl_ca text, ssl_capath text, ssl_cert text, ssl_cipher char(64) default null, ssl_key text, ssl_verify_server_cert tinyint not null default 0, default_file text, default_group char(64) default null, key idx1 (data, format_id, gtrid_length, host)) engine=MyISAM default charset=utf8 collate=utf8_bin mysqld-relay-bin.000002 2239 Gtid 1 1981 GTID 0-1-4 mysqld-relay-bin.000002 2281 Query 1 2907 create table if not exists mysql.spider_xa_failed_log( format_id int not null default 0, gtrid_length int not null default 0, bqual_length int not null default 0, data char(128) charset binary not null default '', scheme char(64) not null default '', host char(64) not null default '', port char(5) not null default '', socket text not null, username char(64) not null default '', password char(64) not null default '', ssl_ca text, ssl_capath text, ssl_cert text, ssl_cipher char(64) default null, ssl_key text, ssl_verify_server_cert tinyint not null default 0, default_file text, default_group char(64) default null, thread_id int default null, status char(8) not null default '', failed_time timestamp not null default current_timestamp, key idx1 (data, format_id, gtrid_length, host)) engine=MyISAM default charset=utf8 collate=utf8_bin mysqld-relay-bin.000002 3207 Gtid 1 2949 GTID 0-1-5 mysqld-relay-bin.000002 3249 Query 1 4053 create table if not exists mysql.spider_tables( db_name char(64) not null default '', table_name char(199) not null default '', link_id int not null default 0, priority bigint not null default 0, server char(64) default null, scheme char(64) default null, host char(64) default null, port char(5) default null, socket text, username char(64) default null, password char(64) default null, ssl_ca text, ssl_capath text, ssl_cert text, ssl_cipher char(64) default null, ssl_key text, ssl_verify_server_cert tinyint not null default 0, monitoring_binlog_pos_at_failing tinyint not null default 0, default_file text, default_group char(64) default null, tgt_db_name char(64) default null, tgt_table_name char(64) default null, link_status tinyint not null default 1, block_status tinyint not null default 0, static_link_id char(64) default null, primary key (db_name, table_name, link_id), key idx1 (priority), unique key uidx1 (db_name, table_name, static_link_id)) engine=MyISAM default charset=utf8 collate=utf8_bin mysqld-relay-bin.000002 4353 Gtid 1 4095 GTID 0-1-6 mysqld-relay-bin.000002 4395 Query 1 4883 create table if not exists mysql.spider_link_mon_servers( db_name char(64) not null default '', table_name char(199) not null default '', link_id char(64) not null default '', sid int unsigned not null default 0, server char(64) default null, scheme char(64) default null, host char(64) default null, port char(5) default null, socket text, username char(64) default null, password char(64) default null, ssl_ca text, ssl_capath text, ssl_cert text, ssl_cipher char(64) default null, ssl_key text, ssl_verify_server_cert tinyint not null default 0, default_file text, default_group char(64) default null, primary key (db_name, table_name, link_id, sid)) engine=MyISAM default charset=utf8 collate=utf8_bin mysqld-relay-bin.000002 5183 Gtid 1 4925 GTID 0-1-7 mysqld-relay-bin.000002 5225 Query 1 5276 create table if not exists mysql.spider_link_failed_log( db_name char(64) not null default '', table_name char(199) not null default '', link_id char(64) not null default '', failed_time timestamp not null default current_timestamp) engine=MyISAM default charset=utf8 collate=utf8_bin mysqld-relay-bin.000002 5576 Gtid 1 5318 GTID 0-1-8 mysqld-relay-bin.000002 5618 Query 1 5771 create table if not exists mysql.spider_table_position_for_recovery( db_name char(64) not null default '', table_name char(199) not null default '', failed_link_id int not null default 0, source_link_id int not null default 0, file text, position text, gtid text, primary key (db_name, table_name, failed_link_id, source_link_id)) engine=MyISAM default charset=utf8 collate=utf8_bin mysqld-relay-bin.000002 6071 Gtid 1 5813 GTID 0-1-9 mysqld-relay-bin.000002 6113 Query 1 6586 create table if not exists mysql.spider_table_sts( db_name char(64) not null default '', table_name char(199) not null default '', data_file_length bigint unsigned not null default 0, max_data_file_length bigint unsigned not null default 0, index_file_length bigint unsigned not null default 0, records bigint unsigned not null default 0, mean_rec_length bigint unsigned not null default 0, check_time datetime not null default '0000-00-00 00:00:00', create_time datetime not null default '0000-00-00 00:00:00', update_time datetime not null default '0000-00-00 00:00:00', checksum bigint unsigned default null, primary key (db_name, table_name)) engine=MyISAM default charset=utf8 collate=utf8_bin mysqld-relay-bin.000002 6886 Gtid 1 6628 GTID 0-1-10 mysqld-relay-bin.000002 6928 Query 1 7002 create table if not exists mysql.spider_table_crd( db_name char(64) not null default '', table_name char(199) not null default '', key_seq int unsigned not null default 0, cardinality bigint not null default 0, primary key (db_name, table_name, key_seq)) engine=MyISAM default charset=utf8 collate=utf8_bin mysqld-relay-bin.000002 7302 Gtid 1 7044 GTID 0-1-11 mysqld-relay-bin.000002 7344 Query 1 7158 drop procedure if exists mysql.spider_fix_one_table mysqld-relay-bin.000002 7458 Gtid 1 7200 GTID 0-1-12 mysqld-relay-bin.000002 7500 Query 1 7318 drop procedure if exists mysql.spider_fix_system_tables mysqld-relay-bin.000002 7618 Gtid 1 7360 GTID 0-1-13 mysqld-relay-bin.000002 7660 Query 1 7937 CREATE DEFINER=`` PROCEDURE `mysql`.`spider_fix_one_table`(tab_name char(255) charset utf8 collate utf8_bin, test_col_name char(255) charset utf8 collate utf8_bin, _sql text charset utf8 collate utf8_bin) begin set @col_exists := 0; select 1 into @col_exists from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = tab_name AND COLUMN_NAME = test_col_name; if @col_exists = 0 then select @stmt := _sql; prepare sp_stmt1 from @stmt; execute sp_stmt1; end if;end mysqld-relay-bin.000002 8237 Gtid 1 7979 GTID 0-1-14 mysqld-relay-bin.000002 8279 Query 1 22409 CREATE DEFINER=`` PROCEDURE `mysql`.`spider_fix_system_tables`() begin select substring_index(substring_index(version(), '-', 2), '-', -1) into @server_name; select substring_index(version(), '.', 1) into @server_major_version; select substring_index(substring_index(version(), '.', 2), '.', -1) into @server_minor_version; call mysql.spider_fix_one_table('spider_tables', 'server', 'alter table mysql.spider_tables add server char(64) default null, add scheme char(64) default null, add host char(64) default null, add port char(5) default null, add socket char(64) default null, add username char(64) default null, add password char(64) default null, add tgt_db_name char(64) default null, add tgt_table_name char(64) default null'); select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_xa' AND COLUMN_NAME = 'data'; if @col_type != 'binary(128)' then alter table mysql.spider_xa modify data binary(128) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_xa_member' AND COLUMN_NAME = 'data'; if @col_type != 'binary(128)' then alter table mysql.spider_xa_member modify data binary(128) not null default ''; end if; call mysql.spider_fix_one_table('spider_tables', 'link_id', 'alter table mysql.spider_tables add column link_id int not null default 0 after table_name, drop primary key, add primary key (db_name, table_name, link_id)'); call mysql.spider_fix_one_table('spider_tables', 'link_status', 'alter table mysql.spider_tables add column link_status tinyint not null default 1'); call mysql.spider_fix_one_table('spider_xa_member', 'ssl_ca', 'alter table mysql.spider_xa_member add column ssl_ca char(64) default null after password, add column ssl_capath char(64) default null after ssl_ca, add column ssl_cert char(64) default null after ssl_capath, add column ssl_cipher char(64) default null after ssl_cert, add column ssl_key char(64) default null after ssl_cipher, add column ssl_verify_server_cert tinyint not null default 0 after ssl_key, add column default_file char(64) default null after ssl_verify_server_cert, add column default_group char(64) default null after default_file'); call mysql.spider_fix_one_table('spider_tables', 'ssl_ca', 'alter table mysql.spider_tables add column ssl_ca char(64) default null after password, add column ssl_capath char(64) default null after ssl_ca, add column ssl_cert char(64) default null after ssl_capath, add column ssl_cipher char(64) default null after ssl_cert, add column ssl_key char(64) default null after ssl_cipher, add column ssl_verify_server_cert tinyint not null default 0 after ssl_key, add column default_file char(64) default null after ssl_verify_server_cert, add column default_group char(64) default null after default_file'); call mysql.spider_fix_one_table('spider_link_mon_servers', 'ssl_ca', 'alter table mysql.spider_link_mon_servers add column ssl_ca char(64) default null after password, add column ssl_capath char(64) default null after ssl_ca, add column ssl_cert char(64) default null after ssl_capath, add column ssl_cipher char(64) default null after ssl_cert, add column ssl_key char(64) default null after ssl_cipher, add column ssl_verify_server_cert tinyint not null default 0 after ssl_key, add column default_file char(64) default null after ssl_verify_server_cert, add column default_group char(64) default null after default_file'); select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_mon_servers' AND COLUMN_NAME = 'sid'; if @col_type != 'int(10) unsigned' then alter table mysql.spider_link_mon_servers modify sid int unsigned not null default 0; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_xa_member' AND COLUMN_NAME = 'socket'; if @col_type = 'char(64)' then alter table mysql.spider_xa_member drop primary key, add index idx1 (data, format_id, gtrid_length, host), modify socket text not null, modify ssl_ca text, modify ssl_capath text, modify ssl_cert text, modify ssl_key text, modify default_file text; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_tables' AND COLUMN_NAME = 'socket'; if @col_type = 'char(64)' then alter table mysql.spider_tables modify socket text, modify ssl_ca text, modify ssl_capath text, modify ssl_cert text, modify ssl_key text, modify default_file text; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_mon_servers' AND COLUMN_NAME = 'socket'; if @col_type = 'char(64)' then alter table mysql.spider_link_mon_servers modify socket text, modify ssl_ca text, modify ssl_capath text, modify ssl_cert text, modify ssl_key text, modify default_file text; end if; call mysql.spider_fix_one_table('spider_tables', 'monitoring_binlog_pos_at_failing', 'alter table mysql.spider_tables add monitoring_binlog_pos_at_failing tinyint not null default 0 after ssl_verify_server_cert'); call mysql.spider_fix_one_table('spider_tables', 'block_status', 'alter table mysql.spider_tables add column block_status tinyint not null default 0 after link_status'); call mysql.spider_fix_one_table('spider_tables', 'static_link_id', 'alter table mysql.spider_tables add column static_link_id char(64) default null after block_status, add unique index uidx1 (db_name, table_name, static_link_id)'); select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_mon_servers' AND COLUMN_NAME = 'link_id'; if @col_type != 'char(64)' then alter table mysql.spider_link_mon_servers modify link_id char(64) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_failed_log' AND COLUMN_NAME = 'link_id'; if @col_type != 'char(64)' then alter table mysql.spider_link_failed_log modify link_id char(64) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_tables' AND COLUMN_NAME = 'table_name'; if @col_type != 'char(199)' then alter table mysql.spider_tables modify table_name char(199) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_mon_servers' AND COLUMN_NAME = 'table_name'; if @col_type != 'char(199)' then alter table mysql.spider_link_mon_servers modify table_name char(199) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_failed_log' AND COLUMN_NAME = 'table_name'; if @col_type != 'char(199)' then alter table mysql.spider_link_failed_log modify table_name char(199) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_table_position_for_recovery' AND COLUMN_NAME = 'table_name'; if @col_type != 'char(199)' then alter table mysql.spider_table_position_for_recovery modify table_name char(199) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_table_sts' AND COLUMN_NAME = 'table_name'; if @col_type != 'char(199)' then alter table mysql.spider_table_sts modify table_name char(199) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_table_crd' AND COLUMN_NAME = 'table_name'; if @col_type != 'char(199)' then alter table mysql.spider_table_crd modify table_name char(199) not null default ''; end if; call mysql.spider_fix_one_table('spider_table_sts', 'checksum', 'alter table mysql.spider_table_sts add column checksum bigint unsigned default null after update_time'); if @server_name = 'MariaDB' and ( @server_major_version > 10 or ( @server_major_version = 10 and @server_minor_version >= 4 ) ) then select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_failed_log'; if @engine_name != 'Aria' then alter table mysql.spider_link_failed_log engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_mon_servers'; if @engine_name != 'Aria' then alter table mysql.spider_link_mon_servers engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_table_crd'; if @engine_name != 'Aria' then alter table mysql.spider_table_crd engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_table_position_for_recovery'; if @engine_name != 'Aria' then alter table mysql.spider_table_position_for_recovery engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_table_sts'; if @engine_name != 'Aria' then alter table mysql.spider_table_sts engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_tables'; if @engine_name != 'Aria' then alter table mysql.spider_tables engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_xa'; if @engine_name != 'Aria' then alter table mysql.spider_xa engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_xa_failed_log'; if @engine_name != 'Aria' then alter table mysql.spider_xa_failed_log engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_xa_member'; if @engine_name != 'Aria' then alter table mysql.spider_xa_member engine=Aria transactional=1; end if; end if; if @server_name = 'MariaDB' and ( @server_major_version > 10 or ( @server_major_version = 10 and @server_minor_version >= 6 ) ) then /* table for ddl pushdown */ create table if not exists mysql.spider_rewrite_tables( table_id bigint unsigned not null auto_increment, db_name char(64) not null default '', table_name char(64) not null default '', primary key (table_id), unique uk1(db_name, table_name) ) engine=Aria transactional=1 default charset=utf8 collate=utf8_bin; create table if not exists mysql.spider_rewrite_table_tables( table_id bigint unsigned not null, partition_id bigint unsigned not null auto_increment, partition_method varchar(18) default '', partition_expression varchar(64) default '', subpartition_method varchar(12) default '', subpartition_expression varchar(64) default '', connection_str text not null default '', comment_str text not null default '', primary key (table_id, partition_id), unique uk1(table_id, partition_method, partition_expression, subpartition_method, subpartition_expression) ) engine=Aria transactional=1 default charset=utf8 collate=utf8_bin; create table if not exists mysql.spider_rewrite_table_partitions( table_id bigint unsigned not null, partition_id bigint unsigned not null, partition_ordinal_position bigint unsigned not null auto_increment, partition_name varchar(64) not null default '', partition_description varchar(64) not null default '', connection_str text not null default '', comment_str text not null default '', primary key (table_id, partition_id, partition_ordinal_position), unique key uk1 (table_id, partition_id, partition_name) ) engine=Aria transactional=1 default charset=utf8 collate=utf8_bin; create table if not exists mysql.spider_rewrite_table_subpartitions( table_id bigint unsigned not null, partition_id bigint unsigned not null, partition_ordinal_position bigint unsigned not null, subpartition_ordinal_position bigint unsigned not null auto_increment, subpartition_name varchar(64) not null default '', subpartition_description varchar(64) not null default '', connection_str text not null default '', comment_str text not null default '', primary key (table_id, partition_id, partition_ordinal_position, subpartition_ordinal_position), unique key uk1 (table_id, partition_id, partition_ordinal_position, subpartition_name) ) engine=Aria transactional=1 default charset=utf8 collate=utf8_bin; create table if not exists mysql.spider_rewritten_tables( db_name char(64) not null, table_name char(64) not null, table_id bigint unsigned not null, partition_id bigint unsigned not null, primary key (db_name, table_name, table_id, partition_id) ) engine=Aria transactional=1 default charset=utf8 collate=utf8_bin; end if;end mysqld-relay-bin.000002 22709 Gtid 1 22451 GTID 0-1-15 mysqld-relay-bin.000002 22751 Query 1 22594 use `mysql`; alter table mysql.spider_link_failed_log engine=Aria transactional=1 mysqld-relay-bin.000002 22894 Gtid 1 22636 GTID 0-1-16 mysqld-relay-bin.000002 22936 Query 1 22780 use `mysql`; alter table mysql.spider_link_mon_servers engine=Aria transactional=1 mysqld-relay-bin.000002 23080 Gtid 1 22822 GTID 0-1-17 mysqld-relay-bin.000002 23122 Query 1 22959 use `mysql`; alter table mysql.spider_table_crd engine=Aria transactional=1 mysqld-relay-bin.000002 23259 Gtid 1 23001 GTID 0-1-18 mysqld-relay-bin.000002 23301 Query 1 23156 use `mysql`; alter table mysql.spider_table_position_for_recovery engine=Aria transactional=1 mysqld-relay-bin.000002 23456 Gtid 1 23198 GTID 0-1-19 mysqld-relay-bin.000002 23498 Query 1 23335 use `mysql`; alter table mysql.spider_table_sts engine=Aria transactional=1 mysqld-relay-bin.000002 23635 Gtid 1 23377 GTID 0-1-20 mysqld-relay-bin.000002 23677 Query 1 23511 use `mysql`; alter table mysql.spider_tables engine=Aria transactional=1 mysqld-relay-bin.000002 23811 Gtid 1 23553 GTID 0-1-21 mysqld-relay-bin.000002 23853 Query 1 23683 use `mysql`; alter table mysql.spider_xa engine=Aria transactional=1 mysqld-relay-bin.000002 23983 Gtid 1 23725 GTID 0-1-22 mysqld-relay-bin.000002 24025 Query 1 23866 use `mysql`; alter table mysql.spider_xa_failed_log engine=Aria transactional=1 mysqld-relay-bin.000002 24166 Gtid 1 23908 GTID 0-1-23 mysqld-relay-bin.000002 24208 Query 1 24045 use `mysql`; alter table mysql.spider_xa_member engine=Aria transactional=1 mysqld-relay-bin.000002 24345 Gtid 1 24087 GTID 0-1-24 mysqld-relay-bin.000002 24387 Query 1 24191 drop procedure mysql.spider_fix_one_table mysqld-relay-bin.000002 24491 Gtid 1 24233 GTID 0-1-25 mysqld-relay-bin.000002 24533 Query 1 24341 drop procedure mysql.spider_fix_system_tables mysqld-relay-bin.000002 24641 Gtid 1 24383 GTID 0-1-26 mysqld-relay-bin.000002 24683 Query 1 24500 drop procedure if exists mysql.spider_plugin_installer mysqld-relay-bin.000002 24800 Gtid 1 24542 GTID 0-1-27 mysqld-relay-bin.000002 24842 Query 1 27467 CREATE DEFINER=`` PROCEDURE `mysql`.`spider_plugin_installer`() begin set @win_plugin := IF(@@version_compile_os like 'Win%', 1, 0); set @have_spider_direct_sql_udf := 0; select @have_spider_direct_sql_udf := 1 from mysql.func where name = 'spider_direct_sql'; if @have_spider_direct_sql_udf = 0 then if @win_plugin = 0 then create function spider_direct_sql returns int soname 'ha_spider.so'; else create function spider_direct_sql returns int soname 'ha_spider.dll'; end if; end if; set @have_spider_bg_direct_sql_udf := 0; select @have_spider_bg_direct_sql_udf := 1 from mysql.func where name = 'spider_bg_direct_sql'; if @have_spider_bg_direct_sql_udf = 0 then if @win_plugin = 0 then create aggregate function spider_bg_direct_sql returns int soname 'ha_spider.so'; else create aggregate function spider_bg_direct_sql returns int soname 'ha_spider.dll'; end if; end if; set @have_spider_ping_table_udf := 0; select @have_spider_ping_table_udf := 1 from mysql.func where name = 'spider_ping_table'; if @have_spider_ping_table_udf = 0 then if @win_plugin = 0 then create function spider_ping_table returns int soname 'ha_spider.so'; else create function spider_ping_table returns int soname 'ha_spider.dll'; end if; end if; set @have_spider_copy_tables_udf := 0; select @have_spider_copy_tables_udf := 1 from mysql.func where name = 'spider_copy_tables'; if @have_spider_copy_tables_udf = 0 then if @win_plugin = 0 then create function spider_copy_tables returns int soname 'ha_spider.so'; else create function spider_copy_tables returns int soname 'ha_spider.dll'; end if; end if; set @have_spider_flush_table_mon_cache_udf := 0; select @have_spider_flush_table_mon_cache_udf := 1 from mysql.func where name = 'spider_flush_table_mon_cache'; if @have_spider_flush_table_mon_cache_udf = 0 then if @win_plugin = 0 then create function spider_flush_table_mon_cache returns int soname 'ha_spider.so'; else create function spider_flush_table_mon_cache returns int soname 'ha_spider.dll'; end if; end if; if @server_name = 'MariaDB' and ( @server_major_version > 10 or ( @server_major_version = 10 and @server_minor_version >= 6 ) ) then set @have_spider_flush_rewrite_cache_udf := 0; select @have_spider_flush_rewrite_cache_udf := 1 from mysql.func where name = 'spider_flush_rewrite_cache'; if @have_spider_flush_rewrite_cache_udf = 0 then if @win_plugin = 0 then create function spider_flush_rewrite_cache returns int soname 'ha_spider.so'; else create function spider_flush_rewrite_cache returns int soname 'ha_spider.dll'; end if; end if; end if;end mysqld-relay-bin.000002 27767 Gtid 1 27509 GTID 0-1-28 mysqld-relay-bin.000002 27809 Query 1 27651 use `mysql`; create function spider_direct_sql returns int soname 'ha_spider.so' mysqld-relay-bin.000002 27951 Gtid 1 27693 GTID 0-1-29 mysqld-relay-bin.000002 27993 Query 1 27848 use `mysql`; create aggregate function spider_bg_direct_sql returns int soname 'ha_spider.so' mysqld-relay-bin.000002 28148 Gtid 1 27890 GTID 0-1-30 mysqld-relay-bin.000002 28190 Query 1 28032 use `mysql`; create function spider_ping_table returns int soname 'ha_spider.so' mysqld-relay-bin.000002 28332 Gtid 1 28074 GTID 0-1-31 mysqld-relay-bin.000002 28374 Query 1 28217 use `mysql`; create function spider_copy_tables returns int soname 'ha_spider.so' mysqld-relay-bin.000002 28517 Gtid 1 28259 GTID 0-1-32 mysqld-relay-bin.000002 28559 Query 1 28412 use `mysql`; create function spider_flush_table_mon_cache returns int soname 'ha_spider.so' mysqld-relay-bin.000002 28712 Gtid 1 28454 GTID 0-1-33 mysqld-relay-bin.000002 28754 Query 1 28561 drop procedure mysql.spider_plugin_installer mysqld-relay-bin.000002 28861 Gtid 1 28603 BEGIN GTID 0-1-34 mysqld-relay-bin.000002 28903 Query 1 28796 use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'unknown variable *' COLLATE 'latin1_swedish_ci')) mysqld-relay-bin.000002 29096 Query 1 28868 COMMIT mysqld-relay-bin.000002 29168 Gtid 1 28910 BEGIN GTID 0-1-35 mysqld-relay-bin.000002 29210 Query 1 29103 use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'unknown variable *' COLLATE 'latin1_swedish_ci')) mysqld-relay-bin.000002 29403 Query 1 29175 COMMIT connection default;   == /home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233/bld/mysql-test/var/tmp/analyze-sync_with_master-mysqld.2.1.err ==   ############################## default ##############################   **** SHOW WARNINGS on default **** SHOW WARNINGS; Level Code Message   **** SELECT replication-related variables on default **** SELECT NOW(), @@SERVER_ID; NOW() @@SERVER_ID 2022-02-07 16:12:15 2   **** SHOW SLAVE STATUS on default **** SHOW SLAVE STATUS;   **** SHOW MASTER STATUS on default **** SHOW MASTER STATUS;   **** SHOW SLAVE HOSTS on default **** SHOW SLAVE HOSTS;   **** SHOW PROCESSLIST on default **** SHOW PROCESSLIST; Id User Host db Command Time State Info Progress 1 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000 2 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000 11 root localhost NULL Query 0 Closing tables SHOW PROCESSLIST 0.000   **** SHOW BINARY LOGS on default **** SHOW BINARY LOGS; ERROR HY000: You are not using binary logging   **** SHOW BINLOG EVENTS on default **** binlog_name = 'No such row' SHOW BINLOG EVENTS IN 'No such row'; Log_name Pos Event_type Server_id End_log_pos Info   **** SHOW RELAYLOG EVENTS on default **** relaylog_name = 'No such row' SHOW RELAYLOG EVENTS IN 'No such row'; Log_name Pos Event_type Server_id End_log_pos Info connection default;   == /home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233/bld/mysql-test/var/tmp/analyze-sync_with_master-mysqld.1.1.err ==   ############################## default ##############################   **** SHOW WARNINGS on default **** SHOW WARNINGS; Level Code Message   **** SELECT replication-related variables on default **** SELECT NOW(), @@SERVER_ID; NOW() @@SERVER_ID 2022-02-07 16:12:15 1   **** SHOW SLAVE STATUS on default **** SHOW SLAVE STATUS;   **** SHOW MASTER STATUS on default **** SHOW MASTER STATUS; File master-bin.000001 Position 29175 Binlog_Do_DB Binlog_Ignore_DB   **** SHOW SLAVE HOSTS on default **** SHOW SLAVE HOSTS; Server_id 3 Host Port 16002 Master_id 1   **** SHOW PROCESSLIST on default **** SHOW PROCESSLIST; Id User Host db Command Time State Info Progress 1 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000 2 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000 12 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 13 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 14 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 15 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 16 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 17 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 18 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 19 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 20 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 21 system user NULL Daemon NULL Spider table background statistics action handler NULL 0.000 22 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 23 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 24 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 25 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 26 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 27 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 28 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 29 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 30 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 31 system user NULL Daemon NULL Spider table background cardinality action handler NULL 0.000 35 root localhost:47786 NULL Binlog Dump 0 Master has sent all binlog to slave; waiting for binlog to be updated NULL 0.000 36 root localhost NULL Query 0 Init SHOW PROCESSLIST 0.000   **** SHOW BINARY LOGS on default **** SHOW BINARY LOGS; Log_name File_size master-bin.000001 29175   **** SHOW BINLOG EVENTS on default **** binlog_name = 'master-bin.000001' SHOW BINLOG EVENTS IN 'master-bin.000001'; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 4 Format_desc 1 256 Server ver: 10.4.23-MariaDB-debug-log, Binlog ver: 4 master-bin.000001 256 Gtid_list 1 285 [] master-bin.000001 285 Binlog_checkpoint 1 329 master-bin.000001 master-bin.000001 329 Gtid 1 371 BEGIN GTID 0-1-1 master-bin.000001 371 Query 1 562 use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'unknown variable' COLLATE 'latin1_swedish_ci')) master-bin.000001 562 Query 1 634 COMMIT master-bin.000001 634 Gtid 1 676 GTID 0-1-2 master-bin.000001 676 Query 1 1100 create table if not exists mysql.spider_xa( format_id int not null default 0, gtrid_length int not null default 0, bqual_length int not null default 0, data char(128) charset binary not null default '', status char(8) not null default '', primary key (data, format_id, gtrid_length), key idx1 (status)) engine=MyISAM default charset=utf8 collate=utf8_bin master-bin.000001 1100 Gtid 1 1142 GTID 0-1-3 master-bin.000001 1142 Query 1 1939 create table if not exists mysql.spider_xa_member( format_id int not null default 0, gtrid_length int not null default 0, bqual_length int not null default 0, data char(128) charset binary not null default '', scheme char(64) not null default '', host char(64) not null default '', port char(5) not null default '', socket text not null, username char(64) not null default '', password char(64) not null default '', ssl_ca text, ssl_capath text, ssl_cert text, ssl_cipher char(64) default null, ssl_key text, ssl_verify_server_cert tinyint not null default 0, default_file text, default_group char(64) default null, key idx1 (data, format_id, gtrid_length, host)) engine=MyISAM default charset=utf8 collate=utf8_bin master-bin.000001 1939 Gtid 1 1981 GTID 0-1-4 master-bin.000001 1981 Query 1 2907 create table if not exists mysql.spider_xa_failed_log( format_id int not null default 0, gtrid_length int not null default 0, bqual_length int not null default 0, data char(128) charset binary not null default '', scheme char(64) not null default '', host char(64) not null default '', port char(5) not null default '', socket text not null, username char(64) not null default '', password char(64) not null default '', ssl_ca text, ssl_capath text, ssl_cert text, ssl_cipher char(64) default null, ssl_key text, ssl_verify_server_cert tinyint not null default 0, default_file text, default_group char(64) default null, thread_id int default null, status char(8) not null default '', failed_time timestamp not null default current_timestamp, key idx1 (data, format_id, gtrid_length, host)) engine=MyISAM default charset=utf8 collate=utf8_bin master-bin.000001 2907 Gtid 1 2949 GTID 0-1-5 master-bin.000001 2949 Query 1 4053 create table if not exists mysql.spider_tables( db_name char(64) not null default '', table_name char(199) not null default '', link_id int not null default 0, priority bigint not null default 0, server char(64) default null, scheme char(64) default null, host char(64) default null, port char(5) default null, socket text, username char(64) default null, password char(64) default null, ssl_ca text, ssl_capath text, ssl_cert text, ssl_cipher char(64) default null, ssl_key text, ssl_verify_server_cert tinyint not null default 0, monitoring_binlog_pos_at_failing tinyint not null default 0, default_file text, default_group char(64) default null, tgt_db_name char(64) default null, tgt_table_name char(64) default null, link_status tinyint not null default 1, block_status tinyint not null default 0, static_link_id char(64) default null, primary key (db_name, table_name, link_id), key idx1 (priority), unique key uidx1 (db_name, table_name, static_link_id)) engine=MyISAM default charset=utf8 collate=utf8_bin master-bin.000001 4053 Gtid 1 4095 GTID 0-1-6 master-bin.000001 4095 Query 1 4883 create table if not exists mysql.spider_link_mon_servers( db_name char(64) not null default '', table_name char(199) not null default '', link_id char(64) not null default '', sid int unsigned not null default 0, server char(64) default null, scheme char(64) default null, host char(64) default null, port char(5) default null, socket text, username char(64) default null, password char(64) default null, ssl_ca text, ssl_capath text, ssl_cert text, ssl_cipher char(64) default null, ssl_key text, ssl_verify_server_cert tinyint not null default 0, default_file text, default_group char(64) default null, primary key (db_name, table_name, link_id, sid)) engine=MyISAM default charset=utf8 collate=utf8_bin master-bin.000001 4883 Gtid 1 4925 GTID 0-1-7 master-bin.000001 4925 Query 1 5276 create table if not exists mysql.spider_link_failed_log( db_name char(64) not null default '', table_name char(199) not null default '', link_id char(64) not null default '', failed_time timestamp not null default current_timestamp) engine=MyISAM default charset=utf8 collate=utf8_bin master-bin.000001 5276 Gtid 1 5318 GTID 0-1-8 master-bin.000001 5318 Query 1 5771 create table if not exists mysql.spider_table_position_for_recovery( db_name char(64) not null default '', table_name char(199) not null default '', failed_link_id int not null default 0, source_link_id int not null default 0, file text, position text, gtid text, primary key (db_name, table_name, failed_link_id, source_link_id)) engine=MyISAM default charset=utf8 collate=utf8_bin master-bin.000001 5771 Gtid 1 5813 GTID 0-1-9 master-bin.000001 5813 Query 1 6586 create table if not exists mysql.spider_table_sts( db_name char(64) not null default '', table_name char(199) not null default '', data_file_length bigint unsigned not null default 0, max_data_file_length bigint unsigned not null default 0, index_file_length bigint unsigned not null default 0, records bigint unsigned not null default 0, mean_rec_length bigint unsigned not null default 0, check_time datetime not null default '0000-00-00 00:00:00', create_time datetime not null default '0000-00-00 00:00:00', update_time datetime not null default '0000-00-00 00:00:00', checksum bigint unsigned default null, primary key (db_name, table_name)) engine=MyISAM default charset=utf8 collate=utf8_bin master-bin.000001 6586 Gtid 1 6628 GTID 0-1-10 master-bin.000001 6628 Query 1 7002 create table if not exists mysql.spider_table_crd( db_name char(64) not null default '', table_name char(199) not null default '', key_seq int unsigned not null default 0, cardinality bigint not null default 0, primary key (db_name, table_name, key_seq)) engine=MyISAM default charset=utf8 collate=utf8_bin master-bin.000001 7002 Gtid 1 7044 GTID 0-1-11 master-bin.000001 7044 Query 1 7158 drop procedure if exists mysql.spider_fix_one_table master-bin.000001 7158 Gtid 1 7200 GTID 0-1-12 master-bin.000001 7200 Query 1 7318 drop procedure if exists mysql.spider_fix_system_tables master-bin.000001 7318 Gtid 1 7360 GTID 0-1-13 master-bin.000001 7360 Query 1 7937 CREATE DEFINER=`` PROCEDURE `mysql`.`spider_fix_one_table`(tab_name char(255) charset utf8 collate utf8_bin, test_col_name char(255) charset utf8 collate utf8_bin, _sql text charset utf8 collate utf8_bin) begin set @col_exists := 0; select 1 into @col_exists from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = tab_name AND COLUMN_NAME = test_col_name; if @col_exists = 0 then select @stmt := _sql; prepare sp_stmt1 from @stmt; execute sp_stmt1; end if;end master-bin.000001 7937 Gtid 1 7979 GTID 0-1-14 master-bin.000001 7979 Query 1 22409 CREATE DEFINER=`` PROCEDURE `mysql`.`spider_fix_system_tables`() begin select substring_index(substring_index(version(), '-', 2), '-', -1) into @server_name; select substring_index(version(), '.', 1) into @server_major_version; select substring_index(substring_index(version(), '.', 2), '.', -1) into @server_minor_version; call mysql.spider_fix_one_table('spider_tables', 'server', 'alter table mysql.spider_tables add server char(64) default null, add scheme char(64) default null, add host char(64) default null, add port char(5) default null, add socket char(64) default null, add username char(64) default null, add password char(64) default null, add tgt_db_name char(64) default null, add tgt_table_name char(64) default null'); select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_xa' AND COLUMN_NAME = 'data'; if @col_type != 'binary(128)' then alter table mysql.spider_xa modify data binary(128) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_xa_member' AND COLUMN_NAME = 'data'; if @col_type != 'binary(128)' then alter table mysql.spider_xa_member modify data binary(128) not null default ''; end if; call mysql.spider_fix_one_table('spider_tables', 'link_id', 'alter table mysql.spider_tables add column link_id int not null default 0 after table_name, drop primary key, add primary key (db_name, table_name, link_id)'); call mysql.spider_fix_one_table('spider_tables', 'link_status', 'alter table mysql.spider_tables add column link_status tinyint not null default 1'); call mysql.spider_fix_one_table('spider_xa_member', 'ssl_ca', 'alter table mysql.spider_xa_member add column ssl_ca char(64) default null after password, add column ssl_capath char(64) default null after ssl_ca, add column ssl_cert char(64) default null after ssl_capath, add column ssl_cipher char(64) default null after ssl_cert, add column ssl_key char(64) default null after ssl_cipher, add column ssl_verify_server_cert tinyint not null default 0 after ssl_key, add column default_file char(64) default null after ssl_verify_server_cert, add column default_group char(64) default null after default_file'); call mysql.spider_fix_one_table('spider_tables', 'ssl_ca', 'alter table mysql.spider_tables add column ssl_ca char(64) default null after password, add column ssl_capath char(64) default null after ssl_ca, add column ssl_cert char(64) default null after ssl_capath, add column ssl_cipher char(64) default null after ssl_cert, add column ssl_key char(64) default null after ssl_cipher, add column ssl_verify_server_cert tinyint not null default 0 after ssl_key, add column default_file char(64) default null after ssl_verify_server_cert, add column default_group char(64) default null after default_file'); call mysql.spider_fix_one_table('spider_link_mon_servers', 'ssl_ca', 'alter table mysql.spider_link_mon_servers add column ssl_ca char(64) default null after password, add column ssl_capath char(64) default null after ssl_ca, add column ssl_cert char(64) default null after ssl_capath, add column ssl_cipher char(64) default null after ssl_cert, add column ssl_key char(64) default null after ssl_cipher, add column ssl_verify_server_cert tinyint not null default 0 after ssl_key, add column default_file char(64) default null after ssl_verify_server_cert, add column default_group char(64) default null after default_file'); select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_mon_servers' AND COLUMN_NAME = 'sid'; if @col_type != 'int(10) unsigned' then alter table mysql.spider_link_mon_servers modify sid int unsigned not null default 0; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_xa_member' AND COLUMN_NAME = 'socket'; if @col_type = 'char(64)' then alter table mysql.spider_xa_member drop primary key, add index idx1 (data, format_id, gtrid_length, host), modify socket text not null, modify ssl_ca text, modify ssl_capath text, modify ssl_cert text, modify ssl_key text, modify default_file text; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_tables' AND COLUMN_NAME = 'socket'; if @col_type = 'char(64)' then alter table mysql.spider_tables modify socket text, modify ssl_ca text, modify ssl_capath text, modify ssl_cert text, modify ssl_key text, modify default_file text; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_mon_servers' AND COLUMN_NAME = 'socket'; if @col_type = 'char(64)' then alter table mysql.spider_link_mon_servers modify socket text, modify ssl_ca text, modify ssl_capath text, modify ssl_cert text, modify ssl_key text, modify default_file text; end if; call mysql.spider_fix_one_table('spider_tables', 'monitoring_binlog_pos_at_failing', 'alter table mysql.spider_tables add monitoring_binlog_pos_at_failing tinyint not null default 0 after ssl_verify_server_cert'); call mysql.spider_fix_one_table('spider_tables', 'block_status', 'alter table mysql.spider_tables add column block_status tinyint not null default 0 after link_status'); call mysql.spider_fix_one_table('spider_tables', 'static_link_id', 'alter table mysql.spider_tables add column static_link_id char(64) default null after block_status, add unique index uidx1 (db_name, table_name, static_link_id)'); select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_mon_servers' AND COLUMN_NAME = 'link_id'; if @col_type != 'char(64)' then alter table mysql.spider_link_mon_servers modify link_id char(64) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_failed_log' AND COLUMN_NAME = 'link_id'; if @col_type != 'char(64)' then alter table mysql.spider_link_failed_log modify link_id char(64) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_tables' AND COLUMN_NAME = 'table_name'; if @col_type != 'char(199)' then alter table mysql.spider_tables modify table_name char(199) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_mon_servers' AND COLUMN_NAME = 'table_name'; if @col_type != 'char(199)' then alter table mysql.spider_link_mon_servers modify table_name char(199) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_failed_log' AND COLUMN_NAME = 'table_name'; if @col_type != 'char(199)' then alter table mysql.spider_link_failed_log modify table_name char(199) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_table_position_for_recovery' AND COLUMN_NAME = 'table_name'; if @col_type != 'char(199)' then alter table mysql.spider_table_position_for_recovery modify table_name char(199) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_table_sts' AND COLUMN_NAME = 'table_name'; if @col_type != 'char(199)' then alter table mysql.spider_table_sts modify table_name char(199) not null default ''; end if; select COLUMN_TYPE INTO @col_type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_table_crd' AND COLUMN_NAME = 'table_name'; if @col_type != 'char(199)' then alter table mysql.spider_table_crd modify table_name char(199) not null default ''; end if; call mysql.spider_fix_one_table('spider_table_sts', 'checksum', 'alter table mysql.spider_table_sts add column checksum bigint unsigned default null after update_time'); if @server_name = 'MariaDB' and ( @server_major_version > 10 or ( @server_major_version = 10 and @server_minor_version >= 4 ) ) then select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_failed_log'; if @engine_name != 'Aria' then alter table mysql.spider_link_failed_log engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_link_mon_servers'; if @engine_name != 'Aria' then alter table mysql.spider_link_mon_servers engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_table_crd'; if @engine_name != 'Aria' then alter table mysql.spider_table_crd engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_table_position_for_recovery'; if @engine_name != 'Aria' then alter table mysql.spider_table_position_for_recovery engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_table_sts'; if @engine_name != 'Aria' then alter table mysql.spider_table_sts engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_tables'; if @engine_name != 'Aria' then alter table mysql.spider_tables engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_xa'; if @engine_name != 'Aria' then alter table mysql.spider_xa engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_xa_failed_log'; if @engine_name != 'Aria' then alter table mysql.spider_xa_failed_log engine=Aria transactional=1; end if; select ENGINE INTO @engine_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'spider_xa_member'; if @engine_name != 'Aria' then alter table mysql.spider_xa_member engine=Aria transactional=1; end if; end if; if @server_name = 'MariaDB' and ( @server_major_version > 10 or ( @server_major_version = 10 and @server_minor_version >= 6 ) ) then /* table for ddl pushdown */ create table if not exists mysql.spider_rewrite_tables( table_id bigint unsigned not null auto_increment, db_name char(64) not null default '', table_name char(64) not null default '', primary key (table_id), unique uk1(db_name, table_name) ) engine=Aria transactional=1 default charset=utf8 collate=utf8_bin; create table if not exists mysql.spider_rewrite_table_tables( table_id bigint unsigned not null, partition_id bigint unsigned not null auto_increment, partition_method varchar(18) default '', partition_expression varchar(64) default '', subpartition_method varchar(12) default '', subpartition_expression varchar(64) default '', connection_str text not null default '', comment_str text not null default '', primary key (table_id, partition_id), unique uk1(table_id, partition_method, partition_expression, subpartition_method, subpartition_expression) ) engine=Aria transactional=1 default charset=utf8 collate=utf8_bin; create table if not exists mysql.spider_rewrite_table_partitions( table_id bigint unsigned not null, partition_id bigint unsigned not null, partition_ordinal_position bigint unsigned not null auto_increment, partition_name varchar(64) not null default '', partition_description varchar(64) not null default '', connection_str text not null default '', comment_str text not null default '', primary key (table_id, partition_id, partition_ordinal_position), unique key uk1 (table_id, partition_id, partition_name) ) engine=Aria transactional=1 default charset=utf8 collate=utf8_bin; create table if not exists mysql.spider_rewrite_table_subpartitions( table_id bigint unsigned not null, partition_id bigint unsigned not null, partition_ordinal_position bigint unsigned not null, subpartition_ordinal_position bigint unsigned not null auto_increment, subpartition_name varchar(64) not null default '', subpartition_description varchar(64) not null default '', connection_str text not null default '', comment_str text not null default '', primary key (table_id, partition_id, partition_ordinal_position, subpartition_ordinal_position), unique key uk1 (table_id, partition_id, partition_ordinal_position, subpartition_name) ) engine=Aria transactional=1 default charset=utf8 collate=utf8_bin; create table if not exists mysql.spider_rewritten_tables( db_name char(64) not null, table_name char(64) not null, table_id bigint unsigned not null, partition_id bigint unsigned not null, primary key (db_name, table_name, table_id, partition_id) ) engine=Aria transactional=1 default charset=utf8 collate=utf8_bin; end if;end master-bin.000001 22409 Gtid 1 22451 GTID 0-1-15 master-bin.000001 22451 Query 1 22594 use `mysql`; alter table mysql.spider_link_failed_log engine=Aria transactional=1 master-bin.000001 22594 Gtid 1 22636 GTID 0-1-16 master-bin.000001 22636 Query 1 22780 use `mysql`; alter table mysql.spider_link_mon_servers engine=Aria transactional=1 master-bin.000001 22780 Gtid 1 22822 GTID 0-1-17 master-bin.000001 22822 Query 1 22959 use `mysql`; alter table mysql.spider_table_crd engine=Aria transactional=1 master-bin.000001 22959 Gtid 1 23001 GTID 0-1-18 master-bin.000001 23001 Query 1 23156 use `mysql`; alter table mysql.spider_table_position_for_recovery engine=Aria transactional=1 master-bin.000001 23156 Gtid 1 23198 GTID 0-1-19 master-bin.000001 23198 Query 1 23335 use `mysql`; alter table mysql.spider_table_sts engine=Aria transactional=1 master-bin.000001 23335 Gtid 1 23377 GTID 0-1-20 master-bin.000001 23377 Query 1 23511 use `mysql`; alter table mysql.spider_tables engine=Aria transactional=1 master-bin.000001 23511 Gtid 1 23553 GTID 0-1-21 master-bin.000001 23553 Query 1 23683 use `mysql`; alter table mysql.spider_xa engine=Aria transactional=1 master-bin.000001 23683 Gtid 1 23725 GTID 0-1-22 master-bin.000001 23725 Query 1 23866 use `mysql`; alter table mysql.spider_xa_failed_log engine=Aria transactional=1 master-bin.000001 23866 Gtid 1 23908 GTID 0-1-23 master-bin.000001 23908 Query 1 24045 use `mysql`; alter table mysql.spider_xa_member engine=Aria transactional=1 master-bin.000001 24045 Gtid 1 24087 GTID 0-1-24 master-bin.000001 24087 Query 1 24191 drop procedure mysql.spider_fix_one_table master-bin.000001 24191 Gtid 1 24233 GTID 0-1-25 master-bin.000001 24233 Query 1 24341 drop procedure mysql.spider_fix_system_tables master-bin.000001 24341 Gtid 1 24383 GTID 0-1-26 master-bin.000001 24383 Query 1 24500 drop procedure if exists mysql.spider_plugin_installer master-bin.000001 24500 Gtid 1 24542 GTID 0-1-27 master-bin.000001 24542 Query 1 27467 CREATE DEFINER=`` PROCEDURE `mysql`.`spider_plugin_installer`() begin set @win_plugin := IF(@@version_compile_os like 'Win%', 1, 0); set @have_spider_direct_sql_udf := 0; select @have_spider_direct_sql_udf := 1 from mysql.func where name = 'spider_direct_sql'; if @have_spider_direct_sql_udf = 0 then if @win_plugin = 0 then create function spider_direct_sql returns int soname 'ha_spider.so'; else create function spider_direct_sql returns int soname 'ha_spider.dll'; end if; end if; set @have_spider_bg_direct_sql_udf := 0; select @have_spider_bg_direct_sql_udf := 1 from mysql.func where name = 'spider_bg_direct_sql'; if @have_spider_bg_direct_sql_udf = 0 then if @win_plugin = 0 then create aggregate function spider_bg_direct_sql returns int soname 'ha_spider.so'; else create aggregate function spider_bg_direct_sql returns int soname 'ha_spider.dll'; end if; end if; set @have_spider_ping_table_udf := 0; select @have_spider_ping_table_udf := 1 from mysql.func where name = 'spider_ping_table'; if @have_spider_ping_table_udf = 0 then if @win_plugin = 0 then create function spider_ping_table returns int soname 'ha_spider.so'; else create function spider_ping_table returns int soname 'ha_spider.dll'; end if; end if; set @have_spider_copy_tables_udf := 0; select @have_spider_copy_tables_udf := 1 from mysql.func where name = 'spider_copy_tables'; if @have_spider_copy_tables_udf = 0 then if @win_plugin = 0 then create function spider_copy_tables returns int soname 'ha_spider.so'; else create function spider_copy_tables returns int soname 'ha_spider.dll'; end if; end if; set @have_spider_flush_table_mon_cache_udf := 0; select @have_spider_flush_table_mon_cache_udf := 1 from mysql.func where name = 'spider_flush_table_mon_cache'; if @have_spider_flush_table_mon_cache_udf = 0 then if @win_plugin = 0 then create function spider_flush_table_mon_cache returns int soname 'ha_spider.so'; else create function spider_flush_table_mon_cache returns int soname 'ha_spider.dll'; end if; end if; if @server_name = 'MariaDB' and ( @server_major_version > 10 or ( @server_major_version = 10 and @server_minor_version >= 6 ) ) then set @have_spider_flush_rewrite_cache_udf := 0; select @have_spider_flush_rewrite_cache_udf := 1 from mysql.func where name = 'spider_flush_rewrite_cache'; if @have_spider_flush_rewrite_cache_udf = 0 then if @win_plugin = 0 then create function spider_flush_rewrite_cache returns int soname 'ha_spider.so'; else create function spider_flush_rewrite_cache returns int soname 'ha_spider.dll'; end if; end if; end if;end master-bin.000001 27467 Gtid 1 27509 GTID 0-1-28 master-bin.000001 27509 Query 1 27651 use `mysql`; create function spider_direct_sql returns int soname 'ha_spider.so' master-bin.000001 27651 Gtid 1 27693 GTID 0-1-29 master-bin.000001 27693 Query 1 27848 use `mysql`; create aggregate function spider_bg_direct_sql returns int soname 'ha_spider.so' master-bin.000001 27848 Gtid 1 27890 GTID 0-1-30 master-bin.000001 27890 Query 1 28032 use `mysql`; create function spider_ping_table returns int soname 'ha_spider.so' master-bin.000001 28032 Gtid 1 28074 GTID 0-1-31 master-bin.000001 28074 Query 1 28217 use `mysql`; create function spider_copy_tables returns int soname 'ha_spider.so' master-bin.000001 28217 Gtid 1 28259 GTID 0-1-32 master-bin.000001 28259 Query 1 28412 use `mysql`; create function spider_flush_table_mon_cache returns int soname 'ha_spider.so' master-bin.000001 28412 Gtid 1 28454 GTID 0-1-33 master-bin.000001 28454 Query 1 28561 drop procedure mysql.spider_plugin_installer master-bin.000001 28561 Gtid 1 28603 BEGIN GTID 0-1-34 master-bin.000001 28603 Query 1 28796 use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'unknown variable *' COLLATE 'latin1_swedish_ci')) master-bin.000001 28796 Query 1 28868 COMMIT master-bin.000001 28868 Gtid 1 28910 BEGIN GTID 0-1-35 master-bin.000001 28910 Query 1 29103 use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'unknown variable *' COLLATE 'latin1_swedish_ci')) master-bin.000001 29103 Query 1 29175 COMMIT   **** SHOW RELAYLOG EVENTS on default **** relaylog_name = 'No such row' SHOW RELAYLOG EVENTS IN 'No such row'; Log_name Pos Event_type Server_id End_log_pos Info connection default;       - saving '/home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233/bld/mysql-test/var/log/spider/bugfix.delete_with_float_column_mariadb/' to '/home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233/bld/mysql-test/var/log/bugfix.delete_with_float_column_mariadb/' -------------------------------------------------------------------------- The servers were restarted 0 times Spent 0.000 of 6 seconds executing testcases   Failure: Failed 1/1 tests, 0.00% were successful.   Failing test(s): spider/bugfix.delete_with_float_column_mariadb   The log files in var/log may give you some hint of what went wrong.   If you want to report this error, please read first the documentation at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html   mysql-test-run: *** ERROR: there were failing test cases

Nayuta Yanagisawa (Inactive) added a comment - 2022-02-07 07:52 - edited Spider initialization via the SQL service seems to somewhat affect the replication. In fact, disabling binlog fixes the Spider test failures in bb-10.4- MDEV-27233 (see the diff below). IMHO, disabling the binlog during the plugin initialization is reasonable because the initialization is done in each individual server and thus there is no need to replicate the queries regarding the plugin initialization. diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index ff3cd01c1a2..1098f62d31e 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -6139,6 +6139,8 @@ extern "C" MYSQL *mysql_real_connect_local(MYSQL *mysql) new_thd->query_cache_is_applicable= 0; new_thd->variables.wsrep_on= 0; new_thd->client_capabilities= client_flag; + new_thd->variables.option_bits&= ~OPTION_BIN_LOG; \ + new_thd->variables.sql_log_bin_off= 1; /* TOSO: decide if we should turn the auditing off for such threads.

Nayuta Yanagisawa (Inactive) added a comment - 2022-06-24 15:49 - edited holyfoot Please review: https://github.com/MariaDB/server/commit/13f66ec198c4f8c8eef43bf2584d0ce264adc21d Note that ... The fix is for 10.7+, because only 10.7+ have the SQL service currently; The fix is based on your commit: https://github.com/MariaDB/server/commit/c9de564367c192e5f9a9769a00216f4e20b56793 .

Nayuta Yanagisawa (Inactive) added a comment - 2022-07-06 07:30 holyfoot I've tested bb-10.4- MDEV-27233 -hf but even the main MTR suite crashes due to the following error: 220706 16:28:04 [ERROR] 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.   To report this bug, see https://mariadb.com/kb/en/reporting-bugs   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.   Server version: 10.4.23-MariaDB-debug key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=0 max_threads=153 thread_count=6 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467873 K bytes of memory Hope that's ok; if not, decrease some variables in the equation.   Thread pointer: 0x62b000062208 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 = 0x7fffb1476a30 thread_stack 0x5fc00 sanitizer_common/sanitizer_common_interceptors.inc:4193(__interceptor_backtrace.part.0)[0x7f68723ecc0e] mysys/stacktrace.c:174(my_print_stacktrace)[0x55884e59f8ab] sql/signal_handler.cc:222(handle_fatal_signal)[0x55884d10cff1] /lib/x86_64-linux-gnu/libc.so.6(+0x42520)[0x7f68717d5520] /lib/x86_64-linux-gnu/libcrypto.so.3(EVP_MD_free+0x17)[0x7f6871e717b7] /lib/x86_64-linux-gnu/libcrypto.so.3(+0x17d859)[0x7f6871e6d859] /lib/x86_64-linux-gnu/libcrypto.so.3(EVP_MD_CTX_reset+0x27)[0x7f6871e6da97] mysys_ssl/my_md5.cc:58(md5_init(evp_md_ctx_st*))[0x55884d90bffb] mysys_ssl/my_md5.cc:90(my_md5)[0x55884d90c162] sql/table.cc:5333(TABLE_LIST::calc_md5(char*))[0x55884cc889d3] sql/sql_view.cc:964(mysql_register_view(THD*, TABLE_LIST*, enum_view_create_mode))[0x55884cc4dbe4] sql/sql_view.cc:635(mysql_create_view(THD*, TABLE_LIST*, enum_view_create_mode))[0x55884cc4b9d8] sql/sql_parse.cc:6007(mysql_execute_command(THD*))[0x55884c968385] sql/sql_parse.cc:7995(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55884c975294] sql/sql_parse.cc:1093(bootstrap(st_mysql_file*))[0x55884c945be1] sql/mysqld.cc:5829(mysqld_main(int, char**))[0x55884c65d15d] sql/main.cc:25(main)[0x55884c643dcd] /lib/x86_64-linux-gnu/libc.so.6(+0x29d90)[0x7f68717bcd90] /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0x80)[0x7f68717bce40] /home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233-hf/bld/sql/mysqld(_start+0x25)[0x55884c643ce5]   Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x62b000069228): CREATE DEFINER='mariadb.sys'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS user AS SELECT Host, User, IF(JSON_VALUE(Priv, '$.plugin') IN ('mysql_native_password', 'mysql_old_password'), IFNULL(JSON_VALUE(Priv, '$.authentication_string'), ''), '') AS Password, IF(JSON_VALUE(Priv, '$.access') & 1, 'Y', 'N') AS Select_priv, IF(JSON_VALUE(Priv, '$.access') & 2, 'Y', 'N') AS Insert_priv, IF(JSON_VALUE(Priv, '$.access') & 4, 'Y', 'N') AS Update_priv, IF(JSON_VALUE(Priv, '$.access') & 8, 'Y', 'N') AS Delete_priv, IF(JSON_VALUE(Priv, '$.access') & 16, 'Y', 'N') AS Create_priv, IF(JSON_VALUE(Priv, '$.access') & 32, 'Y', 'N') AS Drop_priv, IF(JSON_VALUE(Priv, '$.access') & 64, 'Y', 'N') AS Reload_priv, IF(JSON_VALUE(Priv, '$.access') & 128, 'Y', 'N') AS Shutdown_priv, IF(JSON_VALUE(Priv, '$.access') & 256, 'Y', 'N') AS Process_priv, IF(JSON_VALUE(Priv, '$.access') & 512, 'Y', 'N') AS File_priv, IF(JSON_VALUE(Priv, '$.access') & 1024, 'Y', 'N') AS Grant_priv, IF(JSON_VALUE(Priv, '$.access') & 2048, 'Y', 'N') AS References_priv, IF(JSON_VALUE(Priv, '$.access') & 4096, 'Y', 'N') AS Index_priv, IF(JSON_VALUE(Priv, '$.access') & 8192, 'Y', 'N') AS Alter_priv, IF(JSON_VALUE(Priv, '$.access') & 16384, 'Y', 'N') AS Show_db_priv, IF(JSON_VALUE(Priv, '$.access') & 32768, 'Y', 'N') AS Super_priv, IF(JSON_VALUE(Priv, '$.access') & 65536, 'Y', 'N') AS Create_tmp_table_priv, IF(JSON_VALUE(Priv, '$.access') & 131072, 'Y', 'N') AS Lock_tables_priv, IF(JSON_VALUE(Priv, '$.access') & 262144, 'Y', 'N') AS Execute_priv, IF(JSON_VALUE(Priv, '$.access') & 524288, 'Y', 'N') AS Repl_slave_priv, IF(JSON_VALUE(Priv, '$.access') & 1048576, 'Y', 'N') AS Repl_client_priv, IF(JSON_VALUE(Priv, '$.access') & 2097152, 'Y', 'N') AS Create_view_priv, IF(JSON_VALUE(Priv, '$.access') & 4194304, 'Y', 'N') AS Show_view_priv, IF(JSON_VALUE(Priv, '$.access') & 8388608, 'Y', 'N') AS Create_routine_priv, IF(JSON_VALUE(Priv, '$.access') & 16777216, 'Y', 'N') AS Alter_routine_priv, IF(JSON_VALUE(Priv, '$.access') & 33554432, 'Y', 'N') AS Create_user_priv, IF(JSON_VALUE(Priv, '$.access') & 67108864, 'Y', 'N') AS Event_priv, IF(JSON_VALUE(Priv, '$.access') & 134217728, 'Y', 'N') AS Trigger_priv, IF(JSON_VALUE(Priv, '$.access') & 268435456, 'Y', 'N') AS Create_tablespace_priv, IF(JSON_VALUE(Priv, '$.access') & 536870912, 'Y', 'N') AS Delete_history_priv, ELT(IFNULL(JSON_VALUE(Priv, '$.ssl_type'), 0) + 1, '', 'ANY','X509', 'SPECIFIED') AS ssl_type, IFNULL(JSON_VALUE(Priv, '$.ssl_cipher'), '') AS ssl_cipher, IFNULL(JSON_VALUE(Priv, '$.x509_issuer'), '') AS x509_issuer, IFNULL(JSON_VALUE(Priv, '$.x509_subject'), '') AS x509_subject, CAST(IFNULL(JSON_VALUE(Priv, '$.max_questions'), 0) AS UNSIGNED) AS max_questions, CAST(IFNULL(JSON_VALUE(Priv, '$.max_updates'), 0) AS UNSIGNED) AS max_updates, CAST(IFNULL(JSON_VALUE(Priv, '$.max_connections'), 0) AS UNSIGNED) AS max_connections, CAST(IFNULL(JSON_VALUE(Priv, '$.max_user_connections'), 0) AS SIGNED) AS max_user_connections, IFNULL(JSON_VALUE(Priv, '$.plugin'), '') AS plugin, IFNULL(JSON_VALUE(Priv, '$.authentication_string'), '') AS authentication_string, IF(IFNULL(JSON_VALUE(Priv, '$.password_last_changed'), 1) = 0, 'Y', 'N') AS password_expired, ELT(IFNULL(JSON_VALUE(Priv, '$.is_role'), 0) + 1, 'N', 'Y') AS is_role, IFNULL(JSON_VALUE(Priv, '$.default_role'), '') AS default_role, CAST(IFNULL(JSON_VALUE(Priv, '$.max_statement_time'), 0.0) AS DECIMAL(12,6)) AS max_statement_time FROM global_priv;   Connection ID (thread ID): 6 Status: NOT_KILLED   Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on   The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains information that should help you find out what is causing the crash. Writing a core file... Working directory at /home/nayuta_mariadb/repo/mariadb-server/bb-10.4-MDEV-27233-hf/bld/mysql-test/var/install.db Resource Limits: Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size unlimited unlimited bytes Max resident set unlimited unlimited bytes Max processes 127510 127510 processes Max open files 1024 1024 files Max locked memory 4202319872 4202319872 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 127510 127510 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: |/usr/share/apport/apport -p%p -s%s -c%c -d%d -P%P -u%u -g%g -- %E

Nayuta Yanagisawa (Inactive) added a comment - 2022-07-20 00:32 The above failure seems to be my environment specific. Please forget. I've rebased bb-10.4- MDEV-27233 -hf to 10.4 HEAD, and everything seems to work fine. The rebased branch is https://github.com/MariaDB/server/tree/bb-10.4-MDEV-27233-2 .

Roel Van de Paar added a comment - 2022-08-31 08:47 I created a PRELOAD SQL feature in the mariadb-qa/pquery framework, which removes the 'blocker of TODO-3120' from this ticket. The feature works well, and new bugs have been discovered in a few hours of running with the new feature enabled.

Nayuta Yanagisawa (Inactive) added a comment - 2022-09-21 06:37 The updated patch: https://github.com/MariaDB/server/commit/4a1e1a2c3a598c5e02646e6ab78355b80313671c

Alexey Botchkov added a comment - 2022-09-29 21:26 ok to push with two corrections. MYSQL *mysql= mysql_init(NULL); please check for mysql == NULL after it. if (mysql_real_connect_local(mysql) == NULL) { ++ mysql_close(mysql) ; DBUG_RETURN(TRUE); } the 'mysql' should be freed if connect wasn't successful.

Roel Van de Paar added a comment - 2022-09-30 23:14 - edited I am still testing this further. I am using the updated patch with the additional change from the review.

Roel Van de Paar added a comment - 2022-10-01 02:18 The --init-file functionality works correctly post-patch and no longer hangs Other bugs seen during runs: in review

Roel Van de Paar added a comment - 2022-10-07 03:31 The testing is complete. This can be pushed.

Nayuta Yanagisawa (Inactive) added a comment - 2022-10-24 08:19 - edited The fix has been pushed to 10.7, but we still need to fix 10.4-10.6.

Nayuta Yanagisawa (Inactive) added a comment - 2022-10-25 06:21 - edited I've extracted the 10.4-10.6 part to another issue. It will be handled by MDEV-29870 .

Nayuta Yanagisawa (Inactive) added a comment - 2022-10-31 07:59 The fix has been reverted due to MDEV-29904 . Thus, I reopened the issue.

Roel Van de Paar added a comment - 2023-05-20 00:45 - edited Also reminder to add the extra MTR testcase mentioned here as part of a patch for this bug. Thank you

Yuchen Pei added a comment - 2023-05-21 00:23 - edited > Also reminder to add the extra MTR testcase mentioned here as part of a patch for this bug. Thank you Thanks for the reminder. Already done as part of MDEV-30581 I should have probably marked that as related to MDEV-29904 - will do that.

Yuchen Pei added a comment - 2023-09-15 06:01 MDEV-22979 , once pushed, will fix this bug.