| Bug #6832 | NO_ZERO_DATE does not prevent zero dates | ||
|---|---|---|---|
| Submitted: | 25 Nov 2004 20:58 | Modified: | 24 May 2005 19:15 |
| Reporter: | Trudy Pelzer | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | SuSE 9.1 | OS: | 5.0.2-alpha-debug |
| Assigned to: | CPU Architecture: | Any | |
[3 May 2005 17:48]
Charles Sanders
I would like to add to this bug. According to documentation, I should not be able to insert an invalid date in a timestamp field, but I can. I get a warning and the timestamp value in the table is 0. Need some way to configure the server to return an error and NOT perform the insert/update when given an invalid date. Example: create table test (id integer, col_time timestamp); insert into test (id, col_time) values (1, '2004-30-30'); Query OK, 1 row affected, 1 warning (0.03 sec) select * from test; +------+---------------------+ | id | col_time | +------+---------------------+ | 1 | 0000-00-00 00:00:00 | +------+---------------------+ 1 row in set (0.00 sec) If I insert/update an invaid date value, I do not want the insert/update performed. I want an error throw.
[23 May 2005 14:30]
Michael Widenius
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: This is not a bug but a documentation issue NO_ZERO_DATES means the following: If NO_ZERO_DATES is not set it means that 0000-00-00 is a legal value in DATE and DATETIME fields. In other words, one should NOT get any warnings for this. If NO_ZERO_DATES is set and we are NOT in strict or traditional mode, it means that one will get a warning when inserting a zero date into a date or datetime column, but the statement will not be aborted. This is the same thing as inserting a too-long character string into a CHAR/VARCHAR field. By default MySQL just does a warning and continues. If NO_ZERO_DATES are set and we are in strict or traditional mode, one will get an error if a ZERO date is inserted, as if one had tried to insert a too long character string into a CHAR/VARCHAR field.
[24 May 2005 19:15]
Paul DuBois
I have clarified the descriptions for NO_ZERO_DATE (and NO_ZERO_IN_DATE).
Description: In the section that describes the various settings for SQL_MODE, the MySQL Reference Manual says: NO_ZERO_DATE Don't allow '0000-00-00' as a valid date. You can still insert zero dates with the IGNORE option. (New in MySQL 5.0.2) But NO_ZERO_DATE by itself does not disallow zero dates. For DATE and TIMESTAMP columns, it returns a warning but accepts a zero date. For DATETIME columns it accepts a zero date without returning a warning. We want two things: (1) Under "regular" sql_mode (i.e. set sql_mode=''), zero dates should be accepted, with a warning returned by the server: "Storing zero date in column '%s' at row %ld" (2) Under "no_zero_date" sql_mode, zero dates should be rejected by the server with SQLSTATE 22007 "Incorrect datetime value: '0000-00-00 10:00:00' for column '%s' at row %ld" How to repeat: mysql> create table t1 (col1 date, col2 datetime, col3 timestamp); mysql> set sql_mode=''; mysql> insert into t1 (col1) values ('0000-00-00'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 (col2) values ('0000-00-00 10:00:00'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 (col3) values ('0000-00-00 10:00:00'); Query OK, 1 row affected, 1 warning (0.00 sec) -- As expected, but a warning should be returned for each INSERT mysql> show warnings; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1265 | Data truncated for column 'col3' at row 1 | +---------+------+-------------------------------------------+ mysql> select * from t1; +------------+---------------------+---------------------+ | col1 | col2 | col3 | +------------+---------------------+---------------------+ | 0000-00-00 | NULL | 2004-11-25 13:14:15 | | NULL | 0000-00-00 10:00:00 | 2004-11-25 13:14:38 | | NULL | NULL | 0000-00-00 00:00:00 | +------------+---------------------+---------------------+ -- This behaviour is as expected. Under "regular" sql_mode, zero dates are allowed. mysql> set sql_mode='traditional'; mysql> delete from t1; mysql> insert into t1 (col1) values ('0000-00-00'); ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'col1' at row 1 mysql> insert into t1 (col2) values ('0000-00-00 10:00:00'); ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 10:00:00' for column 'col2' at row 1 mysql> insert into t1 (col3) values ('0000-00-00 10:00:00'); ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 10:00:00' for column 'col3' at row 1 mysql> select * from t1; Empty set (0.00 sec) -- This behaviour is as expected. Under "traditional" sql mode, zero dates are disallowed. mysql> set sql_mode=''; mysql> set sql_mode='no_zero_date'; mysql> select @@sql_mode; +--------------+ | @@sql_mode | +--------------+ | NO_ZERO_DATE | +--------------+ mysql> delete from t1; mysql> insert into t1 (col1) values ('0000-00-00'); Query OK, 1 row affected, 1 warning (0.00 sec) -- Not as expected. The zero date should have been rejected, as in "traditional mode". mysql> show warnings; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1265 | Data truncated for column 'col1' at row 1 | +---------+------+-------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into t1 (col2) values ('0000-00-00 10:00:00'); Query OK, 1 row affected (0.00 sec) -- Not as expected. The zero date should have been rejected, as in "traditional mode". mysql> show warnings; Empty set (0.00 sec) mysql> insert into t1 (col3) values ('0000-00-00 10:00:00'); Query OK, 1 row affected, 1 warning (0.00 sec) -- Not as expected. The zero date should have been rejected, as in "traditional mode". mysql> show warnings; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1265 | Data truncated for column 'col3' at row 1 | +---------+------+-------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t1; +------------+---------------------+---------------------+ | col1 | col2 | col3 | +------------+---------------------+---------------------+ | 0000-00-00 | NULL | 2004-11-25 13:32:05 | | NULL | 0000-00-00 10:00:00 | 2004-11-25 13:32:15 | | NULL | NULL | 0000-00-00 00:00:00 | +------------+---------------------+---------------------+