MySQL Bugs: #33060: Crash on subselect with MAX()

Bug #33060	Crash on subselect with MAX()
Submitted:	7 Dec 2007 12:18	Modified:	20 Nov 2008 15:33
Reporter:	Robert Klikics	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	5.1.22-rc	OS:	Linux (Debian 4.0, mysql from src)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	crash, MAX, ndbcluster, subselect

Description:
Mysqld hangs up everytime this query is running:

SELECT PR_SEARCHLOG.SEARCHTERM AS TERM, PR_SOUNDEX.ID AS ID FROM PR_SEARCHLOG, PR_SOUNDEX WHERE PR_SEARCHLOG.ID > ((SELECT MAX(ID) FROM PR_SEARCHLOG)-20)  AND PR_SEARCHLOG.RESULTS >
 0 AND PR_SOUNDEX.TERM=PR_SEARCHLOG.SEARCHTERM GROUP BY PR_SEARCHLOG.SEARCHTERM ORDER BY PR_SEARCHLOG.ID DESC LIMIT 10

I've now made 2 querys for this in my app and it works fine (first get MAX and then the rest).

####
thd: 0x1dc6410
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=0x40e431b8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
(nil)
New value of fp=0x1dc6410 failed sanity check, terminating stack trace!

How to repeat:
SELECT PR_SEARCHLOG.SEARCHTERM AS TERM, PR_SOUNDEX.ID AS ID FROM PR_SEARCHLOG, PR_SOUNDEX WHERE PR_SEARCHLOG.ID > ((SELECT MAX(ID) FROM PR_SEARCHLOG)-20)  AND PR_SEARCHLOG.RESULTS >
 0 AND PR_SOUNDEX.TERM=PR_SEARCHLOG.SEARCHTERM GROUP BY PR_SEARCHLOG.SEARCHTERM ORDER BY PR_SEARCHLOG.ID DESC LIMIT 10

mysql> describe PR_SOUNDEX;
+-------------+---------------------+------+-----+-------------------+----------------+
| Field       | Type                | Null | Key | Default           | Extra          |
+-------------+---------------------+------+-----+-------------------+----------------+
| ID          | int(3) unsigned     | NO   | PRI | NULL              | auto_increment | 
| TERM        | varchar(150)        | YES  | MUL | NULL              |                | 
| SOUND_EX    | varchar(10)         | YES  | MUL | 0                 |                | 
| RANK        | tinyint(3) unsigned | YES  |     | 0                 |                | 
| RESULTS     | int(10) unsigned    | YES  |     | 0                 |                | 
| LAST_SEARCH | timestamp           | NO   |     | CURRENT_TIMESTAMP |                | 
| SEARCHNUM   | int(10) unsigned    | YES  |     | 0                 |                | 
+-------------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0,00 sec)

mysql> describe PR_SEARCHLOG;
+------------+----------------------+------+-----+-------------------+----------------+
| Field      | Type                 | Null | Key | Default           | Extra          |
+------------+----------------------+------+-----+-------------------+----------------+
| ID         | int(10) unsigned     | NO   | PRI | NULL              | auto_increment | 
| DATE_TIME  | timestamp            | NO   |     | CURRENT_TIMESTAMP |                | 
| SEARCHTERM | varchar(200)         | NO   |     |                   |                | 
| RESULTS    | smallint(3) unsigned | NO   | MUL | 0                 |                | 
| SOUNDEX_ID | int(10) unsigned     | YES  |     | NULL              |                | 
+------------+----------------------+------+-----+-------------------+----------------+
5 rows in set (0,00 sec)

Both are ENGINE=NDB;

Thank you for the report.

Please provide output of EXPLAIN EXTENDED for this statement and output of SHOW CREATE TABLE for both tables.

Here is the requested output, I hope it helps.

The EXPLAIN EXTENDED:

mysql> EXPLAIN EXTENDED SELECT PR_SEARCHLOG.SEARCHTERM AS TERM, PR_SOUNDEX.ID AS ID FROM PR_SEARCHLOG, PR_SOUNDEX WHERE PR_SEARCHLOG.ID > ((SELECT MAX(ID) FROM PR_SEARCHLOG)-20)  AND PR_SEARCHLOG.RESULTS >  0 AND PR_SOUNDEX.TERM=PR_SEARCHLOG.SEARCHTERM GROUP BY PR_SEARCHLOG.SEARCHTERM ORDER BY PR_SEARCHLOG.ID DESC LIMIT 10;
+----+-------------+--------------+-------+-----------------+------------+---------+--------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table        | type  | possible_keys   | key        | key_len | ref                                  | rows | filtered | Extra                                        |
+----+-------------+--------------+-------+-----------------+------------+---------+--------------------------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY     | PR_SEARCHLOG | range | PRIMARY,RESULTS | PRIMARY    | 4       | NULL                                 |    1 |   100.00 | Using where; Using temporary; Using filesort | 
|  1 | PRIMARY     | PR_SOUNDEX   | ref   | TERM_INDEX      | TERM_INDEX | 453     | database.PR_SEARCHLOG.SEARCHTERM |    1 |   100.00 | Using where                                  | 
|  2 | SUBQUERY    | NULL         | NULL  | NULL            | NULL       | NULL    | NULL                                 | NULL |     NULL | Select tables optimized away                 | 
+----+-------------+--------------+-------+-----------------+------------+---------+--------------------------------------+------+----------+----------------------------------------------+

