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

Lockless schema updates, Alter table LOCK=NONE

    XMLWordPrintable

Details

    Description

      Customer is requesting the return of online rebuild with indexed virtual columns, ability to alter tables LOCK=NONE. Customer states, "We are a saas solution with 99.95 SLA expectations. Schema updates on larger tables get mirky overall in terms of maintaining that level of service. An alter table that takes over 45s will cause errors in the system and that goes against the SLA so hence the concern."

      Perhaps this check could be toggled on and off with global variable.

      ======= VERSIONS 10.4 and later =======
       
      CREATE TABLE products( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(255) NOT NULL,
             price DECIMAL(9,2) NOT NULL, stock INTEGER NOT NULL, attr VARCHAR(1024),
             attr_color VARCHAR(32) AS (JSON_VALUE(attr, '$.color')) virtual,
             CHECK (attr IS NULL OR JSON_VALID(attr)));
      Query OK, 0 rows affected (0.012 sec)
       
      CREATE INDEX COLOR_IDX ON products(attr_color);
      Query OK, 0 rows affected (0.016 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      INSERT INTO products (id, name, price, stock, attr) 
      VALUES (0, 'Jeans', 10.5, 165, '{"size": 42, "color": "white"}');
      Query OK, 1 row affected (0.006 sec)
       
      ALTER TABLE products ADD COLUMN IF NOT EXISTS stocks INTEGER NOT NULL, LOCK=NONE;
      ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED
       
      ======= VERSION 10.3 and previous =======
       
      ALTER TABLE products ADD COLUMN IF NOT EXISTS stocks INTEGER NOT NULL, LOCK=NONE;
      Query OK, 0 rows affected (0.010 sec)
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              edward Edward Stoever
              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.