[MDEV-6268] SPIDER table with no COMMENT clause causes queries to wait forever Created: 2014-05-26  Updated: 2023-04-04  Resolved: 2020-07-29

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.0.11
Fix Version/s: 10.5.4

Type: Bug Priority: Critical
Reporter: Federico Razzoli Assignee: Kentoku Shiba (Inactive)
Resolution: Fixed Votes: 0
Labels: spider

Issue Links:
Relates
relates to MDEV-30542 Update the spider self_reference test... Closed
relates to MDEV-30994 Earlier detection of spider table sel... Open
relates to MDEV-29583 Disallow Spider self/dual/multi/cross... Closed

 Description    

MariaDB [test]> CREATE OR REPLACE TABLE user (
 
    ->   username VARCHAR(50) NOT NULL
 
    -> , password VARCHAR(50) NOT NULL
 
    -> )
 
    -> ENGINE = SPIDER;
 
Query OK, 0 rows affected (0.12 sec)
 
 
 
MariaDB [test]> SELECT * FROM user;

I beleive that the table should not be created, since the COMMENT clause misses.

The query waits forever. Note that even KILL HARD, in some cases, does not kill the query/process.

 Comments   
Comment by Elena Stepanova [ 2014-05-27 ]

I don't think the problem is the lack of COMMENT as such, but that with the default connection values it attempts to connect the table to itself (to use the same table as a backend table).

On my machine, with the absence of COMMENT, I'm getting an error message:

MariaDB [test]> CREATE OR REPLACE TABLE user ( username VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL ) ENGINE = SPIDER;Query OK, 0 rows affected (0.16 sec)
 
 
 
MariaDB [test]> SELECT * FROM user;
 
ERROR 1429 (HY000): Unable to connect to foreign data source: localhost

But with 127.0.0.1 as a host, it hangs as described:

MariaDB [test]> CREATE OR REPLACE TABLE user ( username VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL ) ENGINE = SPIDER COMMENT 'host "127.0.0.1"';
 
Query OK, 0 rows affected (0.35 sec)
 
 
 
MariaDB [test]> SELECT * FROM user;

CPU usage is high.
Stack trace from the running process:

Thread 3 (Thread 0x7fe97ce75700 (LWP 10826)):
 
#0  0x00007fe97b03fe33 in *__GI___poll (fds=<optimized out>, nfds=<optimized out>, timeout=600000) at ../sysdeps/unix/sysv/linux/poll.c:87
 
#1  0x0000000000e84688 in vio_io_wait (vio=0x7fe95f7a8d70, event=VIO_IO_EVENT_READ, timeout=600000) at 10.0/vio/viosocket.c:969
 
#2  0x0000000000e8310d in vio_socket_io_wait (vio=0x7fe95f7a8d70, event=VIO_IO_EVENT_READ) at 10.0/vio/viosocket.c:108
 
#3  0x0000000000e832bd in vio_read (vio=0x7fe95f7a8d70, buf=0x7fe964b18070 "\a", size=16384) at 10.0/vio/viosocket.c:181
 
#4  0x0000000000e834b7 in vio_read_buff (vio=0x7fe95f7a8d70, buf=0x7fe964b71070 "*", size=4) at 10.0/vio/viosocket.c:223
 
#5  0x00000000005c7bf7 in my_real_read (net=0x7fe964a68470, complen=0x7fe97ce720b0) at 10.0/sql/net_serv.cc:870
 
#6  0x00000000005c831a in my_net_read (net=0x7fe964a68470) at 10.0/sql/net_serv.cc:1072
 
#7  0x0000000000826a87 in cli_safe_read (mysql=0x7fe964a68470) at 10.0/sql-common/client.c:767
 
#8  0x000000000082db12 in cli_read_query_result (mysql=0x7fe964a68470) at 10.0/sql-common/client.c:4108
 
#9  0x000000000082dfaf in mysql_real_query (mysql=0x7fe964a68470, query=0x7fe964b153b0 "show table status from `test` like 'user'", length=41) at 10.0/sql-common/client.c:4194
 
#10 0x00007fe94ed92932 in spider_db_mysql::exec_query (this=0x7fe964b06af0, query=0x7fe964b153b0 "show table status from `test` like 'user'", length=41, quick_mode=-1) at 10.0/storage/spider/spd_db_mysql.cc:1554
 
#11 0x00007fe94ecf93f6 in spider_db_query (conn=0x7fe95fa17680, query=0x7fe964b153b0 "show table status from `test` like 'user'", length=41, quick_mode=-1, need_mon=0x7fe965bbb310) at 10.0/storage/spider/spd_db_conn.cc:611
 
