Ошибка sql 1067 invalid default value for date

The question is too broad for the answer. There are many problems regarding these questions, even more so when the incompatibility of the different MySQL-based engines is notorious.
For me the best option is to know the state of variables at the time of making the backup with the option —opt (mysqldump —opt) and apply it to our backup if it does not have it as usual, either because the original backup I did not have it, or because the one that has happened to us is incorrect.

If the backup does not contain the settings with which it was made, we will have to start investigating, but basically we can do it like this.

Add SETtings to header of backup

echo '
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;' | cat - mybackup.sql > temp && mv temp  mybackup.sql 

Add restore SETtings to end

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;' >> mybackup.sql 

If you don’t have those settings you can make a mysqldump backup with the —opt option on the original server, in order to get them.

If you don’t have it, you can go little by little, setting the necessary settings, both at the start and at the exit.

The post_date default value is 0000-00-00 00:00:00. If you check the sql_mode variable like this:

show variables like 'sql_mode'; 

… it will show you the sql_mode variable, that will be sth like this: 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

You have to set up again sql_mode variable without
NO_ZERO_IN_DATE,NO_ZERO_DATE

So in the previous example you should set the sql_mode like this:

SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Then check the sql_mode variable again to be sure it has changed correctly:

show variables like 'sql_mode';

Then the restriction is gone ;D

Found the solution here: https://stackoverflow.com/a/37696251/504910

Перейти к содержимому

ERROR 1067 (42000) at line 1623: Invalid default value for 'date_added'

`date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'дата добавления страницы',

`date_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'дата добавления страницы',

drop trigger if exists `ocpm_information_date_added_created`; -- удаляем если он есть
create trigger `ocpm_information_date_added_created` before insert -- создаем на событие before insert
    on `ocpm_information` -- имя таблицы
    for each row 
    set new.`date_added` = now();  -- имя поля

вот такой запрос

CREATE TABLE `main_documents` (
  `id` int(11) NOT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `citizenship_id` int(11) DEFAULT NULL,
  `document_type_id` int(11) DEFAULT NULL,
  `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `middle_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `hometown` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `issue_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `expiration_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `gender` int(11) DEFAULT NULL,
  `series` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `scan_id` int(11) DEFAULT NULL,
  `issued_by` text COLLATE utf8_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

выдает такую ошибку

#1067 — Invalid default value for ‘issue_date’

честно пытался гуглить но не смог понять в чем проблема