[MDEV-29583] Disallow Spider self/dual/multi/cross-referencing tables, consider not executing/skipping/disabling init_connect for Spider user - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Not a Bug
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Fix Version/s: 11.0.1
Component/s: Galera, Storage Engine - Spider
Labels:
- affects-tests

Description

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

CREATE USER Spider@localhost IDENTIFIED BY 'PWD1';

CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE 'test',user 'Spider',PASSWORD 'PWD1');

CREATE TABLE t (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';

SET GLOBAL init_connect="dummy";

SELECT c FROM t;

HANDLER t OPEN;

HANDLER t READ FIRST;

Produces some interesting results:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)
10.11.0-opt>source in.sql
Query OK, 0 rows affected, 1 warning (0.030 sec)

Query OK, 0 rows affected (0.007 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.410 sec)

Query OK, 0 rows affected (0.000 sec)

ERROR 12701 (HY000) at line 6 in file: 'in.sql': Remote MariaDB server has gone away
Query OK, 0 rows affected (0.000 sec)

ERROR 1184 (08S01) at line 8 in file: 'in.sql': Aborted connection 30 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
10.11.0-opt>

Or when not sourced/directly executed at the CLI:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)
...
10.11.0-opt>SELECT c FROM t;
ERROR 12701 (HY000): Remote MariaDB server has gone away
10.11.0-opt>HANDLER t OPEN;
Query OK, 0 rows affected (0.000 sec)

10.11.0-opt>HANDLER t READ FIRST;
ERROR 1184 (08S01): Aborted connection 30 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
10.11.0-opt>

Error log:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)
2022-09-21 11:16:45 0 [Note] /test/MD190922-mariadb-10.11.0-linux-x86_64-opt/bin/mysqld: ready for connections.
Version: '10.11.0-MariaDB' socket: '/test/MD190922-mariadb-10.11.0-linux-x86_64-opt/socket.sock' port: 11824 MariaDB Server
2022-09-21 11:16:50 25 [Warning] Aborted connection 25 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-09-21 11:16:50 25 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dummy' at line 1
2022-09-21 11:16:50 26 [Warning] Aborted connection 26 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-09-21 11:16:50 26 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dummy' at line 1
2022-09-21 11:16:50 27 [Warning] Aborted connection 27 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-09-21 11:16:50 27 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dummy' at line 1
2022-09-21 11:16:50 28 [Warning] Aborted connection 28 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-09-21 11:16:50 28 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dummy' at line 1
2022-09-21 11:16:50 29 [Warning] Aborted connection 29 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-09-21 11:16:50 29 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dummy' at line 1
2022-09-21 11:16:50 30 [Warning] Aborted connection 30 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-09-21 11:16:50 30 [Warning] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dummy' at line 1
2022-09-21 11:16:50 4 [ERROR] mysql_ha_read: Got error 1184 when reading table 't'
2022-09-21 11:16:50 4 [Warning] WSREP: handlerton rollback failed, thd 4 226 conf 0 SQL HANDLER t READ FIRST

While the eventual outcome looks understandable (init_connect command failed) the in-between outcomes seem to require some work;
1. The Remote MariaDB server has gone away is odd given the server has not gone away (and is reachable). This is likely due to #2, ref next item.
2. Self-referencing should not be allowed? (Spider t table referencing to itself)
3. The various 'Aborted connections'. This too is likely due to #2? Also see #6 in this regard.
4. 'init_connect' should likely not be allowed for the spider connection?
5. The final errors (mysql_ha_read/ WSREP) are likely due to #4.
6. It is odd that there are 5 attempts? If this is a hardcoded number of retries within Spider it may be best to improve this code to a single attempt with appropriate return values if it fails etc.?
7. More as a question to jplindst - why is WSREP involved in this HANDLER trx?

Attachments

Issue Links

relates to

MDEV-6268 SPIDER table with no COMMENT clause causes queries to wait forever

Closed

