MySQL Bugs: #17998: prevent users from setting ALLOW_INVALID

Bug #17998	prevent users from setting ALLOW_INVALID_DATES in their session
Submitted:	6 Mar 2006 22:21	Modified:	5 Oct 2008 10:29
Reporter:	Lukas Smith	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	all	OS:	Any (irrelevant)
Assigned to:		CPU Architecture:	Any

Description:
In order to ensure data consistency it should be possible to prevent users from setting ALLOW_INVALID_DATES in their session.

The documentation does not indicate anyway to prevent this:
http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

This might affect other (or future) sql_mode's as well.

How to repeat:
set ALLOW_INVALID_DATES in your session and insert a malformed date into a date column breaks data consistency

I think this feature request makes sense.

Possible ways to implement this:
 - A flag to tell the server to maintain AT LEAST the global SQL_MODE setting.
   (i.e. a user cannot set anything less than that within their connection)
 - An option like minimum_sql_mode to do the same, but allow the global setting to differ
   (the global setting should be subject to this minimum also, though)

Thank you for the bug report.

mysql> insert into tb5 (nasc) values ("2006-14-01");
ERROR 1292 (22007): Incorrect date value: '2006-14-01' for column 'nasc' at row 1
mysql> set sql_mode="ALLOW_INVALID_DATES";
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tb5 (nasc) values ("2006-14-01");
Query OK, 1 row affected, 1 warning (0.00 sec)

Hi all, 

what about making it privilege to set the sql_mode, or maybe even privilege the individual sql_mode settings?

I think this would solve the problem. However as more and more privileges are added the more people will miss role's in order to better group and manage privileges.

There is an alternative solution: store SQL_MODE of session with the table when it was created, and activate it for inserts into the table.

This alternate solution suggests a workaround: since we do the exact this thing with stored procedures, only allow inserts into the table via a stored procedure.

Example:

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

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure if exists p1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int unique auto_increment, d date);
Query OK, 0 rows affected (0.00 sec)

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

-- sic: d_in is varchar, otherwise the truncation would happen at assignment of d_in and will be guarded, again, by session sql_mode, not procedure sql_mode
mysql> create procedure p1 (d_in varchar(255)) insert into t1 (d) values (d_in);
Query OK, 0 rows affected (0.00 sec)

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

mysql> call p1('2006-14-01');
ERROR 1292 (22007): Incorrect date value: '2006-14-01' for column 'd' at row 1
mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1 (d) values ('2006-14-01');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'd' at row 1 | 
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

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

Another simple way to fix this: when ALLOW_INVALID_DATES bit is being set, check that the global value has this bit. If it doesn't refuse to change the session setting.

Konstantin, please refer to my original comment which described a simple, more generic, and effective approach. Session mode should not be allowed to be anything less than what's set globally.
Realistically it's not just about bad dates, it's about other nasties.

Well that might be overly harsh. Especially once SQL_MODE's are enabled by default. Then again, if I disallow double quotes for quoting, do I want developers to be able to enable them? After all they would end up in logs etc.

Then again we have no way to enable SQL_MODE's on a per database level. So this means that admins that do want do leverage SQL_MODE's will have to provide a separate server instance for legacy applications.

So maybe we should divide SQL_MODE's two catagories: Those that change the allowed SQL syntax and those that determine storage. The later type is a much bigger problem for a DBA, than the first. We might be more harsh on the later type for this reason.

I personally think the whole sql_mode approach is just broken. The problem is that it contains options that are too fundamental to server operation -- and normally should be the same across the entire site, otherwise interoperability issues will occur inevitably. At the same time it also contains some very basic settings, which make sense to be changed at the runtime.
For the first group, I believe there should be no possibility to change the value dynamically. The second group should not be part of sql_mode.