Tai Hang: part of MySQL with SQLyog can't sync to Slave library

There is a business on the line is a master two from, this afternoon there is a sudden alarm from the library: 1677 error, the hint data type can not be changed from smallint to varchar (20), have encountered such an error before, set parameter sets global Slave_type_ Conversions=all_non_lossy, re-start slave, but this time changed the parameters still have no effect.

The DBA did not change the table structure after asking, except that the developer adjusted the order of the columns. Look at the table structure of the error table, and find that this is different from the library's table structure and the main library. The parse log found that there was a related ALTER TABLE operation in the Relaylog, but it was not executed from the library. It is strange that the other one is normal from the library.

I started to suspect that there is a problem with the configuration file parameter settings, but the comparison found that two from the library configuration is the same, and then re-made from the library, and then let the development of the tool to re-order the table column, to see the two from the library before the error from the library is not error, but the order of the field is still

View the version of the database, the main library is 5.5.33, normal from the library is also 5.5.33, the error from the library is 5.6.23. Just started to wonder if it was because of a different version of the issue, and then executed the ALTER TABLE statement on the command line and found no exception from the library.

Finally began to suspect is a tool problem, I first Use navicat change table structure, copy no problem;

With SQLyog v8.14 version operation, copy no problem;

Called Development with navicat operation no problem;

Called Development with SQLyog operation, the previous from the library and failed to copy the past.

View the SQLyog version of the development as the v11.24 version.

Call development to another version of the SQLyog operation, copy no problem.

This is not a very good problem to troubleshoot, because development is a database of tools, and this problem rarely occurs.

Through this problem, found that with the tool database or there will be a lot of hidden trouble, to fundamentally solve this problem can only strictly control the development of permissions, only give them SELECT permissions.

DBAs operate the database as much as possible at the command line.

This article is from the "Always on the Road" blog, please be sure to keep this source http://chenql.blog.51cto.com/8732050/1715167

Tai Hang: part of MySQL with SQLyog can't sync to Slave library