I'm running MySql Server 5.7.11 and this sentence:
updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
is not working. Giving the error:
ERROR 1067 (42000): Invalid default value for 'updated'
But the following:
updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00'
just works.
The same case for DATE.
As a sidenote, it is mentioned in the MySQL docs:
The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
even if they also say:
Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').
Having also into account the second quote from MySQL documentation, could anyone let me know why it is giving that error?
ベストアンサー1
The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation.
Strict mode affects whether the server permits '0000-00-00' as a valid date: If strict mode is not enabled, '0000-00-00' is permitted and inserts produce no warning. If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.
To Check MYSQL mode
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session
Disabling STRICT_TRANS_TABLES mode
However to allow the format 0000-00-00 00:00:00
you have to disable STRICT_TRANS_TABLES mode in mysql config file or by command
By command
SET sql_mode = '';
or
SET GLOBAL sql_mode = '';
Using the keyword GLOBAL
requires super previliges and it affects the operations all clients connect from that time on
if above is not working than go to /etc/mysql/my.cnf
(as per ubuntu) and comment out STRICT_TRANS_TABLES
Also, if you want to permanently set the sql mode at server startup then include SET sql_mode=''
in my.cnf
on Linux or MacOS. For windows this has to be done in my.ini
file.
Note
However, strict mode is not enabled by default in MYSQL 5.6. Hence it does not produce the error as per MYSQL 5.6 documentation which says
MySQL permits you to store a “zero” value of '0000-00-00' as a “dummy date.” This is in some cases more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable the NO_ZERO_DATE SQL mode.
UPDATE
Regarding the bug matter as said by @Dylan-Su:
I don't think this is a bug - it is the way MYSQL is evolved over the time due to which some things are changed based on further improvement of the product.
However I have another related bug report regarding the NOW()
function:
Datetime field does not accept default NOW()
Another Useful note [see Automatic Initialization and Updating for TIMESTAMP and DATETIME]
MySQL 5.6.5 以降では、TIMESTAMP 列と DATETIME 列は自動的に初期化され、現在の日付と時刻 (つまり、現在のタイムスタンプ) に更新されます。5.6.5 より前では、これは TIMESTAMP にのみ当てはまり、テーブルごとに最大 1 つの TIMESTAMP 列にのみ当てはまります。次の注記では、最初に MySQL 5.6.5 以降の自動初期化と更新について説明し、次に 5.6.5 より前のバージョンの違いについて説明します。
NO_ZERO_DATEに関する更新
MySQL 5.7.4以降ではこのモードは非推奨です。以前のバージョンでは、設定ファイルの該当する行をコメントアウトする必要があります。NO_ZERO_DATE に関する MySQL 5.7 ドキュメント