#12 0x00007fe94edb0e0b in spider_mysql_handler::show_table_status (this=0x7fe964b79a70, link_idx=0, sts_mode=1, flag=88) at 10.0/storage/spider/spd_db_mysql.cc:9862
 
#13 0x00007fe94ed09157 in spider_db_show_table_status (spider=0x7fe964ab2088, link_idx=0, sts_mode=1, flag=88) at 10.0/storage/spider/spd_db_conn.cc:5173
 
#14 0x00007fe94ed42bbd in spider_get_sts (share=0x7fe95fa64080, link_idx=0, tmp_time=1401180942, spider=0x7fe964ab2088, sts_interval=10, sts_mode=1, sts_sync=0, sts_sync_level=1, flag=88) at 10.0/storage/spider/spd_table.cc:6721
 
#15 0x00007fe94ed3c8c4 in spider_get_share (table_name=0x7fe964aea880 "./test/user", table=0x7fe964ab0870, thd=0x7fe95f3f7070, spider=0x7fe964ab2088, error_num=0x7fe97ce72e6c) at 10.0/storage/spider/spd_table.cc:4571
 
#16 0x00007fe94ed62f87 in ha_spider::open (this=0x7fe964ab2088, name=0x7fe964aea880 "./test/user", mode=2, test_if_locked=18) at 10.0/storage/spider/ha_spider.cc:333
 
#17 0x0000000000864476 in handler::ha_open (this=0x7fe964ab2088, table_arg=0x7fe964ab0870, name=0x7fe964aea880 "./test/user", mode=2, test_if_locked=18) at 10.0/sql/handler.cc:2468
 
#18 0x000000000074f832 in open_table_from_share (thd=0x7fe95f3f7070, share=0x7fe964aea288, alias=0x7fe95fa84268 "user", db_stat=39, prgflag=44, ha_open_flags=18, outparam=0x7fe964ab0870, is_create_table=false) at 10.0/sql/table.cc:2851
 
#19 0x00000000006152de in open_table (thd=0x7fe95f3f7070, table_list=0x7fe95fa84270, mem_root=0x7fe97ce73580, ot_ctx=0x7fe97ce73540) at 10.0/sql/sql_base.cc:2459
 
#20 0x000000000061796b in open_and_process_table (thd=0x7fe95f3f7070, lex=0x7fe95f3facb0, tables=0x7fe95fa84270, counter=0x7fe97ce73694, flags=0, prelocking_strategy=0x7fe97ce736d0, has_prelocking_list=false, ot_ctx=0x7fe97ce73540, new_frm_mem=0x7fe97ce73580) at 10.0/sql/sql_base.cc:3917
 
#21 0x0000000000618b4d in open_tables (thd=0x7fe95f3f7070, start=0x7fe97ce73650, counter=0x7fe97ce73694, flags=0, prelocking_strategy=0x7fe97ce736d0) at 10.0/sql/sql_base.cc:4459
 
#22 0x0000000000619a56 in open_and_lock_tables (thd=0x7fe95f3f7070, tables=0x7fe95fa84270, derived=true, flags=0, prelocking_strategy=0x7fe97ce736d0) at 10.0/sql/sql_base.cc:5076
 
#23 0x000000000060e0ef in open_and_lock_tables (thd=0x7fe95f3f7070, tables=0x7fe95fa84270, derived=true, flags=0) at 10.0/sql/sql_base.h:485
 
#24 0x000000000067c958 in execute_sqlcom_select (thd=0x7fe95f3f7070, all_tables=0x7fe95fa84270) at 10.0/sql/sql_parse.cc:5208
 
#25 0x0000000000674fe4 in mysql_execute_command (thd=0x7fe95f3f7070) at 10.0/sql/sql_parse.cc:2554
 
#26 0x000000000067f377 in mysql_parse (thd=0x7fe95f3f7070, rawbuf=0x7fe95fa84088 "SELECT * FROM user", length=18, parser_state=0x7fe97ce74610) at 10.0/sql/sql_parse.cc:6409
 
#27 0x0000000000672285 in dispatch_command (command=COM_QUERY, thd=0x7fe95f3f7070, packet=0x7fe964b0e071 "SELECT * FROM user", packet_length=18) at 10.0/sql/sql_parse.cc:1309
 
#28 0x000000000067162a in do_command (thd=0x7fe95f3f7070) at 10.0/sql/sql_parse.cc:1006
 
#29 0x000000000078caf1 in do_handle_one_connection (thd_arg=0x7fe95f3f7070) at 10.0/sql/sql_connect.cc:1379
 
