MySQL Bugs: #35599: Mysqld crash after delete records from table (with constraint violation)

Bug #35599	Mysqld crash after delete records from table (with constraint violation)
Submitted:	27 Mar 2008 11:47	Modified:	5 Oct 2008 16:36
Reporter:	li zhou	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	telco-6.4	OS:	Any
Assigned to:	Guangbao Ni	CPU Architecture:	Any

Description:
Create a table and insert some record. Try to alter table add UNIQUE INDEX twice.
Mysqld crash after delete records

How to repeat:
mysql> create table t (a int primary key ,b int) engine=ndb;
Query OK, 0 rows affected (0.76 sec)

mysql> insert into t values(1,12);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(2,12);
Query OK, 1 row affected (0.00 sec)

mysql> alter table t add unique key ui_t(b);
ERROR 1169 (23000): Can't write, because of unique constraint, to table 't'
mysql> delete from t;
Query OK, 2 rows affected (0.05 sec)

mysql> insert into t values(1,12);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(2,12);
Query OK, 1 row affected (0.00 sec)

mysql> alter table t add unique key ui_t(b);
ERROR 1169 (23000): Can't write, because of unique constraint, to table 't'
mysql> delete from t;
Query OK, 2 rows affected (0.02 sec)

check ndb status

===============

Connected to Management Server at: localhost:9311
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @127.0.0.1  (mysql-5.1.23 ndb-6.4.0, Nodegroup: 0, Master)
id=2    @127.0.0.1  (mysql-5.1.23 ndb-6.4.0, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=3    @127.0.0.1  (mysql-5.1.23 ndb-6.4.0)

[mysqld(API)]   12 node(s)
id=4 (not connected, accepting connect from any host)
id=5 (not connected, accepting connect from any host)
id=6 (not connected, accepting connect from any host)
id=7    @127.0.0.1  (mysql-5.1.23 ndb-6.4.0)
id=8    @127.0.0.1  (mysql-5.1.23 ndb-6.4.0)
id=9    @127.0.0.1  (mysql-5.1.23 ndb-6.4.0)
id=10 (not connected, accepting connect from any host)
id=63 (not connected, accepting connect from any host)
id=127 (not connected, accepting connect from any host)
id=192 (not connected, accepting connect from any host)
id=228 (not connected, accepting connect from any host)
id=255 (not connected, accepting connect from any host)

Thank you for the report.

Verified with difference: in my case crash occurs when second ALTER is issued.

Do as the above example, will crash 

(gdb) bt
#0  0x00578150 in pthread_kill () from /lib/tls/i686/libpthread.so.0
#1  0x083efebb in write_core (sig=6) at stacktrace.c:305
#2  0x0827e950 in handle_segfault (sig=6) at mysqld.cc:2505
#3  <signal handler called>
#4  0x0036bc38 in raise () from /lib/tls/i686/libc.so.6
#5  0x0036d0b8 in abort () from /lib/tls/i686/libc.so.6
#6  0x00365a5c in __assert_fail () from /lib/tls/i686/libc.so.6
#7  0x08356dfe in Table_map_log_event (this=0xb2954ff0, thd=0xa795310, tbl=0xa7df3a0, tid=18,
    is_transactional=true, flags=0) at log_event.cc:7109
#8  0x0832d7a6 in THD::binlog_write_table_map (this=0xa795310, table=0xa7df3a0, is_trans=true)
    at log.cc:3438
#9  0x083e8657 in injector::transaction::use_table (this=0xb2955240, sid=1, tbl=
      {m_table = 0xa7df3a0, m_is_transactional = true}) at rpl_injector.cc:100
#10 0x08470b1b in ndb_binlog_thread_func (arg=0x0) at ha_ndbcluster_binlog.cc:5370
#11 0x005750dd in start_thread () from /lib/tls/i686/libpthread.so.0
#12 0x003fba2e in clone () from /lib/tls/i686/libc.so.6

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45594

ChangeSet@1.2584, 2008-04-18 17:36:05+08:00, gni@dev3-221.dev.cn.tlan +3 -0
  BUG#35599 Mysqld crash after delete records from table (with constraint violation)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45598

ChangeSet@1.2584, 2008-04-18 19:33:47+08:00, gni@dev3-221.dev.cn.tlan +3 -0
  BUG#35599 Mysqld crash after delete records from table (with constraint violation)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45928

ChangeSet@1.2584, 2008-04-24 09:30:16+08:00, gni@dev3-221.dev.cn.tlan +3 -0
  BUG#35599 Mysqld crash after delete records from table (with constraint violation)

isnt this present also in 6.2/6.3 ?

Hi Jonas,
  Yes, it only exists in telco-6.4 after wl#3600(NDB schema transactions).
Before wl#3600, also have some problems because of not atomic schema operations, but they are different bugs.

Hi Jonas,

  Before NDB schema transaction is merged, when "alter table t add unique key ui_t(b)" statement is executed and failed, the key can't be dropped correctly.

If you use ndb_show_tables utility, the following lines will be seen:
7     OrderedIndex         Online   No      sys          def      ui_t
8     UniqueHashIndex      Broken   Yes     sys          def      ui_t$unique

  So if you execute insert, alter and delete statemenst, the different path will be executed, So have different results.

But It still is a bug.
Should fix it also in telco-6.2 and telco-6.3.

/Guangbao

  Yes, it only exists in telco-6.4 after wl#3600(NDB schema transactions).
Before wl#3600, also have some problems because of not atomic schema operations, but they are different bugs.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51369

2645 Guangbao Ni	2008-08-12
      BUG#35599 Mysqld crash after delete records from table (with constraint violation)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51374

2645 Guangbao Ni	2008-08-12
      BUG#35599 Mysqld crash after delete records from table (with constraint violation)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51376

2662 Guangbao Ni	2008-08-12 [merge]
      BUG#35599 Mysqld crash after delete records from table(with constraint violation)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51377

2661 Guangbao Ni	2008-08-12 [merge]
      BUG#35599 Mysqld crash after delete records from table (with constraint violation)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/51380

2707 Guangbao Ni	2008-08-12 [merge]
      BUG#35599 Mysqld crash after delete records from table (with constraint violation)

pushed mysql-5.1-telco-6.2 and merged into mysql-5.1-telco-6.3 and mysql-5.1-telco-6.4

Documented in the NDB 6.2.16 and 6.3.17 changelogs as follows:

        Attempting to add a UNIQUE INDEX twice to an NDBCLUSTER table, then
        deleting rows from the table could cause the MySQL Server to crash.

Left in PQ status.

Already documented for NDB 6.2/6.3; closed.

Pushed into 6.0.6-alpha  (revid:gni@mysql.com-20080812124740-3lhypj3mu64nf15c) (version source revid:jonas@mysql.com-20080813092004-7zlf6eu87i4ziwm2) (pib:5)