MySQL Bugs: #31708: Cluster crashes on cartesian product

Bug #31708	Cluster crashes on cartesian product
Submitted:	19 Oct 2007 0:49	Modified:	7 Oct 2009 14:15
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	mysql-5.1	OS:	Linux
Assigned to:	Martin Skold	CPU Architecture:	Any
Tags:	5.1.22rc 5.0.48

Description:
When running a query that generates a large cartesian product, the data nodes crash with the following error:

2007-07-05 17:52:05 [MgmSrvr] ALERT -- Node 2: Forced node shutdown completed. Initiated by signal 0. Caused by error 6050: 'WatchDog terminate, internal error or massive overload on the machine running this node(Internal error, programming error or missing error message, please report a bug).

How to repeat:
Create three tables with > 50k records each, and do cartesian join:

CREATE TABLE t1 (
 i INT AUTO_INCREMENT PRIMARY KEY,
 j INT
) ENGINE = NDB;

INSERT INTO t1 (j) values (1);
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;

CREATE TABLE t2 ENGINE = NDB AS SELECT * FROM t1;
CREATE TABLE t3 ENGINE = NDB AS SELECT * FROM t1;

SELECT * FROM t1, t2, t3 WHERE t1.i != t2.i AND t1.i != t3.i;

Suggested fix:
Don't crash data nodes.  Provide alternate means to prevent overload.

Node #2 error log

Attachment: ndb_2_error.log (text/x-log), 1.04 KiB.

Config, error, cluster and trace logs

Attachment: cartesian.tar.gz (application/x-gzip, text), 38.96 KiB.

Repeatable with fixed SQL below, on both 5.1.22RC and 5.0.48-cluster.
Will attach ndb_error_reporter logs for both versions.

-----

CREATE TABLE t1 (
 i INT AUTO_INCREMENT PRIMARY KEY,
 j INT
) ENGINE = NDB;

INSERT INTO t1 (j) values (1);
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;
INSERT INTO t1 (j)  SELECT j FROM t1;

CREATE TABLE t2 ENGINE = NDB AS SELECT * FROM t1;
CREATE TABLE t3 ENGINE = NDB AS SELECT * FROM t1;

SELECT * FROM t1, t2, t3 WHERE t1.i != t2.i AND t1.i != t3.i;

5.1.22 rc logs

Attachment: ndb_error_report_20071021025315_5122rc.tar.bz2 (application/x-bzip2, text), 5.07 KiB.

5.0.48-cluster logs

Attachment: ndb_error_report_20071021022913_5048.tar.bz2 (application/x-bzip2, text), 4.25 KiB.

5.1.22rc
mysql> SELECT * FROM t1, t2, t3 WHERE t1.i != t2.i AND t1.i != t3.i;
Killed

2007-10-21 01:56:48 [MgmSrvr] INFO     -- Node 3: Local checkpoint 7 started. Keep GCI = 55 oldest restorable GCI = 56
2007-10-21 02:01:23 [MgmSrvr] ALERT    -- Node 1: Node 4 Disconnected
2007-10-21 02:01:23 [MgmSrvr] WARNING  -- Node 3: Node 4 missed heartbeat 2
2007-10-21 02:01:23 [MgmSrvr] ALERT    -- Node 1: Node 4 Disconnected
2007-10-21 02:01:25 [MgmSrvr] ALERT    -- Node 3: Node 5 Disconnected
2007-10-21 02:01:25 [MgmSrvr] INFO     -- Node 3: Communication to Node 5 closed
2007-10-21 02:01:25 [MgmSrvr] ALERT    -- Node 1: Node 3 Disconnected
2007-10-21 02:01:29 [MgmSrvr] INFO     -- Node 1: Node 3 Connected
2007-10-21 02:01:29 [MgmSrvr] INFO     -- Node 1: Node 4 Connected
2007-10-21 02:01:29 [MgmSrvr] INFO     -- Node 3: Started arbitrator node 1 [ticket=1ffb0002bfdd3230]

5.0.48-cluster
mysql> SELECT * FROM t1, t2, t3 WHERE t1.i != t2.i AND t1.i != t3.i;
Killed

2007-10-21 02:18:27 [MgmSrvr] INFO     -- Node 3: Local checkpoint 7 started. Keep GCI = 59 oldest restorable GCI = 60
2007-10-21 02:24:56 [MgmSrvr] ALERT    -- Node 1: Node 3 Disconnected
2007-10-21 02:24:57 [MgmSrvr] ALERT    -- Node 1: Node 4 Disconnected
2007-10-21 02:24:57 [MgmSrvr] ALERT    -- Node 1: Node 3 Disconnected
2007-10-21 02:24:57 [MgmSrvr] ALERT    -- Node 1: Node 4 Disconnected
2007-10-21 02:25:02 [MgmSrvr] ALERT    -- Node 3: Forced node shutdown completed. Initiated by signal 9.
2007-10-21 02:25:02 [MgmSrvr] ALERT    -- Node 4: Forced node shutdown completed. Initiated by signal 9.

Can we get an update on where this bug report stands?

As far as i can tell this is not a cluster bug but actually a mysql client consuming all memory and eventually causing swapping on a machine that is also running a ndbd node ... which is the main reason for the "have exclusive data nodes without anything else running on these machines" recommendation ...

See also bug #38448 ...

When running on telco-6.3 I don't see I crash, but do run out of memory
I mysqld. It is handled without a crash though: 

CREATE TABLE t1 (
 i INT AUTO_INCREMENT PRIMARY KEY,
 j INT
) ENGINE = NDB;

INSERT INTO t1 (j) values (1);
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;
INSERT INTO t1 SELECT NULL,j FROM t1;

CREATE TABLE t2 ENGINE = NDB AS SELECT * FROM t1;
CREATE TABLE t3 ENGINE = NDB AS SELECT * FROM t1;

SELECT * FROM t1, t2, t3 WHERE t1.i != t2.i AND t1.i != t3.i;
MySQL/mysql-5.1-telco-6.3/client/.libs/lt-mysql: Out of memory at line 204, 'my_alloc.c'
MySQL/mysql-5.1-telco-6.3/client/.libs/lt-mysql: needed 3612384 byte (3528k), memory in use: 3204390021 bytes (3129288k)
ERROR 2008 (HY000): MySQL client ran out of memory