mysql - 应用错误 1677 : Column 59 of table `sales_orders` cannot be converted from type `datetime` to type `varchar(10)`

Question

当我在 Amazon RDS 只读副本 上执行以下查询时，出现错误

应用错误 1677:表 sales_orders 的第 59 列无法从 datetime 类型转换为 varchar(10) 类型

我在只读副本上执行的查询

ALTER TABLE `sales_orders` 
ADD COLUMN `dw_base_currency` VARCHAR(10) NULL AFTER `source`,
ADD COLUMN `dw_base_total` DECIMAL(12,4) NULL AFTER `dw_base_currency`;

我已经检查了 mysql replication (TokuDB replica): Column X of table 'database.table' cannot be converted from type 'varchar(Y)' to type 'varchar(Y)' 的答案中描述的表的字符集但在我的例子中，表的字符集是相同的。

Answer 1

当复制由查询引起的更改时，如果主服务器选择以 ROW 格式记录事件，则列名不会写入日志——只有值，按表定义中每一列的顺序位置。

这意味着副本的列数可以比主副本多或少，但如果是这样，则差异必须限制在表中最右边的列。

您对副本的更改显然在中间某处引入了新列，这是无效的。

You can replicate a table from the master to the slave such that the master and slave copies of the table have differing numbers of columns, subject to the following conditions:

• Columns common to both versions of the table must be defined in the same order on the master and the slave.

(This is true even if both tables have the same number of columns.)

• Columns common to both versions of the table must be defined before any additional columns.

This means that executing an ALTER TABLE statement on the slave where a new column is inserted into the table within the range of columns common to both tables causes replication to fail [...]

https://dev.mysql.com/doc/mysql-replication-excerpt/5.7/en/replication-features-more-columns.html