Bug #20691 DATETIME col (NOT NULL, NO DEFAULT) may insert garbage when specifying DEFAULT
Submitted: 26 Jun 2006 10:28 Modified: 26 Feb 2007 20:40
Reporter: Michael Wallner (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.12-BK, 5.0.23-BK, 5.0.x OS:Linux (Linux, Debian)
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: Q1

[26 Jun 2006 10:28] Michael Wallner
Description:
When creating a table with a NOT NULL DATETIME column while not specifying a default value *may* cause some sort of corruption.  I wrote "may" because it doesn't seem to happen all the time.

How to repeat:
mysql> create table test_date ( d datetime NOT NULL );
Query OK, 0 rows affected (0.05 sec)

mysql> describe test_date;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | datetime | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into test_date values(DEFAULT);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1364 | Field 'd' doesn't have a default value |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_date;
+---------------------+
| d                   |
+---------------------+
| q*(0-0.-00 98:55:30 |
+---------------------+
1 row in set (0.01 sec)

mysql> insert into test_date values(NULL);
ERROR 1048 (23000): Column 'd' cannot be null
mysql> insert into test_date values(CURRENT_DATE);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_date;
+---------------------+
| d                   |
+---------------------+
| q*(0-0.-00 98:55:30 |
| 2006-06-26 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> alter table test_date change column d d datetime NOT NULL default '0000-00-00 00:00:00';
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'd' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_date;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
| 2006-06-26 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> insert into test_date VALUES(DEFAULT);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_date;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
| 2006-06-26 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
3 rows in set (0.01 sec)

mysql> SHOW VARIABLES like '%version%';
+-------------------------+----------------------------+
| Variable_name           | Value                      |
+-------------------------+----------------------------+
| protocol_version        | 10                         |
| version                 | 5.0.21-Debian_3ubuntu1-log |
| version_comment         | Debian Etch distribution   |
| version_compile_machine | i486                       |
| version_compile_os      | pc-linux-gnu               |
+-------------------------+----------------------------+
5 rows in set (0.00 sec)

mysql> SHOW VARIABLES like 'sql%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
| sql_notes     | ON    |
| sql_warnings  | ON    |
+---------------+-------+
3 rows in set (0.00 sec)
[26 Jun 2006 10:35] Michael Wallner
I figured what triggers a right insert. Everything's fine if you run `DESCRIBE table` prior INSERT.

So above reproduce steps are not able to replicate the issue:

mysql> show variables like '%version%';
+-------------------------+--------------------------+
| Variable_name           | Value                    |
+-------------------------+--------------------------+
| protocol_version        | 10                       |
| version                 | 5.0.22-Debian_2-log      |
| version_comment         | Debian Etch distribution |
| version_compile_machine | i486                     |
| version_compile_os      | pc-linux-gnu             |
+-------------------------+--------------------------+
5 rows in set (0.02 sec)

mysql> show variables like 'sql%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
| sql_notes     | ON    |
| sql_warnings  | ON    |
+---------------+-------+
3 rows in set (0.00 sec)

mysql> create table td (d datetime NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into td values(DEFAULT);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from td;
+---------------------+
| d                   |
+---------------------+
| q283-50-45 76:92:16 |
+---------------------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> insert into td values(DEFAULT);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from td;
+---------------------+
| d                   |
+---------------------+
| q283-50-45 76:92:16 |
| q283-50-45 76:92:16 |
+---------------------+
2 rows in set (0.00 sec)

mysql> describe td;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | datetime | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into td values(DEFAULT);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from td;
+---------------------+
| d                   |
+---------------------+
| q283-50-45 76:92:16 |
| q283-50-45 76:92:16 |
| 0000-00-00 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)
[26 Jun 2006 10:44] Valeriy Kravchuk
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html

With 5.0.23-BK I've got:

mysql> create table test_date ( d datetime NOT NULL );
Query OK, 0 rows affected (0.01 sec)

mysql> desc test_date;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | datetime | NO   |     |         |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into test_date values(DEFAULT);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1364 | Field 'd' doesn't have a default value |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_date;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.23    |
+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'sql%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| sql_big_selects | ON    |
| sql_mode        |       |
| sql_notes       | ON    |
| sql_warnings    | OFF   |
+-----------------+-------+
4 rows in set (0.00 sec)

mysql> set session sql_warnings=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| sql_big_selects | ON    |
| sql_mode        |       |
| sql_notes       | ON    |
| sql_warnings    | ON    |
+-----------------+-------+
4 rows in set (0.01 sec)

mysql> insert into test_date values(DEFAULT);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1364 | Field 'd' doesn't have a default value |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_date;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)
[26 Jun 2006 10:49] Michael Wallner
Sorry to bother you again, but did you also try *without* 'describe table'?

Thanks.
[26 Jun 2006 10:52] Valeriy Kravchuk
Sorry, missed your comment before sending. It is a bug, easily repeatable just as you described:

mysql> create table td (d datetime NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into td values(DEFAULT);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from td;
+---------------------+
| d                   |
+---------------------+
| ?739-85-49 19:27:04 |
+---------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.23    |
+-----------+
1 row in set (0.00 sec)
[26 Jun 2006 10:57] Valeriy Kravchuk
Same problem with 5.1-BK:

mysql> create table td (d datetime NOT NULL);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into td values(DEFAULT);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> select * from td;
+---------------------+
| d                   |
+---------------------+
| Y440-26-98 65:10:45 |
+---------------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.12-beta-debug |
+-------------------+
1 row in set (0.01 sec)

mysql> desc td;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | datetime | NO   |     |         |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into td values(DEFAULT);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from td;
+---------------------+
| d                   |
+---------------------+
| Y440-26-98 65:10:45 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)
[26 Jun 2006 11:00] Valeriy Kravchuk
4.1-BK works properly, though:

mysql> create table td (d datetime NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into td values(DEFAULT);
Query OK, 1 row affected (0.01 sec)

mysql> select * from td;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.21    |
+-----------+
1 row in set (0.00 sec)
[7 Nov 2006 19:25] Chad MILLER
It appears to be using uninitialized memory.  The DESCRIBE merely overwrites some memory that the INSERT later uses.
[9 Nov 2006 23: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/15105

ChangeSet@1.2297, 2006-11-09 18:33:58-05:00, cmiller@zippy.cornsilk.net +3 -0
  Bug#20691: DATETIME col (NOT NULL, NO DEFAULT) may insert garbage when \
  	specifying DEFAULT
  
  This was not specific to datetime.  When there is no default value 
  for a column, and the user inserted DEFAULT, we would write 
  uninitialized memory to the table.  
  
  Now, insist on writing a default value, a zero-ish value, the same 
  one that comes from inserting NULL into a not-NULL field.
  
  (This is, at best, really strange behavior that comes from allowing 
  sloppy usage, and serves as a good reason always to run one's server 
  in a strict SQL mode.)
[13 Dec 2006 19:31] Paul DuBois
Noted in 5.0.32, 5.1.15 changelogs.

Inserting DEFAULT into a column with no default value could result in
garbage in the column. Now the same result occurs as when inserting
NULL into a NOT NULL column.
[14 Jan 2007 21:10] Alexey Botchkov
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/18099

ChangeSet@1.2377, 2007-01-15 01:11:56+04:00, holyfoot@mysql.com +5 -0
  bug #20961 (DEFAULT for NO DEFAULT may insert garbage)
  GEOMETRY field fixed - now it returns an error in this
  case.
[29 Jan 2007 9:15] 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/18936

ChangeSet@1.2390, 2007-01-29 13:18:10+04:00, holyfoot@mysql.com +3 -0
  bug #20691 (garbage specifying DEFAULT)
  
  For the geometry field accessing to the DEFAULT value should produce
  and error when no DEFAULT was specified.
[12 Feb 2007 11:40] 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/19684

ChangeSet@1.2400, 2007-02-12 15:41:36+04:00, holyfoot@mysql.com +5 -0
  bug #20691 (INSERT (DEFAULT) may insert garbage with NO DEFAULT NOT NULL field)
  
  Some fields (GEOMETRY first of all) can't be handled properly in this
  case at all. So we return an error in this case
[14 Feb 2007 10:37] Igor Babaev
The fix has been pushed into 5.0.36 and 5.1.16-beta.
[26 Feb 2007 20:40] Paul DuBois
Moved the changelog entries from 5.0.32, 5.1.15
to 5.0.36, 5.1.16.
[8 Jan 2009 0:14] ful jencio
yes. please fix this one.