[MDEV-31111] ERRORS: Invalid default value for 'check_time', DDL_LOG: Got error 1062 ... for entry 1 of type 'rename table', 1146: Table 'mysql.spider_table_sts' doesn't exist, ER_TABLE_EXISTS_ERROR and Got error 12524 'unknown' from SPIDER on RENAME/DROP TABLE Created: 2023-04-22  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.6, 10.7, 10.8, 10.9, 10.11, 11.0, 11.1
Fix Version/s: 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Major
Reporter: Roel Van de Paar Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: race, regression, regression-10.6, sporadic, sql_mode

Issue Links:
Relates

 Description   

The bug is highly sporadic, and there are likely a number of extraneous clauses in the testcase which are not necessary to reproduce the bug. Currently repeating the testcase about 500-1000 times, using the CLI, with a fresh server start for each t/c loop, reproduces the issue.

SET sql_mode='traditional', max_heap_table_size=1048576;
 
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
SET GLOBAL sql_mode=(SELECT CONCAT (@@sql_mode,',pipes_as_concat'));
 
CREATE TABLE t2 (pk INT(1) NOT NULL,c_int_nokey INT(1),c_intk INT(1),cvck CHAR(1),cvc_nokey VARCHAR(1),PRIMARY KEY(pk),KEY c_intk (c_intk),KEY cvck (cvck,c_intk)) ENGINE=Spider AUTO_INCREMENT=+ 1 DEFAULT CHARSET=latin1;
 
CREATE TABLE t (i1 INT NOT NULL,a INT,PRIMARY KEY(i1)) ENGINE=Spider;
 
ALTER TABLE t2 ENGINE=InnoDB;
 
DROP TABLE t;
 
DROP TABLE t,t2,t3,t4;
 
CREATE TABLE t3 (c INT,c2 CHAR(1)) ENCRYPTION="Y" ENGINE=Spider;
 
RENAME TABLE t TO t2;

Leads to:

11.1.0 2b61ff8f2221745f0a96855a0feb0825c426f993 (Debug)

 
2023-04-22 15:37:10 0 [Note] /test/MD010423-mariadb-11.1.0-linux-x86_64-dbg/bin/mariadbd: ready for connections.
 
Version: '11.1.0-MariaDB-debug'  socket: '/test/MD010423-mariadb-11.1.0-linux-x86_64-dbg/socket.sock'  port: 10910  MariaDB Server
 
[ERROR] Invalid default value for 'check_time'
 
2023-04-22 15:37:10 4 [ERROR] DDL_LOG: Got error 1062 when trying to execute action for entry 1 of type 'rename table'

Further attempts to improve the test setup (MTR vs CLI), testcase length and reproducibility still in progress. Currently observed in 11.1 only, but likely present in other versions.

 Comments   
Comment by Roel Van de Paar [ 2023-04-22 ]

Thus far all attempts at reducing/improving the testcase further have failed. Given the (ftm deemed) requirement to restart mariadbd in between tests replays take several hours. Perhaps an rr trace would be a good next step.

Comment by Roel Van de Paar [ 2023-04-24 ]

Found a shorter testcase, which is as good as "non-sporadic", and reduced the same. No Foreign Keys are needed.

SET SESSION sql_mode='traditional', GLOBAL sql_mode='TRADITIONAL';
 
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE TABLE t1 (c INT) ENGINE=Spider;
 
CREATE TABLE t2 (c INT) ENGINE=Spider;
 
ALTER TABLE t1 ENGINE=InnoDB;
 
DROP TABLE t1;
 
RENAME TABLE t2 TO t1,t2 TO t1;

This leads to, across versions and build types, all these:

[ERROR] Invalid default value for 'check_time'
 
[ERROR] DDL_LOG: Got error 1062 when trying to execute action for entry 1 of type 'rename table'
 
[ERROR] DDL_LOG: Got error 1062 when trying to execute action for entry 2 of type 'rename table'
 
[ERROR] DDL_LOG: Got error 1062 when trying to execute action for entry 3 of type 'rename table'

The CLI testcase output (when the bug occured) is this:

11.1.0 2b61ff8f2221745f0a96855a0feb0825c426f993 (Debug)

 
11.1.0-dbg>RENAME TABLE t2 TO t1,t2 TO t1;
 
ERROR 1062 (23000): Duplicate entry 'test-t1-0' for key 'PRIMARY'

