How to enable NULL values in MySQL timestamp fields?

Question

I need to restore backup from one MariaDB server (version 10.1.23) to another MariaDB server (version 10.1.37). But this version has a much stricter mode by default and does not allow NULL values in timestamp fields and throws the below error when I restore the backup.

1067 - Invalid default value for 'dt'

I try to add this values to *.cnf file in /etc/mysql/conf.d/

[mysqld]

sql_mode=ALLOW_INVALID_DATES,IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
explicit_defaults_for_timestamp=OFF

Queries:

show variables like 'sql_mode';
show variables like 'explicit_defaults_for_timestamp';

show changed values of variables but it does not work. Why?

Please show us the CREATE TABLE that has dt. Also, what version of MariaDB/MySQL was the dump taken on? And loaded onto? — Rick James
– Rick James, Commented Jan 4, 2019 at 21:35
CREATE TABLE check_login` ( id int(11) NOT NULL AUTO_INCREMENT, uid int(11) DEFAULT NULL, dt timestamp DEFAULT NULL, bookmaker varchar(255) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uid_bk (uid,bookmaker) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;` — Qwentor
– Qwentor, Commented Jan 5, 2019 at 8:48

jcolebrand · Accepted Answer · 2019-01-04 16:10:49Z

4

ALTER TABLE table_name 
  MODIFY dt TIMESTAMP NULL;

This will alter the table with name table_name to set the column dt to nullable.

edited Jan 4, 2019 at 16:10

jcolebrand♦

6,3764 gold badges44 silver badges68 bronze badges

answered Jan 4, 2019 at 13:43

Vladix

412 bronze badges

Add a comment |

Stack Exchange Network

How to enable NULL values in MySQL timestamp fields?

1 Answer 1

Your Answer

Hot Network Questions

How to enable NULL values in MySQL timestamp fields?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions