MySQL error #1067 Invalid default value for 'UpdatedAt'

Question

I made restore my MySQL database from backup, but i noticed that encoding characters are set to latin1_swedish_ci, i cant change it to utf8_unicode_ci because i get error as in the header. Any suggestions? output of show create table players; is below and select version(); 8.0.18

CREATE TABLE `players` (
 `Id` mediumint(9) NOT NULL AUTO_INCREMENT,
 `Login` varchar(50) NOT NULL DEFAULT '',
 `Game` varchar(3) NOT NULL DEFAULT '',
 `NickName` varchar(100) NOT NULL DEFAULT '',
 `Nation` varchar(3) NOT NULL DEFAULT '',
 `UpdatedAt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `Wins` mediumint(9) NOT NULL DEFAULT '0',
 `TimePlayed` int(10) unsigned NOT NULL DEFAULT '0',
 `TeamName` char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (`Id`),
 UNIQUE KEY `Login` (`Login`),
 KEY `Game` (`Game`),
 KEY `Nation` (`Nation`),
 KEY `Wins` (`Wins`),
 KEY `UpdatedAt` (`UpdatedAt`)
) ENGINE=MyISAM AUTO_INCREMENT=1014 DEFAULT CHARSET=latin1

i used this command: ALTER TABLE players CONVERT TO CHARACTER SET utf8; — HelghastG75, Apr 18 '21 at 20:06
please edit your question to show output of `show create table players;` and `select version();` — ysth, Apr 18 '21 at 21:17
I set the option for `NO_ZERO_DATE` as described by @ysth below, then created a table using the exact CREATE command you posted above. I added some data with `é` and `ó` in it. Then I used this command: `Alter table players convert to character set utf8mb4` and MySQL converted the database. All this was done in a single session with MySQL Workbench. Character sets are a nightmare. Make sure your use of character sets is consistent throughout your database and code, or something will look wrong. — Tangentially Perpendicular, Apr 19 '21 at 00:48
Does this answer your question? [Error in MySQL when setting default value for DATE or DATETIME](https://stackoverflow.com/questions/36374335/error-in-mysql-when-setting-default-value-for-date-or-datetime) — Nico Haase, Apr 20 '21 at 08:24
Or this? https://stackoverflow.com/questions/9192027/invalid-default-value-for-create-date-timestamp-field — Nico Haase, Apr 20 '21 at 08:24
Does this answer your question? [ERROR 1067 (42000): Invalid default value for 'created\_at'](https://stackoverflow.com/questions/36882149/error-1067-42000-invalid-default-value-for-created-at) — iamsujit, Oct 27 '21 at 08:14

score 3 · Answer 1 · answered Apr 18 '21 at 22:24

3

Your new server has the NO_ZERO_DATE sql mode set, which doesn't allow that default value. Personally I think it is a bug that it isn't letting you make unrelated changes to the table.

You can remove that setting for the current session with:

set session sql_mode=replace(@@sql_mode,'NO_ZERO_DATE','');

but you will likely want to set sql_mode in your configuration files so it applies to all sessions and persists after reboot. You may be doing other queries that new sql_mode defaults don't allow, so you should review other new defaults; in particular, people can have trouble with ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES.

answered Apr 18 '21 at 22:24

ysth

96,171
6
121
214

i used command set session sql_mode=replace(@@sql_mode,'NO_ZERO_DATE',''); and im trying to change the character set to urf8 and still get the same error – HelghastG75 Apr 18 '21 at 22:37
in the same session? – ysth Apr 19 '21 at 00:42
you are doing something wrong then; see https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e25acdc291358e614a6b877fa51b77f8 – ysth Apr 19 '21 at 17:00

score 0 · Answer 2 · edited Oct 27 '21 at 06:16

I have the same problem and I am sure that MySql restriction is help us. So, I do next commands:

Read current variables value: show variables like 'sql_mode';

Result of prevoius command: 
+---------------+-----------------------------------------------------+
| Variable_name | Value                                               |
+---------------+-----------------------------------------------------+
| sql_mode      | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------+

Temporialy remove date resttriction: set sql_mode='NO_ENGINE_SUBSTITUTION';

Run all needed commands, like INSERT/UPDATE/CREATE and etc.

After that restore sql_mode as it was before: set sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';

MySQL error #1067 Invalid default value for 'UpdatedAt'

2 Answers2

Linked