MDEV-30012 Triple thread hang in states 'NULL', 'NULL' and 'closing tables' on INSERT

Open

MDEV-30576 Include spider error codes in extra/perror, especially 12719, 12701

Open

MDEV-29605 SIGSEGV in spider_db_ping, ASAN heap-use-after-free in spider_db_ping and UBSAN dynamic-type-mismatch in spider_db_ping on CREATE TABLE

Closed

MDEV-29606 Assertion `transaction->implicit_xid.is_null()' failed in THD::init_for_queries and Assertion `transaction.is_empty()' failed in prepare_new_connection_state

Confirmed

MDEV-29676 Dual thread hang in 'closing tables' and 'Waiting for table metadata lock' on Spider CREATE OR REPLACE TABLE

Closed

MDEV-29800 ERROR 1062 (23000): Duplicate entry 'test-t-0' for key 'PRIMARY' on CoR for Spider table, DDL_LOG: Got error 1032 or 12524 when trying to execute action for entry 1/2/3/4/5/6/7 of type 'rename table'

Closed

MDEV-29854 SIGSEGV in spider_string::length on LOCK TABLES

Closed

MDEV-30011 SIGSEGV in spider_db_mbase::fin_loop_check on I_S SELECT

Open

split to

MDEV-30578 Multiple connections in spider when executing invalid init_command on spider table

Open

MDEV-30579 Confusing WSREP warning on HANDLER READ FIRST for spider table (possibly caused by zero-length self-referencing)

Open

MDEV-30580 Investigate whether self-/multi- referencing spider tables cause problems

Open

(4 relates to, 3 split to)

Activity

Ascending order - Click to sort in descending order

View 5 older comments

Nayuta Yanagisawa (Inactive) added a comment - 2022-10-04 15:21

> Spider can detect self-referencing (and, generally, circular referencing) tables, it was implemented in ~~MDEV-6268~~. Strange, that it didn't work in this case.

That is because, in the current implementation, the self-referencing check is done after Spider has successfully connected to a remote node. Due to the 'dummy' init_connect, Spider fails to connect to the data node, and the check is not executed.

Nayuta Yanagisawa (Inactive) added a comment - 2022-10-04 15:21 > Spider can detect self-referencing (and, generally, circular referencing) tables, it was implemented in MDEV-6268 . Strange, that it didn't work in this case. That is because, in the current implementation, the self-referencing check is done after Spider has successfully connected to a remote node. Due to the 'dummy' init_connect, Spider fails to connect to the data node, and the check is not executed.

Roel Van de Paar added a comment - 2022-10-15 07:43

Two additional testcases which use init_connect and the base table t is missing.

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

CREATE USER Spider@localhost IDENTIFIED BY 'PWD123';

CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock', DATABASE 'test', USER 'Spider', PASSWORD 'PWD123');

CREATE TABLE t1 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';

SET GLOBAL init_connect="DROP PROCEDURE IF EXISTS p1";

SELECT * FROM t1 WHERE c1 > 0 ORDER BY c1,c6 LIMIT 2;

LOCK TABLES t1 WRITE;

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

CREATE USER Spider@localhost IDENTIFIED BY 'PWD123';

CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock', DATABASE 'test', USER 'Spider', PASSWORD 'PWD123');

CREATE TABLE t1 (pk INT PRIMARY KEY, a INT) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';

SET GLOBAL init_connect='SET autocomit=0';

INSERT INTO t1 VALUES ('a',1);

LOCK TABLES t1 WRITE;

CREATE TABLE t2m (n INT PRIMARY KEY) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';

