MySQL Bugs: #70955: In mysql5.6.14-ndb7.3.3, all data node shutdown by any trigger.

Bug #70955	In mysql5.6.14-ndb7.3.3, all data node shutdown by any trigger.
Submitted:	19 Nov 2013 14:39	Modified:	21 Nov 2013 13:26
Reporter:	李源錫	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Packaging	Severity:	S1 (Critical)
Version:	mysql5.6.14-ndb-7.3.3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In mysql5.6.14-ndb7.3.3, all data node shutdown by any trigger

ndb_mgm> Node 3: Forced node shutdown completed. 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). Temporary error, restart node'.
Node 2: Forced node shutdown completed. Caused by error 2305: 'Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s)(Arbitration error). Temporary error, restart node'.

How to repeat:
1. config.ini as follows

I am using physical two data node.
and I am sharing data node and sql node.

cat config.ini
[ndb_mgmd]
HostName=192.168.253.15

[ndbd default]
NoOfReplicas=2

DataMemory=2G
IndexMemory=1G

DataDir=/data/mysql-cluster5.6.14-ndb7.3.3
MaxNoOfConcurrentOperations=1000000

[ndbd]
HostName=192.168.253.121

[ndbd]
HostName=192.168.253.122

[mysqld]
HostName=192.168.253.121

2. my.cnf as folows
cat my.cnf

[MYSQL_CLUSTER]
ndb-connectstring="host=localhost"

[MYSQLD]                        
basedir=/usr/local/mysql5.6.14-ndb7.3.3
datadir=/data/mysql5.6.14-ndb7.3.3

ndbcluster                            
default_storage_engine=ndb

3. login mysql
/usr/local/mysql5.6.14-ndb7.3.3/bin/mysql -uroot -p test

4. run next sql

create table a (
a1 varchar(100) NOT NULL DEFAULT '', 
a2 int(6) DEFAULT '0', UNIQUE KEY idx_b1 (a1)
);

CREATE TABLE b (
b1 char(20) NOT NULL DEFAULT '',
b2 enum('N','Y') NOT NULL DEFAULT 'N' , 
UNIQUE KEY idx_b1 (b1)
);

delimiter //
CREATE TRIGGER a_update BEFORE UPDATE ON a
FOR EACH ROW
UPDATE b SET b2='Y' WHERE b1=SUBSTRING(old.a1, 1 + INSTR(old.a1, '.'))
//
delimiter ;

delimiter //
drop procedure a_ins//
CREATE PROCEDURE a_ins()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i<=500000) DO INSERT INTO a VALUES(concat('a',i,'.aa'), 1);
SET i=i+1;
END WHILE;
END
//
delimiter ;

call a_ins;

insert into b values('aa','N');

5. confirm record count table a,b
mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.6.14-ndb-7.3.3-cluster-gpl |
+------------------------------+
1 row in set (0.00 sec)

mysql> select table_name,engine from information_schema.tables where table_schema='test';
+------------+------------+
| table_name | engine     |
+------------+------------+
| a          | ndbcluster |
| b          | ndbcluster |
+------------+------------+
2 rows in set (0.00 sec)

mysql> select count(*) from a;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from b;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

6. confirm trigger
mysql> show triggers;
+----------+--------+-------+------------------------------------------------------------------------+--------+---------+------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger  | Event  | Table | Statement                                                              | Timing | Created | sql_mode               | Definer        | character_set_client | collation_connection | Database Collation |
+----------+--------+-------+------------------------------------------------------------------------+--------+---------+------------------------+----------------+----------------------+----------------------+--------------------+
| a_update | UPDATE | a     | UPDATE b SET b2='Y' WHERE b1=SUBSTRING(old.a1, 1 + INSTR(old.a1, '.')) | BEFORE | NULL    | NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+----------+--------+-------+------------------------------------------------------------------------+--------+---------+------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

7. at last, run the problem's query.
mysql> UPDATE a SET a2=2;
ERROR 1296 (HY000): Got error 901 'Inconsistent ordered index. The index needs to be dropped and recreated' from NDBCLUSTER

mysql> show table status;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+----------------------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment                                            |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+----------------------------------------------------+
| a    | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | Got error 157 'Unknown error code' from NDBCLUSTER |
| b    | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | Got error 157 'Unknown error code' from NDBCLUSTER |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+----------------------------------------------------+
2 rows in set, 5 warnings (0.00 sec)

If it was not repeatable, I would like the following confirmation.

a)
please could you upload your config.ini and my.cnf

b) 
please could you upload next result file.
mysqldump -uroot -p -R -d targetdb > targetdb.dmp

c)
please teach os version.
uname -a

thanks.

Looks like a duplicate of http://bugs.mysql.com/bug.php?id=56929

Hello!

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

// with the provided test script

mysql> create table a (
    -> a1 varchar(100) NOT NULL DEFAULT '',
    -> a2 int(6) DEFAULT '0', UNIQUE KEY idx_b1 (a1)
    -> )engine=ndb;
