如何导入包含默认值为CURRENT_TIMESTAMP的TIMESTAMP字段的INSERT的MySQL Binlog [英] How to import MySQL binlog that contains INSERTs of a TIMESTAMP field with default value CURRENT_TIMESTAMP
问题描述
我有一个数据库正在尝试迁移到Amazon RDS.我通常遵循 https://engineering.gosquared.com/migrating- mysql-to-amazon-rds .
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.
我已经完成了原始服务器的mysqldump，并且已经将数据导入了RDS服务器.现在，我准备导入自mysqldump以来发生的更改.当我将mysqlbinlog的输出通过管道传输到mysql时，出现关于NULL值的错误.
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.
我的表有一个名为DateReceived的TIMESTAMP字段，默认值为CURRENT_TIMESTAMP.当我们的应用插入记录时，它会将DateReceived保留为NULL，以便数据库处理当前时间.
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.
当我们尝试导入mysqlbinlog时，它会阻塞那些NULL值插入.
When we try to import the mysqlbinlog, it chokes on those NULL value inserts.
这是mysqlbinlog输出的摘要:
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
我尝试导入时从mysql中收到的错误是:
The error I get from mysql when attempting to import this is:
ERROR 1048 (23000) at line 28: Column 'DateReceived' cannot be null
这是我的表的结构:
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)
如何导入Binlog?
How can I import the binlog?
推荐答案
我找到了答案，并在这里与社区分享.
I found my answer, and am sharing it here for the good of the community.
MySQL 5.6.6中引入了一个新的系统变量，称为"explicit_defaults_for_timestamp".将此值设置为FALSE时，上面的代码将起作用.但是，如果将其设置为true，则MySQL会给出错误消息.
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.
不幸的是，Amazon RDS不允许您更改该参数，并且无法基于每个会话进行设置.
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.
这篇关于如何导入包含默认值为CURRENT_TIMESTAMP的TIMESTAMP字段的INSERT的MySQL Binlog的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
