MySQL Hatalı tarih saat değeri: '0000-00-00 00:00:00'

Yakın zamanda 10 yıl önce oluşturulmuş eski bir projeyi devraldım. MySQL 5.1 kullanıyor.

Diğer şeylerin yanı sıra, varsayılan karakter kümesini latin1'den utf8'e değiştirmem gerekiyor.

Örnek olarak, aşağıdaki gibi tablolarım var:

  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

Bunun üzerinde çalışmak için kendi Mac'imi kurdum. Çok fazla düşünmeden "brew install mysql" komutunu çalıştırdım ve MySQL 5.7 yüklendi. Yani bazı sürüm çakışmalarım var.

Bu veritabanının bir kopyasını indirdim ve içe aktardım.

Eğer böyle bir sorgu çalıştırmaya çalışırsam:

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

Bu hatayı alıyorum:

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

Bunu düzeltebileceğimi düşünmüştüm:

  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

ama anlıyorum:

  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

Her değeri güncellemek zorunda mıyım?

Programlama
mysql
2016ösp23ös22 Şubat 2016 в 10:23
16 görünümler

Bunu yapamadım:

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

(MySQL 5.7.13 üzerinde).

Sürekli Yanlış tarih saat değeri: '0000-00-00 00:00:00' hatası alıyordum.

Garip bir şekilde, bu çalıştı: SELECT * FROM users WHERE created = '0000-00-00 00:00:00'. İlkinin neden başarısız olduğu ve ikincisinin neden çalıştığı hakkında hiçbir fikrim yok... belki bir MySQL hatasıdır?

Her durumda, bu UPDATE sorgusu çalıştı:

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

Bir sütun için varsayılan değeri ALTER TABLE deyimi ile değiştirmek, örn.

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

... zaten kayıtlı olan herhangi bir değeri değiştirmez. Varsayılan" değeri, eklenen ve sütun için bir değer sağlanmayan satırlar için geçerlidir.

Hatayla neden karşılaştığınıza gelince, muhtemelen oturumunuz için sql_mode ayarı NO_ZERO_DATE içeriyordur.

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

İçe aktarma" işlemini yaptığınızda, bu tabloya INSERT işlemini yapan SQL deyimleri sıfır tarihe izin veren bir oturumda çalıştırıldı.

sql_mode ayarını görmek için:

SHOW VARIABLES LIKE 'sql_mode' ;

-ya da-

SELECT @@sql_mode ;

Mevcut sorunu nasıl düzelteceğinize gelince, ALTER TABLE deyimini çalıştırdığınızda hatanın atılmaması için "düzeltin".

Birkaç seçenek var:

  1. NO_ZERO_DATE ve NO_ZERO_IN_DATE öğelerini kaldırarak sql_mode öğesini sıfır tarihe izin verecek şekilde değiştirin. Değişiklik my.cnf dosyasına uygulanabilir, böylece MySQL Server yeniden başlatıldıktan sonra sql_mode değişkeni my.cnf dosyasındaki ayara başlatılacaktır.

Geçici bir değişiklik için, genel bir değişiklik gerektirmeden ayarı tek bir oturumla değiştirebiliriz.

-- 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. created sütununu NULL değerlere izin verecek şekilde değiştirin ve sıfır tarihlerini null değerlere değiştirmek için mevcut satırları güncelleyin

  2. sıfır tarihlerini geçerli bir tarihle değiştirmek için mevcut satırları güncelleyin

Her satırı güncellemek için ayrı ayrı deyimler çalıştırmamız gerekmez. Tüm satırları tek bir hamlede güncelleyebiliriz (makul büyüklükte bir tablo olduğunu varsayarsak. Daha büyük bir tablo için, devasa geri alma / yineleme oluşumundan kaçınmak için, işlemi makul boyutta parçalar halinde gerçekleştirebiliriz).

Soruda, tablo tanımı için gösterilen AUTO_INCREMENT değeri bize satır sayısının aşırı olmadığını garanti eder.

Eğer created sütununu NULL değerlere izin verecek şekilde değiştirdiysek, şöyle bir şey yapabiliriz:

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

Ya da bunları geçerli bir tarihe ayarlayabiliriz, örneğin 2 Ocak 1970

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

(1 Ocak 1970 gece yarısı tarih-zaman değerinin ('1970-01-01 00:00:00') *quot;sıfır tarih" olduğunu unutmayın. Bu, '0000-00-00 00:00:00' olarak değerlendirilecektir.

Yoan Tournade
Edited answer 2017ösp1ös14 Temmuz 2017 в 2:01
Yorumlar (5)
Çözüm

Tablonun boş olması veya çok büyük olmaması durumunda önerim, oluşturma ifadelerini bir .sql dosyası olarak dışa aktarmak ve bunları istediğiniz gibi yeniden yazmaktır. Ayrıca mevcut verileriniz varsa aynısını yapın, yani insert ifadelerini dışa aktarın (bunu oluşturma ifadeleri olarak ayrı bir dosyada yapmanızı öneririm). Son olarak, tabloyu bırakın ve önce oluşturma deyimlerini, ardından ekleme deyimlerini çalıştırın.

Bunun için MySQL kurulumunuzda bulunan mysqldump komutunu kullanabilir veya belirli komut seçeneklerini aramak zorunda kalmadan bu seçeneği de çok özelleştirilebilir bir şekilde içeren ücretsiz bir grafik aracı olan MySQL Workbench'i de yükleyebilirsiniz.

Yorumlar (3)