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

          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-25292 Atomic CREATE OR REPLACE TABLE

          • Critical - Crashes, loss of data, severe memory leak.
          • In Testing
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29583 Disallow Spider self/dual/multi/cross-referencing tables, consider not executing/skipping/disabling init_connect for Spider user

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 6 older comments
            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 - 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

            holyfoot Alexey Botchkov added a comment - https://github.com/MariaDB/server/commit/4cf97ba683bbf54c20380f30ecb41104ea44ef67
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment -

            OK to push.

            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - OK to push.
            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'

            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'
            midenok Aleksey Midenkov added a comment -

            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.