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?
On some versions of MYSQL (tested 5.7.*) under *nix systems you should use this syntax:
[mysqld]
sql-mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_ENGINE_SUBSTITUTION"
dash no quotes
sql-mode=NO_ENGINE_SUBSTITUTION
underscore no quotes
sql_mode=NO_ENGINE_SUBSTITUTION
underscore and quotes
sql_mode="NO_ENGINE_SUBSTITUTION"
A more complete review of config values and sql-mode:
Just add the line: sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
inside file: /etc/mysql/mysql.conf.d/mysqld.cnf
then sudo service mysql restart
It works for 5.7.8:
mysql> create table t1(updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00');
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc |
+-----------+
1 row in set (0.00 sec)
You can create a SQLFiddle to recreate your issue.
If it works for MySQL 5.6 and 5.7.8, but fails on 5.7.11. Then it probably is a regression bug for 5.7.11.
set global sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
I had this error with WAMP 3.0.6 with MySql 5.7.14.
Solution:
change line 70 (if your ini file is untouched) in c:\wamp\bin\mysql\mysql5.7.14\my.ini
file from
sql-mode= "STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"
to
sql-mode="ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"
and restart all services.
This will disable strict mode. As per the documentation, “strict mode” means a mode with either or both STRICT_TRANS_TABLES
or STRICT_ALL_TABLES
enabled.
The documentation says:
"The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION."
I got into a situation where the data was mixed between NULL and 0000-00-00 for a date field. But I did not know how to update the '0000-00-00' to NULL, because
update my_table set my_date_field=NULL where my_date_field='0000-00-00'
is not allowed any more. My workaround was quite simple:
update my_table set my_date_field=NULL where my_date_field<'1000-01-01'
because all the incorrect my_date_field
values (whether correct dates or not) were from before this date.
This answer it's just for MySQL 5.7:
Best is not really set in blank the sql_mode, instead use in PHP a session variable with:
SET SESSION sql_mode= 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
So at least you keep the other default values.
It's crazy that mysql documentation is not clear, you need delete to these default values in sql_mode:
NO_ZERO_IN_DATE,NO_ZERO_DATE, I understand, but in the future versions this will be discontinued.
STRICT_ALL_TABLES, with this, before parameters will be ignored, so you need to delete it too.
Finally TRADITIONAL too, but documentation speaks about this parameter: “give an error instead of a warning” when inserting an incorrect value into a column", with this parameter, dates with zero values is not inserted, but without yes.
MySQL is not really organised with these parameters and combinations.
First select current session sql_mode
:
SELECT @@SESSION.sql_mode;
Then you will get something like that default value:
'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'
and then set sql_mode
without 'NO_ZERO_DATE'
:
SET SESSION 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';
If you have grants, you can do it also for GLOBAL
:
SELECT @@GLOBAL.sql_mode;
SET GLOBAL sql_mode = '...';
Option combinations for mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64)
.
Doesn't throw:
STRICT_TRANS_TABLES
+ NO_ZERO_DATE
Throws:
STRICT_TRANS_TABLES
+ NO_ZERO_IN_DATE
My settings in /etc/mysql/my.cnf
on Ubuntu:
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
In diretory xamp/mysql/bin Open "my.ini" and change line: Sql_node for ->
"sql_mode=NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE"
REMOVE "NO_ZERO_IN_DATE"
I've tested a fix as follow:
1). On the file "system/library/db/mysqli.php" search and comment the line:
"$this->connection->query("SET SESSION sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION'");"
2) Add the following line above the one you just commented:
// Correction by Added by A.benkorich
$this->connection->query("SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY'");
Source: Stackoverflow.com