[MDEV-31968] Spider uses too long variable names on a remote server Created: 2023-08-20  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.5, 10.6, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-23665 Assertion `length <= 64' failed in PF... Open
relates to MDEV-30994 Earlier detection of spider table sel... Open
relates to MDEV-31967 User variable names over 64 symbols c... Closed

 Description   

Upon connecting to a remote server, Spider creates a user variable consisting of a prefix, database name and table name. The identifier can easily exceed 64 symbols. It is currently allowed (see MDEV-31967, MDEV-23665), but causes problems, for example, with performance schema, as described there, and most likely with some other components. If it ends up to be forbidden, it will obviously cause even more problems. It is already forbidden in MySQL, so if Spider intends to be able to read from MySQL, it can be a cause of a failure.

--source plugin/spider/spider/include/init_spider.inc
 
 
 
SET spider_same_server_link= on;
 
eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "spider_remote_db", user "root", port $MASTER_MYPORT);
 
 
 
CREATE DATABASE spider_remote_db;
 
CREATE TABLE spider_remote_db.sufficiently_but_not_exceessively_long_table_name (a INT);
 
 
 
CREATE TABLE t_spider (a INT) ENGINE=SPIDER COMMENT = 'wrapper "mysql", srv "s", table "sufficiently_but_not_exceessively_long_table_name"';
 
SHOW CREATE TABLE t_spider;
 
 
 
# This wll currently cause an assertion failure
 
# SELECT * FROM performance_schema.user_variables_by_thread;
 
 
 
# Cleanup
 
DROP DATABASE spider_remote_db;
 
DROP TABLE t_spider;
 
--source plugin/spider/spider/include/deinit_spider.inc

10.5 7c9837ce general log

 
set session time_zone = '+00:00';set @`spider_lc_./spider_remote_db/sufficiently_but_not_exceessively_long_table_name` = '-2cf05d72f7ce-35bcab-./test/t_spider-'

With uncommented query from performance schema:

10.5 7c9837ce

 
mariadbd: /data/src/10.5/storage/perfschema/table_uvar_by_thread.cc:108: void User_variables::materialize(PFS_thread*, THD*): Assertion `name_length <= sizeof(pfs_uvar.m_name)' failed.
 
230820 17:53:50 [ERROR] mysqld got signal 6 ;
 
 
 
#9  0x00007f9cb0e53df2 in __GI___assert_fail (assertion=0x55d99ea02f80 "name_length <= sizeof(pfs_uvar.m_name)", file=0x55d99ea02d60 "/data/src/10.5/storage/perfschema/table_uvar_by_thread.cc", line=108, function=0x55d99ea02e80 "void User_variables::materialize(PFS_thread*, THD*)") at ./assert/assert.c:101
 
#10 0x000055d99d23fedc in User_variables::materialize (this=0x60d000010ae8, pfs=0x7f9caee0a200, thd=0x62b000230218) at /data/src/10.5/storage/perfschema/table_uvar_by_thread.cc:108
 
#11 0x000055d99d2407c6 in table_uvar_by_thread::materialize (this=0x60d000010ac0, thread=0x7f9caee0a200) at /data/src/10.5/storage/perfschema/table_uvar_by_thread.cc:258
 
#12 0x000055d99d2403e6 in table_uvar_by_thread::rnd_next (this=0x60d000010ac0) at /data/src/10.5/storage/perfschema/table_uvar_by_thread.cc:201
 
#13 0x000055d99d13f831 in ha_perfschema::rnd_next (this=0x61a00002eeb8, buf=0x61900027b0e0 "\377") at /data/src/10.5/storage/perfschema/ha_perfschema.cc:358
 
#14 0x000055d99c8dabd5 in handler::ha_rnd_next (this=0x61a00002eeb8, buf=0x61900027b0e0 "\377") at /data/src/10.5/sql/handler.cc:3120
 
#15 0x000055d99cd2d298 in rr_sequential (info=0x62b00003cd68) at /data/src/10.5/sql/records.cc:519
 
#16 0x000055d99befc764 in READ_RECORD::read_record (this=0x62b00003cd68) at /data/src/10.5/sql/records.h:80
 
