MySQL Valore di data errato: '0000-00-00 00:00:00'

Ho recentemente ripreso un vecchio progetto che è stato creato 10 anni fa. Usa MySQL 5.1.

Tra le altre cose, ho bisogno di cambiare il set di caratteri predefinito da latin1 a utf8.

Come esempio, ho tabelle come questa:

  CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `first_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `last_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `username` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `email` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `pass` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `active` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'Y',
    `created` datetime NOT NULL,
    `last_login` datetime DEFAULT NULL,
    `author` varchar(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'N',
    `locked_at` datetime DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `ripple_token` varchar(36) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `ripple_token_expires` datetime DEFAULT '2014-10-31 08:03:55',
    `authentication_token` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `index_users_on_reset_password_token` (`reset_password_token`),
    UNIQUE KEY `index_users_on_confirmation_token` (`confirmation_token`),
    UNIQUE KEY `index_users_on_unlock_token` (`unlock_token`),
    KEY `users_active` (`active`),
    KEY `users_username` (`username`),
    KEY `index_users_on_email` (`email`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1677 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

Ho impostato il mio Mac per lavorare su questo. Senza pensarci troppo, ho eseguito "brew install mysql" che ha installato MySQL 5.7. Quindi ho alcuni conflitti di versione.

Ho scaricato una copia di questo database e l'ho importato.

Se provo a eseguire una query come questa:

  ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci    NOT NULL

ottengo questo errore:

  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

Pensavo di poterlo risolvere con:

  ALTER TABLE users MODIFY created datetime  NULL DEFAULT '1970-01-01 00:00:00';
  Query OK, 0 rows affected (0.06 sec)
  Records: 0  Duplicates: 0  Warnings: 0

ma ottengo:

  ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci    NOT NULL ;
  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

Devo aggiornare ogni valore?

Programmazione
mysql
22º febbraio 2016 в 10:23
Non ci sono ancora visualizzazioni

Non sono stato in grado di farlo:

UPDATE users SET created = NULL WHERE created = '0000-00-00 00:00:00'

(su MySQL 5.7.13).

Continuavo a ricevere l'errore Incorrect datetime value: '0000-00-00 00:00:00'.

Stranamente, questo ha funzionato: SELECT * FROM users WHERE created = '0000-00-00 00:00:00'. Non ho idea del perché il primo fallisca e il secondo funzioni... forse un bug di MySQL?

In ogni caso, questa query UPDATE ha funzionato:

UPDATE users SET created = NULL WHERE CAST(created AS CHAR(20)) = '0000-00-00 00:00:00'
Commentari (8)

Cambiare il valore di default di una colonna con un'istruzione ALTER TABLE, per esempio

 ALTER TABLE users MODIFY created datetime  NULL DEFAULT '1970-01-02'

... non cambia alcun valore già memorizzato. Il valore "default" si applica alle righe che vengono inserite e per le quali non viene fornito un valore per la colonna.

Per quanto riguarda il motivo per cui stai incontrando l'errore, è probabile che l'impostazione sql_mode per la tua sessione includa NO_ZERO_DATE.

Riferimento: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

Quando hai fatto l'"importazione", le istruzioni SQL che hanno fatto l'INSERT in quella tabella sono state eseguite in una sessione che permetteva le date zero.

Per vedere l'impostazione sql_mode:

SHOW VARIABLES LIKE 'sql_mode' ;

-o-

SELECT @@sql_mode ;

Per quanto riguarda come "fix" il problema attuale, in modo che l'errore non venga lanciato quando si esegue l'istruzione ALTER TABLE.

Ci sono diverse opzioni:

  1. cambiare lo sql_mode per permettere le date zero, rimuovendo NO_ZERO_DATE e NO_ZERO_IN_DATE. La modifica può essere applicata nel file my.cnf, quindi dopo un riavvio di MySQL Server, la variabile sql_mode sarà inizializzata all'impostazione in my.cnf.

Per una modifica temporanea, possiamo modificare l'impostazione con una singola sessione, senza richiedere una modifica globale.

-- save current setting of sql_mode
SET @old_sql_mode := @@sql_mode ;

-- derive a new value by removing NO_ZERO_DATE and NO_ZERO_IN_DATE
SET @new_sql_mode := @old_sql_mode ;
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_DATE,'  ,','));
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_IN_DATE,',','));
SET @@sql_mode := @new_sql_mode ;

-- perform the operation that errors due to "zero dates"

-- when we are done with required operations, we can revert back
-- to the original sql_mode setting, from the value we saved
SET @@sql_mode := @old_sql_mode ;

  1. cambiare la colonna created per permettere valori NULL, e aggiornare le righe esistenti per cambiare le date zero in valori nulli

  2. aggiornare le righe esistenti per cambiare le date zero in una data valida

Non abbiamo bisogno di eseguire singole istruzioni per aggiornare ogni riga. Possiamo aggiornare tutte le righe in un colpo solo (supponendo che sia una tabella di dimensioni ragionevoli. Per una tabella più grande, per evitare un'enorme generazione di rollback/undo, possiamo eseguire l'operazione in pezzi di dimensioni ragionevoli).

Nella domanda, il valore AUTO_INCREMENT mostrato per la definizione della tabella ci assicura che il numero di righe non è eccessivo.

Se abbiamo già cambiato la colonna created per permettere valori NULL, possiamo fare qualcosa del genere:

UPDATE  `users` SET `created` = NULL WHERE `created` = '0000-00-00 00:00:00'

Oppure, possiamo impostarli su una data valida, ad esempio 2 gennaio 1970

UPDATE  `users` SET `created` = '1970-01-02' WHERE `created` = '0000-00-00 00:00:00'

(Si noti che un valore datetime di mezzanotte 1 gennaio 1970 ('1970-01-01 00:00:00') è una "data zero". Questo sarà valutato come '0000-00-00 00:00:00'

Yoan Tournade
Risposta modificata 13º luglio 2017 в 2:01
Commentari (5)
Soluzione

Il mio suggerimento, se è il caso che la tabella sia vuota o non molto grande, è di esportare le istruzioni di creazione come file .sql, riscrivendole come desideri. Fai anche lo stesso se hai dei dati esistenti, cioè esporta le istruzioni di inserimento (ti consiglio di farlo in un file separato come le istruzioni di creazione). Alla fine, abbandonate la tabella ed eseguite prima le istruzioni di creazione e poi quelle di inserimento.

Puoi usare per questo sia il comando mysqldump, incluso nella tua installazione di MySQL o puoi anche installare MySQL Workbench, che è uno strumento grafico gratuito che include anche questa opzione in un modo molto personalizzabile senza dover cercare opzioni di comando specifiche.

Commentari (3)