[MDEV-29420] Galera server hangs when trying to create a Spider table Created: 2022-08-31  Updated: 2023-10-10

Status: Stalled
Project: MariaDB Server
Component/s: Galera, Storage Engine - Spider
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Critical
Reporter: Ramesh Sivaraman Assignee: Julius Goryavsky
Resolution: Unresolved Votes: 2
Labels: not-10.3, thread_hang

Issue Links:
Duplicate
duplicates MDEV-29602 Galera debug build crashes when the s... Closed
Relates

 Description   

Whilst Galera is not meant to support Spider, attempts to use it should not hang the server or cause other issues. Instead, Galera should produce some error message when attempting to create a Spider table. Please replace socket with actual socket in your instance.

10.10.0-opt>INSTALL PLUGIN spider SONAME 'ha_spider.so';
 
Query OK, 0 rows affected (0.123 sec)
 
 
 
10.10.0-opt>CREATE USER spider@localhost IDENTIFIED BY 'PWD123';
 
Query OK, 0 rows affected (0.035 sec)
 
 
 
10.10.0-opt>GRANT ALL ON test.* TO spider@localhost;
 
Query OK, 0 rows affected (0.037 sec)
 
 
 
10.10.0-opt>CREATE server srv FOREIGN DATA wrapper mysql options (socket '/test/GAL_MD080822-mariadb-10.10.0-linux-x86_64-opt/node1/node1_socket.sock', DATABASE 'test', USER 'spider', PASSWORD 'PWD123');
 
Query OK, 0 rows affected (0.031 sec)
 
 
 
10.10.0-opt>CREATE TABLE t (c INT);
 
Query OK, 0 rows affected (0.047 sec)
 
 
 
10.10.0-opt>SET GLOBAL spider_same_server_link=ON;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
10.10.0-opt>CREATE TABLE t_s (c INT) ENGINE=Spider COMMENT='wrapper "mysql", srv "srv", TABLE "t"';

Processlist info

| 12 | root        | localhost | test | Query   |  626 | creating table                                     | CREATE TABLE t_s (c INT) ENGINE=Spider COMMENT='wrapper "mysql", srv "srv", TABLE "t"'               |    0.000 |
 
