MySQL Bugs: #42873: Heavy INSERT on table with UNIQUE indexes makes ndbd crash

Bug #42873	Heavy INSERT on table with UNIQUE indexes makes ndbd crash
Submitted:	16 Feb 2009 10:03	Modified:	26 May 2009 12:14
Reporter:	Henrik Ingo	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	mysql-5.1-telco-7.0	OS:	Solaris
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
MySQL Cluster 6.4 will crash when a heavy enough parallell INSERT is done against following table:

CREATE TABLE bug_test
(
        PK         INTEGER NOT NULL,
        FK         INTEGER NOT NULL,
        col1               SMALLINT NOT NULL,
        col2        VARCHAR(40) NOT NULL,
        VVALID      BIGINT NOT NULL,     
        FK_col1   SMALLINT,            
        FK_col2  VARCHAR(1000),
        VINVALID    BIGINT,            
        id1     TINYINT,             
        id2         TINYINT,         
        CONSTRAINT PK_test PRIMARY KEY (PK, FK)
) ENGINE=NDB
  PARTITION BY KEY(FK)
;
COMMIT;

CREATE ONLINE UNIQUE INDEX
        UQ_test ON bug_test (col2, col1);
CREATE ONLINE INDEX
        IX_test_INV ON bug_test (VINVALID);
CREATE ONLINE INDEX
        IX_test_VAL ON bug_test (VVALID);
CREATE ONLINE INDEX
        IX_test_CAT ON bug_test (id2);

CREATE ONLINE INDEX
        FK_test ON bug_test (FK);

If the indexes (other than PRIMARY) are not created up-front, the INSERT succeeds, after which you can also create the indexes.

How to repeat:
CREATE TABLE bug_test
(
        PK         INTEGER NOT NULL,
        FK         INTEGER NOT NULL,
        col1               SMALLINT NOT NULL,
        col2        VARCHAR(40) NOT NULL,
        VVALID      BIGINT NOT NULL,     
        FK_col1   SMALLINT,            
        FK_col2  VARCHAR(1000),
        VINVALID    BIGINT,            
        id1     TINYINT,             
        id2         TINYINT,         
        CONSTRAINT PK_test PRIMARY KEY (PK, FK)
) ENGINE=NDB
  PARTITION BY KEY(FK)
;
COMMIT;

CREATE ONLINE UNIQUE INDEX
        UQ_test ON bug_test (col2, col1);
CREATE ONLINE INDEX
        IX_test_INV ON bug_test (VINVALID);
CREATE ONLINE INDEX
        IX_test_VAL ON bug_test (VVALID);
CREATE ONLINE INDEX
        IX_test_CAT ON bug_test (id2);

CREATE ONLINE INDEX
        FK_test ON bug_test (FK);

ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     12 node(s)
id=3    @127.0.0.1  (mysql-5.1.31 ndb-6.4.1, Nodegroup: 0, Master)
id=4    @127.0.0.1  (mysql-5.1.31 ndb-6.4.1, Nodegroup: 0)
id=5    @127.0.0.1  (mysql-5.1.31 ndb-6.4.1, Nodegroup: 1)
id=6    @127.0.0.1  (mysql-5.1.31 ndb-6.4.1, Nodegroup: 1)
id=7    @127.0.0.1  (mysql-5.1.31 ndb-6.4.1, Nodegroup: 2)
id=8    @127.0.0.1  (mysql-5.1.31 ndb-6.4.1, Nodegroup: 2)
id=9    @127.0.0.1  (mysql-5.1.31 ndb-6.4.1, Nodegroup: 3)
id=10   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1, Nodegroup: 3)
id=11   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1, Nodegroup: 4)
id=12   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1, Nodegroup: 4)
id=13   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1, Nodegroup: 5)
id=14   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1, Nodegroup: 5)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)

[mysqld(API)]   24 node(s)
id=15   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)
id=16   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)
id=17   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)
id=18   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)
id=19   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)
id=20   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)
id=21   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)
id=22   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)
id=23   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)
id=24   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)
id=25   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)
id=26   @127.0.0.1  (mysql-5.1.31 ndb-6.4.1)
id=27 (not connected, accepting connect from any host)
id=28 (not connected, accepting connect from any host)
id=29 (not connected, accepting connect from any host)
id=30 (not connected, accepting connect from any host)
id=31 (not connected, accepting connect from any host)
id=32 (not connected, accepting connect from any host)
id=33 (not connected, accepting connect from any host)
id=34 (not connected, accepting connect from any host)
id=35 (not connected, accepting connect from any host)
id=36 (not connected, accepting connect from any host)
id=37 (not connected, accepting connect from any host)
id=38 (not connected, accepting connect from any host)

SunOS hostname-foo 5.10 Generic_138888-01 sun4v sparc SUNW,T5140

INSERT INTO bug_test VALUES ((1, 1, 10, cast((4773840000 + 1) as char(20)), 000000000000, 1 %% 10000, CONCAT('<<<<<< fooxxxxx ', cast(1 as char(666)), ' >>>>>'), 999999999999, 1 %% 64, 3), .... );

-- Each INSERT containing 128 rows in the same statements, 200 clients inserting them
-- above "1" is a running number, all else are constants

Error: Got temporary error 4010 'Node failure caused abort of transaction' from NDBCLUSTER

ndb_mgm says:

Node 13: Forced node shutdown completed. Caused by error 2341: 'Internal program error (failed ndbrequire)(Internal error, programming error or missing error message, please report a bug). Temporary error, restart node'.

(I will attach a bigger file with some log and trace files.)

Suggested fix:
NDB must not crash.

Copypasted config.ini, log and trace files, etc...

Attachment: temp.sql.tar.gz (application/gzip, text), 176.31 KiB.

Note that cluster was running in Diskless=1, that should be the only config.ini parameter that may affect things. (For sure it allows to insert much faster!)

Solution prepared for 7.1.

WL4575 Unique Index Operation TC cleanup fixes a number of bugs in the current unique index mechanisms.
This Worklog is currently targetted at mysql-5.1-telco-7.1.0.

Coding and testing is mostly complete.  Code is under review currently.