#17 0x000055d99c20e5a4 in join_init_read_record (tab=0x62b00003cca0) at /data/src/10.5/sql/sql_select.cc:22141
 
#18 0x000055d99c20774c in sub_select (join=0x62b00003b410, join_tab=0x62b00003cca0, end_of_records=false) at /data/src/10.5/sql/sql_select.cc:21174
 
#19 0x000055d99c2056d4 in do_select (join=0x62b00003b410, procedure=0x0) at /data/src/10.5/sql/sql_select.cc:20696
 
#20 0x000055d99c19122d in JOIN::exec_inner (this=0x62b00003b410) at /data/src/10.5/sql/sql_select.cc:4602
 
#21 0x000055d99c18e812 in JOIN::exec (this=0x62b00003b410) at /data/src/10.5/sql/sql_select.cc:4382
 
#22 0x000055d99c192b2d in mysql_select (thd=0x62b000069218, tables=0x62b0000389e0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x62b00003b3e0, unit=0x62b00006d3c8, select_lex=0x62b000038318) at /data/src/10.5/sql/sql_select.cc:4859
 
#23 0x000055d99c163892 in handle_select (thd=0x62b000069218, lex=0x62b00006d300, result=0x62b00003b3e0, setup_tables_done_option=0) at /data/src/10.5/sql/sql_select.cc:450
 
#24 0x000055d99c0cc887 in execute_sqlcom_select (thd=0x62b000069218, all_tables=0x62b0000389e0) at /data/src/10.5/sql/sql_parse.cc:6341
 
#25 0x000055d99c0bb37b in mysql_execute_command (thd=0x62b000069218) at /data/src/10.5/sql/sql_parse.cc:4018
 
#26 0x000055d99c0d784d in mysql_parse (thd=0x62b000069218, rawbuf=0x62b000038238 "SELECT * FROM performance_schema.user_variables_by_thread", length=57, parser_state=0x7f9ca96bac10, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:8118
 
#27 0x000055d99c0ad1ee in dispatch_command (command=COM_QUERY, thd=0x62b000069218, packet=0x629000253219 "", packet_length=57, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:1891
 
#28 0x000055d99c0a9b81 in do_command (thd=0x62b000069218) at /data/src/10.5/sql/sql_parse.cc:1375
 
#29 0x000055d99c4f8be7 in do_handle_one_connection (connect=0x608000002b38, put_in_cache=true) at /data/src/10.5/sql/sql_connect.cc:1416
 
#30 0x000055d99c4f85af in handle_one_connection (arg=0x608000002ab8) at /data/src/10.5/sql/sql_connect.cc:1318
 
#31 0x000055d99d145e18 in pfs_spawn_thread (arg=0x615000005318) at /data/src/10.5/storage/perfschema/pfs.cc:2201
 
#32 0x00007f9cb0ea7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#33 0x00007f9cb0f285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81



 Comments   
Comment by Yuchen Pei [ 2023-08-22 ]

The user variable is for use of spider self-reference detection, see
for example the description of MDEV-30994 how it works.

One possible fix would be to move all information to the right hand
side, i.e. from

set @spider_lc_<data_node_table_info_1>="<spider_node_info_chain_1>";
set @spider_lc_<data_node_table_info_2>="<spider_node_info_chain_2>";
...

to

set @spider_lc="<data_node_table_info_1>=<spider_node_origin_1> <data_node_table_info_2>=<spider_node_origin_2> ...";

where <spider_node_origin_1> is the info about origin spider node
table, as there's no need to encode the whole chain of reference.

It requires more involved encoding and decoding, and very long
variable value string (I assume there's no limit with the value
length?).

A cleaner way could be introducing a new spider system table with two
columns corresponding to the user var names and values. Alternatively,
add a column named "origin" to the existing system table named
mysql.spider_tables used for info about spider tables. But I'm not
sure what is the convention surrounding introducing new system tables
or updating system table schema to existing server versions.

To summarise, I can think of 3 options:

1. Introduce a new spider system table or add a column to
mysql.spider_tables to store the info
2. Same as 1., but only with fixversion set to the latest version
(11.3 at this moment)
3. Still use user var, but move all left hand side to the right hand side

What are your thoughts holyfoot, serg, ralf.gebhardt, elenst?

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