Mysql INSERT query with non-null empty date field. Is it possible?

Question

I just upgraded my installation from MySQL 5.0.5 to MySQL 5.6.

With MySQL 5.0 I could have an insert query like this:

INSERT INTO mytable (myid, mydate, myname) VALUES (NULL, '', 'John');

But apparently not anymore. Now, if I try, it gives me this error:

#1292 - Incorrect date value: '' for column 'mydate' at row 1

By the way, mydate is defined as mydate date NOT NULL,

And if I change the query to ...

INSERT INTO mytable (myid, mydate, myname) VALUES (NULL, '0000-00-00', 'John');

... or ...

INSERT INTO mytable (myid, mydate, myname) VALUES (NULL, 0, 'John');

... than it succeeds.

Is this behavior controlled by some setting in MySQL? If so, where can I find it? Or will I have to change all my queries? Suggestions?

Thanks.

ok, I have to ask. Why are you trying to insert empty strings into a field declared as a not null date? — Nikki9696
– Nikki9696, Commented Oct 9, 2015 at 21:32
Hi, just set your table field mydate = mydate DATETIME NOT NULL DEFAULT NOW() — John Diaz
– John Diaz, Commented Oct 9, 2015 at 21:36
Nikki9696, It is just one of those things that you do without thinking... The field date on a form, if nothing is entered by the user, remains blank. And if MySQL deals with that blank field transforming it to '0000-00-00', that was all I needed... why bother? Now, with version 5.6, it seems to become a problem, so I have to give it a solution, either by changing MySQL configuration, or populating the blank field with a value. — Dentra Andres
– Dentra Andres, Commented Oct 9, 2015 at 21:53
John Diaz, the DEFAULT NOW() is not an option for me, as the field should remain zero (0000-00-00), until someone decides it needs a value. The field represents a person exit date, i.e., remains blank as long as the person has not exited yet. — Dentra Andres
– Dentra Andres, Commented Oct 9, 2015 at 21:56
Dentra Andres, sorry to bother you so long after you wrote this, but may I ask, where did error message #1292 show up? I'm having the same problem as you were and can fix it, but it may be happening in places I don't realize because it seems to fail silently. Getting the error message would help. I've looked in the regular mysql log files and don't see it there. — Marshall Morrise
– Marshall Morrise, Commented Jan 10, 2017 at 21:04

Michael - sqlbot · Accepted Answer · 2015-10-10 02:55:33Z

It should go without saying that you're doing it wrong. The column should be nullable if you intend to store nothing, and you shouldn't try to insert an empty string when you should be using a NULL.

Consider this, though:

If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings.

But when strict mode is in effect...

For transactional tables, an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled. The statement is aborted and rolled back

http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict

SELECT @@SQL_MODE; should reveal that you are running with (at least) STRICT_TRANS_TABLES. While this isn't the internal default in 5.6, it is included in the default 5.6 configuration file. You'll want to remove it and restart the server if you want the old behavior.

...although you should consider making your code behave more correctly.

Nikki9696 · Accepted Answer · 2015-10-09 21:37:16Z

1

Try enabling this

ALLOW_INVALID_DATES

http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_allow_invalid_dates

Note: I do not recommend doing this. I'm just answering how to do it. In my opinion, it is better to clean up the queries and data, and provide a good default for columns that are non-null.

answered Oct 9, 2015 at 21:37

Nikki9696

6,3681 gold badge32 silver badges24 bronze badges

1 Comment

Dentra Andres Over a year ago

Nikki9696. I agree with you. The scope of ALLOW_INVALID_DATES goes beyond what I want. If I can't find a better way to do it, I will probably have to work on my code to generate the query with '0000-00-00'. All I wanted was to keep the behavior it had in the previous version. I guess these are the pains of upgrade... thanks.

Collectives™ on Stack Overflow

Mysql INSERT query with non-null empty date field. Is it possible?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related