MySQL Bugs: #65466: Options ALGORITHM=<> and LOCK=<> do not work

Bug #65466	Options ALGORITHM=<> and LOCK=<> do not work
Submitted:	30 May 2012 19:28	Modified:	11 Jun 2012 14:46
Reporter:	Mario Beck	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Packaging	Severity:	S3 (Non-critical)
Version:	5.6.5 M8	OS:	Linux (Oracle Linux 6)
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	Online DDL

Description:
ALTER TABLE does not accept the new options ALGORITHM=INPLACE or LOCK=NONE.
DROP FOREIGN KEY is not an INPLACE operation.

How to repeat:
mysql> CREATE TABLE t2 (i int);
Query OK, 0 rows affected (0.06 sec)

mysql> ALTER TABLE t2 ADD INDEX bla (i), LOCK=NONE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOCK=NONE' at line 1

mysql> ALTER TABLE t2 ADD INDEX bla (i), ALGORITHM=INPLACE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALGORITHM=INPLACE' at line 1

mysql> ALTER TABLE t2 ADD INDEX bla (i);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> INSERT INTO t2 VALUES (42);
Query OK, 1 row affected (0.01 sec)
mysql> CREATE TABLE t3 (k int, CONSTRAINT myc FOREIGN KEY (k) REFERENCES t2 (i));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t3 VALUES (42);
Query OK, 1 row affected (0.01 sec)

mysql> ALTER TABLE t3 DROP FOREIGN KEY myc;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

Records:1 shows, that it is not INPLACE but a copy operation. According to RefMan dropping a FK should be INPLACE.

Suggested fix:
It seems, online DDL is not fully implemented as documented in the RefMan.

Thank you for the report.

Which storage engine do you use? Please provide output of SHOW CREATE TABLE t1 and SHOW CREATE TABLE t3

I used the best possible storage engine ;-) 

mysql> SHOW CREATE TABLE t3;
+-------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `k` int(11) DEFAULT NULL,
  KEY `myc` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='bla' |
+-------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t2;
+-------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `i` int(11) DEFAULT NULL,
  KEY `bla` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Thank you for the feedback.

This is packaging problem: in development tree everything works as described:

CREATE TABLE t2 (i int) engine=innodb;
ALTER TABLE t2 ADD INDEX bla (i), LOCK=NONE;
ALTER TABLE t2 ADD INDEX bla2 (i), ALGORITHM=INPLACE;
ALTER TABLE t2 ADD INDEX bla3 (i);
INSERT INTO t2 VALUES (42);
CREATE TABLE t3 (k int, CONSTRAINT myc FOREIGN KEY (k) REFERENCES t2 (i)) engine=innodb;
INSERT INTO t3 VALUES (42);
ALTER TABLE t3 DROP FOREIGN KEY myc;
affected rows: 0
info: Records: 0  Duplicates: 0  Warnings: 0

I did something wrong in the bug meta data.
OS is OL6. I downloaded the generic linux package as a TAR package.
(There is no TAR packaging for OL6. Only a TAR of RPMs.

mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.6.5-m8-log |
+--------------+

Hello!

LOCK and ALGORITHM options are not available in the
current development release (5.6.5-m8).
They will be available in 5.6.6-m9. 

If you want to try LOCK/ALGORITHM now, you will have
to download a server snapshot. See http://labs.mysql.com/

Same problem here, with MysQL 5.6.7 (Linux Binary, TAR archive). ALGORITHM and LOCK are unrecognized.

node1 (sakila) > SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.6.7-rc-log |
+--------------+

node1 (sakila) > alter table sakila.rental row_format=compact algorithm=inplace lock=none;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'algorithm=inplace lock=none' at line 1

Hello!

ALTER TABLE options are comma separated.
See: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

mysql> alter table t1 row_format=compact, algorithm=inplace, lock=none;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

@Jon,

Thank you (and to Marc Alff who also pointed this out for me). The syntax is now accepted.

Maybe I should add, though, that the commas make for an inconsistent syntax with what sytax we are usually used to with MySQL -- but no big deal.

Comma separation for ALTER TABLE is not new. It's always (at least 3.23->)
been used to separate different operations you want to do with the
same ALTER statement: E.g. ALTER TABLE t1 RENAME TO t2, ADD INDEX i1(a);

Having comma also for LOCK and ALGORITHM clauses signifies that
LOCK/ALGORITHM does not apply to a single operation, but to the whole
ALTER TABLE statement. If we did not use comma here, we would have ended
up with e.g. ALTER TABLE t1 RENAME TO t2, ADD INDEX i1(a) LOCK=SHARED;
or even worse: ALTER TABLE t1 ADD INDEX i1(a) LOCK=SHARED, RENAME TO t2;
where it looks like LOCK just applies to ADD INDEX.

This is different from CREATE INDEX and DROP INDEX. These statements just
do a single operation, create/drop of one index. Here we do not use comma.
http://dev.mysql.com/doc/refman/5.6/en/create-index.html

CREATE INDEX requires NOT to use commas.
ALTER TABLE .. ADD INDEX requires commas...

So, this works:

------
drop table if exists t1;
create table t1(a int,b int,c int,d int)engine=innodb;
create index a on t1(a) algorithm=inplace;
create index b on t1(b) lock=exclusive;
create index c on t1(c) lock=exclusive algorithm=inplace;
alter table t1 add index d(d),algorithm=inplace,lock=exclusive;
------