| 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: | |
| Category: | MySQL Server: Partitions | Severity: | S1 (Critical) |
| Version: | 5.1+ | OS: | Any |
| Assigned to: | Mattias Jonsson | CPU Architecture: | Any |
| Tags: | crash partition pruning | ||
[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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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.
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