MySQL Bugs: #114085: Incorrect SQL example given for ALGORITHM=INSTANT

Bug #114085	Incorrect SQL example given for ALGORITHM=INSTANT
Submitted:	21 Feb 2024 18:28	Modified:	22 Feb 2024 22:47
Reporter:	Jason Fane	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When we use the ALGORITHM=INSTANT, it is not needed to specify a LOCK type as the only LOCK type supported is "Default" this is documented on the blog entry here:

https://dev.mysql.com/blog-archive/mysql-8-0-innodb-now-supports-instant-add-column/

However, on the web page below, there's a SQL example provided which has "ALGORITHM=INSTANT, LOCK=NONE;" which is not possible, it only produces an error.

SQL: ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INSTANT, LOCK=NONE;

Source: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

When running this example you will get an error message similar to:

mysql> ALTER TABLE test CHANGE old_test new_test varchar(16) DEFAULT NULL, ALGORITHM=INSTANT, LOCK=NONE;
ERROR 1221 (HY000): Incorrect usage of ALGORITHM=INSTANT and LOCK=NONE/SHARED/EXCLUSIVE

How to repeat:
n/a

Suggested fix:
Alter the SQL to remove ", LOCK=NONE;"

SQL AFTER: SQL: ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INSTANT

Hello Jason Fane,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
Thank you Jason for reporting this, it's now fixed in the documentation source and will show up online soon.