I've recently taken over an old project that was created 10 years ago. It uses MySQL 5.1.
Among other things, I need to change the default character set from latin1 to utf8.
As an example, I have tables such as this:
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
I set up my own Mac to work on this. Without thinking too much about it, I ran "brew install mysql" which installed MySQL 5.7. So I have some version conflicts.
I downloaded a copy of this database and imported it.
If I try to run a query like this:
ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
I get this error:
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1
I thought I could fix this with:
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
but I get:
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
Do I have to update every value?
This question is related to
mysql
Instead of
UPDATE your_table SET your_column = new_valid_value where your_column = '0000-00-00 00:00:00';
Use
UPDATE your_table SET your_column = new_valid_value where your_column = 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 found the solution at https://support.plesk.com/hc/en-us/articles/115000666509-How-to-change-the-SQL-mode-in-MySQL. I had this:
mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
Notice the NO_ZERO_IN_DATE,NO_ZERO_DATE
in the results above. I removed that by doing this:
mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Then I had this:
mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
After doing that, I could use ALTER TABLE
successfully and alter my tables.
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 :
SHOW VARIABLES LIKE 'sql_mode';
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
NO_ZERO_IN_DATE,NO_ZERO_DATE
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
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.
I wasn't able to do this:
UPDATE users SET created = NULL WHERE created = '0000-00-00 00:00:00'
(on MySQL 5.7.13).
I kept getting the Incorrect datetime value: '0000-00-00 00:00:00'
error.
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'
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.
My solution
SET sql_mode='';
UPDATE tnx_k2_items
SET created_by = 790
, modified = '0000-00-00 00:00:00'
, modified_by = 0
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
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
This is incredibly ugly, but it also fixed the problem quickly for me. Your table needs a unique key which you will use to fix the tainted columns. In this example, the primary key is called 'id' and the broken timestamp column is called 'BadColumn'.
Select the IDs of the tainted columns.
select id from table where BadColumn='0000-00-00 00:00:00'
Collect the IDs into a comma-delimited string. Example: 1, 22, 33
. I used an external wrapper for this (a Perl script) to quickly spit them all out.
Use your list of IDs to update the old columns with a valid date (1971 through 2038).
update table set BadColumn='2000-01-01 00:00:00' where id in (1, 22, 33)
Make the sql mode non strict
if using laravel go to config->database, the go to mysql settings and make the strict mode false
If you are entering the data manually you may consider removing the values and the zeros on the TIMESTAMP(6).000000 so that it becomes TIMESTAMP. That worked fine with me.
I got it fixed by doing this before the query
SET SQL_MODE='ALLOW_INVALID_DATES';
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
.
For Symfony users: Add a default to your orm column as follows:
/**
* @var DateTime $updatedAt
*
* @ORM\Column(name="updated_at", type="datetime", nullable=false, options={"default" : "CURRENT_TIMESTAMP"})
*/
Check
SELECT @@sql_mode;
if you see 'ZERO_DATE' stuff in there, try
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_DATE',''));
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_IN_DATE',''));
Log out and back in again to your client (this is strange) and try again
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'
Source: Stackoverflow.com