And here the SHOW CREATE TABLE:

CREATE TABLE `PR_SEARCHLOG` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `SEARCHTERM` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `RESULTS` smallint(3) unsigned NOT NULL DEFAULT '0',
  `SOUNDEX_ID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `RESULTS` (`RESULTS`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `PR_SOUNDEX` (
  `ID` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `TERM` varchar(150) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `SOUND_EX` varchar(10) COLLATE utf8_unicode_ci DEFAULT '0',
  `RANK` tinyint(3) unsigned DEFAULT '0',
  `RESULTS` int(10) unsigned DEFAULT '0',
  `LAST_SEARCH` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `SEARCHNUM` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `TERM_INDEX` (`TERM`),
  KEY `SNDX` (`SOUND_EX`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Regards,
Robert

Thank you for the feedback.

I can not repeat described behavior with test data. Please provide output of SHOW TABLE STATUS for both tables, your cluster and mysqld configuration files and configure options you used when compiled the binaries.

Hello,

here are (quite simple) the configure-options:

./configure '--with-ndbcluster'

SHOW TABLE outputs:

mysql> show table status where name = 'PR_SEARCHLOG';
+--------------+------------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| 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 |
+--------------+------------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| PR_SEARCHLOG | ndbcluster |      10 | Dynamic    | 396579 |             44 |   112197632 |               0 |            0 |         0 |       45775744 | NULL        | NULL        | NULL       | utf8_unicode_ci |     NULL |                |         | 
+--------------+------------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0,02 sec)

mysql> show table status where name = 'PR_SOUNDEX';
+------------+------------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| 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 |
+------------+------------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| PR_SOUNDEX | ndbcluster |      10 | Dynamic    | 7165576 |             48 |   635371520 |               0 |            0 |         0 |        7625091 | NULL        | NULL        | NULL       | utf8_unicode_ci |     NULL |                |         | 
+------------+------------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0,01 sec)

Here is my cluster's config.ini:

[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=15360M
IndexMemory=768M
StopOnError=0
MaxNoOfConcurrentTransactions=8192
StartFailureTimeout=2000000
RedoBuffer=32M
NoOfFragmentLogFiles= 100
DiskCheckpointSpeed=15M
TimeBetweenLocalCheckpoints=20

[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
SendBufferMemory=2M

[NDB_MGMD]
DataDir=/var/lib/mysql-cluster
HostName=192.168.10.53

[NDBD]
HostName=192.168.10.210
DataDir=/var/lib/mysql-cluster

[NDBD]
HostName=192.168.10.211
DataDir= /var/lib/mysql-cluster

[NDBD]
HostName=192.168.10.212
DataDir= /var/lib/mysql-cluster

[NDBD]
HostName=192.168.10.213
DataDir= /var/lib/mysql-cluster

[MYSQLD]
HostName=192.168.10.220
[MYSQLD]
HostName=192.168.10.221
[MYSQLD]
HostName=192.168.10.222
[MYSQLD]
HostName=192.168.10.223

And finally the mysqld's my.cnf (that is the same on all mysql'd servers):

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
max_connections = 2500
max_connect_errors = 1000
skip-external-locking
ndbcluster
ndb-connectstring=192.168.10.53 
engine_condition_pushdown=1

myisam_sort_buffer_size = 64M
key_buffer              = 1536M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
tmp_table_size=256M

query_cache_limit       = 1048576
query_cache_size        = 512M
query_cache_type        = 1

I hope this helps. I just changed my application-query's, so that the error does no longer appear here. But I think it should be fixed anyway ...

Regards,
Robert

Thanks for all the information you've already provided.

Unfortunately, this issue does not seem to reproduce with dummy data I've made up to populate the two tables, perhaps the failure is dependent on the actual data you are using?

Do you think you could send some sample data which exposes the problem?

Alternatively, perhaps you could reproduce the error after running MySQLD with the --core-file option, and send the core file (or at least backtraces from the core file).

Thanks for any further information you can provide.
Frazer

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".