MySQL Bugs: #95231: LOCK=SHARED rejected contrary to specification

Bug #95231	LOCK=SHARED rejected contrary to specification
Submitted:	2 May 2019 22:10	Modified:	15 Aug 2019 14:53
Reporter:	monty solomon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Using LOCK=SHARED with REMOVE PARTITIONING as described in the documentation is rejected with an error.

The documentation states that the syntax is
https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

The LOCK is listed in the alter_specification section
  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

Various partition changes are listed in the alter_specification section
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING
  | UPGRADE PARTITIONING

How to repeat:
When I try to use the LOCK and a comma in various places it is rejected

mysql> ALTER TABLE foo REMOVE PARTITIONING, LOCK=SHARED;
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=SHARED' at line 1

mysql> ALTER TABLE foo REMOVE PARTITIONING LOCK=SHARED;
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=SHARED' at line 1

mysql> ALTER TABLE foo, LOCK=SHARED, REMOVE PARTITIONING;
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=SHARED, REMOVE PARTITIONING' at line 1

mysql> ALTER TABLE foo, LOCK=SHARED REMOVE PARTITIONING;
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=SHARED REMOVE PARTITIONING' at line 1

mysql> ALTER TABLE foo LOCK=SHARED, REMOVE PARTITIONING;
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 'REMOVE PARTITIONING' at line 1

Suggested fix:
Update the server to expect/require the commas or update the documentation.

It works without any commas

mysql> ALTER TABLE foo LOCK=SHARED REMOVE PARTITIONING;
Query OK, 20 rows affected (0.05 sec)
Records: 20  Duplicates: 0  Warnings: 0

Some partition specific operations claim that LOCK can't be used

mysql> ALTER TABLE foo LOCK=SHARED, UPGRADE PARTITIONING;
ERROR 1846 (0A000): LOCK=NONE/SHARED/EXCLUSIVE is not supported. Reason: Partition specific operations do not yet support LOCK/ALGORITHM. Try LOCK=DEFAULT.

steps to create the table and reproduce the errors

DROP TABLE IF EXISTS foo;

CREATE TABLE foo (
  id int(11) NOT NULL AUTO_INCREMENT,
  email varchar(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY email_id (email, id)
) ENGINE=InnoDB
PARTITION BY HASH (id)
PARTITIONS 25;

SHOW CREATE TABLE foo\G

ALTER TABLE foo REMOVE PARTITIONING, LOCK=SHARED;

ALTER TABLE foo REMOVE PARTITIONING LOCK=SHARED;

ALTER TABLE foo, LOCK=SHARED, REMOVE PARTITIONING;

ALTER TABLE foo, LOCK=SHARED REMOVE PARTITIONING;

ALTER TABLE foo LOCK=SHARED, REMOVE PARTITIONING;

ALTER TABLE foo LOCK=SHARED REMOVE PARTITIONING;

SHOW CREATE TABLE foo\G

Here is an example where it works as described using LOCK and a comma with a partition. It doesn't get the ERROR 1846 produced by using UPGRADE PARTITIONING with LOCK.

mysql> ALTER TABLE FOO LOCK=SHARED, CHECK PARTITION ALL\G
*************************** 1. row ***************************
   Table: test2.foo
      Op: check
Msg_type: status
Msg_text: OK
1 row in set (0.00 sec)

Independent of the LOCK, it appears that the REMOVE PARTITIONING must appear at the end of the ALTER TABLE statement and it must be used without a comma.

DROP TABLE IF EXISTS foo;

CREATE TABLE foo (
  id int(11) NOT NULL AUTO_INCREMENT,
  email varchar(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY email_id (email, id)
) ENGINE=InnoDB
PARTITION BY HASH (id)
PARTITIONS 25;

mysql> ALTER TABLE foo DROP INDEX email_id, REMOVE PARTITIONING, ADD UNIQUE KEY(email) ;
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 'REMOVE PARTITIONING, ADD UNIQUE KEY(email)' at line 1

mysql> ALTER TABLE foo REMOVE PARTITIONING, DROP INDEX email_id, ADD UNIQUE KEY(email) ; 
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 ' DROP INDEX email_id, ADD UNIQUE KEY(email)' at line 1

mysql> ALTER TABLE foo DROP INDEX email_id, ADD UNIQUE KEY(email), REMOVE PARTITIONING; 
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 'REMOVE PARTITIONING' at line 1

mysql> ALTER TABLE foo DROP INDEX email_id, ADD UNIQUE KEY(email) REMOVE PARTITIONING;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

Hi,

Thank you for your bug report.

I have tested all your examples and I have got exactly the same result. Also, the only way that LOCK clause could work for me was without any commans, like this:

ALTER TABLE foo ADD UNIQUE KEY (email) , LOCK = SHARED REMOVE PARTITIONING;

This is a code bug. Thank you for your contribution.

Verified as reported.

Posted by developer:
 
The documentation and the implementation is not aligned. This was implemented as: REMOVE PARTITIONING and PARTITION BY clauses need to go last in ALTER TABLE and they are separated by space and not comma. Changing the parser will create extra complexity in code and might be confusing to existing users. Moving bug to docs.

Staale,

Thank you very much. I have looked at parser and saw the difference, which led me to believe that this was a code bug.

Changing the category .......

ALTER TABLE ... EXCHANGE PARTITION ... has the same problem.

See this example.

https://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html
https://dev.mysql.com/doc/refman/5.7/en/partitioning-management-exchange.html

Original command as belows.

ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

When I try to add algorithm and lock clause, 1st query fail, and 2nd query success.

mysql [localhost] {msandbox} (test) > ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2, algorithm=copy, lock=none; -- 1st: NG
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=copy, lock=none' at line 1
mysql [localhost] {msandbox} (test) > ALTER TABLE e algorithm=copy, lock=none, EXCHANGE PARTITION p0 WITH TABLE e2; -- 2nd: OK
Query OK, 0 rows affected (0.06 sec)

Also need to describe in the manual.

Thank you, Meiji,

I agree that this too should be documented.

Fixed in the MySQL Manual versions 5.5 and up, in mysqldoc rev 63048.

It appears that the real issue was that the partitioning options for that statement were not actually shown as such in BNF, leading to confusion with the alter options.

Closed.

Thank you, Jon .......