| Bug #14799 | invalid datetime converting when sql_mode is not set | ||
|---|---|---|---|
| Submitted: | 9 Nov 2005 17:51 | Modified: | 15 Nov 2005 19:00 |
| Reporter: | Alexander Y. Fomichev | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.15 | OS: | Linux (Linux) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[10 Nov 2005 0:06]
MySQL Verification Team
Could you please provide which is the expected result you think is
correct?
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.17-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SET @@sql_mode=ALLOW_INVALID_DATES;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@sql_mode;
+---------------------+
| @@sql_mode |
+---------------------+
| ALLOW_INVALID_DATES |
+---------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `a` (
-> `i` int(11) unsigned NOT NULL default '0',
-> `time1` datetime NOT NULL default '0000-00-00 00:00:00',
-> `time2` datetime NOT NULL default '0000-00-00 00:00:00'
-> ) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO a VALUES (1,'2005-00-00 00:00:00','2005-00-00 00:00:00');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO a VALUES (1,'2005-04-31 11:11:11','2005-04-31 11:11:11');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM a;
+---+---------------------+---------------------+
| i | time1 | time2 |
+---+---------------------+---------------------+
| 1 | 2005-00-00 00:00:00 | 2005-00-00 00:00:00 |
| 1 | 2005-04-31 11:11:11 | 2005-04-31 11:11:11 |
+---+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql>
Thanks in advance.
[10 Nov 2005 10:11]
Alexander Y. Fomichev
Sorry, probably i'm couldn't show exactly what i mean, works well, but what about:
localhost tmp # mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36 to server version: 5.0.16-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SET @@sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `a` (
-> `i` int(11) unsigned NOT NULL default '0',
-> `time1` datetime NOT NULL default '0000-00-00 00:00:00',
-> `time2` datetime NOT NULL default '0000-00-00 00:00:00'
-> ) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO a VALUES (1,'2005-04-31 11:11:11','2005-04-31 11:11:11');
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> SELECT * FROM a;
+---+---------------------+---------------------+
| i | time1 | time2 |
+---+---------------------+---------------------+
| 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+---+---------------------+---------------------+
1 row in set (0.00 sec)
If i understand documentation correctly '2005-04-31 11:11:11' _should not_ be converted to '0000-00-00 00:00:00'. In a strict mode it should be ERROR 1292 (22007) : Incorrect datetime value:
and when sql_mode=ALLOW_INVALID_DATES it should be '2005-04-31 11:11:11',
or i missing something?
[10 Nov 2005 10:13]
Alexander Y. Fomichev
- Sorry, probably i'm couldn't show exactly what i mean, works well, but what about: + Sorry, probably i'm couldn't show exactly what i mean, ALLOW_INVALID_DATES works well, but what about:
[15 Nov 2005 19:00]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant product(s). Additional info: The documentation needed to be updated. ALLOW_INVALID_DATES doesn't just apply in strict mode. This is actually how MySQL works now, as of 5.0.2: MySQL requires month and day to be correct (not just month = 1..12, day = 1..31) If a date is invalid, it's converted to 0000-00-00 and a warning occurs in non-strict mode. In strict mode, an error occurs. Setting ALLOW_INVALID_DATES suppresses full checking. The only check is that month = 1..12, day = 1..31. This works in non-strict or strict mode.
Description: When sql_mode is not set mysql converts invalid datetimes in some strange manner. According to documentation: * In non-strict mode, the MySQL server performs only basic checking on the validity of a date: The ranges for year, month, and day are 1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date containing parts not within these ranges is subject to conversion to `'0000-00-00''. Please note that this still allows you to store invalid dates such as `'2002-04-31''. To ensure that a date is valid when not using strict mode, you should perform a check in your application. and, * `ALLOW_INVALID_DATES' [skip] For example, `'2004-04-31'' is legal with strict mode disabled, but illegal with strict mode enabled. To allow such dates in strict mode, enable `ALLOW_INVALID_DATES' as well. How to repeat: localhost tmp # cat test.sql /* SET sql_mode=ALLOW_INVALID_DATES; */ SELECT @@sql_mode; DROP TABLE IF EXISTS a; CREATE TABLE `a` ( `i` int(11) unsigned NOT NULL default '0', `time1` datetime NOT NULL default '0000-00-00 00:00:00', `time2` datetime NOT NULL default '0000-00-00 00:00:00' ) ENGINE=MyISAM DEFAULT CHARSET=cp1251; INSERT INTO a VALUES (1,'2005-00-00 00:00:00','2005-00-00 00:00:00'); INSERT INTO a VALUES (1,'2005-04-31 11:11:11','2005-04-31 11:11:11'); SELECT * FROM a; DROP TABLE IF EXISTS a; localhost tmp # mysql test < /var/tmp/test.sql @@sql_mode i time1 time2 1 2005-00-00 00:00:00 2005-00-00 00:00:00 1 0000-00-00 00:00:00 0000-00-00 00:00:00 AFAICS datetime 2005-04-31 11:11:11 is valide under the terms of basic checking. ( When sql_mode is set to ALLOW_INVALID_DATES or STRICT* all thingth looks good ) localhost tmp # mysql test < /var/tmp/test.sql @@sql_mode ALLOW_INVALID_DATES i time1 time2 1 2005-00-00 00:00:00 2005-00-00 00:00:00 1 2005-04-31 11:11:11 2005-04-31 11:11:11 localhost tmp # mysql test < /var/tmp/test.sql @@sql_mode STRICT_ALL_TABLES ERROR 1292 (22007) at line 10: Incorrect datetime value: '2005-04-31 11:11:11' for column 'time1' at row 1 I could reproduse it with latest bk snapshop 5.0.16 20051109 Suggested fix: I don't know really whether documentation have to be fixed or STRICT_* flags should be checked before check_date() will be called, but something should i mean. And yes, in last case mysqldump should be fixed IMO beacuse it overrides default SQL_MODE and as far as i can see i can't append ALLOW_INVALID_DATES here so can't load dump from 4.1 to 5.0 ( i.g. if i have unique index on the datetime fild with invalid values )