MySQL Error Code: 1411. Incorrect datetime value: '' for function str_to_date

Question

I have been asked to make changes to an existing application that at this time is only deployed to a Linux Production server. I have gone ahead and have the app for the most part working on my local Windows PC. I have a full copy of the MySQL DB from Production installed on my local PC. The Production DB is MySQL v5.0.95 on Linux and my local DB is MySQL v5.5 on Windows. Both are in InnoDB mode.

My issue is with a statement such as the following. Made generic for ease of use by others wanting to help.

update atable 
set adate=DATE_ADD(str_to_date('','%m/%d/%Y'), INTERVAL 0 DAY)
where anum='1'

In some cases an empty string is passed in which in Production does not cause any issues and allows the record to be saved/updated but locally it throws a SQLException. So I tried the SQL statement directly against my local DB and I get the following error message in both cases.

Error Code: 1411. Incorrect datetime value: '' for function str_to_date

I've looked at the Production my.cnf and my local my.ini looking for any major differences and I have also tried to use the sql-mode "ALLOW_INVALID_DATES" locally but it did not change the end result.

I know that I could change the code to not pass in these empty strings in but there are many statements like this and at this time I do not wish to make changes to all of these if possible. This customer has a limited budget and timeframe and I want to focus on their new requirements. I'm looking for input as to how I can get my local environment working as it does in Production if possible.

Thanks for your time.

Answer 1

The SQLException does not come directly from MySQL, it's probably triggered by your client language. MySQL will just generate a warning you can normally ignore. Whatever, the ALLOW_INVALID_DATES SQL mode should actually do the trick:

Warning:

mysql> SET @@SESSION.sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (date_created) VALUES (str_to_date('','%m/%d/%Y'));
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '' for function str_to_date |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

No warning:

mysql> SET @@SESSION.sql_mode='ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (date_created) VALUES (str_to_date('','%m/%d/%Y'));
Query OK, 1 row affected (0.03 sec)

Edit: If you are looking for a way to rewrite the query, you could try something like this:

update atable 
set adate=NULL
where anum='1'

Of course, this requires that adate is nullable.