[mysql] Error in MySQL when setting default value for DATE or DATETIME

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?

This question is related to mysql sql date datetime console

The answer is


Config syntax issue

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"

These won't work:

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:

How to setup permanent Sql Mode flags


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.

http://sqlfiddle.com/

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.


To solve the problem with MySQL Workbench (After applying the solution on the server side) :

Remove SQL_MODE to TRADITIONAL in the preferences panel.

enter image description here


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'");

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to date

How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Typescript Date Type? how to convert current date to YYYY-MM-DD format with angular 2 SQL Server date format yyyymmdd Date to milliseconds and back to date in Swift Check if date is a valid one change the date format in laravel view page Moment js get first and last day of current month How can I convert a date into an integer?

Examples related to datetime

Comparing two joda DateTime instances How to format DateTime in Flutter , How to get current time in flutter? How do I convert 2018-04-10T04:00:00.000Z string to DateTime? How to get current local date and time in Kotlin Converting unix time into date-time via excel Convert python datetime to timestamp in milliseconds SQL Server date format yyyymmdd Laravel Carbon subtract days from current date Check if date is a valid one Why is ZoneOffset.UTC != ZoneId.of("UTC")?

Examples related to console

Error in MySQL when setting default value for DATE or DATETIME Where can I read the Console output in Visual Studio 2015 Chrome - ERR_CACHE_MISS Swift: print() vs println() vs NSLog() Datatables: Cannot read property 'mData' of undefined How do I write to the console from a Laravel Controller? Cannot read property 'push' of undefined when combining arrays Very simple log4j2 XML configuration file using Console and File appender Console.log not working at all Chrome: console.log, console.debug are not working