Fehler in MySQL beim Festlegen des Standardwerts für DATE oder DATETIME

Ich hatte diesen Fehler mit WAMP 3.0.6 mit MySql 5.7.14.

Lösung: 

Ändern Sie Zeile 70 (falls Ihre Ini-Datei nicht betroffen ist) in c:\wamp\bin\mysql\mysql5.7.14\my.ini-Datei aus 

sql-mode= "STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

zu

sql-mode="ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

und starten Sie alle Dienste neu.

Dadurch wird der strikte Modus deaktiviert. Gemäß der Dokumentation bedeutet "strikter Modus" einen Modus, bei dem entweder STRICT_TRANS_TABLES oder STRICT_ALL_TABLES aktiviert ist ..__ Die Dokumentation sagt:

"Der Standard-SQL-Modus in MySQL 5.7 enthält die folgenden Modi: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO_OFFER.

Konfigurationssyntaxproblem

Bei einigen Versionen von MYSQL (getestet 5.7. *) Unter * nix-Systemen sollten Sie folgende Syntax verwenden:

[mysqld]

sql-mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_ENGINE_SUBSTITUTION"

Diese funktionieren nicht:

ohne Anführungszeichen

sql-mode=NO_ENGINE_SUBSTITUTION

Unterstrich ohne Anführungszeichen

sql_mode=NO_ENGINE_SUBSTITUTION

Unterstrich und Anführungszeichen

sql_mode="NO_ENGINE_SUBSTITUTION"

Eine umfassendere Überprüfung der Konfigurationswerte und des SQL-Modus:

So richten Sie permanente SQL-Modus-Flags ein

Fügen Sie einfach die Zeile hinzu: sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

in der Datei: /etc/mysql/mysql.conf.d/mysqld.cnf

dann Sudo service mysql restart

Ich kam in eine Situation, in der die Daten zwischen NULL und 0000-00-00 für ein Datumsfeld gemischt wurden. Aber ich wusste nicht, wie ich die '0000-00-00' auf NULL aktualisieren sollte, weil 

 update my_table set my_date_field=NULL where my_date_field='0000-00-00'

ist nicht mehr erlaubt . Mein Workaround war ziemlich einfach:

update my_table set my_date_field=NULL where my_date_field<'1000-01-01'

weil alle falschen my_date_field-Werte (ob korrekte Daten oder nicht) vor diesem Datum waren.

Es funktioniert für 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)

Sie können eine SQLFiddle erstellen, um Ihr Problem neu zu erstellen.

http://sqlfiddle.com/

Wenn es für MySQL 5.6 und 5.7.8 funktioniert, aber am 5.7.11 fehlschlägt. Dann ist es wahrscheinlich ein Regressionsfehler für 5.7.11.

Wählen Sie zuerst die aktuelle Sitzung aus sql_mode:

SELECT @@SESSION.sql_mode;

Dann erhalten Sie so etwas wie Standardwert :

'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'

und dann sql_mode ohne 'NO_ZERO_DATE' einstellen:

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

Wenn Sie Zuschüsse haben, können Sie dies auch für GLOBAL tun:

SELECT @@GLOBAL.sql_mode;
SET GLOBAL sql_mode = '...';

Diese Antwort gilt nur für MySQL 5.7:

Am besten wird der sql_mode nicht wirklich leer gesetzt, stattdessen verwenden Sie in PHP eine Session-Variable mit:

SET SESSION sql_mode= 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

So behalten Sie zumindest die anderen Standardwerte.

Es ist verrückt, dass die MySQL-Dokumentation nicht klar ist. Sie müssen diese Defeault-Werte in sql_mode löschen:

NO_ZERO_IN_DATE, NO_ZERO_DATE, ich verstehe, aber in den zukünftigen Versionen wird dies nicht mehr funktionieren.

STRICT_ALL_TABLES, damit werden die Parameter ignoriert, daher müssen Sie sie auch löschen.

Schließlich auch TRADITIONAL, aber die Dokumentation spricht über diesen Parameter: "Fehler statt Warnung" beim Einfügen eines falschen Werts in eine Spalte ", mit diesem Parameter werden Datumsangaben mit Nullwerten nicht eingefügt, aber ohne Ja.

MySQL ist mit diesen Parametern und Kombinationen nicht wirklich organisiert.

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

So lösen Sie das Problem mit MySQL Workbench (Nach dem Anwenden der Lösung auf der Serverseite):

Entfernen Sie SQL_MODE in TRADITIONAL im Einstellungsbereich.

Optionskombinationen für mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64).

Wirft nicht:

STRICT_TRANS_TABLES + NO_ZERO_DATE

Wirft:

STRICT_TRANS_TABLES + NO_ZERO_IN_DATE

Meine Einstellungen in /etc/mysql/my.cnf auf Ubuntu:

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"