MySQL Bugs: #25522: Update with IN syntax Cluster table + Trigger leads to mysqld segfault

Bug #25522	Update with IN syntax Cluster table + Trigger leads to mysqld segfault
Submitted:	10 Jan 2007 14:04	Modified:	8 Feb 2007 6:26
Reporter:	Stefan Pasel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.0.30-max	OS:	2.6.18-1.2257.fc5 x86_64
Assigned to:	Martin Skold	CPU Architecture:	Any
Tags:	cluster, crash, segfault, trigger

Description:
We have a cluster table and attached a trigger on the mysqld to it. This trigger  in general works as expected, but as my colleague Thomas issued an UPDATE query with IN('','') as WHERE condition the node segfaulted.

I have made a reproducible test case from it - i did not have the time to check all possibilities (MyISAM -> ndbcluster or 2x ndbcluster tables) but i could not reproduce it on 5.0.27 with 2x MyISAM, so i suspect its some return from the ndb storage engine.

mysqld_err.log:
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=134217728
read_buffer_size=2093056
max_used_connections=16
max_connections=400
threads_connected=11
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 4225468 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x17285e0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x452a5078, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
(nil)
New value of fp=0x17285e0 failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolve
d
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x17346f0 = UPDATE t1 SET var2 = 9 WHERE var1 IN(1,2,3)
thd->thread_id=27
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
070110 13:51:04  mysqld restarted

How to repeat:
mysql> use mysqlbug
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `t1` (
    ->   `var1` int(2) NOT NULL,
    ->   `var2` int(2) NOT NULL,
    ->   PRIMARY KEY  (`var1`)
    -> ) ENGINE=ndbcluster DEFAULT CHARSET=ascii CHECKSUM=1;
Query OK, 0 rows affected (4.85 sec)

mysql>
mysql> CREATE TABLE `t2` (
    ->   `var1` int(2) NOT NULL,
    ->   `var2` int(2) NOT NULL,
    ->   PRIMARY KEY  (`var1`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=ascii CHECKSUM=1;
Query OK, 0 rows affected (0.42 sec)

mysql>
mysql>
mysql> DELIMITER ;;
mysql> CREATE TRIGGER `testtrigger`
    -> AFTER UPDATE ON `t1` FOR EACH ROW BEGIN
    -> REPLACE INTO t2 SELECT * FROM t1 WHERE t1.var1 = NEW.var1;END;;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 ;
+------+------+
| var1 | var2 |
+------+------+
|    3 |    3 |
|    2 |    2 |
|    1 |    1 |
+------+------+
3 rows in set (0.06 sec)

mysql> UPDATE t1 SET var2 = 9 WHERE var1 IN(1,2,3);
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql>  SELECT * FROM t1 ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    55
Current database: mysqlbug

+------+------+
| var1 | var2 |
+------+------+
|    2 |    2 |
|    3 |    3 |
|    1 |    1 |
+------+------+
3 rows in set (0.18 sec)

mysql> UPDATE t1 SET var2 = 9 WHERE var1 IN(1);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  SELECT * FROM t1 ;
+------+------+
| var1 | var2 |
+------+------+
|    2 |    2 |
|    3 |    3 |
|    1 |    9 |
+------+------+
3 rows in set (0.00 sec)

__ Copy&Paste Snippet __

CREATE TABLE `t1` (
  `var1` int(2) NOT NULL,
  `var2` int(2) NOT NULL,
  PRIMARY KEY  (`var1`)
) ENGINE=ndbcluster DEFAULT CHARSET=ascii CHECKSUM=1;

CREATE TABLE `t2` (
  `var1` int(2) NOT NULL,
  `var2` int(2) NOT NULL,
  PRIMARY KEY  (`var1`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii CHECKSUM=1;

DELIMITER ;;
CREATE TRIGGER `testtrigger` 
	AFTER UPDATE ON `t1` FOR EACH ROW BEGIN 
	REPLACE INTO 	t2 SELECT * FROM t1	 WHERE t1	.	var1	 = NEW.	var1	;END;;
DELIMITER ;

__END__

Verified as described with 5.0.34bk on Linux/x86_64 (Debian Sarge).

Here is the stack trace from the crashed mysqld:

#0  0x00002aaaaaee9139 in pthread_kill () from /lib/libpthread.so.0
#1  0x00000000007672aa in write_core (sig=6) at stacktrace.c:244
#2  0x00000000005fc3a8 in handle_segfault (sig=6) at mysqld.cc:2135
#3  <signal handler called>
#4  0x00002aaaab3f4545 in raise () from /lib/libc.so.6
#5  0x00002aaaab3f5cce in abort () from /lib/libc.so.6
#6  0x00002aaaab3ee362 in __assert_fail () from /lib/libc.so.6
#7  0x0000000000710611 in ha_ndbcluster::setup_recattr (this=0x15fc108, 
    curr=0x0) at ha_ndbcluster.cc:6562
#8  0x00000000007104bd in ha_ndbcluster::read_multi_range_next (
    this=0x15fc108, multi_range_found_p=0x4390def8) at ha_ndbcluster.cc:6538
#9  0x00000000006d6087 in QUICK_RANGE_SELECT::get_next (this=0x15e06e0)
    at opt_range.cc:6686
#10 0x00000000006e07c8 in rr_quick (info=0x4390e160) at records.cc:224
#11 0x000000000068e02d in mysql_update (thd=0x15990b8, table_list=0x15d86b8, 
    fields=@0x1599670, values=@0x1599a18, conds=0x15d8f30, order_num=0, 
    order=0x0, limit=18446744073709551614, handle_duplicates=DUP_ERROR, 
    ignore=false) at sql_update.cc:445
#12 0x00000000006183a4 in mysql_execute_command (thd=0x15990b8)
    at sql_parse.cc:3312
#13 0x000000000061e977 in mysql_parse (thd=0x15990b8, 
    inBuf=0x15d85f8 "UPDATE t1 SET var2 = 9 WHERE var1 IN(1,2,3)", length=43)
    at sql_parse.cc:5832

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/19135

ChangeSet@1.2295, 2007-01-31 22:38:06+01:00, mskold@mysql.com +3 -0
  Bug #25522  Update with IN syntax Clustertable + Trigger leads to mysqld segfault: in start_stmt, only change query_state if starting a new transactions, in read_multi_range_next, change query state when end is reached

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/19162

ChangeSet@1.2296, 2007-02-01 09:48:45+01:00, mskold@mysql.com +1 -0
  Bug #25522  Update with IN syntax Clustertable + Trigger leads to mysqld segfault: moved back assignment

pushed to 5.1.16

pushed to 5.0.36

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.36 and 5.1.16 changelogs.