3

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?

5
  • Is the dt column allowed to be null in the DDL?
    – Dave
    Jan 4, 2019 at 13:01
  • Yes, it allowed
    – Qwentor
    Jan 4, 2019 at 17:13
  • 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
    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
    Jan 5, 2019 at 8:48
  • MariaDB 10.1.23 ==> MariaDB 10.1.37
    – Qwentor
    Jan 5, 2019 at 8:51

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.