Query OK, 0 rows affected (0.38 sec)

mysql>
mysql> CREATE TABLE b (
    -> b1 char(20) NOT NULL DEFAULT '',
    -> b2 enum('N','Y') NOT NULL DEFAULT 'N' ,
    -> UNIQUE KEY idx_b1 (b1)
    -> )engine=ndb;
Query OK, 0 rows affected (0.43 sec)

mysql> delimiter //
mysql> CREATE TRIGGER a_update BEFORE UPDATE ON a
    -> FOR EACH ROW
    -> UPDATE b SET b2='Y' WHERE b1=SUBSTRING(old.a1, 1 + INSTR(old.a1, '.'))
    -> //
delimiter ;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql>
mysql> delimiter //
mysql> drop procedure a_ins//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE a_ins()
    -> BEGIN
    -> DECLARE i INT DEFAULT 1;
    -> WHILE (i<=500000) DO REPLACE INTO a VALUES(concat('a',i,'.aa'), 1);
    -> SET i=i+1;
    -> END WHILE;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call a_ins;
Query OK, 1 row affected (4 min 10.74 sec)

mysql> insert into b values('aa','N');
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE a SET a2=2;
ERROR 1296 (HY000): Got error 901 'Inconsistent ordered index. The index needs to be dropped and recreated' from NDBCLUSTER

//
Time: Friday 22 November 2013 - 15:13:11
Status: Temporary error, restart node
Message: 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)
Error: 6050
Error data: Job Handling
Error object: /export/home/pb2/build/sb_0-10782555-1384352627.99/mysql-cluster-gpl-7.3.3/storage/ndb/src/kernel/vm/WatchDog.cpp
Program: ndbd
Pid: 8761
Version: mysql-5.6.14 ndb-7.3.3
Trace: /data/ushast

// with the test script from old bug

mysql> select version();
+----------------------------------+
| version()                        |
+----------------------------------+
| 5.6.14-ndb-7.3.3-cluster-gpl-log |
+----------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `sequences` (
    ->   `seq_id` int(11) NOT NULL,
    ->   `seq_no` bigint(30) NOT NULL,
    ->   PRIMARY KEY (`seq_id`)
    -> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

DELIMITER //
CREATE FUNCTION foo(sequence BIGINT) RETURNS TINYINT
BEGIN
WHILE sequence > 0
DO REPLACE INTO sequences (seq_id,seq_no) VALUES (1,sequence);
SET sequence = sequence - 1; END WHILE;
RETURN 1;
END //
DELIMITER ;

START TRANSACTION;
SELECT foo(100000);Query OK, 0 rows affected (0.43 sec)

mysql>
mysql> DELIMITER //
mysql> CREATE FUNCTION foo(sequence BIGINT) RETURNS TINYINT
    -> BEGIN
    -> WHILE sequence > 0
    -> DO REPLACE INTO sequences (seq_id,seq_no) VALUES (1,sequence);
    -> SET sequence = sequence - 1; END WHILE;
    -> RETURN 1;
    -> END //
ERROR 1304 (42000): FUNCTION foo already exists
mysql> DELIMITER ;
mysql>
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT foo(100000);
ERROR 1296 (HY000): Got error 901 'Inconsistent ordered index. The index needs to be dropped and recreated' from NDBCLUSTER
mysql>

///

Time: Friday 22 November 2013 - 15:02:53
Status: Temporary error, restart node
Message: Internal program error (failed ndbrequire) (Internal error, programming error or missing error message, please report a bug)
Error: 2341
Error data: DbtupTrigger.cpp
Error object: DBTUP (Line: 2184) 0x00000006
Program: ndbd
Pid: 30858
Version: mysql-5.6.14 ndb-7.3.3
Trace: /data/ushastry/cluster/mysql-cluster-gpl-7.3.3/management/ndb_3_trace.log.2 [t1..t1]
***EOM***

Hello Umesh

why "verified" and not "duplicate"? 

As far as i can tell it is simply hitting the "single row can't be modified more than 65535 times within a single transaction" limit as in #56929 

The fact that it only shows with the trigger here is that REPLACE INTO actually does a DELETE/INSERT internally and so isn't touching the same row while the UPDATE in the trigger body is ...

Duplicate of Bug #56929

Hello  Hartmut,

This is marked as duplicate now.
Had to keep it for a while in "verified" status for some technical reasons while status of older bug in internal bugs database is checked.

Regards,
Umesh

please separate this bug from #56929.
(duplicate to verified)

Pay attention to next message of this bugs. 
 Caused by error 6050: 'WatchDog terminate,
This error message is different compare to #56929.
This bug is deeply concern with wathdog.

Hm ... I had only focused on the SQL level error message

  Got error 901 'Inconsistent ordered index.

Different low level error message in node error log 
makes this a different bug, or at least a different
aspect of the same general problem, indeed ...

ok. Indeed, duplicate or verified , either no problem, 

#56929 is bug of 3 years ago. but, not yet fixed.
I am suprised that serius bug #56929 have been deglected during very long period.