| 13 | system user |           | NULL | Query   |  626 | Waiting to execute in isolation                    | create table if not exists mysql.spider_link_failed_log(  db_name char(64) not null default '',  tab |    0.000 |
 
| 14 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                                                                                                 |    0.000 |



 Comments   
Comment by Roel Van de Paar [ 2022-08-31 ]

nayuta-yanagisawa FYI
It looks like some late initialization is causing a conflict (ref two threads in process list).

Comment by Miroslav Marinov [ 2022-11-24 ]

Is it even possible to use "spider" with Galera? I cannot find any documentation on this.

I managed to load the plugin with these errors:

INSTALL SONAME "ha_spider";
 
2022-11-24 11:52:01 23 [ERROR] Slave SQL: 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', Internal MariaDB error code: 1959
 
2022-11-24 11:52:01 23 [Warning] WSREP: Ignoring 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', Error_code: 1959
 
2022-11-24 11:52:01 35 [ERROR] Slave SQL: Error 'Invalid role specification ``' on query. Default database: ''. Query: 'CREATE DEFINER=`` PROCEDURE `mysql`.`spider_fix_system_tables`()
 
begin  select substring_index(substring_index(version(), '-', 2), '-', -1)    into @server_name;  if @server_name regexp '^[0-9]+$' then    select substring_index(substring_index(version(), '-', 3), '-', -1)      into @server_name;  end if;  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 COLU
 
2022-11-24 11:52:01 35 [Warning] WSREP: Ignoring error 'Invalid role specification ``' on query. Default database: ''. Query: 'CREATE DEFINER=`` PROCEDURE `mysql`.`spider_fix_system_tables`()
 
begin  select substring_index(substring_index(version(), '-', 2), '-', -1)    into @server_name;  if @server_name regexp '^[0-9]+$' then    select substring_index(substring_index(version(), '-', 3), '-', -1)      into @server_name;  end if;  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
 
2022-11-24 11:52:01 29 [ERROR] Slave SQL: 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', Internal MariaDB error code: 1959
 
2022-11-24 11:52:01 29 [Warning] WSREP: Ignoring 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', Error_code: 1959
 
2022-11-24 11:52:01 8 [ERROR] Slave SQL: Error 'Invalid role specification ``' on query. Default database: ''. Query: 'CREATE DEFINER=`` PROCEDURE `mysql`.`spider_fix_system_tables`()
 
begin  select substring_index(substring_index(version(), '-', 2), '-', -1)    into @server_name;  if @server_name regexp '^[0-9]+$' then    select substring_index(substring_index(version(), '-', 3), '-', -1)      into @server_name;  end if;  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 COLU
 
2022-11-24 11:52:01 8 [Warning] WSREP: Ignoring error 'Invalid role specification ``' on query. Default database: ''. Query: 'CREATE DEFINER=`` PROCEDURE `mysql`.`spider_fix_system_tables`()
 
begin  select substring_index(substring_index(version(), '-', 2), '-', -1)    into @server_name;  if @server_name regexp '^[0-9]+$' then    select substring_index(substring_index(version(), '-', 3), '-', -1)      into @server_name;  end if;  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
 
[ERROR] PROCEDURE mysql.spider_fix_one_table does not exist
 
2022-11-24 11:52:01 22 [ERROR] Slave SQL: Error 'PROCEDURE mysql.spider_fix_one_table does not exist' on query. Default database: ''. Query: 'drop procedure mysql.spider_fix_one_table', Internal MariaDB error code: 1305
 
2022-11-24 11:52:01 22 [Warning] WSREP: Ignoring error 'PROCEDURE mysql.spider_fix_one_table does not exist' on query. Default database: ''. Query: 'drop procedure mysql.spider_fix_one_table', Error_code: 1305
 
2022-11-24 11:52:01 42 [ERROR] Slave SQL: Error 'PROCEDURE mysql.spider_fix_system_tables does not exist' on query. Default database: ''. Query: 'drop procedure mysql.spider_fix_system_tables', Internal MariaDB error code: 1305
 
2022-11-24 11:52:01 42 [Warning] WSREP: Ignoring error 'PROCEDURE mysql.spider_fix_system_tables does not exist' on query. Default database: ''. Query: 'drop procedure mysql.spider_fix_system_tables', Error_code: 1305
 
2022-11-24 11:52:01 24 [ERROR] Slave SQL: Error 'Invalid role specification ``' on query. Default database: ''. Query: 'CREATE DEFINER=`` PROCEDURE `mysql`.`spider_plugin_installer`()
 
begin  set @win_plugin := IF(@@version_compile_os like 'Win%', 1, 0);  set @have_spider_i_s_alloc_mem_plugin := 0;  select @have_spider_i_s_alloc_mem_plugin := 1    from INFORMATION_SCHEMA.plugins    where PLUGIN_NAME = 'SPIDER_ALLOC_MEM';  set @have_spider_alloc_mem_plugin := 0;  select @have_spider_alloc_mem_plugin := 1 from mysql.plugin    where name = 'spider_alloc_mem';  if @have_spider_i_s_alloc_mem_plugin = 0 then    if @have_spider_alloc_mem_plugin = 1 then      /*        spider_alloc_mem plugin is present in mysql.plugin but not in        information_schema.plugins. Remove spider_alloc_mem plugin entry        in mysql.plugin first.      */      delete from mysql.plugin where name = 'spider_alloc_mem';    end if;    if @win_plugin = 0 then       install plugin spider_alloc_mem soname 'ha_spider.so';    else      install plugin spider_alloc_mem soname
 
2022-11-24 11:52:01 24 [Warning] WSREP: Ignoring error 'Invalid role specification ``' on query. Default database: ''. Query: 'CREATE DEFINER=`` PROCEDURE `mysql`.`spider_plugin_installer`()
 
begin  set @win_plugin := IF(@@version_compile_os like 'Win%', 1, 0);  set @have_spider_i_s_alloc_mem_plugin := 0;  select @have_spider_i_s_alloc_mem_plugin := 1    from INFORMATION_SCHEMA.plugins    where PLUGIN_NAME = 'SPIDER_ALLOC_MEM';  set @have_spider_alloc_mem_plugin := 0;  select @have_spider_alloc_mem_plugin := 1 from mysql.plugin    where name = 'spider_alloc_mem';  if @have_spider_i_s_alloc_mem_plugin = 0 then    if @have_spider_alloc_mem_plugin = 1 then      /*        spider_alloc_mem plugin is present in mysql.plugin but not in        information_schema.plugins. Remove spider_alloc_mem plugin entry        in mysql.plugin first.      */      delete from mysql.plugin where name = 'spider_alloc_mem';    end if;    if @win_plugin = 0 then       install plugin spider_alloc_mem soname 'ha_spider.so';    else      install plugin spider_alloc_mem s
 
2022-11-24 11:52:01 30 [ERROR] Slave SQL: Error 'PROCEDURE mysql.spider_plugin_installer does not exist' on query. Default database: ''. Query: 'drop procedure mysql.spider_plugin_installer', Internal MariaDB error code: 1305
 
2022-11-24 11:52:01 30 [Warning] WSREP: Ignoring error 'PROCEDURE mysql.spider_plugin_installer does not exist' on query. Default database: ''. Query: 'drop procedure mysql.spider_plugin_installer', Error_code: 1305


MariaDB [(none)]> SELECT ENGINE, SUPPORT FROM information_schema.ENGINES;
 
+--------------------+---------+
 
| ENGINE             | SUPPORT |
 
+--------------------+---------+
 
| SPIDER             | YES     |
 
| MRG_MyISAM         | YES     |
 
| MEMORY             | YES     |
 
| Aria               | YES     |
 
| MyISAM             | YES     |
 
| SEQUENCE           | YES     |
 
| InnoDB             | DEFAULT |
 
| PERFORMANCE_SCHEMA | YES     |
 
| CSV                | YES     |
 
+--------------------+---------+


MariaDB [mysql]> show tables;
 
+------------------------------------+
 
| Tables_in_mysql                    |
 
+------------------------------------+
 
| column_stats                       |
 
| columns_priv                       |
 
| db                                 |
 
| event                              |
 
| func                               |
 
| general_log                        |
 
| global_priv                        |
 
| gtid_slave_pos                     |
 
| help_category                      |
 
| help_keyword                       |
 
| help_relation                      |
 
| help_topic                         |
 
| index_stats                        |
 
| innodb_index_stats                 |
 
| innodb_table_stats                 |
 
| plugin                             |
 
| proc                               |
 
| procs_priv                         |
 
| proxies_priv                       |
 
| roles_mapping                      |
 
| servers                            |
 
| slow_log                           |
 
| spider_link_failed_log             |
 
| spider_link_mon_servers            |
 
| spider_table_crd                   |
 
| spider_table_position_for_recovery |
 
| spider_table_sts                   |
 
| spider_tables                      |
 
| spider_xa                          |
 
| spider_xa_failed_log               |
 
| spider_xa_member                   |
 
| table_stats                        |
 
| tables_priv                        |
 
| time_zone                          |
 
| time_zone_leap_second              |
 
| time_zone_name                     |
 
| time_zone_transition               |
 
| time_zone_transition_type          |
 
| transaction_registry               |
 
| user                               |
 
| wsrep_cluster                      |
 
| wsrep_cluster_members              |
 
| wsrep_streaming_log                |
 
+------------------------------------+

But after that, I cannot start/restart or bootstrap the cluster.

2022-11-24 12:07:06 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
 
2022-11-24 12:07:06 0 [Note] InnoDB: Number of pools: 1
 
2022-11-24 12:07:06 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
 
2022-11-24 12:07:06 0 [Note] InnoDB: Using Linux native AIO
 
2022-11-24 12:07:06 0 [Note] InnoDB: Initializing buffer pool, total size = 2845415, chunk size = 134217728
 
2022-11-24 12:07:07 0 [Note] InnoDB: Completed initialization of buffer pool
 
2022-11-24 12:07:08 0 [Note] InnoDB: 128 rollback segments are active.
 
2022-11-24 12:07:08 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2022-11-24 12:07:08 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2022-11-24 12:07:08 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 
2022-11-24 12:07:08 0 [Note] InnoDB: 10.6.11 started; log sequence number 37215265; transaction id 323
 
2022-11-24 12:07:08 0 [Warning] InnoDB: Skipping buffer pool dump/restore during wsrep recovery.
 
2022-11-24 12:07:08 0 [Note] Plugin 'FEEDBACK' is disabled.
 
*2022-11-24 12:07:08 0 [Note] Loaded 'ha_spider.so' with offset 0x7f92aa7f3000*
 
2022-11-24 12:07:08 0 [Note] Server socket created on IP: '0.0.0.0'.
 
2022-11-24 12:07:08 0 [Note] Server socket created on IP: '::'.
 
2022-11-24 12:07:08 0 [Note] WSREP: Recovered position: 95046c45-6be3-11ed-86a5-abea192056e4:125,1-1-109

Any info will be appreciated about the usage of "spider" with Galera.

Thanks

Generated at Thu Feb 08 10:08:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.