mysql5.6.13主从同步问题

hyong2011

两台mysql5.6.13服务器做主从同步，主库修改表结构后，从库不能执行，报如下错误：
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.123
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000945
          Read_Master_Log_Pos: 265372438
               Relay_Log_File: relay-bin.000017
                Relay_Log_Pos: 441203666
        Relay_Master_Log_File: mysql-bin.000924
             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 7 of table 'trade.express_order_info' cannot be converted from type 'smallint' to type 'varchar(16)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 441203503
              Relay_Log_Space: 22815155399
              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 7 of table 'trade.express_order_info' cannot be converted from type 'smallint' to type 'varchar(16)'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 99
                  Master_UUID: 2b1f5667-0e13-11e3-9ea3-6cae8b29de6a
             Master_Info_File: /data/mysql/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: 140508 17:38:16
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
有朋友可遇到此情况？

tiankafeiwu

看看错误日志

hyong2011

2014-05-08 15:47:34 18357 [Note] Event Scheduler: scheduler thread started with id 1
2014-05-08 15:50:59 18357 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.1.123', master_port= 3306, master_log_file='mysql-bin.000919', master_log_pos= 4, master_bind=''.
2014-05-08 15:51:04 18357 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-05-08 15:51:04 18357 [Note] Slave I/O thread: connected to master 'slave@192.168.1.123:3306',replication started in log 'mysql-bin.000919' at position 4
2014-05-08 15:51:04 18357 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2014-05-08 15:51:04 18357 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000919' at position 4, relay log '/data/log/relay-bin.000001' position: 4
2014-05-08 17:38:16 18357 [ERROR] Slave SQL: Column 7 of table 'trade.express_order_info' cannot be converted from type 'smallint' to type 'varchar(16)', Error_code: 1677
2014-05-08 17:38:16 18357 [Warning] Slave: Field 'user_name' doesn't have a default value Error_code: 1364
2014-05-08 17:38:16 18357 [Warning] Slave: Field 'user_rank' doesn't have a default value Error_code: 1364
2014-05-08 17:38:16 18357 [Warning] Slave: Field 'discount' doesn't have a default value Error_code: 1364
2014-05-08 17:38:16 18357 [Warning] Slave: Field 'email' doesn't have a default value Error_code: 1364
2014-05-08 17:38:16 18357 [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-bin.000924' position 441203503
2014-05-09 09:16:48 18357 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
2014-05-09 09:16:48 18357 [Note] Slave I/O thread killed while reading event
2014-05-09 09:16:48 18357 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000945', position 265506114
2014-05-09 09:16:51 18357 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-05-09 09:16:51 18357 [Note] Slave I/O thread: connected to master 'slave@192.168.1.123:3306',replication started in log 'mysql-bin.000945' at position 265506114
2014-05-09 09:16:51 18357 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2014-05-09 09:16:51 18357 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000924' at position 441203503, relay log '/data/log/relay-bin.000017' position: 441203666
2014-05-09 09:16:51 18357 [Note] 'SQL_SLAVE_SKIP_COUNTER=1000' executed at relay_log_file='/data/log/relay-bin.000017', relay_log_pos='441203666', master_log_name='mysql-bin.000924', master_log_pos='441203503' and new position at relay_log_file='/data/log/relay-bin.000017', relay_log_pos='441347702', master_log_name='mysql-bin.000924', master_log_pos='441347539'
2014-05-09 10:28:43 18357 [ERROR] Slave SQL: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'CREATE DATABASE `test`', Error_code: 1007
2014-05-09 10:28:43 18357 [Warning] Slave: Can't create database 'test'; database exists Error_code: 1007
2014-05-09 10:28:43 18357 [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-bin.000928' position 838442591

typuc

Last_SQL_Error: Column 7 of table 'trade.express_order_info' cannot be converted from type 'smallint' to type 'varchar(16

你主表修改了 字段字符类型 ？

hyong2011

是的，照说这个也应该可以执行啊

hyong2011

回复 4# typuc

记得5.5改字段类型是可以同步的，为什么到5.6就不行了呢


   

hyong2011

5.6是不是有个主从日志应用BUG啊，从库还未完成应用主库传过来的日志，重启从数据库时竟然根据日志过期设置把主库传过来的日志删除了，致使主从同步不能工作，显示找不到主库日志文件，

typuc

回复 6# hyong2011

我也是从日志提示看的。具体的你要看版本差异了，不过理论上应该支持结构修改，可能是其他问题，发更多日志看看呢


   

liuxingyuyuni

关键问题在于 smallint to type varchar

hyong2011

主库上字段类型变更也没报错啊，错误日志都是说字段类型变更的问题！