Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. 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

Details

    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.

      Attachments

        Issue Links

          Activity

            Roel Roel Van de Paar added a comment - - edited

            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.

            Roel Roel Van de Paar added a comment - - edited 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.

            This testcase:

            SET GLOBAL sql_mode=(SELECT CONCAT (@@sql_mode,',no_zero_date'));
            INSTALL SONAME 'ha_spider';
            

            Is stable (non-sporadic) and leads to:

            10.6.18 0df4651c13d68f1c228986f940d8ea4a226132f5 (Optimized)

            2024-03-09 14:17:15 0 [Note] /test/MD090324-mariadb-10.6.18-linux-x86_64-opt/bin/mariadbd: ready for connections.
            Version: '10.6.18-MariaDB'  socket: '/test/MD090324-mariadb-10.6.18-linux-x86_64-opt/socket.sock'  port: 12016  MariaDB Server
            [ERROR] SPIDER plugin initialization failed at 'create table if not exists mysql.spider_table_sts(  db_name char(64) not null default '',  table_name char(199) not null default '',  data_file_length bigint unsigned not null default 0,  max_data_file_length bigint unsigned not null default 0,  index_file_length bigint unsigned not null default 0,  records bigint unsigned not null default 0,  mean_rec_length bigint unsigned not null default 0,  check_time datetime not null default '0000-00-00 00:00:00',  create_time datetime not null default '0000-00-00 00:00:00',  update_time datetime not null default '0000-00-00 00:00:00',  checksum bigint unsigned default null,  primary key (db_name, table_name)) engine=MyISAM default charset=utf8 collate=utf8_bin' by 'Invalid default value for 'check_time''
            

            Same outcome on debug. The error in late 2023 was different:

            10.6.16 6f091434f313996436b4f01d7e7d62a9f3786958 (Optimized)

            Version: '10.6.16-MariaDB'  socket: '/data/VARIOUS_BUILDS/MD311023-mariadb-10.6.16-linux-x86_64-opt/socket.sock'  port: 12299  MariaDB Server
            [ERROR] Invalid default value for 'check_time'
            

            Verified in 10.4-11.5, dbg + opt builds.

            Roel Roel Van de Paar added a comment - This testcase: SET GLOBAL sql_mode=( SELECT CONCAT (@@sql_mode, ',no_zero_date' )); INSTALL SONAME 'ha_spider' ; Is stable (non-sporadic) and leads to: 10.6.18 0df4651c13d68f1c228986f940d8ea4a226132f5 (Optimized) 2024-03-09 14:17:15 0 [Note] /test/MD090324-mariadb-10.6.18-linux-x86_64-opt/bin/mariadbd: ready for connections. Version: '10.6.18-MariaDB' socket: '/test/MD090324-mariadb-10.6.18-linux-x86_64-opt/socket.sock' port: 12016 MariaDB Server [ERROR] SPIDER plugin initialization failed at 'create table if not exists mysql.spider_table_sts( db_name char(64) not null default '', table_name char(199) not null default '', data_file_length bigint unsigned not null default 0, max_data_file_length bigint unsigned not null default 0, index_file_length bigint unsigned not null default 0, records bigint unsigned not null default 0, mean_rec_length bigint unsigned not null default 0, check_time datetime not null default '0000-00-00 00:00:00', create_time datetime not null default '0000-00-00 00:00:00', update_time datetime not null default '0000-00-00 00:00:00', checksum bigint unsigned default null, primary key (db_name, table_name)) engine=MyISAM default charset=utf8 collate=utf8_bin' by 'Invalid default value for 'check_time'' Same outcome on debug. The error in late 2023 was different: 10.6.16 6f091434f313996436b4f01d7e7d62a9f3786958 (Optimized) Version: '10.6.16-MariaDB' socket: '/data/VARIOUS_BUILDS/MD311023-mariadb-10.6.16-linux-x86_64-opt/socket.sock' port: 12299 MariaDB Server [ERROR] Invalid default value for 'check_time' Verified in 10.4-11.5, dbg + opt builds.
            ycp Yuchen Pei added a comment -

            > Is stable (non-sporadic) and leads to:

            It is because this commit (0df4651c13d68f1c228986f940d8ea4a226132f5) does not contain the fix for MDEV-33494, which I can confirm fixes the problem reported in this comment.

            ycp Yuchen Pei added a comment - > Is stable (non-sporadic) and leads to: It is because this commit (0df4651c13d68f1c228986f940d8ea4a226132f5) does not contain the fix for MDEV-33494 , which I can confirm fixes the problem reported in this comment.
            ycp Yuchen Pei added a comment -

            > Increased prio as this bug seems to highlight some core Spider engine functionality/sql modes compatibility issues.

            I believe all the sql mode compatibility issues with spider init have been fixed by MDEV-33584, so I have lowered the prio.

            I also ran tests in the description, the first comment and the second comment. All passed (with some expected -error's of course) without DDL LOG errors.

            ycp Yuchen Pei added a comment - > Increased prio as this bug seems to highlight some core Spider engine functionality/sql modes compatibility issues. I believe all the sql mode compatibility issues with spider init have been fixed by MDEV-33584 , so I have lowered the prio. I also ran tests in the description, the first comment and the second comment. All passed (with some expected -error 's of course) without DDL LOG errors.
            Roel Roel Van de Paar added a comment - - edited

            None of the testcases in this issue still reproduces. Closing as fixed.

            Roel Roel Van de Paar added a comment - - edited None of the testcases in this issue still reproduces. Closing as fixed.

            People

              Unassigned Unassigned
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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