Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

LOCK=NONE usage is not supported in PXC-5.7 and above

Description

We are experiencing different behavior between two environments running 5.7.31-34-57-log and 5.7.32-35-57-log.

The environment running .32 cannot process this DDL:

Server version: 5.7.32-35-57-log Percona XtraDB Cluster (GPL), Release rel35, Revision 2055835, WSREP version 31.47, wsrep_31.47 [dbadmin@stage]> CREATE TABLE test_a ( -> a_id int(10) unsigned NOT NULL AUTO_INCREMENT, -> b_id varchar(60) COLLATE utf8_unicode_ci NOT NULL, -> c_id varchar(20) COLLATE utf8_unicode_ci NOT NULL, -> PRIMARY KEY (a_id), -> UNIQUE KEY uniq_b_c_id (b_id,c_id) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Query OK, 0 rows affected (0.02 sec) [dbadmin@stage]> ALTER TABLE test_a ADD state enum('new','active','closed') NOT NULL DEFAULT 'new' AFTER c_id, LOCK = NONE; ERROR 1845 (0A000): LOCK=NONE is not supported for this operation. Try LOCK=SHARED. [dbadmin@stage]> QUIT; Bye.

This is completely unexpected, based on the information in the Online DDL documentation. This bug does not exist in .31:

Server version: 5.7.31-34-57-log Percona XtraDB Cluster (GPL), Release rel34, Revision d76a6e8, WSREP version 31.45, wsrep_31.45 [dbadmin@test]> CREATE TABLE test_a ( -> a_id int(10) unsigned NOT NULL AUTO_INCREMENT, -> b_id varchar(60) COLLATE utf8_unicode_ci NOT NULL, -> c_id varchar(20) COLLATE utf8_unicode_ci NOT NULL, -> PRIMARY KEY (a_id), -> UNIQUE KEY uniq_b_c_id (b_id,c_id) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Query OK, 0 rows affected (0.01 sec) [dbadmin@test]> ALTER TABLE test_a ADD state enum('new','active','closed') NOT NULL DEFAULT 'new' AFTER c_id, LOCK = NONE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 [dbadmin@test]> QUIT; Bye

pt-config-diff does not show any appreciable difference between the two environments besides the server version. These are Galera clusters, running 3 nodes each. Here are the charset/collation settings:

[dbadmin@stage]> SHOW VARIABLES LIKE '%char%'; +---------------------------+---------------------------------------------+ | Variable_name | Value | +---------------------------+---------------------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/percona-xtradb-cluster/charsets/ | | ft_query_extra_word_chars | OFF | +---------------------------+---------------------------------------------+ 9 rows in set (0.00 sec) [dbadmin@stage]> SHOW VARIABLES LIKE '%collat%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_unicode_ci | | collation_database | utf8_unicode_ci | | collation_server | utf8mb4_unicode_ci | +----------------------+--------------------+ 3 rows in set (0.01 sec)

Reduction of the test case or switching to utf8mb4 on the table doesn't help, either:

[dbadmin@stage]> CREATE TABLE test_a ( -> a_id int(10) unsigned NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (a_id) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Query OK, 0 rows affected (0.01 sec) [dbadmin@stage]> ALTER TABLE test_a ADD state enum('new','active','closed') NOT NULL DEFAULT 'new' AFTER a_id, LOCK = NONE; ERROR 1845 (0A000): LOCK=NONE is not supported for this operation. Try LOCK=SHARED. [dbadmin@stage]> ALTER TABLE test_a ADD state VARCHAR(10), LOCK=NONE; ERROR 1845 (0A000): LOCK=NONE is not supported for this operation. Try LOCK=SHARED. [dbadmin@stage-pex-m epay]> ALTER TABLE test_a ADD state BOOLEAN, LOCK=NONE; ERROR 1845 (0A000): LOCK=NONE is not supported for this operation. Try LOCK=SHARED. [dbadmin@stage]> DROP TABLE test_a; Query OK, 0 rows affected (0.01 sec) [dbadmin@stage]> CREATE TABLE test_a ( -> a_id int(10) unsigned NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (a_id) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; Query OK, 0 rows affected (0.01 sec) [dbadmin@stage]> ALTER TABLE test_a ADD state BOOLEAN, LOCK=NONE; ERROR 1845 (0A000): LOCK=NONE is not supported for this operation. Try LOCK=SHARED. [dbadmin@stage]>

Environment

None

Smart Checklist

Activity

puneet.kaushik November 2, 2021 at 7:41 AM

Jira Bot July 2, 2021 at 10:56 AM

To: Former user
CC:

Hi, I'm jira-bot, Percona's Jira automation tool. I've detected that someone from
Percona has made an edit to the Summary field of an issue that you reported.

I'm not sentient (yet) so I'm not sure whether the person fixed a typo, changed
a few words, or completely rewrote the text. In any case, it is Percona Engineering's
intention to make the Summary and Description of an issue as accurate as possible
so that we're fixing the actual problem you're encountering, and to avoid
misunderstandings about symptoms and causes.

If the current Summary does not accurately reflect the problem you are reporting,
or if you feel the change was otherwise inappropriate in some way, please add a
new comment explaining things and we'll address it as soon as we can.

This message will be added only once per issue, regardless of how many times
the Summary is edited.

message-code:summary-edited

Lalit Choudhary April 1, 2021 at 7:10 AM

As discussed with   This locking behavior needs to be documented for PXC

Venkatesh Prasad February 18, 2021 at 8:47 AM

Shared lock is acquired only for clustered environments (when galera is loaded). So, online DDLs in non-clustered environments work as documented in the above link.

Brendan Byrd February 17, 2021 at 4:09 PM

Is the shared lock applied against tables in non-clustered (no Galera or TOI) environments? Galera in TOI mode already locks up the whole cluster, so dropping the LOCK=NONE is a minor inconvenience here.
However, changing this behavior for non-Galera clusters would fundamentally break expectations of how Online DDL works in MySQL.

We use both kinds of environments, so I wanted to confirm.

Done

Details

Assignee

Reporter

Time tracking

3h 50m logged

Components

Fix versions

Affects versions

Priority

Smart Checklist

Created February 16, 2021 at 4:53 PM
Updated March 6, 2024 at 9:14 PM
Resolved July 7, 2021 at 2:06 PM

Flag notifications