MySQL 잘못된 날짜 시간 값 : '0000-00-00 00:00:00'

MySQL 잘못된 날짜 시간 값 : '0000-00-00 00:00:00'

---

저는 최근 10 년 전에 만들어진 오래된 프로젝트를 인수했습니다. MySQL 5.1을 사용합니다.

무엇보다도 기본 문자 세트를 latin1에서 utf8로 변경해야합니다.

예를 들어 다음과 같은 테이블이 있습니다.

  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

이 작업을 수행하기 위해 내 Mac을 설정했습니다. 그것에 대해 너무 많이 생각하지 않고 MySQL 5.7을 설치 한 "brew install mysql"을 실행했습니다. 따라서 버전 충돌이 있습니다.

이 데이터베이스의 사본을 다운로드하여 가져 왔습니다.

다음과 같은 쿼리를 실행하려고하면 :

  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

나는 이것을 고칠 수 있다고 생각했다.

  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

그러나 나는 얻는다 :

  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

모든 값을 업데이트해야합니까?

---

테이블이 비어 있거나 매우 크지 않은 경우 create 문을 .sql 파일로 내보내고 원하는대로 다시 작성하는 것이 좋습니다. 기존 데이터가있는 경우에도 동일한 작업을 수행하십시오 (예 : 내보내기 삽입 문 (생성 문과 별도의 파일에서 수행하는 것이 좋습니다)). 마지막으로 테이블을 삭제하고 먼저 create 문을 실행 한 다음 삽입합니다.

mysqldumpMySQL 설치에 포함 된 명령 중 하나를 사용 하거나 특정 명령 옵션을 찾을 필요없이이 옵션을 매우 사용자 정의 가능한 방식으로 포함하는 무료 그래픽 도구 인 MySQL Workbench를 설치할 수도 있습니다.

---

나는 이것을 할 수 없었다 :

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

(MySQL 5.7.13에서).

계속 Incorrect datetime value: '0000-00-00 00:00:00'오류가 발생했습니다.

Strangely, this worked: SELECT * FROM users WHERE created = '0000-00-00 00:00:00'. I have no idea why the former fails and the latter works... maybe a MySQL bug?

At any case, this UPDATE query worked:

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

---

Changing the default value for a column with an ALTER TABLE statement, e.g.

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

... doesn't change any values that are already stored. The "default" value applies to rows that are inserted, and for which a value is not supplied for the column.

---

As to why you are encountering the error, it's likely that the sql_mode setting for your session includes NO_ZERO_DATE.

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

When you did the "import", the SQL statements that did the INSERT into that table were run in a session that allowed for zero dates.

To see the sql_mode setting:

SHOW VARIABLES LIKE 'sql_mode' ;

-or-

SELECT @@sql_mode ;

As far as how to "fix" the current problem, so that the error won't be thrown when you run the ALTER TABLE statement.

Several options:

1) change the sql_mode to allow zero dates, by removing NO_ZERO_DATE and NO_ZERO_IN_DATE. The change can be applied in the my.cnf file, so after a restart of MySQL Server, sql_mode variable will be initialized to the setting in my.cnf.

For a temporary change, we can modify the setting with a single session, without requiring a global change.

-- 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 ;

2) change the created column to allow NULL values, and update the existing rows to change the zero dates to null values

3) update the existing rows to change the zero dates to a valid date

---

We don't need to run individual statements to update each row. We can update all of the rows in one fell swoop (assuming it's a reasonably sized table. For a larger table, to avoid humongous rollback/undo generation, we can perform the operation in reasonably sized chunks.)

In the question, the AUTO_INCREMENT value shown for the table definition assures us that the number of rows is not excessive.

If we've already changed the created column to allow for NULL values, we can do something like this:

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

Or, we can set those to a valid date, e.g. January 2, 1970

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

