Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
None
-
None
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
- is duplicated by
-
-
- Open
-