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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.11
Fix Version/s: N/A
Component/s: Data Definition - Create Table, Storage Engine - Spider
Labels:

Description

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET'',DATABASE'',USER'',PASSWORD 'PWD0');

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

ALTER TABLE t ADD b TEXT;

CREATE OR REPLACE TABLE t (a INT) ENGINE=Spider;

Leads to:

bb-10.11-midenok 80625038c703d46e2e01d32da5de234a6c31acb8 (Optimized)
10.11.0-opt>CREATE OR REPLACE TABLE t (a INT) ENGINE=Spider;
ERROR 1062 (23000): Duplicate entry 'test-t-0' for key 'PRIMARY'

bb-10.11-midenok 80625038c703d46e2e01d32da5de234a6c31acb8 (Optimized)
2022-10-15 16:40:29 25 [Warning] Access denied for user ''@'localhost' (using password: YES)
2022-10-15 16:40:29 4 [ERROR] mysqld: Can't find record in 'spider_tables'
2022-10-15 16:40:29 4 [ERROR] DDL_LOG: Got error 1032 when trying to execute action for entry 6 of type 'rename table'

Before MDEV-25292 it passes.

Attachments

Issue Links

is caused by

MDEV-25292 Atomic CREATE OR REPLACE TABLE

Stalled

relates to

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

Closed

Activity

Ascending order - Click to sort in descending order

Aleksey Midenkov added a comment - 2022-10-17 07:59

Failure happens here:

#0  0x00007f5f658ad404 in spider_update_sys_table_row (table=0x7f5f4c05de88, do_handle_error=true) at ../src/storage/spider/spd_sys_table.cc:191

#1  0x00007f5f658a3967 in spider_update_tables_name (table=0x7f5f4c05de88, from=0x7f5f640c6ed0 "./test/#sql-create-1811aa-17-t", to=0x7f5f640c6cd0 "./test/t", old_link_count=0x7f5f640c66e4) at ../src/storage/spider/spd_sys_table.cc:1799

#2  0x00007f5f659b7d0e in ha_spider::rename_table (this=0x7f5f4c0c7270, from=0x7f5f640c6ed0 "./test/#sql-create-1811aa-17-t", to=0x7f5f640c6cd0 "./test/t") at ../src/storage/spider/ha_spider.cc:8886

#3  0x000055d4f59156c7 in handler::ha_rename_table (this=0x7f5f4c0c7270, from=0x7f5f640c6ed0 "./test/#sql-create-1811aa-17-t", to=0x7f5f640c6cd0 "./test/t") at ../src/sql/handler.cc:5346

#4  0x000055d4f5de8538 in mysql_rename_table (base=0x55d4f8739728, old_db=0x7f5f640c7fc8, old_name=0x7f5f640c7850, new_db=0x7f5f640c7838, new_name=0x7f5f640c7860, id=0x7f5f640c7870, flags=1) at ../src/sql/sql_table.cc:5650

#5  0x000055d4f5d0bdec in rename_table_and_triggers (thd=0x7f5f4c03e978, param=0x7f5f640c7850, ddl_log_state=0x0, ren_table=0x7f5f640c7fc8, new_db=0x7f5f640c7838, skip_error=false, force_if_exists=0x7f5f640c784f) at ../src/sql/sql_rename.cc:385

#6  0x000055d4f5de5ed0 in HA_CREATE_INFO::finalize_atomic_replace (this=0x7f5f640c7d90, thd=0x7f5f4c03e978, orig_table=0x7f5f4c0b5c70) at ../src/sql/sql_table.cc:4562

#7  0x000055d4f5e01bde in mysql_create_table (thd=0x7f5f4c03e978, create_table=0x7f5f4c0b5c70, create_info=0x7f5f640c7d90, alter_info=0x7f5f640c7ca0) at ../src/sql/sql_table.cc:5369

#8  0x000055d4f5dff922 in Sql_cmd_create_table_like::execute (this=0x7f5f4c0ca230, thd=0x7f5f4c03e978) at ../src/sql/sql_table.cc:12994