These lead to:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug)
2022-10-15 18:36:08 0 [Note] /test/MD190922-mariadb-10.11.0-linux-x86_64-dbg/bin/mysqld: ready for connections.
Version: '10.11.0-MariaDB-debug' socket: '/test/MD190922-mariadb-10.11.0-linux-x86_64-dbg/socket.sock' port: 11344 MariaDB Server
2022-10-15 18:36:13 25 [Warning] Aborted connection 25 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed) 2022-10-15 18:36:13 25 [Warning] Unknown system variable 'autocomit'
2022-10-15 18:36:13 26 [Warning] Aborted connection 26 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-10-15 18:36:13 26 [Warning] Unknown system variable 'autocomit'
2022-10-15 18:36:13 27 [Warning] Aborted connection 27 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-10-15 18:36:13 27 [Warning] Unknown system variable 'autocomit'
2022-10-15 18:36:13 28 [Warning] Aborted connection 28 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-10-15 18:36:13 28 [Warning] Unknown system variable 'autocomit'
2022-10-15 18:36:13 4 [Warning] WSREP: handlerton rollback failed, thd 4 223 conf 0 SQL LOCK TABLES t1 WRITE

Roel Van de Paar added a comment - 2022-10-15 07:43 Two additional testcases which use init_connect and the base table t is missing. INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; CREATE USER Spider@localhost IDENTIFIED BY 'PWD123' ; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock' , DATABASE 'test' , USER 'Spider' , PASSWORD 'PWD123' ); CREATE TABLE t1 (c INT ) ENGINE=Spider COMMENT= 'WRAPPER "mysql", srv "srv", TABLE "t"' ; SET GLOBAL init_connect= "DROP PROCEDURE IF EXISTS p1" ; SELECT * FROM t1 WHERE c1 > 0 ORDER BY c1,c6 LIMIT 2; LOCK TABLES t1 WRITE; INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; CREATE USER Spider@localhost IDENTIFIED BY 'PWD123' ; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock' , DATABASE 'test' , USER 'Spider' , PASSWORD 'PWD123' ); CREATE TABLE t1 (pk INT PRIMARY KEY , a INT ) ENGINE=Spider COMMENT= 'WRAPPER "mysql", srv "srv", TABLE "t"' ; SET GLOBAL init_connect= 'SET autocomit=0' ; INSERT INTO t1 VALUES ( 'a' ,1); LOCK TABLES t1 WRITE; CREATE TABLE t2m (n INT PRIMARY KEY ) ENGINE=Spider COMMENT= 'WRAPPER "mysql", srv "srv", TABLE "t"' ; These lead to: 10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug) 2022-10-15 18:36:08 0 [Note] /test/MD190922-mariadb-10.11.0-linux-x86_64-dbg/bin/mysqld: ready for connections. Version: '10.11.0-MariaDB-debug' socket: '/test/MD190922-mariadb-10.11.0-linux-x86_64-dbg/socket.sock' port: 11344 MariaDB Server 2022-10-15 18:36:13 25 [Warning] Aborted connection 25 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed) 2022-10-15 18:36:13 25 [Warning] Unknown system variable 'autocomit' 2022-10-15 18:36:13 26 [Warning] Aborted connection 26 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed) 2022-10-15 18:36:13 26 [Warning] Unknown system variable 'autocomit' 2022-10-15 18:36:13 27 [Warning] Aborted connection 27 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed) 2022-10-15 18:36:13 27 [Warning] Unknown system variable 'autocomit' 2022-10-15 18:36:13 28 [Warning] Aborted connection 28 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed) 2022-10-15 18:36:13 28 [Warning] Unknown system variable 'autocomit' 2022-10-15 18:36:13 4 [Warning] WSREP: handlerton rollback failed, thd 4 223 conf 0 SQL LOCK TABLES t1 WRITE

Roel Van de Paar added a comment - 2022-11-15 07:50 - edited

A self-referenced table, joined with failed init_connect and failing WSREP handlerton rollback failure:

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

CREATE USER Spider@localhost IDENTIFIED BY 'PWD0';

CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE 'test',user 'Spider',PASSWORD 'PWD0');

