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:
None 
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:03] Kees Hoekzema
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.
[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.