Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15733

Crash during Spider-related CREATE TABLE/ALTER TABLE can leave orphaned entry in mysql.spider_tables

    XMLWordPrintable

    Details

      Description

      If MariaDB crashes during a CREATE TABLE/ALTER TABLE statement that either creates a new spider table or converts an existing table to use the spider engine, then there can be orphaned entries in mysql.spider_tables that reference the spider table that was never actually created.

      At that point, if the user tries to recreate the table again, then they run into an error that is difficult to understand:

      e.g. if they run this CREATE TABLE statement:

      USE spider_test;
       
      CREATE TABLE spider_tab (
         id int auto_increment primary key,
         str varchar(50)
      ) ENGINE=Spider COMMENT='wrapper "mysql", srv "srv1", table "local_tab"';
      

      Then they would see the following error:

      ERROR 1062 (23000): Duplicate entry 'spider_test-spider_tab-0' for key 'PRIMARY'
      

      To workaround the problem, the orphaned row needs to be deleted:

      DELETE FROM mysql.spider_tables WHERE db_name='spider_test' AND table_name='spider_tab';
      

      And then the table can be created.

      To fix this, maybe Spider should do some kind of validation at server startup, like making sure that all rows listed in mysql.spider_tables reference tables that actually exist?

        Attachments

          Activity

            People

            Assignee:
            jacob-mathew Jacob Mathew (Inactive)
            Reporter:
            GeoffMontee Geoff Montee
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration