13

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.

Ryan
  • 22,332
  • 31
  • 176
  • 357
  • The problem could be that the query is just wrong and MySql is holding your hand on your local machine (I suspect due to different SQL mode in effect). If `created` cannot be `null` then why on earth are you trying to set it to `null`? – Jon Jun 13 '14 at 21:52
  • I think this says that setting a non-nullable timestamp field's value to null is one way to get it to automatically set the value as the current time: http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html So whoever wrote this query originally (not me) was probably intending that. What do you mean by "different SQL mode"? How can I check and edit that? – Ryan Jun 13 '14 at 21:58
  • I bet you mean http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html. I'll investigate. – Ryan Jun 13 '14 at 21:59
  • I have just added an answer. – Jon Jun 13 '14 at 22:01
  • Does either server have triggers on the table? Be sure the `--triggers` option in mysqldump is active. – Michael - sqlbot Jun 14 '14 at 02:33

2 Answers2

40

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:

Community
  • 1
  • 1
Ryan
  • 22,332
  • 31
  • 176
  • 357
0

The main problem is that the INSERT is just wrong: it's trying to insert a NULL into a non-nullable column.

What you should do is simply fix the query:

INSERT INTO mytbl (id, user_id) VALUES(88882341234, 765);

The reason this causes an error only on the staging server is that the server operates in strict SQL mode and therefore immediately aborts when you try to insert an incorrect value into created.

You can easily check the SQL mode in effect with SELECT @@SESSION.sql_mode and change it (perhaps so you can reproduce the error on your own server) with

SET SESSION sql_mode = 'STRICT_ALL_TABLES'
Jon
  • 428,835
  • 81
  • 738
  • 806
  • Unfortunately, my 2 servers have the same sql_mode already, so this isn't the problem. And I don't want to edit the query because it has worked for years (and is still working on my local machine) and only stopped working on my Staging server this week suddenly (and I haven't been able to figure out what *changed* with my Staging server). Thanks for trying, though. – Ryan Jun 13 '14 at 22:18
  • @Ryan: What does it matter that it has worked for years if it doesn't work now and you know that it's wrong? What would it take to decide that broken needs fixing? It's your code, your choice for sure but I just can't grok that line of reasoning. – Jon Jun 13 '14 at 22:22
  • 2
    The fact that it's *not* broken on my local machine or in production means that I don't understand the problem yet, and the problem is specific to Staging. I never want to change code (especially when it's making money for us in production) without understanding. I want to figure out what changed within the past few days. – Ryan Jun 13 '14 at 23:09