Description:
The error message for error 1677 might show a column number which doesn't match information_schema.
How to repeat:
master> use test
Database changed
master> create table t1 (id int auto_increment primary key, i1 int, v1 varchar(100));
Query OK, 0 rows affected (0.01 sec)
slave> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
slave> alter table t1 add column i2 int after i1;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
master> insert into t1(i1, v1) values(100, 'test');
Query OK, 1 row affected (0.00 sec)
master> set binlog_format=row;
Query OK, 0 rows affected (0.00 sec)
master> insert into t1(i1, v1) values(200, 'test 2');
Query OK, 1 row affected (0.00 sec)
slave> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 20484
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 3307
Relay_Log_File: mysql_sandbox20485-relay-bin.000002
Relay_Log_Pos: 3267
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1677
Last_Error: Column 2 of table 'test.t1' cannot be converted from type 'varchar(100)' to type 'int(11)'
Skip_Counter: 0
Exec_Master_Log_Pos: 3104
Relay_Log_Space: 3656
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1677
Last_SQL_Error: Column 2 of table 'test.t1' cannot be converted from type 'varchar(100)' to type 'int(11)'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: c513ee57-6ff1-11e4-a229-58946b5e6904
Master_Info_File: /home/dveeden/sandboxes/rsandbox_5_6_19/node1/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 141119 14:44:37
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
slave> select COLUMN_NAME, ORDINAL_POSITION, COLUMN_TYPE from information_schema.columns where table_name='t1' and table_schema='test';
+-------------+------------------+--------------+
| COLUMN_NAME | ORDINAL_POSITION | COLUMN_TYPE |
+-------------+------------------+--------------+
| id | 1 | int(11) |
| i1 | 2 | int(11) |
| i2 | 3 | int(11) |
| v1 | 4 | varchar(100) |
+-------------+------------------+--------------+
4 rows in set (0.00 sec)
----------
According to the error it's column 2 but in information_schema it's column 3.
Seems like replications starts to count at 0, but I_S starts to count at 1.
----------
### INSERT INTO `test`.`t1`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2=200 /* INT meta=0 nullable=1 is_null=0 */
### @3='test 2' /* VARSTRING(100) meta=100 nullable=1 is_null=0 */
----------
In the binlog it's also numbered 3.
Suggested fix:
Try to make sure the numbering is consistent. Also try to give the name of the column in the error.
Description: The error message for error 1677 might show a column number which doesn't match information_schema. How to repeat: master> use test Database changed master> create table t1 (id int auto_increment primary key, i1 int, v1 varchar(100)); Query OK, 0 rows affected (0.01 sec) slave> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed slave> alter table t1 add column i2 int after i1; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 master> insert into t1(i1, v1) values(100, 'test'); Query OK, 1 row affected (0.00 sec) master> set binlog_format=row; Query OK, 0 rows affected (0.00 sec) master> insert into t1(i1, v1) values(200, 'test 2'); Query OK, 1 row affected (0.00 sec) slave> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 20484 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 3307 Relay_Log_File: mysql_sandbox20485-relay-bin.000002 Relay_Log_Pos: 3267 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1677 Last_Error: Column 2 of table 'test.t1' cannot be converted from type 'varchar(100)' to type 'int(11)' Skip_Counter: 0 Exec_Master_Log_Pos: 3104 Relay_Log_Space: 3656 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1677 Last_SQL_Error: Column 2 of table 'test.t1' cannot be converted from type 'varchar(100)' to type 'int(11)' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: c513ee57-6ff1-11e4-a229-58946b5e6904 Master_Info_File: /home/dveeden/sandboxes/rsandbox_5_6_19/node1/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 141119 14:44:37 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) slave> select COLUMN_NAME, ORDINAL_POSITION, COLUMN_TYPE from information_schema.columns where table_name='t1' and table_schema='test'; +-------------+------------------+--------------+ | COLUMN_NAME | ORDINAL_POSITION | COLUMN_TYPE | +-------------+------------------+--------------+ | id | 1 | int(11) | | i1 | 2 | int(11) | | i2 | 3 | int(11) | | v1 | 4 | varchar(100) | +-------------+------------------+--------------+ 4 rows in set (0.00 sec) ---------- According to the error it's column 2 but in information_schema it's column 3. Seems like replications starts to count at 0, but I_S starts to count at 1. ---------- ### INSERT INTO `test`.`t1` ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2=200 /* INT meta=0 nullable=1 is_null=0 */ ### @3='test 2' /* VARSTRING(100) meta=100 nullable=1 is_null=0 */ ---------- In the binlog it's also numbered 3. Suggested fix: Try to make sure the numbering is consistent. Also try to give the name of the column in the error.