CREATE TABLE t (i INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';

SET GLOBAL init_connect="CALL p0 (@sum)";

HANDLER t OPEN;

INSERT INTO t VALUES (0,0),(0,0),(0,0);

HANDLER t READ FIRST;

This gives, in the error log ER_NEW_ABORTING_CONNECTION:

10.11.1 50c5743adc87e1cdec1431a02558f6540fe5a6d5 (Debug)
2022-11-15 18:39:41 0 [Note] /test/MD221022-mariadb-10.11.1-linux-x86_64-dbg/bin/mysqld: ready for connections.
Version: '10.11.1-MariaDB-debug' socket: '/test/MD221022-mariadb-10.11.1-linux-x86_64-dbg/socket.sock' port: 11202 MariaDB Server
2022-11-15 18:39:44 25 [Warning] Aborted connection 25 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-11-15 18:39:44 25 [Warning] No database selected
2022-11-15 18:39:44 26 [Warning] Aborted connection 26 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-11-15 18:39:44 26 [Warning] No database selected
2022-11-15 18:39:44 27 [Warning] Aborted connection 27 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-11-15 18:39:44 27 [Warning] No database selected
2022-11-15 18:39:44 28 [Warning] Aborted connection 28 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed)
2022-11-15 18:39:44 28 [Warning] No database selected
2022-11-15 18:39:44 4 [ERROR] mysql_ha_read: Got error 1184 when reading table 't'
2022-11-15 18:39:44 4 [Warning] WSREP: handlerton rollback failed, thd 4 224 conf 0 SQL HANDLER t READ FIRST

Roel Van de Paar added a comment - 2022-11-15 07:50 - edited A self-referenced table, joined with failed init_connect and failing WSREP handlerton rollback failure: INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; CREATE USER Spider@localhost IDENTIFIED BY 'PWD0' ; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock' , DATABASE 'test' , user 'Spider' , PASSWORD 'PWD0' ); CREATE TABLE t (i INT ) ENGINE=Spider COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; SET GLOBAL init_connect= "CALL p0 (@sum)" ; HANDLER t OPEN ; INSERT INTO t VALUES (0,0),(0,0),(0,0); HANDLER t READ FIRST ; This gives, in the error log ER_NEW_ABORTING_CONNECTION: 10.11.1 50c5743adc87e1cdec1431a02558f6540fe5a6d5 (Debug) 2022-11-15 18:39:41 0 [Note] /test/MD221022-mariadb-10.11.1-linux-x86_64-dbg/bin/mysqld: ready for connections. Version: '10.11.1-MariaDB-debug' socket: '/test/MD221022-mariadb-10.11.1-linux-x86_64-dbg/socket.sock' port: 11202 MariaDB Server 2022-11-15 18:39:44 25 [Warning] Aborted connection 25 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed) 2022-11-15 18:39:44 25 [Warning] No database selected 2022-11-15 18:39:44 26 [Warning] Aborted connection 26 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed) 2022-11-15 18:39:44 26 [Warning] No database selected 2022-11-15 18:39:44 27 [Warning] Aborted connection 27 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed) 2022-11-15 18:39:44 27 [Warning] No database selected 2022-11-15 18:39:44 28 [Warning] Aborted connection 28 to db: 'unconnected' user: 'Spider' host: 'localhost' (init_connect command failed) 2022-11-15 18:39:44 28 [Warning] No database selected 2022-11-15 18:39:44 4 [ERROR] mysql_ha_read: Got error 1184 when reading table 't' 2022-11-15 18:39:44 4 [Warning] WSREP: handlerton rollback failed, thd 4 224 conf 0 SQL HANDLER t READ FIRST

Yuchen Pei added a comment - 2023-02-01 00:08 - edited

One simple fix could be to do the self-referencing check in spider table creation rather than connection, and reject self-referencing create statements (error 12719). To that end, here's an mtr testcase (similar to self_reference.test but with the error placed earlier)

--echo #

--echo # MDEV-29583 Disallow Spider self/dual/multi/cross-referencing tables, consider not executing/skipping/disabling init_connect for Spider user

