MySQL Feil datetime-verdi: 0000-00-00;00:00:00'

Jeg har nylig tatt over et gammelt prosjekt som ble opprettet for 10 år siden. Det bruker MySQL 5.1.

Jeg trenger blant annet å endre standard tegnsett fra latin1 til utf8.

Som et eksempel har jeg tabeller som denne:

  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

Jeg satte opp min egen Mac for å jobbe med dette. Uten å tenke for mye på det, kjørte jeg "brew install mysql" som installerte MySQL 5.7. Så jeg har noen versjonskonflikter.

Jeg lastet ned en kopi av denne databasen og importerte den.

Hvis jeg prøver å kjøre en spørring som dette:

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

får jeg denne feilen:

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

Jeg trodde jeg kunne fikse dette med:

  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

men jeg får:

  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

Må jeg oppdatere hver verdi?

Programmering
mysql
22. februar 2016 в 10:23
1 visninger

Jeg var ikke i stand til å gjøre dette:

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

(på MySQL 5.7.13).

Jeg fikk stadig feilen Feilaktig datetime-verdi: 0000-00-00 00:00:00'.

Merkelig nok fungerte dette: SELECT * FROM users WHERE created = '0000-00-00 00:00:00:00'. Jeg aner ikke hvorfor førstnevnte feiler og sistnevnte fungerer... kanskje en MySQL-feil?

I alle fall fungerte denne UPDATE-spørringen:

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

Endring av standardverdien for en kolonne med en ALTER TABLE-setning, f.eks.

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

... endrer ikke verdier som allerede er lagret. Standardverdien gjelder for rader som settes inn, og der det ikke er oppgitt noen verdi for kolonnen.

Når det gjelder årsaken til feilen, er det sannsynlig at innstillingen sql_mode for økten inkluderer NO_ZERO_DATE.

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

Da du foretok importen, ble SQL-setningene som gjorde INSERT i tabellen, kjørt i en økt som tillot null datoer.

For å se sql_mode-innstillingen:

SHOW VARIABLES LIKE 'sql_mode' ;

-eller-

SELECT @@sql_mode ;

Når det gjelder hvordan du "fikser" det nåværende problemet, slik at feilen ikke blir kastet når du kjører ALTER TABLE-setningen.

Flere alternativer:

  1. endre sql_mode til å tillate null datoer, ved å fjerne NO_ZERO_DATE og NO_ZERO_IN_DATE. Endringen kan gjøres i filen my.cnf, slik at variabelen sql_mode initialiseres til innstillingen i my.cnf etter en omstart av MySQL Server.

For en midlertidig endring kan vi endre innstillingen med en enkelt økt, uten at det kreves en global endring.

-- 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. endre opprettet-kolonnen til å tillate NULL-verdier, og oppdater de eksisterende radene for å endre nulldatoene til nullverdier.

  2. oppdater de eksisterende radene for å endre nulldatoene til en gyldig dato.

Vi trenger ikke å kjøre individuelle setninger for å oppdatere hver enkelt rad. Vi kan oppdatere alle radene på én gang (forutsatt at det er en rimelig stor tabell). Hvis det er en større tabell, kan vi utføre operasjonen i rimelig store biter for å unngå en enorm rollback/undo-generering).

I spørsmålet forsikrer AUTO_INCREMENT-verdien som vises for tabelldefinisjonen oss om at antallet rader ikke er for høyt.

Hvis vi allerede har endret created-kolonnen for å tillate NULL-verdier, kan vi gjøre noe slikt:

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

Eller vi kan sette dem til en gyldig dato, f.eks. 2. januar 1970.

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

(Merk at en datetime-verdi på midnatt 1. januar 1970 ('1970-01-01 00:00:00') er en nulldato. Den vil bli evaluert til å være 0000-00-00 00:00:00:00'.

Yoan Tournade
Redigert svar 13. juli 2017 в 2:01
Kommentarer (5)
Løsning

Mitt forslag hvis tabellen er tom eller ikke veldig stor, er å eksportere create-setningene som en .sql-fil og skrive dem om slik du ønsker. Gjør det samme hvis du har eksisterende data, dvs. eksporter insert-setninger (jeg anbefaler å gjøre dette i en egen fil som create-setningene). Til slutt slipper du tabellen og kjører først create-setningen og deretter inserts.

Du kan enten bruke mysqldump-kommandoen, som er inkludert i MySQL-installasjonen, eller du kan også installere MySQL Workbench, som er et gratis grafisk verktøy som også inneholder dette alternativet på en svært tilpassbar måte uten at du trenger å lete etter spesifikke kommandoalternativer.

Kommentarer (3)