| Bug #79819 | Cannot resolve replication error 1677 | ||
|---|---|---|---|
| Submitted: | 1 Jan 2016 17:06 | Modified: | 5 Dec 2018 13:36 |
| Reporter: | Yael Goldberg | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S2 (Serious) |
| Version: | 5.6.25 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | row based replication error 1677 | ||
[2 Jan 2016 4:58]
MySQL Verification Team
At first, it doesn't look like a bug. Seems more like an incorrect replication setup to me. Please upload complete slave mysql error log here (compress if needed). Are table structure on both servers showing identical columns? Were they ever modified independently on master and slave? show create table amt.cancels;
[3 Jan 2016 4:03]
Yael Goldberg
This table is identical on the master on slave, except that the master is MyISAM and the slave is Innodb. The data is not modified at all on the slave. This is a new slave that I'm in the midst of setting up. This is the first time we are replicating this data. I will upload the MySQL error log since we started replication.
[4 Jan 2016 2:07]
Yael Goldberg
It just occurred to me that possibly the code is adding the primary key after it creates the table (as a create select), and I confirmed in the code that this is the case. The table is created as a create select. After the table is created (and other logic), the code adds the auto increment primary key as column 0: alter table cancels add id int(10) primary key auto_increment first; This is causing the row based replication to error as it's thinking that column 2 is still column 2, when it in fact is now column 3.
[5 Dec 2018 13:34]
BAJRANG LAL PANIGRAHI
Getting similar ERROR on MySQL Multi-Source Replicated slave server, When old attributes are in with COLUMN COLLATION: `COLLATE utf8mb4_unicode_ci` and Table CHARSET is `utf8mb4` Verified this change is only affected to MSR slave on the same MySQL version 5.7.23 release ERROR-LOG: 2018-12-05T12:05:37.961320Z 14284 [ERROR] Slave SQL for channel 'ta-master': Column 10 of table 'yp_time_scheduling.time_scheduling_historicalschedulingseries' cannot be converted from type 'varchar(1(bytes))' to type 'varchar(4(bytes) utf8mb4)', Error_code: 1677 2018-12-05T12:05:37.961343Z 14284 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-binlog.000619' position 102083846
[5 Dec 2018 13:36]
MySQL Verification Team
Not seeing a bug here. If there is anything that is not explained by: https://dev.mysql.com/doc/mysql-replication-excerpt/5.6/en/replication-features-different-... let us know?
Description: I have row based replication set up to replicate to a slave. The master and slave have the same MySQL configuration, the same charsets, the same MySQL version. The master has more databases than on the slave, and the slave is configured with several replicate-wild-ignore-table settings. I am getting error 1677 when replicating a table that was created as MyISAM on the master using (set session default_storage_engine=myisam), and replicating to the slave using the default storage engine of innodb. I have attempted to resolve a few of these errors by actually converting the table column on the slave manually to what the replication is expecting, but this latest error just is so obviously a bug. I don't know what to do with it. Why would replication be trying to convert column 2 (cancel_id) from date to int??? Slave error: Last_SQL_Errno: 1677 Last_SQL_Error: Column 2 of table 'amt.cancels' cannot be converted from type 'date' to type 'int(10) unsigned' mysql> desc amt.cancels; +--------------------+-----------------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-----------------------+------+-----+------------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | order_id | mediumint(8) unsigned | NO | MUL | 0 | | | cancel_id | int(10) unsigned | NO | MUL | 0 | | | cancel_date | date | NO | | 0000-00-00 | | | credit_type | char(1) | NO | | | | <more fields> +--------------------+-----------------------+------+-----+------------+----------------+ 15 rows in set (0.00 sec) While this may be a different bug, I have tried to add the below settings to my.cnf and restart MySQL, but the errors (1677 and 1146 as well) are not being skipped. i have also added the slave-type-conversion setting, but that did not help. I do not want to corrupt my data due to the MySQL bug above incorrectly converting a date field to an int field... slave-skip-errors=1146,1049,1050,1051,1062,1017,1677 slave-type-conversions=ALL_LOSSY,ALL_NON_LOSSY Please advise. How to repeat: i am not sure what is causing this issue. Suggested fix: MySQL should not attempt to do unnecessary conversions, and should handle necessary conversions quietly.