#30 0x000000000078c844 in handle_one_connection (arg=0x7fe95f3f7070) at 10.0/sql/sql_connect.cc:1293
 
#31 0x0000000000cb9cca in pfs_spawn_thread (arg=0x7fe95f7a53f0) at 10.0/storage/perfschema/pfs.cc:1860
 
#32 0x00007fe97cb52b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#33 0x00007fe97b04aa7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
 
#34 0x0000000000000000 in ?? ()
 
 
 
Thread 2 (Thread 0x7fe97ce2c700 (LWP 10828)):
 
#0  0x00007fe97b044573 in select () at ../sysdeps/unix/syscall-template.S:82
 
#1  0x0000000000e33533 in my_sleep (m_seconds=10) at 10.0/mysys/my_sleep.c:29
 
#2  0x00007fe94ed3c9d3 in spider_get_share (table_name=0x7fe964aea880 "./test/user", table=0x7fe95e101c70, thd=0x7fe964bec070, spider=0x7fe95e102888, error_num=0x7fe97ce27e4c) at 10.0/storage/spider/spd_table.cc:4648
 
#3  0x00007fe94ed62f87 in ha_spider::open (this=0x7fe95e102888, name=0x7fe964aea880 "./test/user", mode=2, test_if_locked=18) at 10.0/storage/spider/ha_spider.cc:333
 
#4  0x0000000000864476 in handler::ha_open (this=0x7fe95e102888, table_arg=0x7fe95e101c70, name=0x7fe964aea880 "./test/user", mode=2, test_if_locked=18) at 10.0/sql/handler.cc:2468
 
#5  0x000000000074f832 in open_table_from_share (thd=0x7fe964bec070, share=0x7fe964aea288, alias=0x7fe95e0dacb0 "user", db_stat=39, prgflag=44, ha_open_flags=18, outparam=0x7fe95e101c70, is_create_table=false) at 10.0/sql/table.cc:2851
 
#6  0x00000000006152de in open_table (thd=0x7fe964bec070, table_list=0x7fe95e0f5088, mem_root=0x7fe97ce28560, ot_ctx=0x7fe97ce28520) at 10.0/sql/sql_base.cc:2459
 
#7  0x000000000061796b in open_and_process_table (thd=0x7fe964bec070, lex=0x7fe97ce28740, tables=0x7fe95e0f5088, counter=0x7fe97ce2867c, flags=1026, prelocking_strategy=0x7fe97ce28680, has_prelocking_list=false, ot_ctx=0x7fe97ce28520, new_frm_mem=0x7fe97ce28560) at 10.0/sql/sql_base.cc:3917
 
#8  0x0000000000618b4d in open_tables (thd=0x7fe964bec070, start=0x7fe97ce28630, counter=0x7fe97ce2867c, flags=1026, prelocking_strategy=0x7fe97ce28680) at 10.0/sql/sql_base.cc:4459
 
#9  0x0000000000619c87 in open_normal_and_derived_tables (thd=0x7fe964bec070, tables=0x7fe95e0f5088, flags=1026, dt_phases=34) at 10.0/sql/sql_base.cc:5139
 
#10 0x00000000006f8c92 in fill_schema_table_by_open (thd=0x7fe964bec070, is_show_fields_or_keys=false, table=0x7fe95e085088, schema_table=0x16a3a40, orig_db_name=0x7fe97ce29c30, orig_table_name=0x7fe95e0dac50, open_tables_state_backup=0x7fe97ce29c60, can_deadlock=false) at 10.0/sql/sql_show.cc:4183
 
#11 0x00000000006fa1c1 in get_all_tables (thd=0x7fe964bec070, tables=0x7fe95e07d970, cond=0x0) at 10.0/sql/sql_show.cc:4839
 
#12 0x0000000000707a4c in get_schema_tables_result (join=0x7fe95e083a30, executed_place=PROCESSED_BY_JOIN_EXEC) at 10.0/sql/sql_show.cc:8075
 
#13 0x00000000006aeefd in JOIN::exec_inner (this=0x7fe95e083a30) at 10.0/sql/sql_select.cc:2530
 
#14 0x00000000006ae4dc in JOIN::exec (this=0x7fe95e083a30) at 10.0/sql/sql_select.cc:2365
 
#15 0x00000000006b1873 in mysql_select (thd=0x7fe964bec070, rref_pointer_array=0x7fe964bf06d8, tables=0x7fe95e07d970, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2684619520, result=0x7fe95e083a10, unit=0x7fe964befd78, select_lex=0x7fe964bf0460) at 10.0/sql/sql_select.cc:3303
 
