英文:
mysql replication to read replica fail
问题
我有mysql，带有读取副本。
表列如下：
ID: 整数
Value: 整数
失败的SQL二进制日志
insert into `table` (id, value) VALUES (1, '');
复制失败原因是
Error 'Incorrect integer value: '' for column 'value' at row 1' on query.
两个表都使用STRICT_TRANS_TABLES的SQL模式。
为什么 insert into table (id, value) VALUES (1, '') 被在副本中重放，因为这是一个错误的插入。
英文:
I have mysql, with read replica.
The column table as follow:
ID: integer
Value: integer
SQL binlog that fails
insert into `table` (id, value) VALUES (1, '');
Replication fail due to
Error 'Incorrect integer value: '' for column 'value' at row 1' on query.
Both the table has sql mode of STRICT_TRANS_TABLES
Why is insert into table (id, value) VALUES (1, '') is being replay in replica since it is error insert.
答案1
得分: 1
语句仅在源实例上成功后才写入二进制日志。在某种程度上，该语句在源上是有效的。
sql_mode 默认情况下是严格的，但任何会话都可以覆盖 sql_mode。您应该检查您的应用程序代码。我甚至见过一些应用程序框架在开发者不知情的情况下更改了 sql_mode。
可能的情况是value列在源上是 varchar 类型，但在副本上是数值类型。这不太可能，但有可能，所以您应该仔细检查。
防止错误的一种好方法是使用基于行的复制。然后，二进制日志将不包含 SQL 语句，而是包含行图像，即插入的结果，而不是插入语法。MySQL 8.0 上的 binlog_format 默认为 ROW，所以我假设您有配置将其更改为 MIXED 或 STATEMENT。
英文:
Statements are never written to the binary log unless they succeeded on the source instance. Somehow that statement was valid on the source.
The sql_mode is strict by default, but any session can override the sql_mode. You should check your application code. I've even seen some app frameworks that changed the sql_mode without the developer's knowledge.
It could be that the value column is a varchar type on the source, but a numeric type on the replica. This is unlikely, but possible, so you should double-check that.
A good way to prevent the error is to use ROW-based replication. Then the binary log will not contain SQL statements, it will contain row images, i.e. the result of the insert, not the insert syntax. The binlog_format is ROW by default on MySQL 8.0, so I assume you have configuration to change this to MIXED or STATEMENT.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论