MySQL Bugs: #68041: Zero date can be inserted in strict no-zero mode through a default value

Bug #68041	Zero date can be inserted in strict no-zero mode through a default value
Submitted:	6 Jan 2013 3:29	Modified:	19 Jul 2013 15:02
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.9	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
After recent changes, it has again become possible to insert an invalid value (in this case, a zero date or a date with zeros) into a table through a default value, even if all related restricting sql modes are set at the time of insertion. 

set sql_mode='';
create table t1 (i int, col1 date default 0) engine=myisam;
set sql_mode='no_zero_date,strict_all_tables';
insert into t1 (i) values (1);
select * from t1;
i	col1
1	0000-00-00

Analysis by Sergei Golubchik:

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

How to repeat:
set sql_mode='';
create table t1 (i int, col1 date default 0) engine=myisam;

set sql_mode='no_zero_date,strict_all_tables';

--error ER_TRUNCATED_WRONG_VALUE
insert into t1 values (1,'0000-00-00');

insert into t1 (i) values (1);
select * from t1;

I do not see "fix" documented for 5.5, but looks like there is no problem now to insert zero date with 5.5.29:

mysql> set sql_mode='';
Query OK, 0 rows affected (0.22 sec)

mysql> create table t1 (i int, col1 date default 0) engine=myisam;
Query OK, 0 rows affected (0.41 sec)

mysql> set sql_mode='no_zero_date,strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (i) values (1);
Query OK, 1 row affected (0.19 sec)

mysql> select * from t1;
+------+------------+
| i    | col1       |
+------+------------+
|    1 | 0000-00-00 |
+------+------------+
1 row in set (0.11 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.29    |
+-----------+
1 row in set (0.05 sec)

Hi Valeriy,

You are right of course. Here is the correct test case:

set sql_mode='no_zero_date';
create table t1 (i int, col1 date default 0) engine=myisam;
set sql_mode='no_zero_date,strict_all_tables';
insert into t1 (i) values (1);

The point is, as strange protection as bugfix for bug #5903 gave from inserting wrong values, it was consistent over all data types: if a value is wrong for the given data type (if it causes at least a warning on normal insertion), it was prohibited as default on table creation, regardless the strictness of the current sql_mode. Now even this protection was shifted, but only for zero dates. Please consider the following:

set sql_mode='';
create table t1 (i int, t tinyint default 300) engine=myisam;

It's pretty much the same scenario as 

set sql_mode='no_zero_date';
create table t1 (i int, col1 date default 0) engine=myisam;

But now in 5.6 the latter is allowed while the former is not. 

Sorry for the mess, the bugfix for bug #5903 confuses me a bit, as well as later attempts to change the behavior. I would expect CREATE and INSERT to be handled independently, each according to the current sql_mode, but somehow it never happens, I assume there is a good technical reason for that.

Hi Elena Stepanova,

Thank you for reporting this bug.

The patch for bug#34280 is pushed in with revision id 3690.10.1.

I verified the behavior on build with above revision id and revision before this.

On revision 3690.10.1
      mysql> set sql_mode='';
      Query OK, 0 rows affected (0.00 sec)

      mysql> create table t1 (i int, t tinyint default 300) engine=myisam;
      ERROR 1067 (42000): Invalid default value for 't'

      mysql> set sql_mode='no_zero_date';
      Query OK, 0 rows affected (0.00 sec)

      mysql> create table t1 (i int, col1 date default 0) engine=myisam;
      ERROR 1067 (42000): Invalid default value for 'col1'

On revision BEFORE 3690.10.1,

      mysql> set sql_mode='';
      Query OK, 0 rows affected (0.00 sec)

      mysql> create table t1 (i int, t tinyint default 300) engine=myisam;
      ERROR 1067 (42000): Invalid default value for 't'

      mysql> set sql_mode='no_zero_date';
      Query OK, 0 rows affected (0.00 sec)

      mysql> create table t1 (i int, col1 date default 0) engine=myisam;
      ERROR 1067 (42000): Invalid default value for 'col1'

It is working as expected with or without patch for bug#34280.

Behavior may be broken because of some other bug. I will work to fix this issue.

Thanks,
Praveen

Hi Elena Stepanova,

I did some more study on this bug. The difference in behavior is because of
patch for Bug#34280 only.Please ignore my previous comment. 

I will work to fix this issue.

Thanks,
Praveen

When default value for date/timestamp/datetime is "0",
inserting tuple in sql_mode='no_zero_date,strict_all_tables'
is working in 5.1 and 5.5 versions also without any warnings
or error. Please see the test results below.

So, this issue is not regression. Bug exists in 5.1 and 5.5(without
fix for 34280). It is just noticed in 5.6 now.

Test on 5.1
=================================================
Server version: 5.1.69-debug Source distribution
...
mysql> use test;
Database changed
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(f1 int,f2 date default 0);
Query OK, 0 rows affected (0.08 sec)

mysql> set sql_mode='no_zero_date,strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(f1) values (10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------------+
| f1   | f2         |
+------+------------+
|   10 | 0000-00-00 |
+------+------------+
1 row in set (0.00 sec)

Test on 5.5
=================================================
Server version: 5.5.31-debug Source distribution
...

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table t1;
Query OK, 0 rows affected (0.10 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (f1 int, f2 date default 0);
Query OK, 0 rows affected (0.10 sec)

mysql> set sql_mode='no_zero_date,strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(f1) values(10);
Query OK, 1 row affected (0.09 sec)

mysql> select * from t1;
+------+------------+
| f1   | f2         |
+------+------------+
|   10 | 0000-00-00 |
+------+------------+
1 row in set (0.00 sec)

Noted in 5.6.13, 5.7.2 changelogs.

It is possible for a column DEFAULT value to be valid for the
sql_mode value at table-creation time but invalid for the sql_mode
value when rows are inserted or updated. Example:

SET sql_mode = '';
CREATE TABLE t (d DATE DEFAULT 0);
SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES';
INSERT INTO t (d) VALUES(DEFAULT);

In this case, 0 should be accepted for the CREATE TABLE but rejected
for the INSERT. However, the server did not evaluate DEFAULT values
used for inserts or updates against the current sql_mode. In the
example, the INSERT succeeds and inserts '0000-00-00' into the DATE
column. 

The server now applies the proper sql_mode checks to generate a
warning or error at insert or update time.

A resulting incompatibility for replication if you use
statement-based logging (binlog_format=STATEMENT) is that if a slave
is upgraded, a nonupgraded master will execute the preceding example 
without error, whereas the INSERT will fail on the slave and
replication will stop.

To deal with this, stop all new statements on the master and wait
until the slaves catch up. Then upgrade the slaves followed by the
master. Alternatively, if you cannot stop new statements, temporarily
change to row-based logging on the master (binlog_format=ROW) and
wait until all slaves have processed all binary logs produced up to
the point of this change. Then upgrade the slaves followed by the
master and change the master back to statement-based logging.