MySQL Incorrect date value: null

Question

I'm trying to update a table from a Java application where a certain column may be NULL. I have tried several different approaches but I always get the following error:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: 'null' for column 'scheidingsdatum' at row 1

I made sure that the table allowed NULL values for the scheidingsdatum field, and can insert NULL values when directly inserting in MySQL

This is the table structure in PHPMyAdmin:

The tables use innoDB

I have tried the following solutions:

1: Just use the NULL variable in a parameter

stmnt = conn.prepareStatement("UPDATE gezinnen SET "
                            + "ouder1 = ?," 
                            + "ouder2 = ?," 
                            + "huwelijksdatum = ?,"
                            + "scheidingsdatum = ? "
                            + "WHERE gezinsNummer = ?");

stmnt.setString(1, ouder1);
stmnt.setString(2, ouder2);
stmnt.setString(3, huwelijksdatum);
stmnt.setString(4, scheidingsdatum);
stmnt.setString(5, nummer);

2: Hardcode NULL in the query (inside if/else block)

stmnt = conn.prepareStatement("UPDATE gezinnen SET "
                            + "ouder1 = ?," 
                            + "ouder2 = ?," 
                            + "huwelijksdatum = ?,"
                            + "scheidingsdatum = NULL "
                            + "WHERE gezinsNummer = ?");

stmnt.setString(1, ouder1);
stmnt.setString(2, ouder2);
stmnt.setString(3, huwelijksdatum);
stmnt.setString(4, nummer);

3: Use setNull(4, java.sql.Types.DATE)

stmnt = conn.prepareStatement("UPDATE gezinnen SET "
                            + "ouder1 = ?," 
                            + "ouder2 = ?," 
                            + "huwelijksdatum = ?,"
                            + "scheidingsdatum = ? "
                            + "WHERE gezinsNummer = ?");

stmnt.setString(1, ouder1);
stmnt.setString(2, ouder2);
stmnt.setString(3, huwelijksdatum);
stmnt.setNull(4, java.sql.Types.DATE);
stmnt.setString(5, nummer);

4: Use setNull(4, java.sql.Types.NULL)

stmnt = conn.prepareStatement("UPDATE gezinnen SET "
                            + "ouder1 = ?," 
                            + "ouder2 = ?," 
                            + "huwelijksdatum = ?,"
                            + "scheidingsdatum = ? "
                            + "WHERE gezinsNummer = ?");

stmnt.setString(1, ouder1);
stmnt.setString(2, ouder2);
stmnt.setString(3, huwelijksdatum);
stmnt.setNull(4, java.sql.Types.NULL);
stmnt.setString(5, nummer);

the following is my database.properties file and connection creation:

database.properties

jdbc.drivers=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://IP:3306/TABLE_NAME
jdbc.username=USER
jdbc.password=PASSWORD

Connection creation

Class.forName(props.getProperty("jdbc.drivers")).newInstance();
this.conn = (Connection) DriverManager.getConnection(props.getProperty("jdbc.url"),props.getProperty("jdbc.username"),props.getProperty("jdbc.password"));

