Bug #20389 use partition, server crash
Submitted: 12 Jun 2006 8:22 Modified: 19 Jul 2006 4:08
Reporter: steven zhoucn Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.1.12-BK debug, 5.1.11 OS:Linux (linux 2.4.20-8)
Assigned to: Mikael Ronström CPU Architecture:Any

[12 Jun 2006 8:22] steven zhoucn
Description:
I use thest sql:

--------------------------------------------------------------------------------
CREATE TABLE `crash` (
  `dataId` mediumint(9) NOT NULL AUTO_INCREMENT,
  `deviceNum` decimal(12,0) NOT NULL,
  `readFlag` decimal(1,0) NOT NULL,
  `status` decimal(1,0) NOT NULL,
  `clientIp` varchar(16) NOT NULL,
  `clientPort` decimal(22,0) NOT NULL,
  `serverIp` varchar(16) NOT NULL,
  `serverPort` decimal(22,0) NOT NULL,
  `captureTime` datetime NOT NULL,
  `phoneNum` varchar(40) NOT NULL DEFAULT ' ',
  `account` varchar(60) NOT NULL DEFAULT ' ',
  `clientMac` varchar(20) NOT NULL DEFAULT ' ',
  `userName` varchar(60) DEFAULT ' ',
  `password` varchar(40) DEFAULT ' ',
  `sendTime` varchar(60) DEFAULT ' ',
  `sendAddress` varchar(250) DEFAULT ' ',
  `receiveAddress` varchar(250) DEFAULT ' ',
  `ccAddress` varchar(250) DEFAULT ' ',
  `subject` varchar(60) DEFAULT ' ',
  `dataFile` varchar(250) DEFAULT ' ',
  `hourNum` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`dataId`,`captureTime`,`hourNum`),
  KEY `account` (`account`),
  KEY `phoneNum` (`phoneNum`),
  KEY `clientIp` (`clientIp`),
  KEY `captureTime` (`captureTime`),
  KEY `sendAddress` (`sendAddress`(40)),
  KEY `receiveAddress` (`receiveAddress`(40)),
  KEY `subject` (`subject`(40))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (TO_DAYS(captureTime)) SUBPARTITION BY HASH (hourNum) (PARTITION p0 VALUES LESS THAN (732736)  (SUBPARTITION S030100 ENGINE = InnoDB, SUBPARTITION S030101 ENGINE = InnoDB, SUBPARTITION S030102 ENGINE = InnoDB, SUBPARTITION S030103 ENGINE = InnoDB, SUBPARTITION S030104 ENGINE = InnoDB, SUBPARTITION S030105 ENGINE = InnoDB, SUBPARTITION S030106 ENGINE = InnoDB, SUBPARTITION S030107 ENGINE = InnoDB, SUBPARTITION S030108 ENGINE = InnoDB, SUBPARTITION S030109 ENGINE = InnoDB, SUBPARTITION S030110 ENGINE = InnoDB, SUBPARTITION S030111 ENGINE = InnoDB, SUBPARTITION S030112 ENGINE = InnoDB, SUBPARTITION S030113 ENGINE = InnoDB, SUBPARTITION S030114 ENGINE = InnoDB, SUBPARTITION S030115 ENGINE = InnoDB, SUBPARTITION S030116 ENGINE = InnoDB, SUBPARTITION S030117 ENGINE = InnoDB, SUBPARTITION S030118 ENGINE = InnoDB, SUBPARTITION S030119 ENGINE = InnoDB, SUBPARTITION S030120 ENGINE = InnoDB, SUBPARTITION S030121 ENGINE = InnoDB, SUBPARTITION S030122 ENGINE = InnoDB, SUBPARTITION S030123 ENGINE = InnoDB), PARTITION p1 VALUES LESS THAN (732770)  (SUBPARTITION s04030 ENGINE = InnoDB, SUBPARTITION s04031 ENGINE = InnoDB, SUBPARTITION s04032 ENGINE = InnoDB, SUBPARTITION s04033 ENGINE = InnoDB, SUBPARTITION s04034 ENGINE = InnoDB, SUBPARTITION s04035 ENGINE = InnoDB, SUBPARTITION s04036 ENGINE = InnoDB, SUBPARTITION s04037 ENGINE = InnoDB, SUBPARTITION s04038 ENGINE = InnoDB, SUBPARTITION s04039 ENGINE = InnoDB, SUBPARTITION s040310 ENGINE = InnoDB, SUBPARTITION s040311 ENGINE = InnoDB, SUBPARTITION s040312 ENGINE = InnoDB, SUBPARTITION s040313 ENGINE = InnoDB, SUBPARTITION s040314 ENGINE = InnoDB, SUBPARTITION s040315 ENGINE = InnoDB, SUBPARTITION s040316 ENGINE = InnoDB, SUBPARTITION s040317 ENGINE = InnoDB, SUBPARTITION s040318 ENGINE = InnoDB, SUBPARTITION s040319 ENGINE = InnoDB, SUBPARTITION s040320 ENGINE = InnoDB, SUBPARTITION s040321 ENGINE = InnoDB, SUBPARTITION s040322 ENGINE = InnoDB, SUBPARTITION s040323 ENGINE = InnoDB), PARTITION p2 VALUES LESS THAN (732771)  (SUBPARTITION s04040 ENGINE = InnoDB, SUBPARTITION s04041 ENGINE = InnoDB, SUBPARTITION s04042 ENGINE = InnoDB, SUBPARTITION s04043 ENGINE = InnoDB, SUBPARTITION s04044 ENGINE = InnoDB, SUBPARTITION s04045 ENGINE = InnoDB, SUBPARTITION s04046 ENGINE = InnoDB, SUBPARTITION s04047 ENGINE = InnoDB, SUBPARTITION s04048 ENGINE = InnoDB, SUBPARTITION s04049 ENGINE = InnoDB, SUBPARTITION s040410 ENGINE = InnoDB, SUBPARTITION s040411 ENGINE = InnoDB, SUBPARTITION s040412 ENGINE = InnoDB, SUBPARTITION s040413 ENGINE = InnoDB, SUBPARTITION s040414 ENGINE = InnoDB, SUBPARTITION s040415 ENGINE = InnoDB, SUBPARTITION s040416 ENGINE = InnoDB, SUBPARTITION s040417 ENGINE = InnoDB, SUBPARTITION s040418 ENGINE = InnoDB, SUBPARTITION s040419 ENGINE = InnoDB, SUBPARTITION s040420 ENGINE = InnoDB, SUBPARTITION s040421 ENGINE = InnoDB, SUBPARTITION s040422 ENGINE = InnoDB, SUBPARTITION s040423 ENGINE = InnoDB), PARTITION p3 VALUES LESS THAN (732772)  (SUBPARTITION s04050 ENGINE = InnoDB, SUBPARTITION s04051 ENGINE = InnoDB, SUBPARTITION s04052 ENGINE = InnoDB, SUBPARTITION s04053 ENGINE = InnoDB, SUBPARTITION s04054 ENGINE = InnoDB, SUBPARTITION s04055 ENGINE = InnoDB, SUBPARTITION s04056 ENGINE = InnoDB, SUBPARTITION s04057 ENGINE = InnoDB, SUBPARTITION s04058 ENGINE = InnoDB, SUBPARTITION s04059 ENGINE = InnoDB, SUBPARTITION s040510 ENGINE = InnoDB, SUBPARTITION s040511 ENGINE = InnoDB, SUBPARTITION s040512 ENGINE = InnoDB, SUBPARTITION s040513 ENGINE = InnoDB, SUBPARTITION s040514 ENGINE = InnoDB, SUBPARTITION s040515 ENGINE = InnoDB, SUBPARTITION s040516 ENGINE = InnoDB, SUBPARTITION s040517 ENGINE = InnoDB, SUBPARTITION s040518 ENGINE = InnoDB, SUBPARTITION s040519 ENGINE = InnoDB, SUBPARTITION s040520 ENGINE = InnoDB, SUBPARTITION s040521 ENGINE = InnoDB, SUBPARTITION s040522 ENGINE = InnoDB, SUBPARTITION s040523 ENGINE = InnoDB));