(Note that a datetime value of midnight Jan 1, 1970 ('1970-01-01 00:00:00') is a "zero date". That will be evaluated to be '0000-00-00 00:00:00'

---

I got it fixed by doing this before the query

SET SQL_MODE='ALLOW_INVALID_DATES';

---

According to MySQL 5.7 Reference Manual:

The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

Since 0000-00-00 00:00:00 is not a valid DATETIME value, your database is broken. That is why MySQL 5.7 – which comes with NO_ZERO_DATE mode enabled by default – outputs an error when you try to perform a write operation.

You can fix your table updating all invalid values to any other valid one, like NULL:

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

Also, to avoid this problem, I recomend you always set current time as default value for your created-like fields, so they get automatically filled on INSERT. Just do:

ALTER TABLE users
ALTER created SET DEFAULT CURRENT_TIMESTAMP

---

SET sql_mode = 'NO_ZERO_DATE';
UPDATE `news` SET `d_stop`='2038-01-01 00:00:00' WHERE `d_stop`='0000-00-00 00:00:00'

---

You can change the type of created field from datetime to varchar(255), then you can set (update) all records that have the value "0000-00-00 00:00:00" to NULL.

Now, you can do your queries without error. After you finished, you can alter the type of the field created to datetime.

---

Here what my solution PhpMyAdmin / Fedora 29 / MySQL 8.0 (for example):

set sql_mode='SOMETHING'; doesn't work, command call successful but nothing was change.

set GLOBAL sql_mode='SOMETHING'; change global configuration permanent change.

set SESSION sql_mode='SOMETHING'; change session configuration SESSION variable affects only the current client.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

So I do this :

• Get SQL_MODE : SHOW VARIABLES LIKE 'sql_mode';
• Result : ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
• Remove on the result : NO_ZERO_IN_DATE,NO_ZERO_DATE
• Set new configuration : set GLOBAL SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

You can remove or add other mode in the same way.

This is helpful to change global for using and testing frameworks or sql_mode must be specified in each file or bunch of queries.

Adapted from a question ask here : how-can-i-disable-mysql-strict-mode

Example : install latest Joomla 4.0-alpha content.

Edit: In PhpMyadmin, if you have the control of the server, you can change the sql_mode (and all others parameters) directly in Plus > Variables > sql_mode

---

Make the sql mode non strict

if using laravel go to config->database, the go to mysql settings and make the strict mode false

---

I also got

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column

error info

Fix this by changing 0000-00-00 00:00:00 to 1970-01-01 08:00:00

1970-01-01 08:00:00 unix timestamp is 0

---

I have this error as well after upgrading MySQL from 5.6 to 5.7

I figured out that the best solution for me was to combine some of the solutions here and make something of it that worked with the minimum of input.

I use MyPHPAdmin for the simplicity of sending the queries through the interface because then I can check the structure and all that easily. You might use ssh directly or some other interface. The method should be similar or same anyway.

...

1.

First check out the actual error when trying to repair the db:

joomla.jos_menu Note : TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.

Warning : Incorrect datetime value: '0000-00-00 00:00:00' for column 'checked_out_time' at row 1

Error : Invalid default value for 'checked_out_time'

status : Operation failed

This tells me the column checked_out_time in the table jos_menu needs to have all bad dates fixed as well as the "default" changed.

...

2.

I run the SQL query based on the info in the error message:

UPDATE jos_menu SET checked_out_time = '1970-01-01 08:00:00' WHERE checked_out_time = 0

If you get an error you can use the below query instead that seems to always work:

UPDATE jos_menu SET checked_out_time = '1970-01-01 08:00:00' WHERE CAST(checked_out_time AS CHAR(20)) = '0000-00-00 00:00:00'

...

3.

Then once that is done I run the second SQL query:

ALTER TABLE `jos_menu` CHANGE `checked_out_time` `checked_out_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP;

Or in the case it is a date that has to be NULL

ALTER TABLE `jos_menu` CHANGE `checked_out_time` `checked_out_time` DATETIME NULL DEFAULT NULL;

...

If I run repair database now I get:

joomla.jos_menu OK

...

Works just fine :)

---

I had a similar problem but in my case some line had the value NULL.

so first I update the table:

update `my_table`set modified = '1000-01-01 00:00:00' WHERE modified is null

problem solved, at least in my case.

---

This is what I did to solve my problem. I tested in local MySQL 5.7 ubuntu 18.04.

set global sql_mode="NO_ENGINE_SUBSTITUTION";

Before running this query globally I added a cnf file in /etc/mysql/conf.d directory. The cnf file name is mysql.cnf and codes

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Then I restart mysql

sudo service mysql restart

Hope this can help someone.

참고URL : https://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000