Stack Overflow for Teams is a private, secure spot for you and
your coworkers to find and share information.
Learn more
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?
My suggestion if it is the case that the table is empty or not very very big is to export the create statements as a .sql file, rewrite them as you wish. Also do the same if you have any existing data, i.e. export insert statements (I recommend doing this in a separate file as the create statements). Finally, drop the table and execute first create statement and then inserts.
You can use for that either mysqldump command, included in your MySQL installation or you can also install MySQL Workbench, which is a free graphical tool that includes also this option in a very customisable way without having to look for specific command options.
–
–
–
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' ;
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'
–
–
–
–
–
–
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
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
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.
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.
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.
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'
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 :)
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
–
–
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.
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 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.
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.
–
–
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)
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