--echo #

--disable_query_log

--disable_result_log

--source ../../t/test_init.inc

--enable_result_log

--enable_query_log

--connection master_1

CREATE DATABASE auto_test_local;

USE auto_test_local;

SET spider_same_server_link= on;

#this should be when the error occurs

--error 12719

eval CREATE TABLE tbl_a (

    a INT

) $MASTER_1_ENGINE COMMENT='table "tbl_a", srv "s_1"';

#--error 12719 - this is currently when the error occurs

#SELECT a FROM tbl_a;

--connection master_1

DROP DATABASE IF EXISTS auto_test_local;

--disable_query_log

--disable_result_log

--source ../t/test_deinit.inc

--enable_query_log

--enable_result_log

This approach is similar to ~~MDEV-29562~~, but the self-referencing check ("loop check") implemented in commit 23c8adda749 is much more complicated than the charset check. BTW spider_db_mbase::set_loop_check() is not covered by any test, see the attachment in ~~MDEV-30437~~.

Hopefully this will fix all the problems that occur with self-referencing spider tables, including ~~MDEV-29676~~.

Will this fix the multi-referencing (e.g. t1 -> t <- t2) or cross-referencing (e.g. t1 -> t2 -> t1)? Depends on the implementation of loop check. But I think fixing just the self-referencing is sufficient for now.

Yuchen Pei added a comment - 2023-02-01 00:08 - edited One simple fix could be to do the self-referencing check in spider table creation rather than connection, and reject self-referencing create statements (error 12719). To that end, here's an mtr testcase (similar to self_reference.test but with the error placed earlier) --echo # --echo # MDEV-29583 Disallow Spider self/dual/multi/cross-referencing tables, consider not executing/skipping/disabling init_connect for Spider user --echo # --disable_query_log --disable_result_log --source ../../t/test_init.inc --enable_result_log --enable_query_log --connection master_1 CREATE DATABASE auto_test_local; USE auto_test_local; SET spider_same_server_link= on ; #this should be when the error occurs --error 12719 eval CREATE TABLE tbl_a ( a INT ) $MASTER_1_ENGINE COMMENT= 'table "tbl_a", srv "s_1"' ; # --error 12719 - this is currently when the error occurs # SELECT a FROM tbl_a; --connection master_1 DROP DATABASE IF EXISTS auto_test_local; --disable_query_log --disable_result_log --source ../t/test_deinit.inc --enable_query_log --enable_result_log This approach is similar to MDEV-29562 , but the self-referencing check ("loop check") implemented in commit 23c8adda749 is much more complicated than the charset check. BTW spider_db_mbase::set_loop_check() is not covered by any test, see the attachment in MDEV-30437 . Hopefully this will fix all the problems that occur with self-referencing spider tables, including MDEV-29676 . Will this fix the multi-referencing (e.g. t1 -> t <- t2) or cross-referencing (e.g. t1 -> t2 -> t1)? Depends on the implementation of loop check. But I think fixing just the self-referencing is sufficient for now.

Yuchen Pei added a comment - 2023-02-06 07:07

The problem in the ticket description is mainly caused by issuing an invalid init_command, causing the data node server to disconnect. The same error messages would occur if the spider table is not self-referencing.

OTOH, there are outstanding items raised in the ticket, and they have been splitted into their own tickets MDEV-30578, MDEV-30579 and MDEV-30580.

Yuchen Pei added a comment - 2023-02-06 07:07 The problem in the ticket description is mainly caused by issuing an invalid init_command, causing the data node server to disconnect. The same error messages would occur if the spider table is not self-referencing. OTOH, there are outstanding items raised in the ticket, and they have been splitted into their own tickets MDEV-30578 , MDEV-30579 and MDEV-30580 .

MariaDB Server

Disallow Spider self/dual/multi/cross-referencing tables, consider not executing/skipping/disabling init_connect for Spider user

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration