| Bug #33060 | Crash on subselect with MAX() | ||
|---|---|---|---|
| Submitted: | 7 Dec 2007 13:18 | Modified: | 20 Nov 2008 16:33 |
| Reporter: | Robert Klikics | ||
| Status: | No Feedback | ||
| Category: | Server: Cluster | Severity: | S1 (Critical) |
| Version: | 5.1.22-rc | OS: | Linux (Debian 4.0, mysql from src) |
| Assigned to: | Frazer Clement | Target Version: | |
| Tags: | ndbcluster, MAX, crash, subselect | ||
| Triage: | D2 (Serious) | ||
[7 Dec 2007 13:18]
Robert Klikics
[8 Dec 2007 22:00]
Sveta Smirnova
Thank you for the report. Please provide output of EXPLAIN EXTENDED for this statement and output of SHOW CREATE TABLE for both tables.
[8 Dec 2007 23:52]
Robert Klikics
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
[1 Feb 2008 0:57]
Sveta Smirnova
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.
[1 Feb 2008 10:02]
Robert Klikics
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
[20 Oct 2008 17:33]
Frazer Clement
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
[21 Nov 2008 1:00]
Bugs System
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".
