diff options
Diffstat (limited to 'storage/spider/mysql-test')
97 files changed, 1252 insertions, 228 deletions
diff --git a/storage/spider/mysql-test/spider/bugfix/disabled.def b/storage/spider/mysql-test/spider/bugfix/disabled.def index 3fd0a2d4..530b2825 100644 --- a/storage/spider/mysql-test/spider/bugfix/disabled.def +++ b/storage/spider/mysql-test/spider/bugfix/disabled.def @@ -1,3 +1,4 @@ wait_timeout : MDEV-26045 -mdev_29904 : MDEV-31101 mdev_27239: MDEV-32046 +mdev_27575 : MDEV-32997 +mdev_28739_simple : MDEV-33343 diff --git a/storage/spider/mysql-test/spider/bugfix/include/spider_table_sts_deinit.inc b/storage/spider/mysql-test/spider/bugfix/include/spider_table_sts_deinit.inc deleted file mode 100644 index 01645e85..00000000 --- a/storage/spider/mysql-test/spider/bugfix/include/spider_table_sts_deinit.inc +++ /dev/null @@ -1,12 +0,0 @@ ---connection master_1 -alter table mysql.spider_table_sts add column checksum bigint unsigned default null after update_time; -DROP DATABASE IF EXISTS auto_test_local; - ---let $MASTER_1_COMMENT_2_1= $MASTER_1_COMMENT_2_1_BACKUP ---disable_warnings ---disable_query_log ---disable_result_log ---source ../t/test_deinit.inc ---enable_result_log ---enable_query_log ---enable_warnings diff --git a/storage/spider/mysql-test/spider/bugfix/include/spider_table_sts_init.inc b/storage/spider/mysql-test/spider/bugfix/include/spider_table_sts_init.inc deleted file mode 100644 index 1e438812..00000000 --- a/storage/spider/mysql-test/spider/bugfix/include/spider_table_sts_init.inc +++ /dev/null @@ -1,13 +0,0 @@ ---disable_warnings ---disable_query_log ---disable_result_log ---source ../t/test_init.inc ---enable_result_log ---enable_query_log ---enable_warnings ---let $MASTER_1_COMMENT_2_1_BACKUP= $MASTER_1_COMMENT_2_1 -let $MASTER_1_COMMENT_2_1= - COMMENT='table "tbl_a", host "127.0.0.1", port "$MASTER_1_MYPORT", user "root"'; ---connection master_1 -alter table mysql.spider_table_sts drop column checksum; -insert into mysql.spider_table_sts values ('auto_test_local', 'tbl_a', 0, 0, 0, 0, 0, '2019-01-01 00:00:00', '2019-01-01 00:00:00', '2019-01-01 00:00:00'); diff --git a/storage/spider/mysql-test/spider/bugfix/r/gbh_outer_fields_in_join.result b/storage/spider/mysql-test/spider/bugfix/r/gbh_outer_fields_in_join.result new file mode 100644 index 00000000..75c2f2c2 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/gbh_outer_fields_in_join.result @@ -0,0 +1,50 @@ +# +# MDEV-26247 Spider: Valid LEFT JOIN results in ERROR 1064 +# +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +connection child2_1; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +CREATE TABLE t1 (a int) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE t2 (a int) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE t3 (a int) ENGINE=InnoDB DEFAULT CHARSET=utf8; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1), (2); +INSERT INTO t3 VALUES (1), (2), (3); +connection master_1; +CREATE DATABASE auto_test_local; +USE auto_test_local; +CREATE TABLE t1 (a int) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='srv "s_2_1", table "t1"'; +CREATE TABLE t2 (a int) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='srv "s_2_1", table "t2"'; +CREATE TABLE t3 (a int) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='srv "s_2_1", table "t3"'; +select * from t3 left join t1 on t3.a = t1.a left join t2 on t3.a = t2.a; +a a a +1 1 1 +2 NULL 2 +3 NULL NULL +select * from t1 left join t2 on t1.a = t2.a right join t3 on t3.a = t1.a; +a a a +1 1 1 +NULL NULL 2 +NULL NULL 3 +select * from t3 left join (t1 left join t2 on t1.a = t2.a) on t3.a = t1.a; +a a a +1 1 1 +2 NULL NULL +3 NULL NULL +drop table t1, t2, t3; +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_19866.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_19866.result index dbf0f54c..4386b4cb 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_19866.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_19866.result @@ -72,6 +72,7 @@ SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argum argument select `pkey`,`val` from `auto_test_remote`.`tbl_a` select `pkey`,`val` from `auto_test_remote`.`tbl_a` where `pkey` = 1 +select 1 from (select 1) t0 select `pkey`,`val` from `auto_test_remote`.`tbl_a` select `pkey`,`val` from `auto_test_remote`.`tbl_a` SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' @@ -85,6 +86,7 @@ argument select `pkey`,`val` from `auto_test_remote2`.`tbl_a` select `pkey`,`val` from `auto_test_remote2`.`tbl_a` select `pkey`,`val` from `auto_test_remote2`.`tbl_a` where `pkey` = 2 +select 1 from (select 1) t0 select `pkey`,`val` from `auto_test_remote2`.`tbl_a` SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT pkey, val FROM tbl_a ORDER BY pkey; diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_26151.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_26151.result index b0a430e0..326b84a0 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_26151.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_26151.result @@ -6,6 +6,9 @@ for child2 for child3 set @old_spider_bgs_mode= @@spider_bgs_mode; set session spider_bgs_mode=1; +set spider_same_server_link=1; +set @old_spider_same_server_link=@@global.spider_same_server_link; +set global spider_same_server_link=1; CREATE SERVER $srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); create table td (a int, PRIMARY KEY (a)); create table ts (a int, PRIMARY KEY (a)) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv_mdev_26151",TABLE "td", casual_read "3"'; @@ -26,6 +29,7 @@ min(a) drop table td, ts; drop server srv_mdev_26151; set session spider_bgs_mode=@old_spider_bgs_mode; +set global spider_same_server_link=@old_spider_same_server_link; for master_1 for child2 for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_26247.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_26247.result index 723a81a9..6eddf9a7 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_26247.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_26247.result @@ -28,8 +28,6 @@ INSERT INTO t1 VALUES (1), (2), (3); INSERT INTO t2 VALUES (1, 11), (2, 22), (3, 33); INSERT INTO t3 VALUES (1, 111), (2, 222), (3, 333); connection master_1; -set @old_spider_disable_group_by_handler=@@spider_disable_group_by_handler; -set spider_disable_group_by_handler=1; CREATE DATABASE auto_test_local; USE auto_test_local; CREATE TABLE t1 ( @@ -82,7 +80,6 @@ a22 22 connection master_1; DROP DATABASE IF EXISTS auto_test_local; -set spider_disable_group_by_handler=@old_spider_disable_group_by_handler; connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; for master_1 @@ -91,6 +88,3 @@ child2_1 child2_2 child2_3 for child3 -# -# end of test mdev_26247 -# diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_26541.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_26541.result index 72921d2e..35a9d916 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_26541.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_26541.result @@ -2,18 +2,3 @@ # MDEV-26541 Undefined symbol: _ZTI12ha_partition when attempting to use ha_spider.so in UBSAN builds # INSTALL SONAME 'ha_spider.so'; -DROP FUNCTION spider_flush_table_mon_cache; -DROP FUNCTION spider_copy_tables; -DROP FUNCTION spider_ping_table; -DROP FUNCTION spider_bg_direct_sql; -DROP FUNCTION spider_direct_sql; -UNINSTALL SONAME IF EXISTS "ha_spider"; -DROP TABLE IF EXISTS mysql.spider_xa; -DROP TABLE IF EXISTS mysql.spider_xa_member; -DROP TABLE IF EXISTS mysql.spider_xa_failed_log; -DROP TABLE IF EXISTS mysql.spider_tables; -DROP TABLE IF EXISTS mysql.spider_link_mon_servers; -DROP TABLE IF EXISTS mysql.spider_link_failed_log; -DROP TABLE IF EXISTS mysql.spider_table_position_for_recovery; -DROP TABLE IF EXISTS mysql.spider_table_sts; -DROP TABLE IF EXISTS mysql.spider_table_crd; diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_27575.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_27575.result new file mode 100644 index 00000000..3b137eaa --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_27575.result @@ -0,0 +1,17 @@ +# +# MDEV-27575 Spider: UBSAN member access within null pointer of type 'struct st_plugin_int and SIGSEGV in intern_plugin_lock on SHUTDOWN when setting Spider as default storage engine (temporary or global) +# +for master_1 +for child2 +for child3 +SET GLOBAL default_tmp_storage_engine=spider; +ERROR HY000: Table storage engine 'SPIDER' does not support the create option 'TEMPORARY' +# restart +SET GLOBAL default_storage_engine=Spider; +# restart +for master_1 +for child2 +for child3 +# +# end of test mdev_27575 +# diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_28683.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_28683.result new file mode 100644 index 00000000..358c794e --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_28683.result @@ -0,0 +1,22 @@ +# +# MDEV-28683 Spider: SIGSEGV in spider_db_direct_delete, SIGSEGV in spider_db_connect, ASAN: heap-use-after-free in spider_db_direct_delete +# +for master_1 +for child2 +for child3 +CREATE TABLE t (c INT) ENGINE=Spider; +SELECT * FROM t; +ERROR HY000: Unable to connect to foreign data source: localhost +INSERT INTO t (SELECT 1 FROM t); +ERROR HY000: Unable to connect to foreign data source: localhost +LOCK TABLES t WRITE CONCURRENT; +DELETE FROM t; +ERROR HY000: Unable to connect to foreign data source: localhost +UNLOCK TABLES; +DROP TABLE t; +for master_1 +for child2 +for child3 +# +# end of test mdev_28683 +# diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_28739.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_28739.result new file mode 100644 index 00000000..ee497bb3 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_28739.result @@ -0,0 +1,34 @@ +# +# MDEV-28739 Trying to lock uninitialized mutex or hang upon shutdown after using Spider with query_cache +# +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +connection child2_1; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +CREATE TABLE tbl_a (id INT); +connection master_1; +CREATE DATABASE auto_test_local; +USE auto_test_local; +set global query_cache_type= on; +set spider_same_server_link = on; +CREATE TABLE tbl_a ( +id INT +) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='table "tbl_a", srv "s_2_1"'; +SELECT * FROM tbl_a; +id +# restart +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_28739_simple.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_28739_simple.result new file mode 100644 index 00000000..1c337c3d --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_28739_simple.result @@ -0,0 +1,21 @@ +# +# MDEV-28739 Trying to lock uninitialized mutex or hang upon shutdown after using Spider with query_cache +# +for master_1 +for child2 +for child3 +set global query_cache_type= on; +set spider_same_server_link=1; +CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +create table t2 (c int); +create table t1 (c int) ENGINE=Spider +COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"'; +SELECT * FROM t1; +c +# restart +drop table t1, t2; +drop server srv; +for master_1 +for child2 +for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_28856.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_28856.result index fae3cc6b..7e4fd3cd 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_28856.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_28856.result @@ -4,6 +4,7 @@ for master_1 for child2 for child3 +set spider_same_server_link=1; CREATE SERVER srv FOREIGN DATA WRAPPER mysql OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); # testing monitoring_* diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_28998.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_28998.result index d664b090..e92fb199 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_28998.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_28998.result @@ -4,8 +4,7 @@ for master_1 for child2 for child3 -set @old_spider_disable_group_by_handler=@@spider_disable_group_by_handler; -set spider_disable_group_by_handler=1; +set spider_same_server_link=1; CREATE SERVER s FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); @@ -17,7 +16,6 @@ a 2 DROP TABLE t2, t1_SPIDER, t1; drop server s; -set spider_disable_group_by_handler=@old_spider_disable_group_by_handler; for master_1 for child2 for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_29002.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_29002.result new file mode 100644 index 00000000..894f51c5 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_29002.result @@ -0,0 +1,34 @@ +for master_1 +for child2 +for child3 +SET spider_same_server_link= on; +CREATE SERVER s FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +CREATE TABLE t (a INT); +CREATE TABLE t1_spider (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'"; +CREATE TABLE t2_spider (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'"; +SELECT * FROM t1_spider, t2_spider; +a a +SELECT table_name, index_name, cardinality FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name IN ('t1_spider','t2_spider'); +table_name index_name cardinality +RENAME TABLE t1_spider TO t3_spider; +SELECT * FROM t3_spider; +a +DROP TABLE t3_spider, t2_spider, t; +drop server s; +CREATE TABLE t1 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",HOST "srv",TABLE "t"'; +CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",HOST "srv",TABLE "t"'; +CREATE TABLE t3 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",HOST "srv",TABLE "t"'; +SHOW TABLE STATUS; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary +t1 SPIDER 10 NULL 0 0 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL Unable to connect to foreign data source: srv 0 +t2 SPIDER 10 NULL 0 0 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL Unable to connect to foreign data source: srv 0 +t3 SPIDER 10 NULL 0 0 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL Unable to connect to foreign data source: srv 0 +Warnings: +Warning 1429 Unable to connect to foreign data source: srv +Warning 1429 Unable to connect to foreign data source: srv +Warning 1429 Unable to connect to foreign data source: srv +drop table t1, t2, t3; +for master_1 +for child2 +for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_29163.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_29163.result index 96d8a729..f58ab605 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_29163.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_29163.result @@ -4,8 +4,7 @@ for master_1 for child2 for child3 -set @old_spider_disable_group_by_handler=@@spider_disable_group_by_handler; -set spider_disable_group_by_handler=1; +set spider_same_server_link=1; CREATE SERVER s FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); CREATE TABLE t1 (a INT); CREATE TABLE t2 (b INT); @@ -17,7 +16,6 @@ SELECT t1_spider.* FROM t1_spider LEFT JOIN t2_spider LEFT JOIN t3_spider ON b = a DROP TABLE t1_spider, t2_spider, t3_spider, t1, t2, t3; drop server s; -set spider_disable_group_by_handler=@old_spider_disable_group_by_handler; for master_1 for child2 for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_29421.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_29421.result new file mode 100644 index 00000000..bcbf050d --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_29421.result @@ -0,0 +1,19 @@ +for master_1 +for child2 +for child3 +set @old_table_open_cache=@@global.table_open_cache; +set global table_open_cache=10; +CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +CREATE TABLE t (c INT) ENGINE=InnoDB; +CREATE TABLE t_s (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"'; +CREATE TABLE t1 (a INT) ENGINE=Spider; +SELECT * FROM t1; +ERROR HY000: Unable to connect to foreign data source: localhost +SELECT * FROM information_schema.tables; +DROP TABLE t, t_s, t1; +drop server srv; +set global table_open_cache=@old_table_open_cache; +for master_1 +for child2 +for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_29456.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_29456.result new file mode 100644 index 00000000..365c3d63 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_29456.result @@ -0,0 +1,42 @@ +# +# MDEV-29456 Spider hangs in 'Waiting for table metadata lock' state on CREATE TABLE after LOCK TABLES +# +for master_1 +for child2 +for child3 +set spider_same_server_link=1; +CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +create table t1 (c int); +create table t2 (c int) ENGINE=Spider +COMMENT='WRAPPER "mysql", srv "srv",TABLE "t1"'; +CREATE TABLE t3 (c INT KEY) ENGINE=Spider; +LOCK TABLE t2 WRITE; +LOCK TABLE t3 WRITE; +ERROR HY000: Unable to connect to foreign data source: localhost +UNLOCK TABLES; +drop table t1, t2, t3; +CREATE TABLE t (c INT) ENGINE=InnoDB; +CREATE TABLE t1 (c INT) ENGINE=Spider; +CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; +LOCK TABLES t2 WRITE; +LOCK TABLES t1 READ; +ERROR HY000: Unable to connect to foreign data source: localhost +CREATE TABLE t (c INT) ENGINE=Spider; +ERROR 42S01: Table 't' already exists +drop table t, t1, t2; +CREATE TABLE t (c INT) ENGINE=InnoDB; +CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; +CREATE TABLE t3 (c INT) ENGINE=InnoDB; +LOCK TABLES t2 WRITE; +LOCK TABLES mysql.proc WRITE,mysql.user WRITE; +ERROR HY000: You can't combine write-locking of system tables with other tables or lock types +INSERT INTO t3 SELECT * FROM t; +drop table t, t2, t3; +drop server srv; +for master_1 +for child2 +for child3 +# +# end of test mdev_29456 +# diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_29502.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_29502.result index 54c5e9a6..7749898e 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_29502.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_29502.result @@ -4,6 +4,7 @@ for master_1 for child2 for child3 +set spider_same_server_link=1; CREATE SERVER $srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); CREATE TABLE t (a INT); INSERT INTO t VALUES (23),(48); diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_29667.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_29667.result new file mode 100644 index 00000000..f2e90760 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_29667.result @@ -0,0 +1,40 @@ +# +# MDEV-29667 Server hangs on DROP DATABASE after failing LOCK TABLES on Spider table +# +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +connection child2_1; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +CREATE TABLE tbl_a ( +a INT +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +connection master_1; +CREATE DATABASE auto_test_local; +USE auto_test_local; +CREATE TABLE tbl_a ( +a INT +) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='table "tbl_a", srv "s_2_1"'; +CREATE TABLE tbl_b ( +a INT +) ENGINE=Spider DEFAULT CHARSET=utf8; +LOCK TABLES tbl_a WRITE; +LOCK TABLES tbl_b READ, tbl_a READ; +ERROR HY000: Unable to connect to foreign data source: localhost +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +# +# end of test mdev_29667 +# diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_29963.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_29963.result new file mode 100644 index 00000000..60451596 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_29963.result @@ -0,0 +1,44 @@ +# +# MDEV-29963 SIGSEGV in spider_db_mbase::append_lock_tables on LOCK TABLES +# +for master_1 +for child2 +for child3 +set spider_same_server_link=1; +CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +CREATE TABLE t (a INT) ENGINE=Spider; +CREATE TABLE t2 (b INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; +LOCK TABLES t AS a READ,t2 AS b LOW_PRIORITY WRITE,t2 AS c WRITE; +ERROR HY000: Unable to connect to foreign data source: localhost +DROP TABLE t2; +CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; +LOCK TABLES t2 WRITE; +ERROR HY000: Unable to connect to foreign data source: localhost +DROP TABLE t2,t; +CREATE TABLE t (a INT); +CREATE TABLE t1 (a INT) ENGINE=Spider; +CREATE TABLE t2 (b INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; +LOCK TABLES t1 READ, t2 WRITE; +ERROR HY000: Unable to connect to foreign data source: localhost +DROP TABLE t2; +CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; +LOCK TABLES t2 WRITE; +UNLOCK TABLES; +DROP TABLE t, t1, t2; +CREATE TABLE t1 (c INT) ENGINE=Spider; +CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT="WRAPPER 'mysql',srv 'srv',TABLE 't1'"; +LOCK TABLES t1 WRITE,t2 WRITE; +ERROR HY000: Unable to connect to foreign data source: localhost +TRUNCATE t2; +ERROR HY000: Unable to connect to foreign data source: localhost +LOCK TABLES t2 AS o WRITE; +ERROR HY000: Unable to connect to foreign data source: localhost +drop table t1, t2; +drop server srv; +for master_1 +for child2 +for child3 +# +# end of test mdev_29963 +# diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_30014.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_30014.result new file mode 100644 index 00000000..e971f323 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_30014.result @@ -0,0 +1,37 @@ +# +# MDEV-30014 heap-use-after-free in ha_spider::lock_tables(), highly sporadic SIGSEGV in intern_close_table +# +for master_1 +for child2 +for child3 +set spider_same_server_link=1; +CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +create table t1 (c int); +create table t2 (c int) ENGINE=Spider +COMMENT='WRAPPER "mysql", srv "srv",TABLE "t1"'; +CREATE TABLE t3 (c INT KEY) ENGINE=Spider; +LOCK TABLE t2 WRITE,t3 WRITE; +ERROR HY000: Unable to connect to foreign data source: localhost +CREATE TABLE t4 (c INT) ENGINE=Spider; +FLUSH NO_WRITE_TO_BINLOG TABLES t4 WITH READ LOCK; +Warnings: +Error 1429 Unable to connect to foreign data source: localhost +Error 1429 Unable to connect to foreign data source: localhost +UNLOCK TABLES; +drop table t1, t2, t3, t4; +create table t1 (c int); +create table t2 (c int) ENGINE=Spider +COMMENT='WRAPPER "mysql", srv "srv",TABLE "t1"'; +CREATE TABLE t3 (c INT KEY) ENGINE=Spider; +LOCK TABLE t2 WRITE, t3 WRITE; +ERROR HY000: Unable to connect to foreign data source: localhost +UNLOCK TABLES; +drop table t1, t2, t3; +drop server srv; +for master_1 +for child2 +for child3 +# +# end of test mdev_30014 +# diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_30170.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_30170.result new file mode 100644 index 00000000..2183447b --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_30170.result @@ -0,0 +1,7 @@ +install soname 'ha_spider'; +DROP TABLE non_existing_table; +ERROR 42S02: Unknown table 'test.non_existing_table' +create or replace table non_existing_table (c int) engine=Spider; +drop table non_existing_table; +Warnings: +Warning 1620 Plugin is busy and will be uninstalled on shutdown diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_30392.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_30392.result index 13297e10..cefa5248 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_30392.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_30392.result @@ -4,8 +4,7 @@ for master_1 for child2 for child3 -set @old_spider_disable_group_by_handler=@@spider_disable_group_by_handler; -set spider_disable_group_by_handler=1; +set spider_same_server_link=1; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); @@ -16,7 +15,6 @@ a 2 DROP TABLE t1, t2; DROP SERVER srv; -set spider_disable_group_by_handler=@old_spider_disable_group_by_handler; for master_1 for child2 for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_31338.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_31338.result index 62b06336..f156cf38 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_31338.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_31338.result @@ -4,6 +4,7 @@ for master_1 for child2 for child3 +set spider_same_server_link=1; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); CREATE TABLE t (c BLOB) ENGINE=InnoDB; CREATE TABLE ts (c BLOB) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_31524.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_31524.result index c1aa7a1f..26877967 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_31524.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_31524.result @@ -5,6 +5,7 @@ for master_1 for child2 for child3 SET @old_spider_read_only_mode = @@session.spider_read_only_mode; +set spider_same_server_link=1; CREATE SERVER $srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); set session spider_read_only_mode = default; create table t2 (c int); diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_31645.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_31645.result index 28cb546e..5197abd3 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_31645.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_31645.result @@ -4,22 +4,17 @@ for master_1 for child2 for child3 -set @old_spider_disable_group_by_handler=@@spider_disable_group_by_handler; -set spider_disable_group_by_handler=1; +set spider_same_server_link=1; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); CREATE TABLE t1 ( a bigint(20) NOT NULL, b bigint(20) DEFAULT 0, PRIMARY KEY (a)); CREATE TABLE t2 ( a bigint(20) NOT NULL, b bigint(20) DEFAULT 0, PRIMARY KEY (a)) ENGINE=SPIDER COMMENT='srv "srv", WRAPPER "mysql", TABLE "t1"'; -SET SESSION optimizer_switch='semijoin=off'; -SELECT * FROM t2 -WHERE A BETWEEN 0 AND 10 AND B IN(SELECT B FROM t2 WHERE A BETWEEN 11 AND 20); +SELECT * FROM t2 WHERE b IN (SELECT b FROM t2 WHERE a > 10); a b -SET SESSION optimizer_switch='semijoin=on'; SELECT * FROM t2 WHERE A BETWEEN 0 AND 10 AND B IN(SELECT B FROM t2 WHERE A BETWEEN 11 AND 20); a b drop table t1, t2; drop server srv; -set spider_disable_group_by_handler=@old_spider_disable_group_by_handler; for master_1 for child2 for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_31996.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_31996.result index 04d7e884..cbc91432 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/mdev_31996.result +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_31996.result @@ -1,6 +1,7 @@ for master_1 for child2 for child3 +set spider_same_server_link=1; CREATE SERVER srv FOREIGN DATA WRAPPER mysql OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); set session spider_delete_all_rows_type=0; diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_32753.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_32753.result new file mode 100644 index 00000000..4260d80f --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_32753.result @@ -0,0 +1,10 @@ +# +# MDEV-32753 Spider engine does not load in ORACLE mode +# +select * from mysql.plugin; +name dl +create table t (c int) Engine=SPIDER; +drop table t; +# +# end of test mdev_32753 +# diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_32753_after_start.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_32753_after_start.result new file mode 100644 index 00000000..4e046d16 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_32753_after_start.result @@ -0,0 +1,14 @@ +# +# MDEV-32753 Spider engine does not load in ORACLE mode +# +install soname 'ha_spider'; +select * from mysql.plugin; +name dl +SPIDER ha_spider.so +SPIDER_ALLOC_MEM ha_spider.so +SPIDER_WRAPPER_PROTOCOLS ha_spider.so +create table t (c int) Engine=SPIDER; +drop table t; +# +# end of test mdev_32753_after_start +# diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_32753_after_start_session.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_32753_after_start_session.result new file mode 100644 index 00000000..b9d02563 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_32753_after_start_session.result @@ -0,0 +1,17 @@ +# +# MDEV-32753 Spider engine does not load in ORACLE mode +# +set @old_sql_mode=@@sql_mode; +SET @@sql_mode = CONCAT(@@sql_mode, ',ORACLE'); +install soname 'ha_spider'; +select * from mysql.plugin; +name dl +SPIDER ha_spider.so +SPIDER_ALLOC_MEM ha_spider.so +SPIDER_WRAPPER_PROTOCOLS ha_spider.so +create table t (c int) Engine=SPIDER; +drop table t; +set sql_mode=@old_sql_mode; +# +# end of test mdev_32753_after_start +# diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_32986.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_32986.result new file mode 100644 index 00000000..c3bdef98 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_32986.result @@ -0,0 +1,24 @@ +# +# MDEV-32907 +# +for master_1 +for child2 +for child3 +set spider_same_server_link=1; +CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +create table t2 (c varchar(16)); +create table t1 (c varchar(16)) ENGINE=Spider +COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"'; +insert into t1 values ('TestSpiderRegex'); +select c from t1 where c regexp '(Test|Spider|Regex)'; +c +TestSpiderRegex +drop table t1, t2; +drop server srv; +for master_1 +for child2 +for child3 +# +# end of test mdev_32907 +# diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_33008.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_33008.result new file mode 100644 index 00000000..3bcb4bb0 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_33008.result @@ -0,0 +1,25 @@ +for master_1 +for child2 +for child3 +set spider_same_server_link=on; +CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +create table t2 ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`code` varchar(10) DEFAULT NULL, +PRIMARY KEY (`id`) +); +create table t1 ENGINE=Spider +COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"'; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `code` varchar(10) DEFAULT NULL, + PRIMARY KEY (`id`) +) ENGINE=SPIDER DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"' +drop table t1, t2; +drop server srv; +for master_1 +for child2 +for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_33191.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_33191.result new file mode 100644 index 00000000..75ca6c2f --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_33191.result @@ -0,0 +1,14 @@ +INSTALL SONAME 'ha_spider'; +set spider_same_server_link=on; +CREATE TABLE t2(c INT); +CREATE TABLE t1(c INT) ENGINE=Spider COMMENT='socket "$SOCKET", user "root", table "t2 t3"'; +Warnings: +Warning 138 The high availability feature of Spider has been deprecated and will be removed in a future release +ALTER TABLE t1 ENGINE=Spider; +Warnings: +Warning 138 The high availability feature of Spider has been deprecated and will be removed in a future release +TRUNCATE TABLE t1; +ERROR 42S02: Table 'test.t3' doesn't exist +drop table t1, t2; +Warnings: +Warning 1620 Plugin is busy and will be uninstalled on shutdown diff --git a/storage/spider/mysql-test/spider/bugfix/r/perfschema.result b/storage/spider/mysql-test/spider/bugfix/r/perfschema.result new file mode 100644 index 00000000..9ce2e38f --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/perfschema.result @@ -0,0 +1,11 @@ +# +# MDEV-33031 Assertion failure upon reading from performance schema with binlog enabled +# +connect foo,localhost,root; +select variable_name, variable_value from performance_schema.status_by_thread +where variable_name like '%spider_direct_aggregate%'; +variable_name variable_value +Spider_direct_aggregate 0 +Spider_direct_aggregate 0 +disconnect foo; +connection default; diff --git a/storage/spider/mysql-test/spider/bugfix/r/self_reference_multi.result b/storage/spider/mysql-test/spider/bugfix/r/self_reference_multi.result index 196e48d5..0118f575 100644 --- a/storage/spider/mysql-test/spider/bugfix/r/self_reference_multi.result +++ b/storage/spider/mysql-test/spider/bugfix/r/self_reference_multi.result @@ -4,6 +4,9 @@ for child3 MDEV-6268 SPIDER table with no COMMENT clause causes queries to wait forever +set spider_same_server_link=1; +set @old_spider_same_server_link=@@global.spider_same_server_link; +set global spider_same_server_link=1; CREATE SERVER $srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); create table t2 (c int); create table t1 (c int) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv_self_reference_multi",TABLE "t2"'; @@ -17,6 +20,7 @@ select * from t2; ERROR HY000: An infinite loop is detected when opening table test.t0 drop table t0, t1, t2; drop server srv_self_reference_multi; +set global spider_same_server_link=@old_spider_same_server_link; for master_1 for child2 for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/r/signal_ddl_fail.result b/storage/spider/mysql-test/spider/bugfix/r/signal_ddl_fail.result new file mode 100644 index 00000000..c86e600b --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/signal_ddl_fail.result @@ -0,0 +1,8 @@ +# +# MDEV-32559 Move alter table statements in spider init queries to be executed in the signal_ddl_recovery_done callback +# +select * from mysql.plugin; +name dl +# +# end of test signal_ddl_fail +# diff --git a/storage/spider/mysql-test/spider/bugfix/r/spider_join_with_non_spider.result b/storage/spider/mysql-test/spider/bugfix/r/spider_join_with_non_spider.result new file mode 100644 index 00000000..420ca657 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/spider_join_with_non_spider.result @@ -0,0 +1,24 @@ +# +# Test joining a spider table with a non-spider table +# +for master_1 +for child2 +for child3 +set spider_same_server_link=1; +CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +create table t1 (c int); +create table t2 (d int); +insert into t2 values (1), (2); +create table t3 (c int) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t1"'; +insert into t3 values (2), (3); +select c from t3 join t2 on c = d; +c +2 +drop table t1, t2, t3; +drop server srv; +for master_1 +for child2 +for child3 +# +# end of test spider_join_with_non_spider +# diff --git a/storage/spider/mysql-test/spider/bugfix/r/spider_table_sts.result b/storage/spider/mysql-test/spider/bugfix/r/spider_table_sts.result deleted file mode 100644 index f915cc95..00000000 --- a/storage/spider/mysql-test/spider/bugfix/r/spider_table_sts.result +++ /dev/null @@ -1,38 +0,0 @@ -for master_1 -for child2 -for child3 -connection master_1; -alter table mysql.spider_table_sts drop column checksum; -insert into mysql.spider_table_sts values ('auto_test_local', 'tbl_a', 0, 0, 0, 0, 0, '2019-01-01 00:00:00', '2019-01-01 00:00:00', '2019-01-01 00:00:00'); - -this test is for MDEV-19842 - -drop and create databases -connection master_1; -CREATE DATABASE auto_test_local; -USE auto_test_local; - -create table -connection master_1; -CREATE TABLE tbl_a ( -pkey int NOT NULL, -PRIMARY KEY (pkey) -) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1 - -select test 1 -connection master_1; -SELECT pkey FROM tbl_a; -ERROR HY000: System table spider_table_sts is different version - -deinit -connection master_1; -DROP DATABASE IF EXISTS auto_test_local; -ERROR HY000: System table spider_table_sts is different version -connection master_1; -alter table mysql.spider_table_sts add column checksum bigint unsigned default null after update_time; -DROP DATABASE IF EXISTS auto_test_local; -for master_1 -for child2 -for child3 - -end of test diff --git a/storage/spider/mysql-test/spider/bugfix/r/subquery.result b/storage/spider/mysql-test/spider/bugfix/r/subquery.result new file mode 100644 index 00000000..280f5715 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/subquery.result @@ -0,0 +1,25 @@ +# +# Test spider select with subqueries +# +for master_1 +for child2 +for child3 +set spider_same_server_link=1; +CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +create table t1 (c1 int); +create table t2 (c2 int); +insert into t1 values (1), (2); +insert into t2 values (0), (1), (2); +create table t1s (c1 int) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t1"'; +create table t2s (c2 int) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"'; +select c1 from t1s, (select c2 from t2s where c2 > 0) t where c1 + 1 = c2; +c1 +1 +drop table t1, t2, t1s, t2s; +drop server srv; +for master_1 +for child2 +for child3 +# +# end of test subquery +# diff --git a/storage/spider/mysql-test/spider/bugfix/t/spider_table_sts.cnf b/storage/spider/mysql-test/spider/bugfix/t/gbh_outer_fields_in_join.cnf index b0853e32..05dfd8a0 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/spider_table_sts.cnf +++ b/storage/spider/mysql-test/spider/bugfix/t/gbh_outer_fields_in_join.cnf @@ -1,2 +1,3 @@ !include include/default_mysqld.cnf !include ../my_1_1.cnf +!include ../my_2_1.cnf diff --git a/storage/spider/mysql-test/spider/bugfix/t/gbh_outer_fields_in_join.test b/storage/spider/mysql-test/spider/bugfix/t/gbh_outer_fields_in_join.test new file mode 100644 index 00000000..f60abc30 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/gbh_outer_fields_in_join.test @@ -0,0 +1,46 @@ +--echo # +--echo # MDEV-26247 Spider: Valid LEFT JOIN results in ERROR 1064 +--echo # + +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +--connection child2_1 +CREATE DATABASE auto_test_remote; +USE auto_test_remote; + +eval CREATE TABLE t1 (a int) $CHILD2_1_ENGINE $CHILD2_1_CHARSET; +eval CREATE TABLE t2 (a int) $CHILD2_1_ENGINE $CHILD2_1_CHARSET; +eval CREATE TABLE t3 (a int) $CHILD2_1_ENGINE $CHILD2_1_CHARSET; + +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1), (2); +INSERT INTO t3 VALUES (1), (2), (3); + +--connection master_1 +CREATE DATABASE auto_test_local; +USE auto_test_local; + +eval CREATE TABLE t1 (a int) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='srv "s_2_1", table "t1"'; +eval CREATE TABLE t2 (a int) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='srv "s_2_1", table "t2"'; +eval CREATE TABLE t3 (a int) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='srv "s_2_1", table "t3"'; + +select * from t3 left join t1 on t3.a = t1.a left join t2 on t3.a = t2.a; +select * from t1 left join t2 on t1.a = t2.a right join t3 on t3.a = t1.a; +select * from t3 left join (t1 left join t2 on t1.a = t2.a) on t3.a = t1.a; + +drop table t1, t2, t3; + +--connection master_1 +DROP DATABASE IF EXISTS auto_test_local; +--connection child2_1 +DROP DATABASE IF EXISTS auto_test_remote; + +--disable_query_log +--disable_result_log +--source ../t/test_deinit.inc +--enable_query_log +--enable_result_log diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_26151.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_26151.test index f9e157d3..dcf1438f 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_26151.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_26151.test @@ -14,6 +14,10 @@ --let $srv=srv_mdev_26151 set @old_spider_bgs_mode= @@spider_bgs_mode; set session spider_bgs_mode=1; +set spider_same_server_link=1; +set @old_spider_same_server_link=@@global.spider_same_server_link; +set global spider_same_server_link=1; + evalp CREATE SERVER $srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); # casual_read != 0 && casual_read != 1 @@ -42,6 +46,7 @@ drop table td, ts; eval drop server $srv; set session spider_bgs_mode=@old_spider_bgs_mode; +set global spider_same_server_link=@old_spider_same_server_link; --disable_query_log --disable_result_log diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.test index 8d27c6e0..80328e05 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_26247.test @@ -7,6 +7,7 @@ --source ../../t/test_init.inc --enable_result_log --enable_query_log + --connection child2_1 CREATE DATABASE auto_test_remote; USE auto_test_remote; @@ -33,10 +34,6 @@ INSERT INTO t2 VALUES (1, 11), (2, 22), (3, 33); INSERT INTO t3 VALUES (1, 111), (2, 222), (3, 333); --connection master_1 -#FIXME: this is a workaround. -set @old_spider_disable_group_by_handler=@@spider_disable_group_by_handler; -set spider_disable_group_by_handler=1; - CREATE DATABASE auto_test_local; USE auto_test_local; @@ -71,7 +68,6 @@ SELECT a22 FROM t1 RIGHT JOIN t2 ON a21 = a11 WHERE a11 IN (1,2); --connection master_1 DROP DATABASE IF EXISTS auto_test_local; -set spider_disable_group_by_handler=@old_spider_disable_group_by_handler; --connection child2_1 DROP DATABASE IF EXISTS auto_test_remote; @@ -80,6 +76,3 @@ DROP DATABASE IF EXISTS auto_test_remote; --source ../t/test_deinit.inc --enable_query_log --enable_result_log ---echo # ---echo # end of test mdev_26247 ---echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_26541.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_26541.test index bf6cb255..add5f621 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_26541.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_26541.test @@ -2,10 +2,7 @@ --echo # MDEV-26541 Undefined symbol: _ZTI12ha_partition when attempting to use ha_spider.so in UBSAN builds --echo # -if (`select not(count(*)) from information_schema.system_variables where variable_name='have_sanitizer' and global_value like "%UBSAN%"`) -{ ---skip test needs to be run with UBSAN -} +# this test should be checked with ubsan # init spider @@ -20,4 +17,5 @@ while (!$PLUGIN_EXIST) `SELECT COUNT(*) FROM mysql.func WHERE name = '$PLUGIN_NAME'`; } +--disable_query_log --source ../../include/clean_up_spider.inc diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_27575.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_27575.test new file mode 100644 index 00000000..79a08489 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_27575.test @@ -0,0 +1,23 @@ +--echo # +--echo # MDEV-27575 Spider: UBSAN member access within null pointer of type 'struct st_plugin_int and SIGSEGV in intern_plugin_lock on SHUTDOWN when setting Spider as default storage engine (temporary or global) +--echo # +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +SET GLOBAL default_tmp_storage_engine=spider; +--source include/restart_mysqld.inc + +SET GLOBAL default_storage_engine=Spider; +--source include/restart_mysqld.inc + +--disable_query_log +--disable_result_log +--source ../../t/test_deinit.inc +--enable_result_log +--enable_query_log +--echo # +--echo # end of test mdev_27575 +--echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_28683.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_28683.test new file mode 100644 index 00000000..0508a999 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_28683.test @@ -0,0 +1,29 @@ +--echo # +--echo # MDEV-28683 Spider: SIGSEGV in spider_db_direct_delete, SIGSEGV in spider_db_connect, ASAN: heap-use-after-free in spider_db_direct_delete +--echo # +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +CREATE TABLE t (c INT) ENGINE=Spider; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +SELECT * FROM t; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +INSERT INTO t (SELECT 1 FROM t); +LOCK TABLES t WRITE CONCURRENT; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +DELETE FROM t; + +UNLOCK TABLES; +DROP TABLE t; + +--disable_query_log +--disable_result_log +--source ../../t/test_deinit.inc +--enable_result_log +--enable_query_log +--echo # +--echo # end of test mdev_28683 +--echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_28739.cnf b/storage/spider/mysql-test/spider/bugfix/t/mdev_28739.cnf new file mode 100644 index 00000000..05dfd8a0 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_28739.cnf @@ -0,0 +1,3 @@ +!include include/default_mysqld.cnf +!include ../my_1_1.cnf +!include ../my_2_1.cnf diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_28739.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_28739.test new file mode 100644 index 00000000..d4ac8e42 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_28739.test @@ -0,0 +1,41 @@ +--echo # +--echo # MDEV-28739 Trying to lock uninitialized mutex or hang upon shutdown after using Spider with query_cache +--echo # + +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +--connection child2_1 +CREATE DATABASE auto_test_remote; +USE auto_test_remote; + +CREATE TABLE tbl_a (id INT); + +--connection master_1 +CREATE DATABASE auto_test_local; +USE auto_test_local; + +set global query_cache_type= on; +set spider_same_server_link = on; + +eval CREATE TABLE tbl_a ( + id INT +) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_a", srv "s_2_1"'; + +SELECT * FROM tbl_a; + +--source include/restart_mysqld.inc + +--connection master_1 +DROP DATABASE IF EXISTS auto_test_local; +--connection child2_1 +DROP DATABASE IF EXISTS auto_test_remote; + +--disable_query_log +--disable_result_log +--source ../t/test_deinit.inc +--enable_query_log +--enable_result_log diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_28739_simple.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_28739_simple.test new file mode 100644 index 00000000..feff85df --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_28739_simple.test @@ -0,0 +1,31 @@ +--echo # +--echo # MDEV-28739 Trying to lock uninitialized mutex or hang upon shutdown after using Spider with query_cache +--echo # + +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +# set @@global.debug_dbug="+d,xid_thd_trace,enter,exit,info,error:o,/tmp/trace2.out:i:F:L"; +#set @@global.debug_dbug="d:t:i:o,mysqld.trace"; + +set global query_cache_type= on; +set spider_same_server_link=1; +evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +create table t2 (c int); +create table t1 (c int) ENGINE=Spider +COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"'; +SELECT * FROM t1; +#shutdown; +--source include/restart_mysqld.inc +drop table t1, t2; +drop server srv; + +--disable_query_log +--disable_result_log +--source ../t/test_deinit.inc +--enable_query_log +--enable_result_log diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_28856.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_28856.test index 4f23168e..a1642f7a 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_28856.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_28856.test @@ -9,6 +9,7 @@ # This test covers some table params under consideration for inclusion # in the engine-defined options to be implemented in MDEV-28856. +set spider_same_server_link=1; evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_28998.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_28998.test index d67011ca..51d4c5c9 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_28998.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_28998.test @@ -2,19 +2,14 @@ --echo # MDEV-28998 ASAN errors in spider_fields::free_conn_holder or spider_create_group_by_handler --echo # -if (`select not(count(*)) from information_schema.system_variables where variable_name='have_sanitizer' and global_value like "%ASAN%"`) -{ ---skip test needs to be run with ASAN -} +# this test should be checked with ubsan --disable_query_log --disable_result_log --source ../../t/test_init.inc --enable_result_log --enable_query_log -# FIXME: this is a workaround. -set @old_spider_disable_group_by_handler=@@spider_disable_group_by_handler; -set spider_disable_group_by_handler=1; +set spider_same_server_link=1; evalp CREATE SERVER s FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); CREATE TABLE t1 (a INT); @@ -27,7 +22,6 @@ SELECT * FROM t2; # Cleanup DROP TABLE t2, t1_SPIDER, t1; drop server s; -set spider_disable_group_by_handler=@old_spider_disable_group_by_handler; --disable_query_log --disable_result_log diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_29002.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_29002.test new file mode 100644 index 00000000..51620a5a --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_29002.test @@ -0,0 +1,32 @@ +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log +SET spider_same_server_link= on; +evalp CREATE SERVER s FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); + +CREATE TABLE t (a INT); +CREATE TABLE t1_spider (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'"; +CREATE TABLE t2_spider (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'"; +SELECT * FROM t1_spider, t2_spider; +SELECT table_name, index_name, cardinality FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name IN ('t1_spider','t2_spider'); +RENAME TABLE t1_spider TO t3_spider; +SELECT * FROM t3_spider; + +DROP TABLE t3_spider, t2_spider, t; +drop server s; + +# case by roel +CREATE TABLE t1 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",HOST "srv",TABLE "t"'; +CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",HOST "srv",TABLE "t"'; +CREATE TABLE t3 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",HOST "srv",TABLE "t"'; +SHOW TABLE STATUS; +drop table t1, t2, t3; + +--disable_query_log +--disable_result_log +--source ../../t/test_deinit.inc +--enable_result_log +--enable_query_log diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_29163.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_29163.test index 0bfcc46f..2e56583d 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_29163.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_29163.test @@ -6,9 +6,7 @@ --source ../../t/test_init.inc --enable_result_log --enable_query_log -#FIXME: this is a workaround. -set @old_spider_disable_group_by_handler=@@spider_disable_group_by_handler; -set spider_disable_group_by_handler=1; +set spider_same_server_link=1; evalp CREATE SERVER s FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); CREATE TABLE t1 (a INT); @@ -24,8 +22,6 @@ SELECT t1_spider.* FROM t1_spider LEFT JOIN t2_spider LEFT JOIN t3_spider ON b = # Cleanup DROP TABLE t1_spider, t2_spider, t3_spider, t1, t2, t3; drop server s; -set spider_disable_group_by_handler=@old_spider_disable_group_by_handler; - --disable_query_log --disable_result_log --source ../../t/test_deinit.inc diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_29421.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_29421.test new file mode 100644 index 00000000..47ad42d3 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_29421.test @@ -0,0 +1,26 @@ +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log +set @old_table_open_cache=@@global.table_open_cache; +set global table_open_cache=10; +evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +CREATE TABLE t (c INT) ENGINE=InnoDB; +CREATE TABLE t_s (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"'; +CREATE TABLE t1 (a INT) ENGINE=Spider; +--error 1429 +SELECT * FROM t1; +--disable_result_log +SELECT * FROM information_schema.tables; +--enable_result_log +DROP TABLE t, t_s, t1; +drop server srv; +set global table_open_cache=@old_table_open_cache; + +--disable_query_log +--disable_result_log +--source ../../t/test_deinit.inc +--enable_result_log +--enable_query_log diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_29456.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_29456.test new file mode 100644 index 00000000..89d53227 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_29456.test @@ -0,0 +1,53 @@ +--echo # +--echo # MDEV-29456 Spider hangs in 'Waiting for table metadata lock' state on CREATE TABLE after LOCK TABLES +--echo # +--source include/have_innodb.inc +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +set spider_same_server_link=1; +evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); + +create table t1 (c int); +create table t2 (c int) ENGINE=Spider +COMMENT='WRAPPER "mysql", srv "srv",TABLE "t1"'; +CREATE TABLE t3 (c INT KEY) ENGINE=Spider; +LOCK TABLE t2 WRITE; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +LOCK TABLE t3 WRITE; +UNLOCK TABLES; +drop table t1, t2, t3; + +CREATE TABLE t (c INT) ENGINE=InnoDB; +CREATE TABLE t1 (c INT) ENGINE=Spider; +CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; +LOCK TABLES t2 WRITE; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +LOCK TABLES t1 READ; +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t (c INT) ENGINE=Spider; +drop table t, t1, t2; + +# MDEV-30049 +CREATE TABLE t (c INT) ENGINE=InnoDB; +CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; +CREATE TABLE t3 (c INT) ENGINE=InnoDB; +LOCK TABLES t2 WRITE; +--error 1428 +LOCK TABLES mysql.proc WRITE,mysql.user WRITE; # ERROR 1428 (HY000): You can't combine write-locking of system tables with other tables or lock types +INSERT INTO t3 SELECT * FROM t; +drop table t, t2, t3; + +drop server srv; +--disable_query_log +--disable_result_log +--source ../../t/test_deinit.inc +--enable_result_log +--enable_query_log +--echo # +--echo # end of test mdev_29456 +--echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_29502.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_29502.test index 88591f80..bacf5073 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_29502.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_29502.test @@ -8,6 +8,7 @@ --enable_result_log --enable_query_log +set spider_same_server_link=1; --let $srv=srv_mdev_29502 evalp CREATE SERVER $srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_29667.cnf b/storage/spider/mysql-test/spider/bugfix/t/mdev_29667.cnf new file mode 100644 index 00000000..05dfd8a0 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_29667.cnf @@ -0,0 +1,3 @@ +!include include/default_mysqld.cnf +!include ../my_1_1.cnf +!include ../my_2_1.cnf diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_29667.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_29667.test new file mode 100644 index 00000000..c96eb60e --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_29667.test @@ -0,0 +1,44 @@ +--echo # +--echo # MDEV-29667 Server hangs on DROP DATABASE after failing LOCK TABLES on Spider table +--echo # +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +--connection child2_1 +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +eval CREATE TABLE tbl_a ( + a INT +) $CHILD2_1_ENGINE $CHILD2_1_CHARSET; + +--connection master_1 +CREATE DATABASE auto_test_local; +USE auto_test_local; +eval CREATE TABLE tbl_a ( + a INT +) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_a", srv "s_2_1"'; +eval CREATE TABLE tbl_b ( + a INT +) $MASTER_1_ENGINE $MASTER_1_CHARSET; + +LOCK TABLES tbl_a WRITE; +--error 1429 +LOCK TABLES tbl_b READ, tbl_a READ; + +--connection master_1 +DROP DATABASE IF EXISTS auto_test_local; + +--connection child2_1 +DROP DATABASE IF EXISTS auto_test_remote; + +--disable_query_log +--disable_result_log +--source ../t/test_deinit.inc +--enable_query_log +--enable_result_log +--echo # +--echo # end of test mdev_29667 +--echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_29963.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_29963.test new file mode 100644 index 00000000..93b38c79 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_29963.test @@ -0,0 +1,56 @@ +--echo # +--echo # MDEV-29963 SIGSEGV in spider_db_mbase::append_lock_tables on LOCK TABLES +--echo # +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +set spider_same_server_link=1; +evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); + +CREATE TABLE t (a INT) ENGINE=Spider; +CREATE TABLE t2 (b INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +LOCK TABLES t AS a READ,t2 AS b LOW_PRIORITY WRITE,t2 AS c WRITE; +DROP TABLE t2; +CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +LOCK TABLES t2 WRITE; +DROP TABLE t2,t; + +# A less complex scenario +CREATE TABLE t (a INT); +CREATE TABLE t1 (a INT) ENGINE=Spider; +CREATE TABLE t2 (b INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +LOCK TABLES t1 READ, t2 WRITE; +DROP TABLE t2; +CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; +LOCK TABLES t2 WRITE; +UNLOCK TABLES; +DROP TABLE t, t1, t2; + +# MDEV-31357 +CREATE TABLE t1 (c INT) ENGINE=Spider; +CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT="WRAPPER 'mysql',srv 'srv',TABLE 't1'"; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +LOCK TABLES t1 WRITE,t2 WRITE; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +TRUNCATE t2; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +LOCK TABLES t2 AS o WRITE; +drop table t1, t2; + +drop server srv; + +--disable_query_log +--disable_result_log +--source ../../t/test_deinit.inc +--enable_result_log +--enable_query_log +--echo # +--echo # end of test mdev_29963 +--echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_30014.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_30014.test new file mode 100644 index 00000000..9c59adc8 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_30014.test @@ -0,0 +1,46 @@ +--echo # +--echo # MDEV-30014 heap-use-after-free in ha_spider::lock_tables(), highly sporadic SIGSEGV in intern_close_table +--echo # +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +set spider_same_server_link=1; +evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); + +create table t1 (c int); +create table t2 (c int) ENGINE=Spider +COMMENT='WRAPPER "mysql", srv "srv",TABLE "t1"'; +CREATE TABLE t3 (c INT KEY) ENGINE=Spider; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +LOCK TABLE t2 WRITE,t3 WRITE; +CREATE TABLE t4 (c INT) ENGINE=Spider; +FLUSH NO_WRITE_TO_BINLOG TABLES t4 WITH READ LOCK; +UNLOCK TABLES; + +drop table t1, t2, t3, t4; + +# This is a test case in MDEV-29456 but it is more like the above +# case. +create table t1 (c int); +create table t2 (c int) ENGINE=Spider +COMMENT='WRAPPER "mysql", srv "srv",TABLE "t1"'; +CREATE TABLE t3 (c INT KEY) ENGINE=Spider; +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +LOCK TABLE t2 WRITE, t3 WRITE; +UNLOCK TABLES; +drop table t1, t2, t3; + +drop server srv; + +--disable_query_log +--disable_result_log +--source ../../t/test_deinit.inc +--enable_result_log +--enable_query_log +--echo # +--echo # end of test mdev_30014 +--echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_30170.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_30170.test new file mode 100644 index 00000000..69026843 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_30170.test @@ -0,0 +1,8 @@ +install soname 'ha_spider'; +--error ER_BAD_TABLE_ERROR +DROP TABLE non_existing_table; +# Test that create or replace a non existing spider table work +create or replace table non_existing_table (c int) engine=Spider; +drop table non_existing_table; +--disable_query_log +--source ../../include/clean_up_spider.inc diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_30392.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_30392.test index 6d14f155..36e06f3f 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_30392.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_30392.test @@ -6,9 +6,7 @@ --source ../../t/test_init.inc --enable_result_log --enable_query_log -set @old_spider_disable_group_by_handler=@@spider_disable_group_by_handler; -set spider_disable_group_by_handler=1; - +set spider_same_server_link=1; evalp CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); CREATE TABLE t1 (a INT); @@ -21,7 +19,6 @@ SELECT a FROM t2 WHERE a IN ( SELECT a FROM t2 ); DROP TABLE t1, t2; DROP SERVER srv; -set spider_disable_group_by_handler=@old_spider_disable_group_by_handler; --disable_query_log --disable_result_log --source ../../t/test_deinit.inc diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_30981.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_30981.test index cc24ce82..ca3f000c 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_30981.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_30981.test @@ -2,21 +2,18 @@ --echo # MDEV-30981 Spider UBSAN: null pointer passed as argument 2, which is declared to never be null in spider_create_trx_alter_table on ALTER --echo # -if (`select not(count(*)) from information_schema.system_variables where variable_name='have_sanitizer' and global_value like "%UBSAN%"`) -{ ---skip test needs to be run with UBSAN -} - +# this test should be checked with ubsan + --disable_query_log --disable_result_log --source ../../t/test_init.inc --enable_result_log --enable_query_log - + CREATE TABLE t (c INT) ENGINE=Spider PARTITION BY LIST (c) (PARTITION p VALUES IN (1,2)); ALTER TABLE t ENGINE=InnoDB; drop table t; - + --disable_query_log --disable_result_log --source ../t/test_deinit.inc diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_31338.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_31338.test index e628c3b9..a3698c97 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_31338.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_31338.test @@ -9,6 +9,7 @@ --enable_result_log --enable_query_log +set spider_same_server_link=1; evalp CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); CREATE TABLE t (c BLOB) ENGINE=InnoDB; CREATE TABLE ts (c BLOB) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_31524.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_31524.test index 64cbf415..a5942fad 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_31524.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_31524.test @@ -10,6 +10,7 @@ --let $srv=srv_mdev_31524 SET @old_spider_read_only_mode = @@session.spider_read_only_mode; +set spider_same_server_link=1; evalp CREATE SERVER $srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); # when the user does not set var nor the table option, the default diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_31645.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_31645.test index 48bfa740..4dfe3b57 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_31645.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_31645.test @@ -6,25 +6,17 @@ --source ../../t/test_init.inc --enable_result_log --enable_query_log -set @old_spider_disable_group_by_handler=@@spider_disable_group_by_handler; -set spider_disable_group_by_handler=1; - +set spider_same_server_link=1; evalp CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); CREATE TABLE t1 ( a bigint(20) NOT NULL, b bigint(20) DEFAULT 0, PRIMARY KEY (a)); CREATE TABLE t2 ( a bigint(20) NOT NULL, b bigint(20) DEFAULT 0, PRIMARY KEY (a)) ENGINE=SPIDER COMMENT='srv "srv", WRAPPER "mysql", TABLE "t1"'; -SET SESSION optimizer_switch='semijoin=off'; -SELECT * FROM t2 -WHERE A BETWEEN 0 AND 10 AND B IN(SELECT B FROM t2 WHERE A BETWEEN 11 AND 20); - -SET SESSION optimizer_switch='semijoin=on'; - +SELECT * FROM t2 WHERE b IN (SELECT b FROM t2 WHERE a > 10); SELECT * FROM t2 WHERE A BETWEEN 0 AND 10 AND B IN(SELECT B FROM t2 WHERE A BETWEEN 11 AND 20); drop table t1, t2; drop server srv; -set spider_disable_group_by_handler=@old_spider_disable_group_by_handler; --disable_query_log --disable_result_log --source ../../t/test_deinit.inc diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_31996.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_31996.test index 3e823790..93b004a0 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_31996.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_31996.test @@ -4,6 +4,7 @@ --enable_result_log --enable_query_log +set spider_same_server_link=1; evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_32753.opt b/storage/spider/mysql-test/spider/bugfix/t/mdev_32753.opt new file mode 100644 index 00000000..c3151b0e --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_32753.opt @@ -0,0 +1,2 @@ +--sql-mode=oracle +--plugin-load-add=ha_spider diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_32753.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_32753.test new file mode 100644 index 00000000..2be7289f --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_32753.test @@ -0,0 +1,12 @@ +--echo # +--echo # MDEV-32753 Spider engine does not load in ORACLE mode +--echo # + +# This test tests spider init during server startup under global +# ORACLE mode +select * from mysql.plugin; +create table t (c int) Engine=SPIDER; +drop table t; +--echo # +--echo # end of test mdev_32753 +--echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_32753_after_start.opt b/storage/spider/mysql-test/spider/bugfix/t/mdev_32753_after_start.opt new file mode 100644 index 00000000..a918abb9 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_32753_after_start.opt @@ -0,0 +1 @@ +--sql-mode=oracle diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_32753_after_start.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_32753_after_start.test new file mode 100644 index 00000000..de2ab789 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_32753_after_start.test @@ -0,0 +1,20 @@ +--echo # +--echo # MDEV-32753 Spider engine does not load in ORACLE mode +--echo # + +# This test tests spider init after startup under global ORACLE mode +install soname 'ha_spider'; +--replace_regex /\.dll/.so/ +select * from mysql.plugin; +create table t (c int) Engine=SPIDER; +drop table t; + +--disable_query_log +--disable_result_log +--source ../../include/clean_up_spider.inc +--enable_result_log +--enable_query_log + +--echo # +--echo # end of test mdev_32753_after_start +--echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_32753_after_start_session.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_32753_after_start_session.test new file mode 100644 index 00000000..bf7bdb4f --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_32753_after_start_session.test @@ -0,0 +1,22 @@ +--echo # +--echo # MDEV-32753 Spider engine does not load in ORACLE mode +--echo # + +# This test tests spider init after startup under session ORACLE mode +set @old_sql_mode=@@sql_mode; +SET @@sql_mode = CONCAT(@@sql_mode, ',ORACLE'); +install soname 'ha_spider'; +select * from mysql.plugin; +create table t (c int) Engine=SPIDER; +drop table t; +set sql_mode=@old_sql_mode; + +--disable_query_log +--disable_result_log +--source ../../include/clean_up_spider.inc +--enable_result_log +--enable_query_log + +--echo # +--echo # end of test mdev_32753_after_start +--echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_32986.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_32986.test new file mode 100644 index 00000000..14438745 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_32986.test @@ -0,0 +1,29 @@ +--echo # +--echo # MDEV-32907 +--echo # +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log +set spider_same_server_link=1; +evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); + +create table t2 (c varchar(16)); +create table t1 (c varchar(16)) ENGINE=Spider +COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"'; + +insert into t1 values ('TestSpiderRegex'); +select c from t1 where c regexp '(Test|Spider|Regex)'; +drop table t1, t2; +drop server srv; + +--disable_query_log +--disable_result_log +--source ../../t/test_deinit.inc +--enable_result_log +--enable_query_log +--echo # +--echo # end of test mdev_32907 +--echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_33008.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_33008.test new file mode 100644 index 00000000..48d9a4f0 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_33008.test @@ -0,0 +1,24 @@ +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log +set spider_same_server_link=on; +evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql +OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +create table t2 ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `code` varchar(10) DEFAULT NULL, + PRIMARY KEY (`id`) +); +create table t1 ENGINE=Spider +COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"'; +show create table t1; +drop table t1, t2; + +drop server srv; +--disable_query_log +--disable_result_log +--source ../../t/test_deinit.inc +--enable_result_log +--enable_query_log diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_33191.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_33191.test new file mode 100644 index 00000000..90709127 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_33191.test @@ -0,0 +1,11 @@ +INSTALL SONAME 'ha_spider'; +set spider_same_server_link=on; +CREATE TABLE t2(c INT); +--let $SOCKET=`SELECT @@global.socket` +evalp CREATE TABLE t1(c INT) ENGINE=Spider COMMENT='socket "$SOCKET", user "root", table "t2 t3"'; +ALTER TABLE t1 ENGINE=Spider; +--error ER_NO_SUCH_TABLE +TRUNCATE TABLE t1; +drop table t1, t2; +--disable_query_log +--source ../../include/clean_up_spider.inc diff --git a/storage/spider/mysql-test/spider/bugfix/t/perfschema.opt b/storage/spider/mysql-test/spider/bugfix/t/perfschema.opt new file mode 100644 index 00000000..d2ed32dd --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/perfschema.opt @@ -0,0 +1 @@ +--loose-performance-schema diff --git a/storage/spider/mysql-test/spider/bugfix/t/perfschema.test b/storage/spider/mysql-test/spider/bugfix/t/perfschema.test new file mode 100644 index 00000000..9346d2b5 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/perfschema.test @@ -0,0 +1,16 @@ +source include/have_perfschema.inc; +disable_query_log; +source ../../include/init_spider.inc; +enable_query_log; + +--echo # +--echo # MDEV-33031 Assertion failure upon reading from performance schema with binlog enabled +--echo # +connect foo,localhost,root; +select variable_name, variable_value from performance_schema.status_by_thread +where variable_name like '%spider_direct_aggregate%'; +disconnect foo; +connection default; + +disable_query_log; +source ../../include/deinit_spider.inc; diff --git a/storage/spider/mysql-test/spider/bugfix/t/self_reference_multi.test b/storage/spider/mysql-test/spider/bugfix/t/self_reference_multi.test index 2ac927d9..4263560b 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/self_reference_multi.test +++ b/storage/spider/mysql-test/spider/bugfix/t/self_reference_multi.test @@ -8,6 +8,9 @@ --echo MDEV-6268 SPIDER table with no COMMENT clause causes queries to wait forever --echo +set spider_same_server_link=1; +set @old_spider_same_server_link=@@global.spider_same_server_link; +set global spider_same_server_link=1; --let $srv=srv_self_reference_multi evalp CREATE SERVER $srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); create table t2 (c int); @@ -22,6 +25,7 @@ select * from t1; select * from t2; drop table t0, t1, t2; eval drop server $srv; +set global spider_same_server_link=@old_spider_same_server_link; --disable_query_log --disable_result_log diff --git a/storage/spider/mysql-test/spider/bugfix/t/signal_ddl_fail.opt b/storage/spider/mysql-test/spider/bugfix/t/signal_ddl_fail.opt new file mode 100644 index 00000000..d883df7b --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/signal_ddl_fail.opt @@ -0,0 +1,2 @@ +--plugin-load-add=ha_spider +--debug-dbug=d,fail_spider_ddl_recovery_done diff --git a/storage/spider/mysql-test/spider/bugfix/t/signal_ddl_fail.test b/storage/spider/mysql-test/spider/bugfix/t/signal_ddl_fail.test new file mode 100644 index 00000000..f13eae3a --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/signal_ddl_fail.test @@ -0,0 +1,10 @@ +--source include/have_debug.inc +--echo # +--echo # MDEV-32559 Move alter table statements in spider init queries to be executed in the signal_ddl_recovery_done callback +--echo # +# This test tests that failure in ddl_recovery callback causes the +# plugin to be deinitialized. +select * from mysql.plugin; +--echo # +--echo # end of test signal_ddl_fail +--echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/spider_join_with_non_spider.test b/storage/spider/mysql-test/spider/bugfix/t/spider_join_with_non_spider.test new file mode 100644 index 00000000..294b469a --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/spider_join_with_non_spider.test @@ -0,0 +1,29 @@ +--echo # +--echo # Test joining a spider table with a non-spider table +--echo # + +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +set spider_same_server_link=1; +evalp CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +create table t1 (c int); +create table t2 (d int); +insert into t2 values (1), (2); +create table t3 (c int) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t1"'; +insert into t3 values (2), (3); +select c from t3 join t2 on c = d; +drop table t1, t2, t3; +drop server srv; + +--disable_query_log +--disable_result_log +--source ../../t/test_deinit.inc +--enable_result_log +--enable_query_log +--echo # +--echo # end of test spider_join_with_non_spider +--echo # diff --git a/storage/spider/mysql-test/spider/bugfix/t/spider_table_sts.test b/storage/spider/mysql-test/spider/bugfix/t/spider_table_sts.test deleted file mode 100644 index 72130175..00000000 --- a/storage/spider/mysql-test/spider/bugfix/t/spider_table_sts.test +++ /dev/null @@ -1,46 +0,0 @@ ---source ../include/spider_table_sts_init.inc ---echo ---echo this test is for MDEV-19842 ---echo ---echo drop and create databases - ---connection master_1 ---disable_warnings -CREATE DATABASE auto_test_local; -USE auto_test_local; ---enable_warnings - ---echo ---echo create table - ---connection master_1 ---disable_query_log -echo CREATE TABLE tbl_a ( - pkey int NOT NULL, - PRIMARY KEY (pkey) -) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1; -eval CREATE TABLE tbl_a ( - pkey int NOT NULL, - PRIMARY KEY (pkey) -) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1; ---enable_query_log - ---echo ---echo select test 1 - ---connection master_1 ---error 12609 -SELECT pkey FROM tbl_a; - ---echo ---echo deinit ---disable_warnings - ---connection master_1 ---error 12609 -DROP DATABASE IF EXISTS auto_test_local; - ---enable_warnings ---source ../include/spider_table_sts_deinit.inc ---echo ---echo end of test diff --git a/storage/spider/mysql-test/spider/bugfix/t/subquery.test b/storage/spider/mysql-test/spider/bugfix/t/subquery.test new file mode 100644 index 00000000..70238a52 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/subquery.test @@ -0,0 +1,31 @@ +--echo # +--echo # Test spider select with subqueries +--echo # +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log +set spider_same_server_link=1; +evalp CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root'); +create table t1 (c1 int); +create table t2 (c2 int); + +insert into t1 values (1), (2); +insert into t2 values (0), (1), (2); + +create table t1s (c1 int) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t1"'; +create table t2s (c2 int) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv",TABLE "t2"'; + +select c1 from t1s, (select c2 from t2s where c2 > 0) t where c1 + 1 = c2; + +drop table t1, t2, t1s, t2s; +drop server srv; +--disable_query_log +--disable_result_log +--source ../../t/test_deinit.inc +--enable_result_log +--enable_query_log +--echo # +--echo # end of test subquery +--echo # diff --git a/storage/spider/mysql-test/spider/include/clean_up_spider.inc b/storage/spider/mysql-test/spider/include/clean_up_spider.inc index 1f0659dc..1c977bfb 100644 --- a/storage/spider/mysql-test/spider/include/clean_up_spider.inc +++ b/storage/spider/mysql-test/spider/include/clean_up_spider.inc @@ -3,7 +3,8 @@ DROP FUNCTION spider_copy_tables; DROP FUNCTION spider_ping_table; DROP FUNCTION spider_bg_direct_sql; DROP FUNCTION spider_direct_sql; -UNINSTALL SONAME IF EXISTS "ha_spider"; +--replace_regex /\.dll/.so/ +UNINSTALL SONAME IF EXISTS 'ha_spider'; DROP TABLE IF EXISTS mysql.spider_xa; DROP TABLE IF EXISTS mysql.spider_xa_member; DROP TABLE IF EXISTS mysql.spider_xa_failed_log; diff --git a/storage/spider/mysql-test/spider/include/init_spider.inc b/storage/spider/mysql-test/spider/include/init_spider.inc index ccf9e305..6014b1d7 100644 --- a/storage/spider/mysql-test/spider/include/init_spider.inc +++ b/storage/spider/mysql-test/spider/include/init_spider.inc @@ -158,27 +158,6 @@ let $PLUGIN_VERSION= `SELECT SUBSTRING_INDEX(plugin_version, '.', 1) FROM information_schema.plugins WHERE plugin_name = 'SPIDER'`; -if (`SELECT IF($PLUGIN_VERSION = 3, 1, 0)`) -{ - let $HAS_REWRITE= - `SELECT IF (STRCMP('$SERVER_NAME', 'MariaDB') = 0, - IF ($SERVER_MAJOR_VERSION = 10, - IF ($SERVER_MINOR_VERSION < 4, 0, 1), - IF ($SERVER_MAJOR_VERSION < 10, 0, 1)), - 0)`; - let $HAS_REWRITE= 0; - if ($HAS_REWRITE) - { - let $PLUGIN_NAME= spider_flush_rewrite_cache; - let $PLUGIN_EXIST= - `SELECT COUNT(*) FROM mysql.func WHERE name = '$PLUGIN_NAME'`; - while (!$PLUGIN_EXIST) - { - let $PLUGIN_EXIST= - `SELECT COUNT(*) FROM mysql.func WHERE name = '$PLUGIN_NAME'`; - } - } -} let $PLUGIN_NAME= spider_flush_table_mon_cache; let $PLUGIN_EXIST= `SELECT COUNT(*) FROM mysql.func WHERE name = '$PLUGIN_NAME'`; diff --git a/storage/spider/mysql-test/spider/r/direct_join.result b/storage/spider/mysql-test/spider/r/direct_join.result index 398985ac..96f18a54 100644 --- a/storage/spider/mysql-test/spider/r/direct_join.result +++ b/storage/spider/mysql-test/spider/r/direct_join.result @@ -167,7 +167,7 @@ connection child2_1; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %'; argument select `id`,`hr_status`,`region_code`,`region` from `auto_test_remote`.`tbl_person` where `id` = '24FC3F0A5119432BAE13DD65AABAA39C' and `region` = 510411 -select count(0) `count(0)` from `auto_test_remote`.`tbl_ncd_cm_person` t0 where ((t0.`person_id` = '24FC3F0A5119432BAE13DD65AABAA39C') and (t0.`diseaseKind_id` = '52A0328740914BCE86ED10A4D2521816')) +select count(0) `count(0)` from (select 1) t0 join `auto_test_remote`.`tbl_ncd_cm_person` t1 where ((t1.`person_id` = '24FC3F0A5119432BAE13DD65AABAA39C') and (t1.`diseaseKind_id` = '52A0328740914BCE86ED10A4D2521816')) SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT * FROM tbl_person; id hr_status region_code region diff --git a/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result index a6bd3a7c..96b00c61 100644 --- a/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result @@ -87,7 +87,7 @@ NULL NULL NULL 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 join `auto_test_remote`.`ta_r` t0) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`) and (t0.`a` = t1.`a`) and (t1.`a` is not null)) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r` t0 join `auto_test_remote`.`ta_r_3` t1 join `auto_test_remote`.`ta_r_auto_inc` t2) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`) and (t0.`a` = t1.`a`) and (t1.`a` is not null)) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/direct_left_right_left_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_left_right_left_join_nullable.result index 8fab6d24..7ba1084a 100644 --- a/storage/spider/mysql-test/spider/r/direct_left_right_left_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_left_right_left_join_nullable.result @@ -86,7 +86,7 @@ NULL c 2000-01-03 00:00:00 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_auto_inc` t2 left join (`auto_test_remote`.`ta_r_3` t1 join `auto_test_remote`.`ta_r` t0) on ((t1.`c` = t2.`c`) and (t0.`a` = t1.`a`) and (t1.`a` is not null)) left join `auto_test_remote`.`ta_r_no_idx` t3 on (t3.`b` = t2.`b`) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_auto_inc` t2 left join (`auto_test_remote`.`ta_r` t0 join `auto_test_remote`.`ta_r_3` t1) on ((t1.`c` = t2.`c`) and (t0.`a` = t1.`a`) and (t1.`a` is not null)) left join `auto_test_remote`.`ta_r_no_idx` t3 on (t3.`b` = t2.`b`) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/direct_right_join.result b/storage/spider/mysql-test/spider/r/direct_right_join.result index d7780b94..ee0940a4 100644 --- a/storage/spider/mysql-test/spider/r/direct_right_join.result +++ b/storage/spider/mysql-test/spider/r/direct_right_join.result @@ -79,7 +79,7 @@ a b c connection child2_1; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %'; argument -select t0.`b` `b`,t0.`a` `a`,t2.`b` `b`,t2.`c` `c` from `auto_test_remote`.`ta_r_int` t2 left join (`auto_test_remote`.`ta_r` t0 join `auto_test_remote`.`ta_r_3` t1) on ((t0.`a` = t2.`a`) and (t1.`a` = t2.`a`)) where 1 order by t0.`b` desc +select t0.`b` `b`,t0.`a` `a`,t2.`b` `b`,t2.`c` `c` from `auto_test_remote`.`ta_r_int` t2 left join (`auto_test_remote`.`ta_r_3` t1 join `auto_test_remote`.`ta_r` t0) on ((t0.`a` = t2.`a`) and (t1.`a` = t2.`a`)) where 1 order by t0.`b` desc SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/direct_right_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_right_join_nullable.result index 5101ea50..fefb67fa 100644 --- a/storage/spider/mysql-test/spider/r/direct_right_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_right_join_nullable.result @@ -87,7 +87,7 @@ NULL c 2000-01-03 00:00:00 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join `auto_test_remote`.`ta_r_auto_inc` t2 on (t2.`b` = t3.`b`) left join `auto_test_remote`.`ta_r_3` t1 on (t1.`c` = t2.`c`) left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null)) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 left join (`auto_test_remote`.`ta_r_3` t1 left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null))) on (t1.`c` = t2.`c`)) on (t2.`b` = t3.`b`) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/direct_right_left_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_right_left_join_nullable.result index c90fe3ab..64c3b57c 100644 --- a/storage/spider/mysql-test/spider/r/direct_right_left_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_right_left_join_nullable.result @@ -86,7 +86,7 @@ NULL c 2000-01-03 00:00:00 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_auto_inc` t2 left join `auto_test_remote`.`ta_r_3` t1 on (t1.`c` = t2.`c`) left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null)) left join `auto_test_remote`.`ta_r_no_idx` t3 on (t3.`b` = t2.`b`) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_auto_inc` t2 left join (`auto_test_remote`.`ta_r_3` t1 left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null))) on (t1.`c` = t2.`c`) left join `auto_test_remote`.`ta_r_no_idx` t3 on (t3.`b` = t2.`b`) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result index f6c808be..68d11934 100644 --- a/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result @@ -87,7 +87,7 @@ NULL c 2000-01-03 00:00:00 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null))) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`)) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_3` t1 left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null)) join `auto_test_remote`.`ta_r_auto_inc` t2) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`)) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/partition_join_pushdown_for_single_partition.result b/storage/spider/mysql-test/spider/r/partition_join_pushdown_for_single_partition.result index 3eed8df2..850546fa 100644 --- a/storage/spider/mysql-test/spider/r/partition_join_pushdown_for_single_partition.result +++ b/storage/spider/mysql-test/spider/r/partition_join_pushdown_for_single_partition.result @@ -86,8 +86,10 @@ SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argum argument select `value` from `auto_test_remote2`.`tbl_a` where `value` = 5 select `value2` from `auto_test_remote2`.`tbl_b` where `value2` = 5 +select sum('5') `sum(a.value)`,count('5') `count(b.value2)` from (select 1) t0 join (select 1) t1 select `value` from `auto_test_remote2`.`tbl_a` where `value` = 5 select `value2` from `auto_test_remote2`.`tbl_b` where `value2` = 5 +select sum('5') `sum(a.value)`,count('5') `count(b.value2)` from (select 1) t0 join (select 1) t1 SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT value FROM tbl_a ORDER BY value; value diff --git a/storage/spider/mysql-test/spider/r/variable_deprecation.result b/storage/spider/mysql-test/spider/r/variable_deprecation.result index cea36ff2..be964ce4 100644 --- a/storage/spider/mysql-test/spider/r/variable_deprecation.result +++ b/storage/spider/mysql-test/spider/r/variable_deprecation.result @@ -75,8 +75,6 @@ Warning 1287 '@@spider_load_crd_at_startup' is deprecated and will be removed in SHOW VARIABLES LIKE "spider_load_crd_at_startup"; Variable_name Value spider_load_crd_at_startup 0 -DROP TABLE tbl_a; -DROP TABLE tbl_b; # MDEV-28008 Deprecate spider_crd_mode and spider_sts_mode SET spider_crd_mode = 1; Warnings: diff --git a/storage/spider/mysql-test/spider/regression/e1121/r/direct_join_by_pkey_key.result b/storage/spider/mysql-test/spider/regression/e1121/r/direct_join_by_pkey_key.result index a5553304..72645f4f 100644 --- a/storage/spider/mysql-test/spider/regression/e1121/r/direct_join_by_pkey_key.result +++ b/storage/spider/mysql-test/spider/regression/e1121/r/direct_join_by_pkey_key.result @@ -46,7 +46,7 @@ connection child2_1; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%`tbl_a`%' ; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%`tbl_b`%'; argument -select t0.`val` `val`,t0.`akey` `akey` from `auto_test_remote`.`tbl_a` t0 where (t0.`akey` = '4') +select t0.`val` `val`,t0.`akey` `akey` from `auto_test_remote`.`tbl_a` t0 join (select 1) t1 where (t0.`akey` = '4') SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%`tbl_a`%' ; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%`tbl_b`%' argument diff --git a/storage/spider/mysql-test/spider/t/variable_deprecation.test b/storage/spider/mysql-test/spider/t/variable_deprecation.test index 3ed8dcf5..6159b25b 100644 --- a/storage/spider/mysql-test/spider/t/variable_deprecation.test +++ b/storage/spider/mysql-test/spider/t/variable_deprecation.test @@ -47,9 +47,6 @@ SHOW VARIABLES LIKE "spider_load_sts_at_startup"; SET GLOBAL spider_load_crd_at_startup = 0; SHOW VARIABLES LIKE "spider_load_crd_at_startup"; -DROP TABLE tbl_a; -DROP TABLE tbl_b; - --echo # MDEV-28008 Deprecate spider_crd_mode and spider_sts_mode SET spider_crd_mode = 1; SHOW VARIABLES LIKE "spider_crd_mode"; |