MySQL Bugs: #20691: DATETIME col (NOT NULL, NO DEFAULT) may insert garbage when specifying DEFAULT

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

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)

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)

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)

Sorry to bother you again, but did you also try *without* 'describe table'?

Thanks.

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)

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)

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)

It appears to be using uninitialized memory.  The DESCRIBE merely overwrites some memory that the INSERT later uses.

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.)

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.

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.

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.

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

The fix has been pushed into 5.0.36 and 5.1.16-beta.

Moved the changelog entries from 5.0.32, 5.1.15
to 5.0.36, 5.1.16.

yes. please fix this one.