[MDEV-30984] Online ALTER table is denied with non-informative error messages Created: 2023-04-01  Updated: 2023-08-16  Resolved: 2023-08-16

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: N/A
Fix Version/s: 11.2.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Nikita Malyavin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-16329 Engine-independent online ALTER TABLE Closed
Relates
relates to MDEV-30906 Variable alter_algorithm=COPY takes p... Stalled
relates to MDEV-28808 Test MDEV-16329 (ALTER ONLINE TABLE) ... Stalled

 Description   

serg wrote elsewhere:

Conditions under which online alter works are getting more and more complicated, lots of cases will disable online mode. Perhaps it'd make sense to issue a warning of why exactly online was disabled? Otherwise, I suspect, users will be hardly able to navigate this maze of conditions.

So far this extra diagnostics has not been implemented, error messages still look the way they did before:

bb-11.0-oalter fd06124e66c

MariaDB [test]> alter online table t1 rename to t1a;
ERROR 1845 (0A000): LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE
MariaDB [test]> show warnings;
+-------+------+--------------------------------------------------------------------------+
| Level | Code | Message                                                                  |
+-------+------+--------------------------------------------------------------------------+
| Error | 1845 | LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE |
+-------+------+--------------------------------------------------------------------------+
1 row in set (0.000 sec)



 Comments   
Comment by Elena Stepanova [ 2023-04-01 ]

It turns out that ALTER ONLINE TABLE is not the same online alter as online alter in MDEV-16329, so the example in the description doesn't exactly relate to it (you can't do ALTER ONLINE TABLE ... RENAME but you can do ALTER ... RENAME .. ALGORITHM=COPY, LOCK=NONE); but the question of non-detailed error messages remain the same, regardless the example.

Comment by Elena Stepanova [ 2023-04-10 ]

I think we do need better diagnostics. The error message for 1845 ER_ALTER_OPERATION_NOT_SUPPORTED, as in the description, is at least still correct, even if not very informative ("LOCK=NONE is not supported for this operation. Try ..."), but the more generic 1846 ER_ALTER_OPERATION_NOT_SUPPORTED_REASON is now plain wrong: "LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try ...".

That is, instead of referring to a specific operation, as 1845 does, this one claims that LOCK=NONE is not supported for the COPY algorithm at all, which is of course not true anymore.

Besides, it can be very confusing, e.g. when you can do ALTER, but cannot do the same ALTER with IGNORE:

MariaDB [test]> alter table t force, algorithm=copy, lock=none;
Query OK, 2 rows affected (0.065 sec)                      
Records: 2  Duplicates: 0  Warnings: 0
 
MariaDB [test]> alter ignore table t force, algorithm=copy, lock=none;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED

Comment by Sergei Golubchik [ 2023-07-12 ]

dc7136481bf5 looks ok

Generated at Thu Feb 08 10:20:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.