0

There is a difference in the behavior of my local MySQL docker container and the RDS instance in another environment. I have read up on the documentation about this error and different options for sql_mode. But I could not figure out where this difference comes from.

  • I am running the exact same script.
  • The version is the same (MySQL 5.7.26) and all the parameters that are relevant look the same
  • Locally for docker I am using the image mysql:5.7 with the default command

The script that I'm running to reproduce the issue:

CREATE DATABASE `test_db`;
USE `test_db`;

SET SESSION `sql_mode`='STRICT_TRANS_TABLES';

CREATE TABLE `my_table` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    PRIMARY KEY (`id`)
);

SHOW SESSION VARIABLES WHERE variable_name = 'sql_mode';
/* outputs: sql_mode STRICT_TRANS_TABLES */

INSERT INTO `my_table` (`id`, `name`, `date_created`) VALUES (NULL, 'John', NULL);

SHOW WARNINGS;

Result on localhost

/* Affected rows: 1  Found rows: 0  Warnings: 0  Duration for 1 query: 0.000 sec. */

Result on RDS

/* SQL Error (1048): Column 'date_created' cannot be null */

Apparently it's not the sql_mode parameter and not the version. Where else could that difference in behavior come from?

webtopf
  • 304
  • 3
  • 12
  • I just tried the same script but using `NO_ENGINE_SUBSTITUTION` instead, because the GLOBAL sql_mode on RDS is set to that. I still get the same result. – webtopf Mar 30 '21 at 00:57
  • Check the value of [`explicit_defaults_for_timestamp`](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp) (and read the explanation in the link). – Bill Karwin Mar 30 '21 at 01:05
  • 1
    @BillKarwin That was it. Thank you for the help! I now added this flag in the docker-compose file. This way my local environment has the same settings like the RDS instance. – webtopf Mar 30 '21 at 02:05

0 Answers0