Bug #40972 Partition pruning can lead to crash for bad dates
Submitted: 24 Nov 2008 10:54 Modified: 20 Jan 2009 21:11
Reporter: rock liu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.1+ OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Tags: crash partition pruning
Triage: Triaged: D1 (Critical)

[24 Nov 2008 10:54] rock liu
Description:
platform details:
base on DELL 1950 with 1 Xeon 5445.
HDD:SAS RAID 1 with 146G
RAM:4 * FBD 667 1G
we deploy mysql5.1.29 with the binary version(RPM package).

we create a table ptsstat with patitions .there is about 15 million row record in this table .the table DDL fpllowing:
CREATE TABLE `pts_log` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '        ',
  `ACTION` varchar(20) NOT NULL DEFAULT '' COMMENT '        ',
  `STARTTIME` datetime NOT NULL,
  `ENDTIME` varchar(20) NOT NULL,
  `SRCUSERNAME` varchar(100) DEFAULT '-1' COMMENT '        ',
  `SRCPHONENO` varchar(50) DEFAULT '-1' COMMENT '        ',
  `DESTUSERNAME` varchar(100) DEFAULT '-1' COMMENT '          ',
  `DESCPHONENO` varchar(50) DEFAULT '-1' COMMENT '          ',
  `DURATION` varchar(20) DEFAULT '-1' COMMENT '        ,          ',
  `FLUX` varchar(20) DEFAULT '-1' COMMENT '      ,  byte      ',
  `STATUS` varchar(2) DEFAULT '-1' COMMENT '            (            PTS          )',
  `EXTEND` varchar(2000) DEFAULT '-1' COMMENT '        ',
  `PRE_SRCPHONENO` varchar(50) DEFAULT NULL COMMENT '                ',
  `PRE_DESCPHONENO` varchar(50) DEFAULT NULL COMMENT '                ',
  `PRE_SRCPHONENO_COUNTRYID` int(11) DEFAULT NULL,
  `PRE_DESCPHONENO_COUNTRYID` int(11) DEFAULT NULL COMMENT '                ',
  `VID` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`,`STARTTIME`),
  KEY `ACTION` (`ACTION`),
  KEY `VID` (`VID`),
  KEY `STARTTIME` (`STARTTIME`,`ENDTIME`),
  KEY `STARTTIME_single` (`STARTTIME`),
  KEY `ENDTIME_single` (`ENDTIME`)
) ENGINE=InnoDB AUTO_INCREMENT=12873854 DEFAULT CHARSET=utf8 COMMENT='pts        ; InnoDB free: 1247232 kB' /*!50100 PARTITION BY RANGE (to_days(STARTTIME)) (PARTITION p0 VALUES LESS THAN (733346) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (733376) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (733407) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (733438) ENGINE = InnoDB, PARTITION p5 VALUES LESS THAN (733467) ENGINE = InnoDB, PARTITION p6 VALUES LESS THAN (733498) ENGINE = InnoDB, PARTITION p7 VALUES LESS THAN (733528) ENGINE = InnoDB, PARTITION p8 VALUES LESS THAN (733559) ENGINE = InnoDB, PARTITION p9 VALUES LESS THAN (733589) ENGINE = InnoDB, PARTITION p10 VALUES LESS THAN (733620) ENGINE = InnoDB, PARTITION p11 VALUES LESS THAN (733651) ENGINE = InnoDB, PARTITION p12 VALUES LESS THAN (733681) ENGINE = InnoDB, PARTITION p13 VALUES LESS THAN (733712) ENGINE = InnoDB, PARTITION p14 VALUES LESS THAN (733742) ENGINE = InnoDB, PARTITION p15 VALUES LESS THAN (733773) ENGINE = InnoDB, PARTITION p16 VALUES LESS THAN (733804) ENGINE = InnoDB, PARTITION p17 VALUES LESS THAN (733832) ENGINE = InnoDB, PARTITION p18 VALUES LESS THAN (733863) ENGINE = InnoDB, PARTITION p19 VALUES LESS THAN (733893) ENGINE = InnoDB, PARTITION p20 VALUES LESS THAN (733924) ENGINE = InnoDB, PARTITION p21 VALUES LESS THAN (733954) ENGINE = InnoDB, PARTITION p22 VALUES LESS THAN (733985) ENGINE = InnoDB, PARTITION p23 VALUES LESS THAN (734016) ENGINE = InnoDB, PARTITION p24 VALUES LESS THAN (734046) ENGINE = InnoDB, PARTITION p25 VALUES LESS THAN (734077) ENGINE = InnoDB, PARTITION p26 VALUES LESS THAN (734107) ENGINE = InnoDB, PARTITION p27 VALUES LESS THAN (734138) ENGINE = InnoDB, PARTITION p28 VALUES LESS THAN (734169) ENGINE = InnoDB, PARTITION p29 VALUES LESS THAN (734197) ENGINE = InnoDB, PARTITION p30 VALUES LESS THAN (734228) ENGINE = InnoDB, PARTITION p31 VALUES LESS THAN (734258) ENGINE = InnoDB, PARTITION p32 VALUES LESS THAN (734289) ENGINE = InnoDB, PARTITION p33 VALUES LESS THAN (734319) ENGINE = InnoDB, PARTITION p34 VALUES LESS THAN (734350) ENGINE = InnoDB, PARTITION p35 VALUES LESS THAN (734381) ENGINE = InnoDB, PARTITION p36 VALUES LESS THAN (734411) ENGINE = InnoDB, PARTITION p37 VALUES LESS THAN (734442) ENGINE = InnoDB, PARTITION p38 VALUES LESS THAN (734472) ENGINE = InnoDB, PARTITION p39 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

CREATE TRIGGER `pts_log_bind_vid` BEFORE INSERT ON `pts_log`
  FOR EACH ROW
BEGIN
    DECLARE tempvid VARCHAR(100);
  	select user.vid into tempvid from user where `user`.`name` = NEW.SRCUSERNAME limit 1;
    SET NEW.VID= tempvid;
END;
.

configuration file: 
my-innodb-heavy-4G.cnf default

the error code details :
/usr/sbin/mysqld(my_print_stacktrace+0x21)[0x8483971]
/usr/sbin/mysqld(handle_segfault+0x37f)[0x81e009f]
[0x5a9420]
/usr/sbin/mysqld[0x82a25ee]
/usr/sbin/mysqld[0x82a4db3]
/usr/sbin/mysqld(_Z16prune_partitionsP3THDP8st_tableP4Item+0x581)[0x82afc61]
/usr/sbin/mysqld(_ZN4JOIN8optimizeEv+0x3b3)[0x824f853]
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x66)[0x8258996]
/usr/sbin/mysqld(_Z13handle_selectP3THDP6st_lexP13select_resultm+0x146)[0x8259326]
/usr/sbin/mysqld[0x81eb13f]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x508e)[0x81f236e]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x1ee)[0x81f49ce]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x489)[0x81f4e79]
/usr/sbin/mysqld(_Z10do_commandP3THD+0xe1)[0x81f6081]
/usr/sbin/mysqld(handle_one_connection+0x7b1)[0x81e74c1]
/lib/libpthread.so.0[0x90546b]
/lib/libc.so.6(clone+0x5e)[0x885dbe]
081124 18:23:41 - 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=89128960
read_buffer_size=262144
max_used_connections=13
max_threads=100
threads_connected=11
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 164474 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x976bd780
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...
stack_bottom = 0x9a09d210 thread_stack 0x30000
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x976c7118 is an invalid pointer
thd->thread_id=47
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
081124 18:23:41 mysqld_safe Number of processes running now: 0
081124 18:23:41 mysqld_safe mysqld restarted
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
081124 18:23:41  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 11900970, file name ./mysql-bin.000020
081124 18:23:41  InnoDB: Started; log sequence number 8 327292182
081124 18:23:41 [Note] Event Scheduler: Loaded 0 events
081124 18:23:41 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.29-rc-community-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

How to repeat:
if we execute the sql:

SELECT
    *
 FROM pts_log
 WHERE pts_log.STARTTIME >= '2008-10-01 00:00:00' 
       AND pts_log.STARTTIME < STR_TO_DATE(EXTRACT(YEAR_MONTH FROM CURDATE()), '%Y%m');

server crashing & auto recovery.

Suggested fix:
No solution ,we are eager for the solution
[25 Nov 2008 2:15] rock liu
At last ,we find object 'STR_TO_DATE(EXTRACT(YEAR_MONTH FROM CURDATE()), '%Y%m')' is not validate date data type .
we cahnge the legacy sql execution like :SELECT
    *
 FROM pts_log
 WHERE pts_log.STARTTIME >= '2008-03-01 00:00:00' 
       AND pts_log.STARTTIME < '2008-11-24 00:00' ;
the server won't crash down now .
but we're still puzzled that the invalid date data type will lead the whole server crash.could any one tell us .thank you very much!!!
[25 Dec 2008 17:09] Miguel Solorzano
To get the crash isn't necessary to populate the table, just create the table, the trigger and run the offended invalid query. I will test 6.0 tree:

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.31-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >use test
Database changed
mysql 5.1 >SELECT
    ->     *
    ->  FROM pts_log;
Empty set (0.20 sec)

mysql 5.1 >SELECT
    ->     *
    ->  FROM pts_log
    ->  WHERE pts_log.STARTTIME >= '2008-10-01 00:00:00'
    ->        AND pts_log.STARTTIME < STR_TO_DATE(EXTRACT(YEAR_MONTH FROM CURDATE()), '%Y%m');
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql 5.1 >

c:\dbs>c:\dbs\5.1\bin\mysqld --defaults-file=c:\dbs\5.1\my.ini --standalone --console
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
081225 13:59:47  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
081225 13:59:48  InnoDB: Started; log sequence number 0 508056
081225 13:59:48 [Note] Recovering after a crash using server51
081225 13:59:48 [Note] Starting crash recovery...
081225 13:59:48 [Note] Crash recovery finished.
081225 13:59:48 [Note] Event Scheduler: Loaded 0 events
081225 13:59:48 [Note] c:\dbs\5.1\bin\mysqld: ready for connections.
Version: '5.1.31-nt-log'  socket: ''  port: 3510  Source distribution
081225 14:01:42 - mysqld got exception 0xc0000005 ;
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=8388600
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337711 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x1909c90
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...
0049316C    mysqld.exe!mark_full_partition_used_no_parts()[opt_range.cc:2856]
004935D3    mysqld.exe!find_used_partitions()[opt_range.cc:3262]
0049EB01    mysqld.exe!prune_partitions()[opt_range.cc:2729]
0052D85B    mysqld.exe!JOIN::optimize()[sql_select.cc:879]
0053004C    mysqld.exe!mysql_select()[sql_select.cc:2357]
005303DC    mysqld.exe!handle_select()[sql_select.cc:269]
004FC674    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4890]
004FD8EB    mysqld.exe!mysql_execute_command()[sql_parse.cc:2184]
00502FF0    mysqld.exe!mysql_parse()[sql_parse.cc:5793]
005039AE    mysqld.exe!dispatch_command()[sql_parse.cc:1202]
0050452A    mysqld.exe!do_command()[sql_parse.cc:857]
0056FEDB    mysqld.exe!handle_one_connection()[sql_connect.cc:1115]
005CEC0B    mysqld.exe!_pthread_start()
006B9FAA    mysqld.exe!_threadstart()[thread.c:196]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 019609D8=
[25 Dec 2008 17:20] Miguel Solorzano
Repeatable on older release too:

C:\temp\mysql-5.1.22-rc-win32>BIN\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.22-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT
    ->     *
    ->  FROM pts_log
    ->  WHERE pts_log.STARTTIME >= '2008-10-01 00:00:00'
    ->        AND pts_log.STARTTIME < STR_TO_DATE(EXTRACT(YEAR_MONTH FROM CURDATE()), '%Y%m');
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[25 Dec 2008 18:00] Miguel Solorzano
Thank you for the bug report.

081225 14:57:01 [Note] c:\dbs\6.0\bin\mysqld: ready for connections.
Version: '6.0.10-alpha-nt-log'  socket: ''  port: 3600  Source distribution
081225 14:58:24 - mysqld got exception 0xc0000005 ;
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=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337754 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x77e9370
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...
0049A95C    mysqld.exe!mark_full_partition_used_no_parts()[opt_range.cc:2908]
0049ADC3    mysqld.exe!find_used_partitions()[opt_range.cc:3314]
004A6681    mysqld.exe!prune_partitions()[opt_range.cc:2781]
0053FC66    mysqld.exe!JOIN::optimize()[sql_select.cc:1513]
0054216D    mysqld.exe!mysql_select()[sql_select.cc:3027]
005424FC    mysqld.exe!handle_select()[sql_select.cc:298]
005082E0    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4747]
0050A71E    mysqld.exe!mysql_execute_command()[sql_parse.cc:3361]
0050F87B    mysqld.exe!mysql_parse()[sql_parse.cc:5735]
00510206    mysqld.exe!dispatch_command()[sql_parse.cc:1009]
00510EBD    mysqld.exe!do_command()[sql_parse.cc:690]
005846E1    mysqld.exe!handle_one_connection()[sql_connect.cc:1145]
005F6344    mysqld.exe!pthread_start()[my_winthread.c:61]
007F3877    mysqld.exe!_threadstartex()[threadex.c:241]
7C80B713    kernel32.dll!GetModuleFileNameA()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0775E038=SELECT
    *
 FROM pts_log
 WHERE pts_log.STARTTIME >= '2008-10-01 00:00:00'
       AND pts_log.STARTTIME < STR_TO_DATE(EXTRACT(YEAR_MONTH FROM CURDATE()), '%Y%m')
thd->thread_id=2
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

c:\dbs>
[27 Dec 2008 22:34] Mattias Jonsson
The crash can be avoided by changing '==' to '>=' in sql_partition.cc:~6941:
uint32 get_next_partition_id_range(PARTITION_ITERATOR* part_iter)
{
  if (part_iter->part_nums.cur == part_iter->part_nums.end)

Investigating further... (the optimizer should break this impossible query before, since '2008-12-00' is not an acceptable date in get_arg0_date, which is used when looking for the val_int_endpoint).
[28 Dec 2008 11:34] Bugs System
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/62384

2754 Mattias Jonsson	2008-12-28
      Bug#40972: some sql execution lead the whole databse crashing
      
      Problem was an errornous date that lead to end partition
      was before the start, leading to a crash.
      
      Solution was to check greater or equal instead of only
      equal between start and end partition.
      
      NOTE: partitioning pruning handles incorrect dates
      differently than index lookup, which can give different
      results in a partitioned table versus a non partitioned
      table for queries having 'bad' dates in the where clause.
[28 Dec 2008 11:42] Mattias Jonsson
Changing the synopsis, version, tags and OS according to the real bug.

Note to docs:
Partitioning pruning handles 'bad' dates differently than a normal WHERE clause.
If the date is 'bad' i.e. have zero for day or month, it is treated by the partitioning pruning as NULL, which leads to pruning of all partitions accept the first ones.

So a WHERE datetime >= <value in third partition> AND datetime < '2008-12-00'
will not give any result in a range partitioned table, as it would have done in a non partitioned table.
[8 Jan 2009 10:38] Mattias Jonsson
Patch queued into mysql-5.1-bugteam and mysql-6.0-bugteam
[9 Jan 2009 13:18] Bugs System
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/62827

2729 Mattias Jonsson	2009-01-09
      Bug#40972: Partition pruning can lead to crash for bad dates
      
      post push fix, added test found a valgrind warning
[14 Jan 2009 9:33] Bugs System
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/63197

2748 He Zhenxing	2009-01-14 [merge]
      Auto merge
[15 Jan 2009 6:41] Bugs System
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:davi.arnaut@sun.com-20090113150631-y84w12o2zmh7j3qd) (merge vers: 5.1.31) (pib:6)
[15 Jan 2009 16:16] Jon Stephens
Documented bugfix in the 5.1.31 changelog as follows:

        A comparison with an invalid DATE value in a query against a
        partitioned table could lead to a crash of the MySQL server.

        NOTE: Invalid DATE and DATETIME values referenced in the 
        WHERE clause of a query on a partitioned table are treated 
        as NULL. See "Partition Pruning", for more information.

Set back to PQ status pending merge to 6.0 tree.
[19 Jan 2009 11:29] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:07] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 13:47] Jon Stephens
Set back to NDI pending merge to 6.0.
[19 Jan 2009 16:13] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[19 Jan 2009 17:02] Jon Stephens
Set back to NDI pending merge to 6.0.
[20 Jan 2009 18:58] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:mattias.jonsson@sun.com-20090109132538-e92ay57yc1op0an2) (merge vers: 6.0.10-alpha) (pib:6)
[20 Jan 2009 21:11] Jon Stephens
Also noted in 6.0.10 changelog; closed.