@YassinHajaj NULL is a special value, SQL is a 3-valued logic language. The OP wants NULL, and not some replacement for it. If you use replacement you need to remember this and modify accordingly all your queries. — peter.petrov, Oct 19 '15 at 20:54
@dognose That works for inserts, but what about updating it? It should be possible to change it from an actual date to null later. — Cas, Oct 19 '15 at 20:55
Strange, if you're indeed seeing this issue, it's probably some problem with the JDBC driver. I cannot imagine that at least one of your approaches won't work. — peter.petrov, Oct 19 '15 at 20:56
See this. Seems related... But not useful. http://stackoverflow.com/questions/27957727/insert-null-for-datetime-field-through-preparestatement-using-jdbc — peter.petrov, Oct 19 '15 at 20:57
@RealSkeptic That was also one of the solutions I tried. (The third one) — Cas, Oct 19 '15 at 20:59
See also this. Read about this strict mode. http://stackoverflow.com/questions/21957421/mysql-datatime-null — peter.petrov, Oct 19 '15 at 20:59
@RealSkeptic I updated the question with my database.properties and connection part. — Cas, Oct 19 '15 at 21:10
@peter.petrov Disabling strict mode didn't fix it unfortunately. — Cas, Oct 19 '15 at 21:11
Did you try to add `?zeroDateTimeBehavior=convertToNull` to the database URL as mentioned in one of the links @peter.petrov gave? — RealSkeptic, Oct 19 '15 at 21:12
@RealSkeptic I did, but it didn't work so I removed it again. — Cas, Oct 19 '15 at 21:13
@RealSkeptic I tried both adding it to the url in the database.properties file and concatenating it with the string in the part where I create the Connection (so `props.getProperty("jdbc.url")+"?zeroDateTimeBehavior=convertToNull"`) — Cas, Oct 19 '15 at 21:15
No, I meant - which of the null-setting methods did you try when you did that? — RealSkeptic, Oct 19 '15 at 21:16
Found a few similiar posts. most answered with "I updated the JDBC Driver, problem gone" - which version are you running on? — dognose, Oct 19 '15 at 21:16
@RealSkeptic Oh, I combined it with `stmnt.setNull(4, java.sql.Types.DATE);` — Cas, Oct 19 '15 at 21:17
@dognose I'm running 5.1.37, downloaded it from the MySQL site today. — Cas, Oct 19 '15 at 21:20
Have you tried using `setDate` with a `null` `java.util.Date` object? In your first try, you're setting the value as a `String` Is it possible you're using the String `"null"` instead of a true `null` value? — Ian McLaird, Oct 19 '15 at 21:20
I fixed it because I made a dumb mistake of not properly checking the code made by someone else and making assumptions about how it would work. — Cas, Oct 19 '15 at 21:39

score 4 · Accepted Answer · answered Oct 19 '15 at 21:37

4

I just made a test and it worked for me with stmnt.setNull(4, java.sql.Types.Date);, are you sure that for stmnt.setString(3, huwelijksdatum); the value of huwelijksdatum is a valid mysql date string and not "null" ?

answered Oct 19 '15 at 21:37

Eusebiu Biroas

71
1
4

That was indeed the issue as I explain here http://stackoverflow.com/a/33224384/4385713 – Cas Oct 19 '15 at 21:45

score 3 · Answer 2 · answered Oct 19 '15 at 21:38

Well, this is the dumbest fix ever.

The code was originally made by someone else, and I only expanded on it a bit. They first created a string scheidingsDatum = "null";, which would then be overwritten by an actual date if there was one.

I assumed (I know, it's never smart to assume) that it would be null (Notice the lack of quotation marks?) when it didn't have a value.

So, in my check, the string wasn't null (since it was "null") and so the first part was executed. Which made it try to insert a string "null", which is obviously an incorrect date.

Simply modifying the string to be null instead of "null" upon instantiation fixed the issue.

score 2 · Answer 3 · answered Oct 19 '15 at 21:11

2

You could try TIMESTAMP instead of DATE in your prepared statement.

stmnt.setNull(4, java.sql.Types.TIMESTAMP);

answered Oct 19 '15 at 21:11

Prem

157
7

I'm still getting the same exception when I do that, unfortunately :( – Cas Oct 19 '15 at 21:13

score 0 · Answer 4 · edited Oct 19 '15 at 20:55

0

If scheidingsdatum is a nullable field, then simply remove it from your UPDATE statement when its value is null. In other words, when scheidingsdatum is null, change the statement to:

UPDATE gezinnen SET ouder1 = ?, ouder2 = ?, huwelijksdatum = ?
WHERE gezinsNummer = ?

edited Oct 19 '15 at 20:55

John Ruddell

25,283
6
57
86

answered Oct 19 '15 at 20:54

Eric Mintz

126
1

3

But then wouldn't it remain the same as it was before the update? Scheidingsdatum can be nulled after it's set. – Cas Oct 19 '15 at 20:55
Ah good point. Then in the case where scheidingsdatum is supposed to be saved as null, replace "scheidingsdatum = ?" with "scheidingsdatum = null". – Eric Mintz Oct 19 '15 at 21:59

MySQL Incorrect date value: null

4 Answers4