MySQL Bugs: #70863: Wrong error message on a failed ALTER .. LOCK=NONE attempt

Bug #70863	Wrong error message on a failed ALTER .. LOCK=NONE attempt
Submitted:	8 Nov 2013 14:50	Modified:	8 Nov 2013 23:26
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.6.14	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When ... ALTER TABLE .. LOCK=NONE fails because concurrent reads/writes are not supported, it produces the following error message:

ERROR 1845 (0A000): LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.

It happens even if LOCK=SHARED is actually supported.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i INT) ENGINE=MyISAM;
--error ER_ALTER_OPERATION_NOT_SUPPORTED
ALTER TABLE t1 MODIFY i INT DEFAULT '0', LOCK=NONE;
ALTER TABLE t1 MODIFY i INT DEFAULT '0', LOCK=SHARED;

Thank you for the bug report.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.15-debug Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > USE Sx
Database changed
mysql 5.6 > CREATE TABLE t1 (i INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql 5.6 > ALTER TABLE t1 MODIFY i INT DEFAULT '0', LOCK=NONE;
ERROR 1845 (0A000): LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
mysql 5.6 > ALTER TABLE t1 MODIFY i INT DEFAULT '0', LOCK=SHARED;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

Hi Elena,

The reason this happens is that if you explicitly specify LOCK=SHARED, we 
switch to the COPY algorithm rather than INPLACE since COPY supports SHARED.

ALTER TABLE t1 MODIFY i INT DEFAULT '0', LOCK=NONE;
fails since neither COPY nor INPLACE supports NONE for this operation.

ALTER TABLE t1 MODIFY i INT DEFAULT '0', LOCK=SHARED;
works, but uses COPY.

ALTER TABLE t1 MODIFY i INT DEFAULT '1';
ALTER TABLE t1 MODIFY i INT DEFAULT '2', LOCK=EXCLUSIVE;
both works and uses INPLACE.

If you insert a row into r1 and add --enable_info to the MTR test script,
you can look for "affected rows" in the output to check if COPY or INPLACE
was used.

Since INPLACE is more efficient than COPY we always try to use INPLACE
unless using COPY allows us to satisfy a LOCK constraint give by the user.

So while the error message might be a bit obtuse, LOCK=EXCLUSIVE is not
such bad advice in this case.