--------------------------------------------------------------------------------

INSERT INTO `crash` VALUES ('1', '1240', '0', '1', '48.66.174.144', '1642', '251.96.108.147', '9182', '2006-04-03 00:30:55', '54488073', 'zbwzyajpyijpuwkbogrfoqdgwimsxglebdhnaaknobmsgjsh', 'E3-76-A6-E5-E8-EF', 'kggmncfjddlzjfkrexayugdxawndombqapbuowiqsztnxdosps', 'twwrugnzegrftbuypf', '2006-04-03 00:30:50', '67x62m6af5awg@odly5xislfr6dz6y.com', 'litlst@crone2x3khps1yg1pgpp0jl6he2agrhotrmn11q981gngbbsppwapy787o5e0w1fufrlxo34seysi0x13k3xp645ngva1ogruhdvjn44lr7rz7royc93uoe5qjvl7anqlj9xwetxqg2rv26te1njrkfk34j.com', 'vy3kpwgr@wmgvjcy8heyro4jlufi2qm3k5p75texmk.com', 'ukisolspivzpdxpkstxsdofyrncxlzezyaiduvjggrumlgtgnj', 'edccnrgqsifengepsnnnzmzxkwnnkakvvnxagvyppezjhwgxrvdzplnkelxsova', '0');
.........

