Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Won't Fix
-
Affects Version/s: 10.0.0, 5.5.28, 5.3.9
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
Description
With sql_mode='NO_ZERO_DATE' MariaDB 5.1-10.0 and MySQL 5.1-5.5 throw an error on an attempt to create a table with a default zero date, e.g.
set sql_mode='NO_ZERO_DATE'; # Query OK, 0 rows affected (0.00 sec) CREATE TABLE t1 (d datetime default 0); # ERROR 1067 (42000): Invalid default value for 'd'
But MySQL 5.6 in the same situation only produces a warning:
SET sql_mode='NO_ZERO_DATE'; # Query OK, 0 rows affected (0.00 sec) CREATE TABLE t1 (d datetime default 0); # Query OK, 0 rows affected, 1 warning (2.32 sec) SHOW WARNINGS; # +---------+------+--------------------------------------------+ # | Level | Code | Message | # +---------+------+--------------------------------------------+ # | Warning | 1264 | Out of range value for column 'd' at row 1 | # +---------+------+--------------------------------------------+ # 1 row in set (0.00 sec)
As a result, replication from MySQL 5.6 to MariaDB 10.0 fails with 'Invalid default value for 't'' on query.
Same is true for NO_ZERO_IN_DATE and corresponding values.
Gliffy Diagrams
Attachments
Issue Links
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I'll re-open it for now and will assign to myself so that I don't forget to submit it to MySQL bugbase – if it's a non-intentional change, we need to have it fixed there, since it breaks the cross-replication.
Filed in MySQL bug base as http://bugs.mysql.com/bug.php?id=68041.
Looking at the history of this issue, I can't help wondering if there is a good technical reason for it to be such a long-term problem. From a user's point of view, the expected behavior seems to be pretty obvious, and it was actually described back in bug #5903. There was no requirement to reject creation of a table, even less so to do it regardless the current mode, and from the bug comments it's not clear whether it was a judgement call or a technical necessity.
Instead, the reasonable behavior seems to be to handle creation and insertion separately, each time depending on the current mode. That is, no matter how the table was created and how it appeared in the server – maybe it was just placed in the data directory? – if the current mode prohibits inserting a certain value, it should be rejected, whether it's inserted explicitly or via default.
Anyway, I agree that their bugfix for bug #34280 is incorrect, closing this bugreport.
I was going to report a new bug, but it is probably better to comment here.
I was able to insert a zero-date in MariaDB 5.5.25. And I didn't use a zero default value, I used another default that was ignored.
As far as I can understand, this behaviour really seems to be wrong:
MariaDB [test]> SET sql_mode = 'NO_ZERO_DATE';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> CREATE TABLE t (c DATE DEFAULT '1994-01-01');
Query OK, 0 rows affected (0.12 sec)
MariaDB [test]> INSERT INTO t (c) VALUES ('0000-00-00');
Query OK, 1 row affected, 1 warning (0.09 sec)
MariaDB [test]> SHOW WARNINGS;
-------------------------------------------------------
| Level | Code | Message |
-------------------------------------------------------
| Warning | 1264 | Out of range value for column 'c' at row 1 |
-------------------------------------------------------
1 row in set (0.00 sec)
MariaDB [test]> SELECT * FROM t;
------------
| c |
------------
| 0000-00-00 |
------------
1 row in set (0.01 sec)
MariaDB [test]>
Hi Federico,
This is actually the documented behavior. NO_ZERO_DATE alone makes server to produce a warning on inserting a zero date, but still allows to do so, while NO_ZERO_DATE + strict mode, e.g. NO_ZERO_DATE,STRICT_ALL_TABLES should prohibit inserting a zero date at all.
Actually I didn't set 'STRICT_ALL_TABLES' - sorry for wasting your time.
We won't fix this issue.
MySQL's behavior has changed in a revid:raveenkumar.hulakund@oracle.com-20120319175914-0wm47wvsglkl24ui as a fix for http://bugs.mysql.com/34280
In fact, the bug report is incorrect - the issue is not at all specific to invalid dates, all invalid values (numbers and strings too) in defaults are rejected. One can easily see from the test cases and bugdb that this was done to fix bug http://bugs.mysql.com/5903
Now Oracle has re-introduced this bug, that allows to break the data consistency and insert invalid data in the TRADITIONAL mode.
We won't do that.