0000-00-00 00:00:00 date in table when is set to be NOT NULL-CodePudding

Why?

In the scheme I have:

`date_published` DATETIME NOT NULL,

but now when I:

mysql> select min(date_published) from articles;
 --------------------- 
| min(date_published) |
 --------------------- 
| 0000-00-00 00:00:00 |
 --------------------- 
1 row in set (5.47 sec)

But when I:

mysql> select * from articles where date_published = '0000-00-00 00:00:00';
ERROR 1525 (HY000): Incorrect DATETIME value: '0000-00-00 00:00:00'

I don't understand. How did I insert that?

How do I find the rows that have this date?

Update

>>> db.execute("INSERT INTO articles (edition_id, url, date_published, title, weight) VALUES (%s, %s, %s, %s, %s)", (1, "", "0000-00-00 00:00:00", "", 0))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  ... yadda yadda stack trace
packages/mysql/connector/connection.py", line 784, in _handle_result
    raise get_exception(packet)
mysql.connector.errors.DataError: 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'date_published' at row 1

I've tried it from both mysql client and from the code. Both don't seem to work when I test it yet the row is in the table.

Update 2

After disabling the NO_ZERO_DATE in my client for the session I was able to find the rows that cause this problem.

They were inserted with the date '0003-01-26 00:00:00 00:00'

And now when I query for that I find them, but the value is 0000 instead if 0003:

mysql> select date_published from articles where date_published = '0003-01-26 00:00:00 00:00';
 --------------------- 
| date_published      |
 --------------------- 
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
 --------------------- 
4 rows in set (5.41 sec)

The 00:00 is important, without that I get an empty set.

CodePudding user response：

You probably inserted the zero date at some time in the past when the MySQL Server configuration allowed it.

Zero dates used to be allowed by default, and as of MySQL 5.7, they are not allowed by default. This is considered an improvement, because 0000-00-00 is not a valid date. I.e. it doesn't appear in any calendar.

The configuration that determines whether these dates are allowed is the SQL mode NO_ZERO_DATE. When this mode is enabled, an SQL expression that casts '0000-00-00 00:00:00' as a datetime value returns an error.

If that data is already stored in your table, you can at least fetch it. But you can't form a new value like that, including the literal value you used in your WHERE clause.

To search for that value, you have to change your sql_mode configuration option, at least temporarily in the session where you run that query. I would recommend do not change it globally. It's a good thing to disallow invalid dates.

The manual says:

Because NO_ZERO_DATE is deprecated; expect it to be removed in a future release of MySQL as a separate mode name and its effect included in the effects of strict SQL mode.

So in some future version of MySQL, you might have to disable strict mode to access such date values. I recommend you replace those values now with either some valid datetime value, or else NULL.