INSERT INTO `crash` VALUES ('139', '1241', '0', '1', '247.153.26.241', '6950', '226.84.149.20', '9808', '2006-04-03 00:12:41', '82794055', 'ojnkrzsdvznjuhqpgcszhtmkjc', 'A6-76-95-32-69-C4', 'xqowbdjlai', 'pyuacpdvywvpkxhvd', '2006-04-03 00:57:09', 'v6qthki2xsy3v@qmgrm2rtsq505qqokpbwlldoll1cbok9sfopqao16x9lscig4ystovr2ioorogl1x9qpivm3atlz45dzsohxhfdxeieynl9d.com', '3u72ks17rqphrn5d@xezlyyy3tkd3jyia94n59ixdm4lxinwzplhcgkzm00mruegsoedlitihiow3po1bsflyo1ygio0gaulj3kuftxdgrxbw4sru93414dd30r96tuy8nzyjblsgqvhrf1pd8l9y5v7yhyi41ukxfugswypmavfxeckiimq4.com', 'i8nxxfq3312z@z9x2s4bekkluk1teq084pk3ztsfuvswfc05e63ikr6qd74xqgtel5nzq4t6q1jqj14zgkvn131nmd4ywplxxi8llrwqadmsdbwwfgb43tewfs6a.com', 'kurgtofcdjilzhjfjgmkukbhuetvgoioumkbdbphyfedvpfrdpco', 'cdabnbiugceqdhtfvperbzjthabwkpajddwdmwcchvydpdndsyazjadghftlvcbqwpheecsmltgbyrtybkzbhfcwhudlwbadcbjhvvroykgpxssmxukqnpkfaflvyaytdfafrpocoxoagiyhkihgtvkyllcxoxsiljynbwsabanhocujkadmjjwiakfcabltcttkmwdsblqtolmcsmczwuenva', '0');

--------------------------------------------------------------------------------

and,  When use this select sql, the sever crash!

select dataId from crash where account like 'g%' and captureTime between '2006-04-03 00:00:00' and '2006-04-03 01:00:00' and hourNum=0 order by account desc limit 10;
SHOW TABLE STATUS LIKE 'normal';

How to repeat:
create the table crash, and insert the values, when use the select sql, the server crash!

Note:
when use mysql-5.0.11, must use :
select dataId from crash where account like 'g%' and captureTime between '2006-04-03 00:00:00' and '2006-04-03 01:00:00' and hourNum=0 order by account desc limit 10;

The server not crash!, but use:
 SHOW TABLE STATUS LIKE 'normal';
Server crash!

But I use bitkeepr get source from Source Distribution at 2006-06-11, compiled it,
use configure-option:
./configure \
	--prefix=/private/mysql-5.1-debug \
	--with-big-tables --with-innodb --with-federated-storage-engine \
	--with-partition --with-csv-storage-engine  --without-debug \
	--without-docs --without-man --without-bench --without-berkeley-db \
	--without-berkeley-db-libs --without-ndbcluster \
	--without-ndb-sci --without-ndb-test --without-ndb-docs \
	--without-ndb-port --without-ndb-port-base --without-ndb-debug \
	--without-example-storage-engine  --without-blackhole-storage-engine --enable-assembler \
        --enable-local-infile --with-charset=utf8 --with-extra-charsets=complex \
	--enable-thread-safe-client --with-readline \
	--with-mysqld-ldflags='-all-static' \
	--with-client-ldflags='-all-static' \
	--with-zlib-dir=bundled