Interestingly, with the following small testcase change (GLOBAL sql_mode to use NO_ZERO_DATE instead of traditional):

SET sql_mode='traditional', GLOBAL sql_mode='NO_ZERO_DATE';

We get the same issue.

Additionally, with the following small testcase change (GLOBAL sql_mode not using traditional):

SET SESSION sql_mode='traditional', GLOBAL sql_mode='';

We instead see:

11.1.0 2b61ff8f2221745f0a96855a0feb0825c426f993 (Debug)

 
11.1.0-dbg>RENAME TABLE t2 TO t1,t2 TO t1;
 
ERROR 1146 (42S02): Table 'test.t2' doesn't exist

The test.t2 does not exist error could be valid here, given the double attempt in ALTER.

Comment by Roel Van de Paar [ 2023-04-24 ]

With the following testcase:

SET sql_mode='traditional', GLOBAL sql_mode='NO_ZERO_DATE';
 
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE TABLE t1 (c INT) ENGINE=Spider;
 
CREATE TABLE t2 (c INT) ENGINE=Spider;
 
ALTER TABLE t1 ENGINE=Spider;
 
DROP TABLE t1;
 
RENAME TABLE t2 TO t1,t2 TO t1;

We produce these interesting errors:

11.1.0 2b61ff8f2221745f0a96855a0feb0825c426f993 (Debug)

 
11.1.0-dbg>DROP TABLE t1;
 
ERROR 1146 (42S02): Table 'mysql.spider_table_sts' doesn't exist
 
11.1.0-dbg>RENAME TABLE t2 TO t1,t2 TO t1;
 
ERROR 1050 (42S01): Table 't1' already exists

This looks to be an additional bug.
Also note that here t1 is mentioned in the second error, whereas with the similar testcase above, t2 is mentioned.

Comment by Roel Van de Paar [ 2023-04-24 ]

This MTR testcase:

--source include/have_innodb.inc
 
SET SESSION sql_mode='traditional', GLOBAL sql_mode='TRADITIONAL';
 
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE TABLE t1 (c INT) ENGINE=Spider;
 
CREATE TABLE t2 (c INT) ENGINE=Spider;
 
ALTER TABLE t1 ENGINE=InnoDB;
 
DROP TABLE t1;
 
--error ER_DUP_ENTRY
 
RENAME TABLE t2 TO t1,t2 TO t1;

Produces both the

[ERROR] Invalid default value for 'check_time'
 
[ERROR] DDL_LOG: Got error 1062 when trying to execute action for entry 3 of type 'rename table'

Errors in the error log.

Comment by Roel Van de Paar [ 2023-04-24 ]

This MTR testcase:

SET sql_mode='traditional', GLOBAL sql_mode='NO_ZERO_DATE';
 
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE TABLE t1 (c INT) ENGINE=Spider;
 
CREATE TABLE t2 (c INT) ENGINE=Spider;
 
ALTER TABLE t1 ENGINE=Spider;
 
DROP TABLE t1;

Produces the

11.1.0 2b61ff8f2221745f0a96855a0feb0825c426f993 (Debug, MTR run)

 
mysqltest: At line 7: query 'DROP TABLE t1' failed: ER_NO_SUCH_TABLE (1146): Table 'mysql.spider_table_sts' doesn't exist

Error output in MTR. Note that ER_NO_SUCH_TABLE is not valid in either testcase in this comment.

Additionally there is:

Warnings:
 
Warning	1146	Table 'mysql.spider_table_sts' doesn't exist
 
Warning	12524	Can't open system table mysql.spider_table_sts
 
Warning	1296	Got error 12524 'unknown' from SPIDER

And,

SET sql_mode='traditional', GLOBAL sql_mode='NO_ZERO_DATE';
 
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE TABLE t1 (c INT) ENGINE=Spider;
 
CREATE TABLE t2 (c INT) ENGINE=Spider;
 
ALTER TABLE t1 ENGINE=Spider;
 
--error ER_NO_SUCH_TABLE
 
DROP TABLE t1;
 
RENAME TABLE t2 TO t1,t2 TO t1;

Produces:

11.1.0 2b61ff8f2221745f0a96855a0feb0825c426f993 (Debug, MTR run)

 
mysqltest: At line 8: query 'RENAME TABLE t2 TO t1,t2 TO t1' failed: ER_TABLE_EXISTS_ERROR (1050): Table 't1' already exists

With the same warnings on the DROP.

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