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

Details

    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

          Activity

            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.

            holyfoot Alexey Botchkov added a comment - 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.
            holyfoot Alexey Botchkov added a comment - https://github.com/MariaDB/server/commit/4cf97ba683bbf54c20380f30ecb41104ea44ef67

            OK to push.

            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - OK to push.

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

            Fixed in bb-11.0-midenok-MDEV-25292

            midenok Aleksey Midenkov added a comment - Fixed in bb-11.0-midenok- MDEV-25292

            People

              midenok Aleksey Midenkov
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              7 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.