and make; make install, use the server:
I find: only use select sql: select dataId from crash where account like 'g%' and captureTime between '2006-04-03 00:00:00' and '2006-04-03 01:00:00' and hourNum=0 order by account desc limit 10;
can make server crash!
[13 Jun 2006 14:00] Valeriy Kravchuk
Thank you for a problem report. Please, send/upload a complete dump of your `crash` table data. I was not able to get any crash form 5.1.12-BK with only 2 those rows you had presented.
[14 Jun 2006 12:51] steven zhoucn
the full records

Attachment: crash.sql (text/plain), 119.42 KiB.

[14 Jun 2006 12:52] steven zhoucn
config file

Attachment: my.cnf (application/octet-stream, text), 4.88 KiB.

[14 Jun 2006 12:53] steven zhoucn
select sql

Attachment: select.sql (text/plain), 165 bytes.

[14 Jun 2006 12:59] steven zhoucn
hello Valeriy Kravchuk
I think you can't get any crash! because I don't provide the full records!

So I have submit 3 files! the crash.sql include all records!
[4 Jul 2006 15:34] Valeriy Kravchuk
Verified just as described on your `crash` table data uploaded:

mysql> select dataId from crash where account like 'g%' and captureTime between
 '2006-04-03 00:00:00' and '2006-04-03 01:00:00' and hourNum=0 order by account
 desc limit 10;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
060704 14:05:36  mysqld restarted

mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: test

+-------------------+
| version()         |
+-------------------+
| 5.1.12-beta-debug |
+-------------------+
1 row in set (1.03 sec)

mysql> select count(*) from crash;
+----------+
| count(*) |
+----------+
|      139 |
+----------+
1 row in set (0.02 sec)

In the error log I've got:

Stack range sanity check OK, backtrace follows:
0x8215556
0xffffe420
(nil)
0x82e9913
0x82f2c65
0x828508b
0x8284c11
0x827435b
0x8274847
0x826f70e
0x8236e94
0x8230015
0x82388c0
0x822e3ab
0x822dc1e
0x822cdab
0x40047aa7
0x4023ec2e
New value of fp=(nil) 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. Resolved
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 0x9274b28 = select dataId from crash where account like 'g%' and c
aptureTime between '2006-04-03 00:00:00' and '2006-04-03 01:00:00' and hourNum=0
 order by account desc limit 10
thd->thread_id=4

Resolved to:

0x8215556 handle_segfault + 412
0xffffe420 _end + -143818624
(nil)
0x82e9913 _ZN17QUICK_SELECT_DESC8get_nextEv + 191
0x82f2c65 _Z8rr_quickP14st_read_record + 35
0x828508b _Z10sub_selectP4JOINP13st_join_tableb + 329
0x8284c11 _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 673
0x827435b _ZN4JOIN4execEv + 6843
0x8274847 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orde
rSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 635
0x826f70e _Z13handle_selectP3THDP6st_lexP13select_resultm + 306
0x8236e94 _Z21execute_sqlcom_selectP3THDP13st_table_list + 592
0x8230015 _Z21mysql_execute_commandP3THD + 1271
0x82388c0 _Z11mysql_parseP3THDPcj + 484
0x822e3ab _Z16dispatch_command19enum_server_commandP3THDPcj + 1917
0x822dc1e _Z10do_commandP3THD + 518
0x822cdab handle_one_connection + 803
0x40047aa7 _end + 930223879
0x4023ec2e _end + 932284558
[12 Jul 2006 8:26] Mikael Ronström
There was some misunderstandings of the handler interface that have now been corrected
for reverse order scans.
[18 Jul 2006 8:54] Mikael Ronström
Patch will appear in 5.1.12
[19 Jul 2006 4:08] Jon Stephens
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://www.mysql.com/doc/en/Installing_source_tree.html
[19 Jul 2006 4:09] Jon Stephens
Documented bugfix in 5.1.12 changelog.