1

I want to configure a MySQL db instance not to accept zero dates(0000-00-00 00:00:00) because the software running on top of it fails to run if it encounters such a field.

The server is running MySQL version 5.5.40.

I added the following lines in the my.cnf file, in the [mysqld] section:

sql-mode="NO_ZERO_DATE,NO_ZERO_IN_DATE"
innodb_strict_mode="ON"

After a restart, if I check from the command line I get the following:

mysql> SELECT @@GLOBAL.sql_mode;
+------------------------------+
| @@GLOBAL.sql_mode            |
+------------------------------+
| NO_ZERO_IN_DATE,NO_ZERO_DATE |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.innodb_strict_mode;
+-----------------------------+
| @@GLOBAL.innodb_strict_mode |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

But if I connect with a client app to the database and try to fill in a zero date, it still works. I tried to recreate the database, still no luck. Any ideas why this is?

Thank you.

3
  • Does the app do an INSERT IGNORE ?
    – NickW
    Jan 7, 2015 at 14:15
  • Also does the client have any SESSION settings? As the client can specifiy innodb_strict_mode=0 for its own session..
    – NickW
    Jan 7, 2015 at 14:19
  • I'm voting to close this question as off-topic because it should have been migrated to Database Administrators.
    – Jenny D
    Jul 26, 2016 at 14:54

1 Answer 1

1

From reading the documentation on MySQL server modes:

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.

and later on

Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Each client can change its session sql_mode value at any time

Basically, the setting in the configuration file only controls the default. Individual clients and applications are still free to do whatever they want if they are willing to work to set the session settings. If you turn on the general_log you can actually watch this happening as clients connect and run queries.

As an aside, I think the responsibility of validating the data should be in application code and not in the data-store. You add a thin layer to your overall application structure that ensures that INSERTS comply with your requirements.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .