Errore in mysql quando si imposta il valore predefinito per DATE o DATETIME

Sto usando MySql Server 5.7.11 e questa frase:

updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00' 

non funziona Dando l’errore:

 ERROR 1067 (42000): Invalid default value for 'updated' 

Ma il seguente:

 updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00' 

funziona

Lo stesso caso per DATE.

Come sidenote , è menzionato nei documenti mysql :

Il tipo DATE viene utilizzato per i valori con una parte di data ma nessuna parte di tempo. MySQL recupera e visualizza i valori DATE nel formato ‘AAAA-MM-GG’. L’intervallo supportato è ‘1000-01-01’ su ‘9999-12-31’.

anche se dicono anche:

I valori DATE, DATETIME o TIMESTAMP non validi vengono convertiti nel valore “zero” del tipo appropriato (“0000-00-00” o “0000-00-00 00:00:00”).

Avendo anche in considerazione la seconda citazione della documentazione di mysql, qualcuno potrebbe farmi sapere perché sta dando quell’errore?

L’errore è dovuto alla modalità sql che può essere una modalità rigorosa come da documentazione aggiornata di MYSQL 5.7

La documentazione di MySQL 5.7 dice :

La modalità rigorosa influisce sul fatto che il server consenta “0000-00-00” come data valida: se la modalità rigorosa non è abilitata, “0000-00-00” è consentito e gli inserimenti non producono alcun avviso. Se la modalità strict è abilitata, ‘0000-00-00’ non è consentito e gli inserimenti generano un errore, a meno che non venga dato anche IGNORE. Per INSERT IGNORE e UPDATE IGNORE, ‘0000-00-00’ è permesso e gli inserti generano un avviso.

Per controllare la modalità MYSQL

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session

Disabilitazione della modalità STRICT_TRANS_TABLES

Tuttavia per consentire il formato 0000-00-00 00:00:00 devi disabilitare la modalità STRICT_TRANS_TABLES nel file di configurazione mysql o tramite comando

Per comando

SET sql_mode = '';

o

SET GLOBAL sql_mode = '';

L’utilizzo della parola chiave GLOBAL richiede super-precedenti e influenza le operazioni su cui tutti i client si connettono da quel momento

se sopra non funziona, vai su /etc/mysql/my.cnf (come per ubuntu) e commenta STRICT_TRANS_TABLES

Inoltre, se si desidera impostare in modo permanente la modalità sql all’avvio del server, includere SET sql_mode='' in my.cnf su Linux o MacOS. Per Windows questo deve essere fatto nel file my.ini .

Nota

Tuttavia la modalità strict non è abilitata di default in MYSQL 5.6. Quindi non produce l’errore secondo la documentazione di MYSQL 6 che dice

MySQL consente di memorizzare un valore “zero” di “0000-00-00” come una “data fittizia”. Ciò è in alcuni casi più conveniente rispetto all’utilizzo di valori NULL e utilizza meno dati e spazio indice. Per disabilitare ‘0000-00-00’, abilitare la modalità SQL NO_ZERO_DATE.

AGGIORNARE

Per quanto riguarda la questione del bug come ha detto @ Dylan-Su:

Non penso che questo sia il bug nel modo in cui MYSQL si è evoluto nel tempo a causa del quale alcune cose sono cambiate in base ad un ulteriore miglioramento del prodotto.

Tuttavia, ho un altro bug relativo alla funzione NOW()

Il campo Datetime non accetta l’impostazione predefinita NOW ()

Un’altra nota utile [vedi Inizializzazione e aggiornamento automatici per TIMESTAMP e DATETIME ]

A partire da MySQL 5.6.5, le colonne TIMESTAMP e DATETIME possono essere inizializzate automaticamente e aggiornate alla data e all’ora correnti (ovvero il timestamp corrente). Prima di 5.6.5, questo è vero solo per TIMESTAMP e per al massimo una colonna TIMESTAMP per tabella. Le seguenti note descrivono per prima cosa l’inizializzazione e l’aggiornamento automatici per MySQL 5.6.5 e versioni successive, quindi le differenze per le versioni precedenti alla 5.6.5.

Aggiornamento relativo a NO_ZERO_DATE

A partire da MySQL a partire da 5.7.4 questa modalità è deprecata. Per la versione precedente è necessario commentare la rispettiva riga nel file di configurazione. Consultare la documentazione di MySQL 5.7 in data NO_ZERO_DATE

Ho avuto questo errore con WAMP 3.0.6 con MySql 5.7.14.

Soluzione :

cambia linea 70 (se il tuo file ini non è stato toccato) nel file c:\wamp\bin\mysql\mysql5.7.14\my.ini da

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

a

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

e riavviare tutti i servizi.

Questo disabiliterà la modalità rigorosa. Secondo la documentazione, “modalità rigorosa” indica una modalità con uno o entrambi STRICT_TRANS_TABLES o STRICT_ALL_TABLES abilitati. La documentazione dice:

“La modalità SQL predefinita in MySQL 5.7 include queste modalità: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER e NO_ENGINE_SUBSTITUTION.”

Problema di syntax della configurazione

Su alcune versioni di MYSQL (testato 5.7. *) In sistemi * nix dovresti usare questa syntax:

 [mysqld] sql-mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_ENGINE_SUBSTITUTION" 

Questi non funzioneranno:

trattini senza virgolette

 sql-mode=NO_ENGINE_SUBSTITUTION 

sottolineatura senza virgolette

 sql_mode=NO_ENGINE_SUBSTITUTION 

sottolineatura e citazioni

 sql_mode="NO_ENGINE_SUBSTITUTION" 

Una revisione più completa dei valori di configurazione e della modalità sql:

Come configurare flag Sql Mode permanenti

Basta aggiungere la riga: sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

all’interno del file: /etc/mysql/mysql.conf.d/mysqld.cnf

poi sudo service mysql restart

Funziona per 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) 

È ansible creare un SQLFiddle per ricreare il problema.

http://sqlfiddle.com/

Se funziona per MySQL 5.6 e 5.7.8, ma non riesce su 5.7.11. Quindi probabilmente si tratta di un bug di regressione per 5.7.11.

questa risposta è solo per mysql 5.7:

la cosa migliore non è in realtà impostata in bianco su sql_mode, ma usa in php una variabile di sessione con:

 SET SESSION sql_mode= 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' 

così almeno si mantengono gli altri valori di default

È assurdo che la documentazione di mysql non sia chiara, è necessario eliminare i valori di defeault in sql_mode:

NO_ZERO_IN_DATE, NO_ZERO_DATE, ho capito, ma nelle future versioni questo sarà interrotto.

STRICT_ALL_TABLES, con questo, prima che i parametri vengano ignorati, quindi devi eliminarlo anche tu.

anche TRADIZIONALE, ma la documentazione parla di questo parametro: “dare un errore invece di un avvertimento” quando si inserisce un valore errato in una colonna “, con questo parametro, le date con valori zero non sono inserite, ma senza si

mysql non è veramente organizzato con questi parametri e combinazioni.

Per risolvere il problema con MySQL Workbench (dopo aver applicato la soluzione sul lato server):

Rimuovi SQL_MODE in TRADITIONAL nel pannello delle preferenze.

inserisci la descrizione dell'immagine qui

Combinazioni di opzioni per mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) .

Non gettare:

STRICT_TRANS_TABLES + NO_ZERO_DATE

Produce:

STRICT_TRANS_TABLES + NO_ZERO_IN_DATE

Le mie impostazioni in /etc/mysql/my.cnf su Ubuntu:

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

Prima seleziona la sessione corrente sql_mode :

 SELECT @@SESSION.sql_mode; 

Quindi otterrai qualcosa come quel valore predefinito :

‘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’

e quindi imposta sql_mode senza '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'; 

Se hai delle sovvenzioni, puoi farlo anche per GLOBAL :

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