4

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.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
ItBHarsH
  • 115
  • 1
  • 2
  • 6
  • 1
    `''` is not a valid date .. what date is supposed to be added? The current date? – Explosion Pills Feb 19 '13 at 15:48
  • No, it is passing in an empty string in both local and Production. I know it is strange and I'm not sure how it is working in Production but it is. – ItBHarsH Feb 19 '13 at 15:50

3 Answers3

9

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.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • Sorry if I was not clear. I get the SQLException in my code that is reporting the same error that I get when running the query in the query window of MySQL Workbench. The following output is what I get from when just executing the raw query outside of my code directly in MySQL Workbench query window. `ACTION: update atable set adate=DATE_ADD(str_to_date('xxx','%m/%d/%Y'), INTERVAL 0 DAY) where anum='1' Message: Error Code: 1411. Incorrect datetime value: '' for function str_to_date` This happens with or without `ALLOW_INVALID_DATES` mode enabled in my.ini. – ItBHarsH Feb 19 '13 at 16:54
  • So both your code and Workbench report warnings as errors. Not sure what answer you want—you can relax SQL mode to avoid the base warning or you can change the query. That's basically all. – Álvaro González Feb 19 '13 at 16:59
  • As mentioned I tried ALLOW_INVALID_DATES mode and it did not help. The production server does not have any modes defined. I've been looking at the SQL Modes documentation and not sure what other option to try. Suggestions? --- BTW Thanks for all your input! – ItBHarsH Feb 19 '13 at 18:02
  • @ItBHarsH - Is there any chance that you didn't get the syntax right or your ran queries in different sessions? – Álvaro González Feb 20 '13 at 12:49
  • I have a breakpoint in my code and I'm doing a copy/paste so the syntax is the same. – ItBHarsH Feb 20 '13 at 20:30
0

I was getting the same 1411 error when trying to load data which has some blank values for dates:

CLM_FROM_DT is a DATE

LOAD DATA INFILE 'Sample_1.csv'
INTO TABLE INPATIENT
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(DESYNPUF_ID,
@CLM_FROM_DT)
SET CLM_FROM_DT = STR_TO_DATE(@CLM_FROM_DT, '%Y%m%d')

Awhile back I had tried ALLOW_INVALID_DATES with MySQL v.5 or so, and I don't think it worked. Now I'm on MySQL 8.0 and when I set ALLOW_INVALID_DATES in the mysql terminal session, it works. So, it seems ALLOW_INVALID_DATES is touchy. I'm running Mac OS 10.11.6

Jessi
  • 1,378
  • 6
  • 17
  • 37
Keith
  • 1
0

I had the same problem while using MySQL Server version 8.0.28-0ubuntu0.20.04.3 (Ubuntu) with the following code:

insert into documents (data) values (IF((@d := STR_TO_DATE("", "%Y-%m-%d")) IS NULL, null, @d));

but the problem didn't show up using SELECT:

select IF((@d := STR_TO_DATE("", "%Y-%m-%d")) IS NULL, null, @d);

The solution was to change from MySQL to MariaDb 10.3.31. The error code 1411 is no longer occurring.

I guess there is some bug going on with MySQL 8.0.28 since I used exactly the same script in both versions.

user24302
  • 1
  • 1