MySQL Neteisingas datos laikas: '0000-00-00 00:00:00'

Neseniai perėmiau seną projektą, kuris buvo sukurtas prieš 10 metų. Jame naudojama MySQL 5.1.

Be kitų dalykų, man reikia pakeisti numatytąjį simbolių rinkinį iš latin1 į utf8.

Pavyzdžiui, turiu tokias lenteles:

  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

Aš sukūriau savo "Mac" kompiuterį, kad galėčiau su tuo dirbti. Per daug negalvodamas paleidau "brew install mysql", kuris įdiegė MySQL 5.7. Taigi turiu tam tikrų versijų konfliktų.

Atsisiunčiau šios duomenų bazės kopiją ir ją importavau.

Jei bandau paleisti tokią užklausą:

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

gaunu šią klaidą:

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

Maniau, kad galiu tai ištaisyti:

  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

bet aš gaunu:

  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

Ar turiu atnaujinti kiekvieną reikšmę?

Programavimas
mysql
popietr vasaris 2016 в 10:23
1 peržiūrėti

Man nepavyko to padaryti:

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

(MySQL 5.7.13).

Aš vis gaudavau Neteisingos datos reikšmės: '0000-00-00 00:00:00:00' klaidą.

Keista, bet tai veikė: SELECT * FROM users WHERE created = '0000-00-00 00:00:00:00'. Neįsivaizduoju, kodėl pirmasis variantas nepavyksta, o antrasis veikia... gal tai MySQL klaida?

Bet kuriuo atveju ši UPDATE užklausa suveikė:

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

Stulpelio numatytoji reikšmė keičiama naudojant ALTER TABLE teiginį, pvz.

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

... nekeičia jokių jau išsaugotų reikšmių. Numatytoji reikšmė taikoma įterptoms eilutėms, kurių stulpelio reikšmė nepateikta.

Klaida, su kuria susiduriate, gali būti, kad jūsų sesijos sql_mode nustatyme yra NO_ZERO_DATE.

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

Kai atlikote "importą", SQL užklausos, kurios atliko INSERT į šią lentelę, buvo vykdomos sesijoje, kurioje leidžiama naudoti nulines datas.

Norėdami peržiūrėti sql_mode nustatymą:

SHOW VARIABLES LIKE 'sql_mode' ;

-arba-

SELECT @@sql_mode ;

Kaip ištaisyti dabartinę problemą, kad paleidus ALTER TABLE komandą klaida nebūtų išmetama.

Yra kelios galimybės:

  1. pakeiskite sql_mode, kad būtų leidžiama naudoti nulines datas, pašalindami NO_ZERO_DATE ir NO_ZERO_IN_DATE. Pakeitimą galima taikyti my.cnf faile, todėl iš naujo paleidus "MySQL" serverį kintamasis sql_mode bus inicializuotas pagal my.cnf nustatymą.

Norėdami laikinai pakeisti nustatymą, jį galime pakeisti per vieną seansą, nereikalaudami visuotinio pakeitimo.

-- 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. pakeiskite stulpelį created, kad jame būtų leidžiamos NULL reikšmės, ir atnaujinkite esamas eilutes, kad nulinės datos būtų pakeistos į nulines reikšmes

  2. atnaujinkite esamas eilutes, kad nulinės datos būtų pakeistos galiojančiomis datomis

Kiekvienai eilutei atnaujinti nereikia paleisti atskirų teiginių. Galime atnaujinti visas eilutes vienu šūviu (jei tai yra pakankamo dydžio lentelė. Didesnių lentelių atveju, kad būtų išvengta didžiulio grįžimo atgal ir (arba) pakartotinių operacijų generavimo, operaciją galime atlikti pakankamai didelėmis dalimis).

Klausime nurodyta lentelės apibrėžties AUTO_INCREMENT reikšmė užtikrina, kad eilučių skaičius nėra per didelis.

Jei jau pakeitėme stulpelį created, kad jame būtų galima įrašyti NULL reikšmes, galime daryti maždaug taip:

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

Arba galime nustatyti galiojančią datą, pvz., 1970 m. sausio 2 d.

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

(Atkreipkite dėmesį, kad 1970 m. sausio 1 d. vidurnakčio datos reikšmė ('1970-01-01 00:00:00:00') *yra "nulinė data". Ji bus įvertinta kaip '0000-00-00 00:00:00'

Yoan Tournade
Redaguotas atsakymas popietr liepa 2017 в 2:01
Komentarai (5)
Sprendimas

Mano pasiūlymas, jei lentelė yra tuščia arba nėra labai didelė, yra eksportuoti "create" teiginius kaip .sql failą ir perrašyti juos kaip norite. Tą patį padarykite ir tuo atveju, jei turite kokių nors esamų duomenų, t. y. eksportuokite įterpimo teiginius (rekomenduoju tai daryti atskirame faile kaip ir kūrimo teiginius). Galiausiai išmeskite lentelę ir vykdykite pirmiausia create, o paskui insert komandas.

Tam galite naudoti mysqldump komandą, įtrauktą į MySQL diegimo programą, arba galite įdiegti MySQL Workbench - nemokamą grafinę priemonę, kurioje taip pat yra tokia galimybė, kurią galima labai lengvai pritaikyti, neieškant konkrečių komandų parinkčių.

Komentarai (3)