#9  0x000055d4f5cb9f2a in mysql_execute_command (thd=0x7f5f4c03e978, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:5997

#10 0x000055d4f5caafdf in mysql_parse (thd=0x7f5f4c03e978, rawbuf=0x7f5f4c0bcb30 "CREATE OR REPLACE TABLE t (a INT) ENGINE=Spider", length=47, parser_state=0x7f5f640ca288) at ../src/sql/sql_parse.cc:8023

(rr) p table->s->table_name

$9 = {

  str = 0x7f5f582138fe "spider_tables",

  length = 13

spider_tables is Aria tables.

Aleksey Midenkov added a comment - 2022-10-17 07:59 Failure happens here: #0 0x00007f5f658ad404 in spider_update_sys_table_row (table=0x7f5f4c05de88, do_handle_error=true) at ../src/storage/spider/spd_sys_table.cc:191 #1 0x00007f5f658a3967 in spider_update_tables_name (table=0x7f5f4c05de88, from=0x7f5f640c6ed0 "./test/#sql-create-1811aa-17-t", to=0x7f5f640c6cd0 "./test/t", old_link_count=0x7f5f640c66e4) at ../src/storage/spider/spd_sys_table.cc:1799 #2 0x00007f5f659b7d0e in ha_spider::rename_table (this=0x7f5f4c0c7270, from=0x7f5f640c6ed0 "./test/#sql-create-1811aa-17-t", to=0x7f5f640c6cd0 "./test/t") at ../src/storage/spider/ha_spider.cc:8886 #3 0x000055d4f59156c7 in handler::ha_rename_table (this=0x7f5f4c0c7270, from=0x7f5f640c6ed0 "./test/#sql-create-1811aa-17-t", to=0x7f5f640c6cd0 "./test/t") at ../src/sql/handler.cc:5346 #4 0x000055d4f5de8538 in mysql_rename_table (base=0x55d4f8739728, old_db=0x7f5f640c7fc8, old_name=0x7f5f640c7850, new_db=0x7f5f640c7838, new_name=0x7f5f640c7860, id=0x7f5f640c7870, flags=1) at ../src/sql/sql_table.cc:5650 #5 0x000055d4f5d0bdec in rename_table_and_triggers (thd=0x7f5f4c03e978, param=0x7f5f640c7850, ddl_log_state=0x0, ren_table=0x7f5f640c7fc8, new_db=0x7f5f640c7838, skip_error=false, force_if_exists=0x7f5f640c784f) at ../src/sql/sql_rename.cc:385 #6 0x000055d4f5de5ed0 in HA_CREATE_INFO::finalize_atomic_replace (this=0x7f5f640c7d90, thd=0x7f5f4c03e978, orig_table=0x7f5f4c0b5c70) at ../src/sql/sql_table.cc:4562 #7 0x000055d4f5e01bde in mysql_create_table (thd=0x7f5f4c03e978, create_table=0x7f5f4c0b5c70, create_info=0x7f5f640c7d90, alter_info=0x7f5f640c7ca0) at ../src/sql/sql_table.cc:5369 #8 0x000055d4f5dff922 in Sql_cmd_create_table_like::execute (this=0x7f5f4c0ca230, thd=0x7f5f4c03e978) at ../src/sql/sql_table.cc:12994 #9 0x000055d4f5cb9f2a in mysql_execute_command (thd=0x7f5f4c03e978, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:5997 #10 0x000055d4f5caafdf in mysql_parse (thd=0x7f5f4c03e978, rawbuf=0x7f5f4c0bcb30 "CREATE OR REPLACE TABLE t (a INT) ENGINE=Spider", length=47, parser_state=0x7f5f640ca288) at ../src/sql/sql_parse.cc:8023 (rr) p table->s->table_name $9 = { str = 0x7f5f582138fe "spider_tables", length = 13 } spider_tables is Aria tables.

Aleksey Midenkov added a comment - 2022-10-17 08:05 - edited

Cause

First rename (from original to backup) avoids spider_update_sys_table_row():

#0  handler::ha_rename_table (this=0x7f5f4c0d2a20, from=0x7f5f640c6ed0 "./test/t", to=0x7f5f640c6cd0 "./test/#sql-backup-1811aa-17-t") at ../src/sql/handler.cc:5343

#1  0x000055d4f5de8538 in mysql_rename_table (base=0x55d4f8739728, old_db=0x7f5f4c0b5c70, old_name=0x7f5f640c7850, new_db=0x7f5f640c7ff8, new_name=0x7f5f640c7860, id=0x7f5f640c7870, flags=2) at ../src/sql/sql_table.cc:5650

#2  0x000055d4f5d0bdec in rename_table_and_triggers (thd=0x7f5f4c03e978, param=0x7f5f640c7850, ddl_log_state=0x0, ren_table=0x7f5f4c0b5c70, new_db=0x7f5f640c7ff8, skip_error=false, force_if_exists=0x7f5f640c784f) at ../src/sql/sql_rename.cc:385

#3  0x000055d4f5de5a44 in HA_CREATE_INFO::finalize_atomic_replace (this=0x7f5f640c7d90, thd=0x7f5f4c03e978, orig_table=0x7f5f4c0b5c70) at ../src/sql/sql_table.cc:4493

#4  0x000055d4f5e01bde in mysql_create_table (thd=0x7f5f4c03e978, create_table=0x7f5f4c0b5c70, create_info=0x7f5f640c7d90, alter_info=0x7f5f640c7ca0) at ../src/sql/sql_table.cc:5369

#5  0x000055d4f5dff922 in Sql_cmd_create_table_like::execute (this=0x7f5f4c0ca230, thd=0x7f5f4c03e978) at ../src/sql/sql_table.cc:12994

#6  0x000055d4f5cb9f2a in mysql_execute_command (thd=0x7f5f4c03e978, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:5997

#7  0x000055d4f5caafdf in mysql_parse (thd=0x7f5f4c03e978, rawbuf=0x7f5f4c0bcb30 "CREATE OR REPLACE TABLE t (a INT) ENGINE=Spider", length=47, parser_state=0x7f5f640ca288) at ../src/sql/sql_parse.cc:8023

8870      if (

8871        sql_command == SQLCOM_RENAME_TABLE ||

8872        (sql_command == SQLCOM_ALTER_TABLE && !trx->tmp_flg) ||

8873        !(alter_table_from =

8874          (SPIDER_ALTER_TABLE*) my_hash_search(&trx->trx_alter_table_hash,

8875          (uchar*) from, from_len))

8876      ) {

Aleksey Midenkov added a comment - 2022-10-17 08:05 - edited Cause First rename (from original to backup) avoids spider_update_sys_table_row(): #0 handler::ha_rename_table (this=0x7f5f4c0d2a20, from=0x7f5f640c6ed0 "./test/t", to=0x7f5f640c6cd0 "./test/#sql-backup-1811aa-17-t") at ../src/sql/handler.cc:5343 #1 0x000055d4f5de8538 in mysql_rename_table (base=0x55d4f8739728, old_db=0x7f5f4c0b5c70, old_name=0x7f5f640c7850, new_db=0x7f5f640c7ff8, new_name=0x7f5f640c7860, id=0x7f5f640c7870, flags=2) at ../src/sql/sql_table.cc:5650 #2 0x000055d4f5d0bdec in rename_table_and_triggers (thd=0x7f5f4c03e978, param=0x7f5f640c7850, ddl_log_state=0x0, ren_table=0x7f5f4c0b5c70, new_db=0x7f5f640c7ff8, skip_error=false, force_if_exists=0x7f5f640c784f) at ../src/sql/sql_rename.cc:385 #3 0x000055d4f5de5a44 in HA_CREATE_INFO::finalize_atomic_replace (this=0x7f5f640c7d90, thd=0x7f5f4c03e978, orig_table=0x7f5f4c0b5c70) at ../src/sql/sql_table.cc:4493 #4 0x000055d4f5e01bde in mysql_create_table (thd=0x7f5f4c03e978, create_table=0x7f5f4c0b5c70, create_info=0x7f5f640c7d90, alter_info=0x7f5f640c7ca0) at ../src/sql/sql_table.cc:5369 #5 0x000055d4f5dff922 in Sql_cmd_create_table_like::execute (this=0x7f5f4c0ca230, thd=0x7f5f4c03e978) at ../src/sql/sql_table.cc:12994 #6 0x000055d4f5cb9f2a in mysql_execute_command (thd=0x7f5f4c03e978, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:5997 #7 0x000055d4f5caafdf in mysql_parse (thd=0x7f5f4c03e978, rawbuf=0x7f5f4c0bcb30 "CREATE OR REPLACE TABLE t (a INT) ENGINE=Spider", length=47, parser_state=0x7f5f640ca288) at ../src/sql/sql_parse.cc:8023 8870 if ( 8871 sql_command == SQLCOM_RENAME_TABLE || 8872 (sql_command == SQLCOM_ALTER_TABLE && !trx->tmp_flg) || 8873 !(alter_table_from = 8874 (SPIDER_ALTER_TABLE*) my_hash_search(&trx->trx_alter_table_hash, 8875 (uchar*) from, from_len)) 8876 ) {

Aleksey Midenkov added a comment - 2022-10-17 08:09

Suggested fix

--- storage/spider/ha_spider.cc

+++ storage/spider/ha_spider.cc

@@ -8868,6 +8868,7 @@ int ha_spider::rename_table(

     goto error;

   if (

+    sql_command == SQLCOM_CREATE_TABLE ||

     sql_command == SQLCOM_RENAME_TABLE ||

     (sql_command == SQLCOM_ALTER_TABLE && !trx->tmp_flg) ||

     !(alter_table_from =

Aleksey Midenkov added a comment - 2022-10-17 08:09 Suggested fix --- storage/spider/ha_spider.cc +++ storage/spider/ha_spider.cc @@ -8868,6 +8868,7 @@ int ha_spider::rename_table( goto error; } if ( + sql_command == SQLCOM_CREATE_TABLE || sql_command == SQLCOM_RENAME_TABLE || (sql_command == SQLCOM_ALTER_TABLE && !trx->tmp_flg) || !(alter_table_from =

Roel Van de Paar added a comment - 2022-10-20 03:18 - edited

midenok and nayuta-yanagisawa, here is an additional testcase which leads to error 12524 instead of error 1032. It looks like the same issue but please let me know if not, and I will create a seperate ticket for the same.

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

CREATE TABLE t (a INT KEY) ENGINE=Spider;

UPDATE t SET c0=b0;

DROP TABLE IF EXISTS mysql.spider_tables;

CREATE OR REPLACE TABLE t (a TEXT);

Leads to:

bb-10.11-midenok 80625038c703d46e2e01d32da5de234a6c31acb8 (Optimized)
2022-10-20 14:14:20 4 [ERROR] DDL_LOG: Got error 12524 when trying to execute action for entry 6 of type 'rename table'

(Before MDEV-25292 it passes.)

Roel Van de Paar added a comment - 2022-10-20 03:18 - edited midenok and nayuta-yanagisawa , here is an additional testcase which leads to error 12524 instead of error 1032. It looks like the same issue but please let me know if not, and I will create a seperate ticket for the same. INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; CREATE TABLE t (a INT KEY ) ENGINE=Spider; UPDATE t SET c0=b0; DROP TABLE IF EXISTS mysql.spider_tables; CREATE OR REPLACE TABLE t (a TEXT); Leads to: bb-10.11-midenok 80625038c703d46e2e01d32da5de234a6c31acb8 (Optimized) 2022-10-20 14:14:20 4 [ERROR] DDL_LOG: Got error 12524 when trying to execute action for entry 6 of type 'rename table' (Before MDEV-25292 it passes.)

Roel Van de Paar added a comment - 2022-10-20 05:33 - edited

This testcase

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

GRANT ALL ON test.* TO Spider@localhost;

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

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

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

RENAME TABLE test.t2 TO db1.t2;

Produces:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)
2022-10-20 15:12:44 4 [ERROR] mysqld: Can't find record in 'spider_tables'
2022-10-20 15:12:44 4 [ERROR] DDL_LOG: Got error 1032 when trying to execute action for entry 2 of type 'rename table'

As well as:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)
2022-10-20 15:12:11 4 [ERROR] mysqld: Can't find record in 'spider_tables'
2022-10-20 15:12:11 4 [ERROR] DDL_LOG: Got error 1032 when trying to execute action for entry 3 of type 'rename table'

Which result one gets (2 or 3) is sporadic.

Roel Van de Paar added a comment - 2022-10-20 05:33 - edited This testcase INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; GRANT ALL ON test.* TO Spider@localhost; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock' , DATABASE 'test' , user 'Spider' , PASSWORD 'PWD1' ); CREATE TABLE t2 (f2 INT KEY ) ENGINE=Spider COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; CREATE TABLE t (a INT ) ENGINE=Spider COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; RENAME TABLE test.t2 TO db1.t2; Produces: 10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized) 2022-10-20 15:12:44 4 [ERROR] mysqld: Can't find record in 'spider_tables' 2022-10-20 15:12:44 4 [ERROR] DDL_LOG: Got error 1032 when trying to execute action for entry 2 of type 'rename table' As well as: 10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized) 2022-10-20 15:12:11 4 [ERROR] mysqld: Can't find record in 'spider_tables' 2022-10-20 15:12:11 4 [ERROR] DDL_LOG: Got error 1032 when trying to execute action for entry 3 of type 'rename table' Which result one gets (2 or 3) is sporadic.

Roel Van de Paar added a comment - 2022-10-22 00:09 - edited

This additional sporadic-in-outcome testcase:

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

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

CREATE TABLE t1 (pk2 INT,i2 INT,c2 CHAR(1),KEY(pk2)) ENGINE=InnoDB;

CREATE OR REPLACE TABLE t1 (a INT,b INT) ENGINE=InnoDB;

SET SESSION storage_engine='Spider';

CREATE TABLE t (id INT AUTO_INCREMENT,name CHAR(1),myDATE DATE,KEY(id,myDATE),INDEX idx_date (myDATE)) COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"' PARTITION BY RANGE (TO_DAYS (myDATE)) (PARTITION p0 VALUES LESS THAN (1),PARTITION p VALUES LESS THAN (734029),PARTITION p2 VALUES LESS THAN (734030),PARTITION p3 VALUES LESS THAN MAXVALUE);

ALTER TABLE t ADD b INT;

INSERT INTO t SELECT 1 t;

CREATE OR REPLACE TABLE t (a INT,b INT,c INT) COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';

CREATE OR REPLACE TABLE t (pk INT KEY) COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"' WITH SYSTEM VERSIONING PARTITION BY KEY() PARTITIONS 3;

DROP TABLE t;

CREATE TABLE t (a INT,b INT AUTO_INCREMENT,KEY(a,b)) COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';

SET STATEMENT sql_mode='no_engine_substitution' FOR ALTER TABLE t MODIFY a YEAR;

CREATE OR REPLACE TABLE t (a INT ZEROFILL);

Produces the additional

bb-10.11-midenok 80625038c703d46e2e01d32da5de234a6c31acb8 (Optimized)
...when trying to execute action for entry 7 of type 'rename table'

Where entry 7 can be entry 1, 2, 3, 4, 5, and 7 for this testcase. It will only very occassionally produced entry 1 (1-in-50+). entry 3 is also very rare for this testcase.

Roel Van de Paar added a comment - 2022-10-22 00:09 - edited This additional sporadic-in-outcome testcase: INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock' , DATABASE 'a' , USER 'Spider' , PASSWORD 'PWD1' ); CREATE TABLE t1 (pk2 INT ,i2 INT ,c2 CHAR (1), KEY (pk2)) ENGINE=InnoDB; CREATE OR REPLACE TABLE t1 (a INT ,b INT ) ENGINE=InnoDB; SET SESSION storage_engine= 'Spider' ; CREATE TABLE t (id INT AUTO_INCREMENT, name CHAR (1),myDATE DATE , KEY (id,myDATE), INDEX idx_date (myDATE)) COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' PARTITION BY RANGE (TO_DAYS (myDATE)) (PARTITION p0 VALUES LESS THAN (1),PARTITION p VALUES LESS THAN (734029),PARTITION p2 VALUES LESS THAN (734030),PARTITION p3 VALUES LESS THAN MAXVALUE); ALTER TABLE t ADD b INT ; INSERT INTO t SELECT 1 t; CREATE OR REPLACE TABLE t (a INT ,b INT ,c INT ) COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; CREATE OR REPLACE TABLE t (pk INT KEY ) COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' WITH SYSTEM VERSIONING PARTITION BY KEY () PARTITIONS 3; DROP TABLE t; CREATE TABLE t (a INT ,b INT AUTO_INCREMENT, KEY (a,b)) COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; SET STATEMENT sql_mode= 'no_engine_substitution' FOR ALTER TABLE t MODIFY a YEAR ; CREATE OR REPLACE TABLE t (a INT ZEROFILL); Produces the additional bb-10.11-midenok 80625038c703d46e2e01d32da5de234a6c31acb8 (Optimized) ...when trying to execute action for entry 7 of type 'rename table' Where entry 7 can be entry 1, 2, 3, 4, 5, and 7 for this testcase. It will only very occassionally produced entry 1 (1-in-50+). entry 3 is also very rare for this testcase.

Alexey Botchkov added a comment - 2022-10-25 11:27

This part produces the expected error.

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

CREATE TABLE t (a INT KEY) ENGINE=Spider;

UPDATE t SET c0=b0;

DROP TABLE IF EXISTS mysql.spider_tables;

CREATE OR REPLACE TABLE t (a TEXT);

Since the system mysql.spider_tables table was deleted, we can't expect the spider working normally.
So not a bug..

I wasn't able to reproduce the error with this:

INSTALL PLUGIN Spider SONAME 'ha_spider.so';

GRANT ALL ON test.* TO Spider@localhost;

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

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

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

RENAME TABLE test.t2 TO db1.t2;

Can something still be wrong with the mysql.spider_tables on your computer?

Other issues seem to manifest that initial problem. And fixed with the patch Aleksey mentions.

Alexey Botchkov added a comment - 2022-10-25 11:27 This part produces the expected error. INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; CREATE TABLE t (a INT KEY) ENGINE=Spider; UPDATE t SET c0=b0; DROP TABLE IF EXISTS mysql.spider_tables; CREATE OR REPLACE TABLE t (a TEXT); Since the system mysql.spider_tables table was deleted, we can't expect the spider working normally. So not a bug.. I wasn't able to reproduce the error with this: INSTALL PLUGIN Spider SONAME 'ha_spider.so' ; GRANT ALL ON test.* TO Spider @localhost ; CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock' ,DATABASE 'test' ,user 'Spider' ,PASSWORD 'PWD1' ); CREATE TABLE t2 (f2 INT KEY) ENGINE=Spider COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; CREATE TABLE t (a INT) ENGINE=Spider COMMENT= 'WRAPPER "mysql",srv "srv",TABLE "t"' ; RENAME TABLE test.t2 TO db1.t2; Can something still be wrong with the mysql.spider_tables on your computer? Other issues seem to manifest that initial problem. And fixed with the patch Aleksey mentions.

Alexey Botchkov added a comment - 2022-10-25 12:53

https://github.com/MariaDB/server/commit/4cf97ba683bbf54c20380f30ecb41104ea44ef67

Alexey Botchkov added a comment - 2022-10-25 12:53 https://github.com/MariaDB/server/commit/4cf97ba683bbf54c20380f30ecb41104ea44ef67

Nayuta Yanagisawa (Inactive) added a comment - 2022-10-26 06:25

OK to push.

Nayuta Yanagisawa (Inactive) added a comment - 2022-10-26 06:25 OK to push.

Roel Van de Paar added a comment - 2023-01-20 23:42

bb-11.0-midenok-MDEV-25292 reproduces the original issue

11.0.1 bb-11.0-midenok-MDEV-25292 b986107a777e3f900f235d969d569358c7a5edfe (Debug)
11.0.1-dbg>INSTALL PLUGIN Spider SONAME 'ha_spider.so';
Query OK, 0 rows affected, 1 warning (0.018 sec)

11.0.1-dbg>CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET'',DATABASE'',USER'',PASSWORD 'PWD0');
Query OK, 0 rows affected (0.002 sec)

11.0.1-dbg>CREATE TABLE t (a INT KEY AUTO_INCREMENT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
Query OK, 0 rows affected (0.422 sec)

11.0.1-dbg>ALTER TABLE t ADD b TEXT;
Query OK, 0 rows affected (0.010 sec)
Records: 0 Duplicates: 0 Warnings: 0

11.0.1-dbg>CREATE OR REPLACE TABLE t (a INT) ENGINE=Spider;
ERROR 1062 (23000): Duplicate entry 'test-t-0' for key 'PRIMARY'

11.0.1 bb-11.0-midenok-MDEV-25292 b986107a777e3f900f235d969d569358c7a5edfe (Debug)
2023-01-21 10:35:40 0 [Note] /test/bb-11.0-midenok-MDEV-25292_MD200123-mariadb-11.0.1-linux-x86_64-dbg/bin/mysqld: ready for connections.
Version: '11.0.1-MariaDB-debug' socket: '/test/bb-11.0-midenok-MDEV-25292_MD200123-mariadb-11.0.1-linux-x86_64-dbg/socket.sock' port: 10995 MariaDB Server
2023-01-21 10:39:49 4 [ERROR] mysqld: Can't find record in 'spider_tables'
2023-01-21 10:39:49 4 [ERROR] DDL_LOG: Got error 1032 when trying to execute action for entry 6 of type 'rename table'

Roel Van de Paar added a comment - 2023-01-20 23:42 bb-11.0-midenok- MDEV-25292 reproduces the original issue 11.0.1 bb-11.0-midenok-MDEV-25292 b986107a777e3f900f235d969d569358c7a5edfe (Debug) 11.0.1-dbg>INSTALL PLUGIN Spider SONAME 'ha_spider.so'; Query OK, 0 rows affected, 1 warning (0.018 sec) 11.0.1-dbg>CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET'',DATABASE'',USER'',PASSWORD 'PWD0'); Query OK, 0 rows affected (0.002 sec) 11.0.1-dbg>CREATE TABLE t (a INT KEY AUTO_INCREMENT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"'; Query OK, 0 rows affected (0.422 sec) 11.0.1-dbg>ALTER TABLE t ADD b TEXT; Query OK, 0 rows affected (0.010 sec) Records: 0 Duplicates: 0 Warnings: 0 11.0.1-dbg>CREATE OR REPLACE TABLE t (a INT) ENGINE=Spider; ERROR 1062 (23000): Duplicate entry 'test-t-0' for key 'PRIMARY' 11.0.1 bb-11.0-midenok-MDEV-25292 b986107a777e3f900f235d969d569358c7a5edfe (Debug) 2023-01-21 10:35:40 0 [Note] /test/bb-11.0-midenok-MDEV-25292_MD200123-mariadb-11.0.1-linux-x86_64-dbg/bin/mysqld: ready for connections. Version: '11.0.1-MariaDB-debug' socket: '/test/bb-11.0-midenok-MDEV-25292_MD200123-mariadb-11.0.1-linux-x86_64-dbg/socket.sock' port: 10995 MariaDB Server 2023-01-21 10:39:49 4 [ERROR] mysqld: Can't find record in 'spider_tables' 2023-01-21 10:39:49 4 [ERROR] DDL_LOG: Got error 1032 when trying to execute action for entry 6 of type 'rename table'

Aleksey Midenkov added a comment - 2023-01-25 13:54

Fixed in bb-11.0-midenok-MDEV-25292

Aleksey Midenkov added a comment - 2023-01-25 13:54 Fixed in bb-11.0-midenok- MDEV-25292

People

Assignee:: Aleksey Midenkov

Reporter:: Roel Van de Paar

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2022-10-15 05:44

Updated:: 2023-01-26 14:15

Resolved:: 2023-01-25 13:54

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.