4

I have a database that I'm attempting to migrate into Amazon RDS. I'm generally following the instructions https://engineering.gosquared.com/migrating-mysql-to-amazon-rds.

I've done a mysqldump of the original server, and I've imported the data into the RDS server. Now, I'm ready to import the changes that have occurred since the mysqldump. When I pipe the output of mysqlbinlog to mysql, I get an error about a NULL value.

My table has a TIMESTAMP field called DateReceived with a default value of CURRENT_TIMESTAMP. When our app inserts records, it leaves DateReceived as NULL so that the database handles populating the current time.

When we try to import the mysqlbinlog, it chokes on those NULL value inserts.

Here's a snippet of the output from mysqlbinlog:

BEGIN
/*!*/;
# at 42615369
#130813 13:41:12 server id 60  end_log_pos 42615397     Intvar
SET INSERT_ID=173164716/*!*/;
# at 42615397
#130813 13:41:12 server id 60  end_log_pos 42618804     Query   thread_id=680551        exec_time=0     error_code=0
use mydatabase/*!*/;
SET TIMESTAMP=1376426472/*!*/;
INSERT INTO email_History (`From`, `Subject`, `DateReceived`) VALUES ('someone@acme.com', 'FW: Message', NULL)
/*!*/;
# at 42618804
#130813 13:41:12 server id 60  end_log_pos 42618831     Xid = 37399491

The error I get from mysql when attempting to import this is:

ERROR 1048 (23000) at line 28: Column 'DateReceived' cannot be null

Here's how my table is structured:

mysql> describe email_History;
+--------------+------------------------------+------+-----+-------------------+----------------+
| Field        | Type                         | Null | Key | Default           | Extra          |
+--------------+------------------------------+------+-----+-------------------+----------------+
| From         | varchar(512)                 | YES  | MUL | NULL              |                |
| Subject      | varchar(512)                 | YES  | MUL | NULL              |                |
| DateReceived | timestamp                    | NO   | MUL | CURRENT_TIMESTAMP |                |
+--------------+------------------------------+------+-----+-------------------+----------------+
3 rows in set (0.00 sec)

How can I import the binlog?

Anthony Neace
  • 25,013
  • 7
  • 114
  • 129
Travis Austin
  • 439
  • 5
  • 14
  • Our old database server is running MySQL 5.1. The new one is running 5.6. When I do an INSERT on 5.1, and set DateReceived = NULL, it works. When I do the same on 5.6, it rejects it. – Travis Austin Aug 16 '13 at 16:59
  • I thought I was on to something with changing the SQL_MODE, but I'm not finding anything in SQL_MODE that changes the value of inserting NULL for a TIMESTAMP field. The MySQL docs do say that I should be able to INSERT a NULL value for my TIMESTAMP field, and that it should automatically insert the CURRENT_TIMESTAMP. http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html ... but doing an INSERT INTO email_History SET DateReceived = NULL throws an error. – Travis Austin Aug 16 '13 at 17:33

1 Answers1

5

I found my answer, and am sharing it here for the good of the community.

There's a new System Variable introduced in MySQL 5.6.6, called "explicit_defaults_for_timestamp". With this value set to FALSE, my code above will work. If this is set to true, though, MySQL gives an error.

Unfortunately, Amazon RDS doesn't allow you to change that parameter, and it's not able to be set on a per-session basis.

mysql> SET SESSION explicit_defaults_for_timestamp=false;
ERROR 1238 (HY000): Variable 'explicit_defaults_for_timestamp' is a read only variable

You can read more about the variable here.

Travis Austin
  • 439
  • 5
  • 14