Bug #23240 --init-file statements with NOW() reports '1970-01-01 11:00:00' as the date time
Submitted: 13 Oct 2006 0:38 Modified: 8 Mar 2007 3:23
Reporter: Nicholas Ring Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.24a/4.1BK OS:Windows (Windws XP/Linux)
Assigned to: Kristofer Pettersson CPU Architecture:Any
Tags: init-file, NOW()

[13 Oct 2006 0:38] Nicholas Ring 
Description:
If --init-file=file.sql is supplied and file.sql contains a query that inserts a date using the NOW() function, the date reported is '1970-01-01 11:00:00' and not the actual date/time.

How to repeat:
Add the following line to the my.ini file (assuming default install).

init-file="C:/Program Files/MySQL/MySQL Server 5.0/Server-init-file.sql"

create the file "C:/Program Files/MySQL/MySQL Server 5.0/Server-init-file.sql" with the following contents:
-- Start

CREATE DATABASE IF NOT EXISTS init_file;
CREATE TABLE IF NOT EXISTS init_file.startup ( startdate DATETIME );
INSERT INTO init_file.startup VALUES ( NOW() );

-- End

Now start the server.
[13 Oct 2006 0:39] Nicholas Ring 
I forgot to add, now check the table "init_file.startup"
[30 Oct 2006 14:58] MySQL Verification Team 
Thank you for the bug report:

mysql> select * from startup;
+---------------------+
| startdate           |
+---------------------+
| 1969-12-31 20:00:00 | 
+---------------------+
1 row in set (0.01 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.0.27-debug-log | 
+------------------+
1 row in set (0.00 sec)
-----------------------------------------------------------------
+------------------+
| version()        |
+------------------+
| 4.1.22-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> select * from startup;
+---------------------+
| startdate           |
+---------------------+
| 1969-12-31 20:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql>
[15 Feb 2007 17:49] 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/19957

ChangeSet@1.2594, 2007-02-15 18:49:18+01:00, thek@kpdesk.mysql.com +5 -0
  Bug#23240 --init-file statements with NOW() reports '1970-01-01 11:00:00'as the date time
  
  - Starting time of a query sent by file bootstrapping wasn't initialized
    and starting time defaulted to 0. This later used value by the Now-
    item and is translated to 1970-01-01 11:00:00.
  - marking the time with thd->set_time() before the call to 
    mysql_parse resolves this issue.
[16 Feb 2007 13:14] Kristofer Pettersson 
From review on IRC:
1. Highlight that the error happens in bootstrap and investigate if separate --bootstrap test needed.
2. is THD::end_time necessary?
3. Investigate if possible to move set_time/end_time into mysql_parse
[19 Feb 2007 14:57] Konstantin Osipov 
Approved the second version of the patch over email.
[19 Feb 2007 17:37] 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/20064

ChangeSet@1.2594, 2007-02-19 09:37:34+01:00, thek@kpdesk.mysql.com +4 -0
  Bug#23240 --init-file statements with NOW() reports '1970-01-01 11:00:00'as the date time
  
  - Starting time of a query sent by file bootstrapping wasn't initialized
    and starting time defaulted to 0. This later used value by the Now-
    item and is translated to 1970-01-01 11:00:00.
  - marking the time with thd->set_time() before the call to 
    mysql_parse resolves this issue.
[26 Feb 2007 19:34] Konstantin Osipov 
Approved the second version of the patch by email.
[7 Mar 2007 22:00] Konstantin Osipov 
Pushed into 5.0.38 and 5.1.17. Not in 4.1 (huh?)
[7 Mar 2007 22:01] Konstantin Osipov 
Actually, no, present in 4.1. Version 4.1.23
[8 Mar 2007 3:23] Paul DuBois 
Noted in 4.1.23, 5.0.38, 5.1.17 changelogs.