#16 0x00000000006a7e73 in handle_select (thd=0x7fe964bec070, lex=0x7fe964befcb0, result=0x7fe95e083a10, setup_tables_done_option=0) at 10.0/sql/sql_select.cc:372
 
#17 0x000000000067cbed in execute_sqlcom_select (thd=0x7fe964bec070, all_tables=0x7fe95e07d970) at 10.0/sql/sql_parse.cc:5263
 
#18 0x0000000000674fe4 in mysql_execute_command (thd=0x7fe964bec070) at 10.0/sql/sql_parse.cc:2554
 
#19 0x000000000067f377 in mysql_parse (thd=0x7fe964bec070, rawbuf=0x7fe95e07c088 "show table status from `test` like 'user'", length=41, parser_state=0x7fe97ce2b610) at 10.0/sql/sql_parse.cc:6409
 
#20 0x0000000000672285 in dispatch_command (command=COM_QUERY, thd=0x7fe964bec070, packet=0x7fe964b4b071 "show table status from `test` like 'user'", packet_length=41) at 10.0/sql/sql_parse.cc:1309
 
#21 0x000000000067162a in do_command (thd=0x7fe964bec070) at 10.0/sql/sql_parse.cc:1006
 
#22 0x000000000078caf1 in do_handle_one_connection (thd_arg=0x7fe964bec070) at 10.0/sql/sql_connect.cc:1379
 
#23 0x000000000078c844 in handle_one_connection (arg=0x7fe964bec070) at 10.0/sql/sql_connect.cc:1293
 
#24 0x0000000000cb9cca in pfs_spawn_thread (arg=0x7fe95f7a62f0) at 10.0/storage/perfschema/pfs.cc:1860
 
#25 0x00007fe97cb52b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#26 0x00007fe97b04aa7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
 
#27 0x0000000000000000 in ?? ()

Stack trace from:

revision-id: elenst@montyprogram.com-20140526184135-hrn5hsqvvuk1eyf0
 
date: 2014-05-26 22:41:35 +0400
 
build-date: 2014-05-27 13:08:16 +0400
 
revno: 4212
 
branch-nick: 10.0

Processlist:

MariaDB [test]> show full processlist;
 
+----+------+-----------------+------+---------+------+----------------+-------------------------------------------+----------+
 
| Id | User | Host            | db   | Command | Time | State          | Info                                      | Progress |
 
+----+------+-----------------+------+---------+------+----------------+-------------------------------------------+----------+
 
|  4 | root | localhost:58610 | test | Query   |  154 | closing tables | SELECT * FROM user                        |    0.000 |
 
|  5 |      | localhost:58611 | test | Query   |  154 | Opening tables | show table status from `test` like 'user' |    0.000 |
 
|  6 | root | localhost:58613 | test | Query   |    0 | init           | show full processlist                     |    0.000 |
 
+----+------+-----------------+------+---------+------+----------------+-------------------------------------------+----------+

Locks:

MariaDB [test]> select * from information_schema.metadata_lock_info;
 
+-----------+----------------------+-----------------+---------------------+--------------+------------+
 
| THREAD_ID | LOCK_MODE            | LOCK_DURATION   | LOCK_TYPE           | TABLE_SCHEMA | TABLE_NAME |
 
+-----------+----------------------+-----------------+---------------------+--------------+------------+
 
|         4 | MDL_SHARED_READ      | MDL_TRANSACTION | Table metadata lock | test         | user       |
 
|         5 | MDL_SHARED_HIGH_PRIO | MDL_TRANSACTION | Table metadata lock | test         | user       |
 
|         7 | MDL_SHARED_HIGH_PRIO | MDL_TRANSACTION | Table metadata lock | test         | user       |
 
+-----------+----------------------+-----------------+---------------------+--------------+------------+
 
3 rows in set (0.00 sec)

Comment by Kentoku Shiba (Inactive) [ 2019-05-28 ]

I added mysql_mutex_timedlock() for resolving this issue.
Would you please review the following commit especially about mysql_mutex_timedlock()?
23b7014

Comment by Kentoku Shiba (Inactive) [ 2019-08-26 ]

I fixed this issue using user variables as your suggestion. It needs a TABLE_SHARE object of parent table to know the table name. Would you please review server side changes in the following changes?
8ea6014

Comment by Kentoku Shiba (Inactive) [ 2019-09-01 ]

serg
I refactored for reduce hash cascading. Please review the following changes.
4a8dafe

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