mysql - Need to allow '0000-00-00 00:00:00' dates

Question

I want zero dates to be allowed in MySQL. I have changed the sql_mode to ALLOW_INVALID_DATES,NO_ENGINE_SUBSTITUTION.

I have changed it in /etc/mysql/my.cnf.

Yet, I when I try to insert data I get the error,

Data truncation: Incorrect datetime value: '0000-00-00 00:00:00'

The MySQL version is 5.7.18.

Any ideas on this would be of great help.

Answer 1

To allow zero dates (0000-00-00 00:00:00), you need to remove that restriction.

To remove particular SQL mode (in this case NO_ZERO_DATE), find the current SQL mode:

SELECT @@GLOBAL.sql_mode;

copy the result and remove from it what you don't need (NO_ZERO_DATE)

e.g.:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

to

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

create and open this file:

sudo vim /etc/mysql/conf.d/disable_strict_mode.cnf

and write and past into it your new SQL mode:

[mysqld]
sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

restart MySQL:

sudo service mysql restart

Answer 2

You might want to read about other mode settings, such as strict and NO_ZERO_IN_DATE and NO_ZERO_DATE https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

Answer 3

I am going to assume here that you want to have a valid date so that your queries never have to check for NULL.

One way to do this, is to use what I like to call "In perpetuity" date(s).

These are essentially the min/max dates allowable for the DATETIME data type.

In my uses, there were typically "windows" of from - to pairs, but you might only need the minimum date.

From the Mysql manual:

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

So one way that might work for you, is to utilize '1000-01-01 00:00:00' instead of the zero date.

Answer 4

I could solve this by using zeroDateTimeBehavior=convertToNull

Answer 5

My solution when update table

SET sql_mode='';
UPDATE tnx_k2_items
SET created_by = 790
, modified = '0000-00-00 00:00:00'
, modified_by = 0

Answer 6

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.

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.