timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP can be null on one machine but not another?

Question

I have a MySql table with a field defined as:

`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

On my local machine, I can run:

INSERT INTO mytbl (id, user_id, created) VALUES(88882341234, 765, null);
SELECT id, user_id, created FROM mytbl WHERE id = '88882341234';

And then 'created' will show something like '2014-06-13 21:16:42'.

But on my staging server, if I run the same queries, I get this error:

Column 'created' cannot be null.

The schemas of the tables are the same (across local and staging), which I ensured via mysqldump (to clone the table before running this test).

I'm running MySql 5.6.17 on both machines. I've also ensured that both have the same sql_mode.

What could be the problem?

P.S. For people who don't know why I'd be setting a non-nullable field's value to null, MySql Docs say:

In addition, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

Answer 1

I found what the problem was. The MySql variable/parameter explicit_defaults_for_timestamp was OFF on my local machine but ON on my remote machine.

I visited my AWS RDS Parameter Groups page and changed explicit_defaults_for_timestamp from 1 to 0. Then I went to my AWS RDS instances page to watch when "Parameter Group" changed from "Applying" to "pending-reboot". Then I rebooted the particular instance.

These links helped me:

• https://stackoverflow.com/a/23392448/470749
• How to import MySQL binlog that contains INSERTs of a TIMESTAMP field with default value CURRENT_TIMESTAMP
• https://forums.aws.amazon.com/thread.jspa?threadID=132676