Ik'heb onlangs een oud project overgenomen dat 10 jaar geleden is gemaakt. Het gebruikt MySQL 5.1.
Ik moet onder andere de standaard karakterset veranderen van latin1 naar utf8.
Als voorbeeld, ik heb tabellen zoals deze:
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
Ik heb mijn eigen Mac opgezet om hieraan te werken. Zonder er veel bij na te denken, draaide ik "brew install mysql" welke MySQL 5.7 installeerde. Dus ik heb wat versie conflicten.
Ik heb een kopie van deze database gedownload en deze geïmporteerd.
Als ik probeer om een query als deze uit te voeren:
ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
krijg ik deze foutmelding:
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1
Ik dacht dat ik dit kon oplossen met:
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
maar ik krijg:
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
Moet ik elke waarde updaten?
Ik was niet in staat om dit te doen:
UPDATE users SET created = NULL WHERE created = '0000-00-00 00:00:00'
(op MySQL 5.7.13).
Ik kreeg steeds de Incorrecte datetime waarde: '0000-00-00 00:00:00'
fout.
Vreemd genoeg, dit werkte: SELECT * FROM users where created = '0000-00-00 00:00:00'
. Ik heb geen idee waarom de eerste mislukt en de laatste werkt ... misschien een MySQL bug?
In ieder geval, deze UPDATE query werkte:
UPDATE users SET created = NULL WHERE CAST(created AS CHAR(20)) = '0000-00-00 00:00:00'
Het veranderen van de standaard waarde voor een kolom met een ALTER TABLE
statement, b.v.
ALTER TABLE users MODIFY created datetime NULL DEFAULT '1970-01-02'
... verandert niets aan de waarden die al zijn opgeslagen. De "default" waarde geldt voor rijen die worden ingevoegd, en waarvoor geen waarde voor de kolom is opgegeven.
Wat betreft de reden waarom u de fout tegenkomt, het is waarschijnlijk dat de sql_mode
instelling voor uw sessie NO_ZERO_DATE
bevat.
Referentie: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date
Toen u de "import" deed, werden de SQL statements die de INSERT in die tabel deden uitgevoerd in een sessie die nul data toestond.
Om de sql_mode instelling te zien:
SHOW VARIABLES LIKE 'sql_mode' ;
-of-
SELECT @@sql_mode ;
Wat betreft het "fix" huidige probleem, zodat de fout niet zal'worden gegooid wanneer u de ALTER TABLE
statement uitvoert.
Verschillende opties:
sql_mode
om nul data toe te staan, door NO_ZERO_DATE
en NO_ZERO_IN_DATE
te verwijderen. De wijziging kan worden toegepast in het my.cnf bestand, zodat na een herstart van MySQL Server, sql_mode
variabele zal worden geïnitialiseerd naar de instelling in my.cnf.Voor een tijdelijke wijziging, kunnen we de instelling wijzigen met een enkele sessie, zonder dat een globale wijziging nodig is.
-- 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 ;
verander de created
kolom om NULL waarden toe te laten, en update de bestaande rijen om de nul data te veranderen in null waarden
update de bestaande rijen om de nul data te veranderen in een geldige datum
We hoeven geen afzonderlijke statements uit te voeren om elke rij bij te werken. We kunnen alle rijen in één keer bijwerken (ervan uitgaande dat het een redelijk grote tabel is. Voor een grotere tabel, om gigantische rollback/undo generatie te vermijden, kunnen we de operatie in redelijk grote brokken uitvoeren).
In de vraag verzekert de AUTO_INCREMENT
waarde die getoond wordt voor de tabel definitie ons dat het aantal rijen niet buitensporig is.
Als we de created
kolom al veranderd hebben om NULL
waarden toe te staan, kunnen we iets als dit doen:
UPDATE `users` SET `created` = NULL WHERE `created` = '0000-00-00 00:00:00'
Of, we kunnen die op een geldige datum zetten, b.v. 2 januari 1970
UPDATE `users` SET `created` = '1970-01-02' WHERE `created` = '0000-00-00 00:00:00'
(Merk op dat een datetime waarde van middernacht 1 januari 1970 ('1970-01-01 00:00:00'
) is een "zero date". Dat zal worden geëvalueerd als '0000-00-00 00:00:00'
Mijn suggestie als de tabel leeg is of niet erg groot, is om de create statements te exporteren als een .sql bestand, herschrijf ze zoals je wilt. Doe hetzelfde als je bestaande gegevens hebt, d.w.z. exporteer insert statements (ik raad aan dit in een apart bestand te doen als de create statements). Tenslotte, laat de tabel vallen en voer eerst de create statement uit en daarna de inserts.
Je kunt daarvoor het mysqldump
commando gebruiken, dat in je MySQL installatie zit, of je kunt ook MySQL Workbench installeren, wat een gratis grafisch hulpmiddel is dat ook deze optie bevat op een zeer aanpasbare manier zonder dat je naar specifieke commando opties hoeft te zoeken.