|
Also see MDEV-29605 SIGSEGV in spider_db_ping on INSERT
|
|
Another thing to consider disallowing is dual-referenced/multi-referenced/cross-referenced tables. i.e. one InnoDB table referenced by two or more Spider tables, or Spider tables cross referenced, or a Spider table referenced by two or more other Spider tables etc.
|
|
nayuta-yanagisawa, 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.
|
|
serg Thank you for the information. I'm wondering why the patch for MDEV-6268 has been pushed only to 10.5+. https://github.com/MariaDB/server/commit/23c8adda74935211ca8f8a50676cf4f94e9215fb
|
|
An additional longer testcase which also produces the exact same outcomes as the one in this comment (infinite loop, error 12719).
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);
|
CREATE TABLE t1 (a DATE) ENGINE=MEMORY;
|
CREATE TABLE t4 (c INT);
|
CREATE TABLE t2 (d INT,KEY(d)) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
|
CREATE TABLE t5 (id_product INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
|
INSERT INTO t2 VALUES();
|
CREATE TABLE t6 (a INT);
|
CREATE OR REPLACE TABLE t (a INT,b INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
|
SELECT * FROM t5;
|
FLUSH TABLES WITH READ LOCK;
|
UNLOCK TABLES;
|
INSERT INTO t1 VALUES (NULL),('foo'),('bar'),(NULL);
|
SELECT * FROM (t1 JOIN t2) NATURAL LEFT JOIN (t6 NATURAL JOIN t4);
|
|
|
> 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.
|
|
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
|
|
|
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
|
|
|
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.
|
|
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.
|