| Bug #41470 | DATE_FORMAT() crashes the complete server with a valid date | ||
|---|---|---|---|
| Submitted: | 15 Dec 2008 13:03 | Modified: | 28 Jan 2009 21:25 |
| Reporter: | Kees Hoekzema | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S1 (Critical) |
| Version: | 5.0/5.1/6.0 | OS: | Linux (x86_64-icc-glibc23) |
| Assigned to: | Tatiana Azundris Nuernberg | CPU Architecture: | Any |
| Tags: | regression | ||
[15 Dec 2008 13:24]
Kees Hoekzema
typo fixed & version added
[15 Dec 2008 14:33]
MySQL Verification Team
Thank you for the bug report. I was able to repeat on 5.0/5.1/6.0 bzr source 64-bit and couldn't repeat on Windows server 32-bit:
miguel@hegel:~/dbs$ 5.0/bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.76-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT DATE_FORMAT("0000-01-01",'%W %d %M %Y') as bla;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> SELECT DATE_FORMAT("0000-02-28",'%W %d %M %Y') as bla;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
ERROR:
Can't connect to the server
mysql>
c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.76-nt-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql 5.0 > SELECT DATE_FORMAT("0000-01-01",'%W %d %M %Y') as bla;
+---------------------+
| bla |
+---------------------+
| Sep 01 January 0000 |
+---------------------+
1 row in set (0.06 sec)
mysql 5.0 > SELECT DATE_FORMAT("0000-02-28",'%W %d %M %Y') as bla;
+----------------------+
| bla |
+----------------------+
| Nov 28 February 0000 |
+----------------------+
1 row in set (0.00 sec)
[8 Jan 2009 5:28]
Tatiana Azundris Nuernberg
Docs team, pls see below! On a side-note, "valid date" is relative. On one hand, some people may contest the existence of "year zero." http://en.wikipedia.org/wiki/Year_zero On the other, the docs say that outside the null value ('0000-00-00'), the supported range for the date-type time begins in year 1000. http://dev.mysql.com/doc/refman/5.0/en/datetime.html That of course doesn't mean we get to crash as merry as the day is long, that would be ridiculous, just that any results should be taken with a dose of salt. Now for the rub. calc_daynr() is tagged thus: "Calculate nr of day since year 0 in new date-system (from 1615)" This may look innocuous enough, so let's spell it out: in 1582, "the West" switched from the Julian to the Gregorian calendar. Not only did this reform leap day handling, it also resulted in a few days going AWOL: "Ten days were removed from the calendar, and it was decreed that the day following Thursday, October 4, 1582 (i.e., October 5, 1582 of the Julian calendar) would be known as Friday, October 15, 1582." In real terms this means that while all other calendars seem to acknowledge this and give pre-switch dates (and days-of-the-week) in Julian, the calendar system that actually applied at the time, we give the data in Gregorian. I'll leave it for you to decide whether our handling there is outright rubbish, but I think I'm not going out on a ledge if I say that some users may at least find it *unexpected* (even if they're not calendar geeks, they may notice that we give pre-switch days-of-the-week that differ from every other calendar they consult), so there should probably be a note to that effect in the docs.
[8 Jan 2009 9:32]
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/62661 2725 Tatiana A. Nurnberg 2009-01-08 Bug#41470: DATE_FORMAT() crashes the complete server with a valid date Passing dubious "year zero" in non-zero date (not "0000-00-00") could lead to negative value for year internally, while variable was unsigned. This led to Really Bad Things further down the line. Now doing calculations with signed type for year internally.
[8 Jan 2009 9:44]
Tatiana Azundris Nuernberg
As a side-note, the calendar-problem has no easy answer since different countries switched in different years, in some case different centuries -- Turkey did not switch to Gregorian until 1927!
[12 Jan 2009 2:09]
Tatiana Azundris Nuernberg
patch queued for 5.0.76, 5.1.31, 6.0.10 in -bugteam
[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 6:44]
Bugs System
Pushed into 5.0.77 (revid:joro@sun.com-20090115043518-cn6jeeg78j6mupp3) (version source revid:azundris@mysql.com-20090108092531-ysz636a6uu66mu38) (merge vers: 5.0.76) (pib:6)
[19 Jan 2009 11:23]
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:00]
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 16:06]
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)
[20 Jan 2009 18:53]
Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:igor@mysql.com-20090110022839-s9xt3acu2yq33n26) (merge vers: 6.0.10-alpha) (pib:6)
[28 Jan 2009 21:25]
Paul DuBois
Noted in 5.0.78, 5.1.31, 6.0.10 changelogs. DATE_FORMAT() could cause a server crash for year-zero dates.
[9 Jun 2009 19:03]
Paul DuBois
Noted in 5.0.74sp1 changelog.
Description: When you use the statement below on a mysql server, the server crashes with a signal 11. This new functionality only occurs in 5.0.67. The older version i tested (5.0.51a-12) is not affected. The bug occurs in the following binaries downloaded from the MySQL site: mysql-5.0.67-linux-x86_64-glibc23.tar.gz mysql-5.0.67-linux-x86_64-icc-glibc23.tar.gz How to repeat: SELECT DATE_FORMAT("0000-01-01",'%W %d %M %Y') as bla; SELECT DATE_FORMAT("0000-02-28",'%W %d %M %Y') as bla; And all dates between 0000-01-01 and 0000-02-28. Apparently we have some immortals visiting our site who were born close to the original Christmas. Suggested fix: not a fix; but a workaround: downgrading to 5.0.51a-12 or do not use dates between 0000-01-01 and 0000-02-28.