| Bug #71000 | update/delete where col like (x is null) query hangs on cluster | ||
|---|---|---|---|
| Submitted: | 25 Nov 2013 13:18 | Modified: | 26 Nov 2013 6:13 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | 7.3.3, 7.3.2, 7.2.14, 7.1.29 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[25 Nov 2013 13:18]
MySQL Verification Team
just wanted to catch bug #71000 :)
[25 Nov 2013 19:59]
MySQL Verification Team
more samples.
Attachment: bug71000.txt (text/plain), 12.61 KiB.
[26 Nov 2013 6:13]
MySQL Verification Team
Hello Shane, Thank you for the bug report and test case. Verified as described. Thanks, Umesh
[29 Nov 2013 17:41]
MySQL Verification Team
// 7.3.2 - affected
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.6.11-ndb-7.3.2-log |
+----------------------+
1 row in set (0.00 sec)
mysql> drop table if exists d;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> create table d(a varchar(1),b bigint,c varchar(255),d int,key(d),key(a),key(b),unique key(c,d))engine=ndbcluster default character set=utf8;
insert into d values ('-',1,null,null),('1',0,'hi',3);
update `d` set `d`=4 where (-3.3 like (`d` is null));
Query OK, 0 rows affected (2.36 sec)
mysql> insert into d values ('-',1,null,null),('1',0,'hi',3);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> update `d` set `d`=4 where (-3.3 like (`d` is null));
^^ hangs forever
[29 Nov 2013 17:55]
MySQL Verification Team
// 7.2.14 is also affected
mysql> select version();
+---------------------------------------------------+
| version() |
+---------------------------------------------------+
| 5.5.34-ndb-7.2.14-cluster-commercial-advanced-log |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop table if exists d;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> create table d(a varchar(1),b bigint,c varchar(255),d int,key(d),key(a),key(b),unique key(c,d))engine=ndbcluster default character set=utf8;
insert into d values ('-',1,null,null),('1',0,'hi',3);
update `d` set `d`=4 where (-3.3 like (`d` is null));
Query OK, 0 rows affected (2.34 sec)
mysql> insert into d values ('-',1,null,null),('1',0,'hi',3);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> update `d` set `d`=4 where (-3.3 like (`d` is null));
^^^ hangs forever
[29 Nov 2013 18:26]
MySQL Verification Team
// 7.1.29 also affected
mysql> select version();
+-----------------------------------+
| version() |
+-----------------------------------+
| 5.1.72-ndb-7.1.29-cluster-com-log |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> drop table if exists d;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> create table d(a varchar(1),b bigint,c varchar(255),d int,key(d),key(a),key(b),unique key(c,d))engine=ndbcluster default character set=utf8;
insert into d values ('-',1,null,null),('1',0,'hi',3);
update `d` set `d`=4 where (-3.3 like (`d` is null));
Query OK, 0 rows affected (2.22 sec)
mysql> insert into d values ('-',1,null,null),('1',0,'hi',3);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> update `d` set `d`=4 where (-3.3 like (`d` is null));
^^^ hangs
Description: The query hangs even though table contains a couple of rows. | 1787 | updating | delete from `a` where ( `a` .`a` like ( `a` . `c` is null ) ) | 1166 | updating | delete from `a` where ( `a` .`a` like ( `a` . `d` is null ) ) | 503 | updating | update `a` set `b` = `d` where ( ( `a` ) ) like ( ( `a` . `c` is null ) | 444 | updating | delete ignore from `a` where ( `a` .`a` like ( `a` . `c` is null ) ) | 119 | updating | update `d` set `d`=4 where (-3.3 like (`d` is null)) How to repeat: drop table if exists d; create table d(a varchar(1),b bigint,c varchar(255),d int,key(d),key(a),key(b),unique key(c,d))engine=ndbcluster default character set=utf8; insert into d values ('-',1,null,null),('1',0,'hi',3); update `d` set `d`=4 where (-3.3 like (`d` is null));