-
Neeraj Bisht authored
Problem: In RBR, replication between master and slave tables are failing with an error (1677), when the tables contain temporal type fields(TIMESTAMP, DATETIME,TIME). Analysis: In the following scenarion: Master(mysql-5.6.12) -->created a table with TIMESTAMP field. Slave(mysql-5.6.14) -->created a table with TIMESTAMP field in mysql-5.5. -->upgraded the 5.5 data directory for 5.6.14 and used that as slave for replication. Now when we are trying to insert a record with row based replication. We will get an error saying "Column * of table '****' cannot be converted from type 'timestamp' to type 'timestamp'" The Problem is as in mysql-5.6 we introduce a new type MYSQL_TYPE_TIMESTAMP2(this will carry fraction part for timestamp field) and in mysql-5.5 we dont have MYSQL_TYPE_TIMESTAMP2 type. So when we create a field of TIMESTAMP(sql type) in 5.6 it will be of MYSQL_TYPE_TIMESTAMP2(internal type) and in 5.5 it will create a MYSQL_TYPE_TIMESTAMP(internal type). According to documentation, when we upgrade from 5.5 to 5.6, there is some Incomatible changes document in http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html "Due to the temporal type changes described in the previous incompatible change item above, importing pre-MySQL 5.6.4 tables that contain DATETIME and TIMESTAMP types into MySQL 5.6.4 (or later) fails." Which result that, in upgraded slave we have old TIMESTAMP type field. So when we are trying to insert a value in RBR mode, we get an error as the type mismatch happen. In replication, we are supporting MYSQL_TYPE_TIMESTAMP->MYSQL_TYPE_TIMESTAMP2 but the other way is not supported(i.e., Master with MYSQL_TYPE_TIMESTAMP2, slave with MYSQL_TYPE_TIMESTAMP), which happens in upgraded server because of the limitation of mysql_upgrade. Solution: Added code to do conversion between MYSQL_TYPE_TIMESTAMP2->MYSQL_TYPE_TIMESTAMP
Neeraj Bisht authoredProblem: In RBR, replication between master and slave tables are failing with an error (1677), when the tables contain temporal type fields(TIMESTAMP, DATETIME,TIME). Analysis: In the following scenarion: Master(mysql-5.6.12) -->created a table with TIMESTAMP field. Slave(mysql-5.6.14) -->created a table with TIMESTAMP field in mysql-5.5. -->upgraded the 5.5 data directory for 5.6.14 and used that as slave for replication. Now when we are trying to insert a record with row based replication. We will get an error saying "Column * of table '****' cannot be converted from type 'timestamp' to type 'timestamp'" The Problem is as in mysql-5.6 we introduce a new type MYSQL_TYPE_TIMESTAMP2(this will carry fraction part for timestamp field) and in mysql-5.5 we dont have MYSQL_TYPE_TIMESTAMP2 type. So when we create a field of TIMESTAMP(sql type) in 5.6 it will be of MYSQL_TYPE_TIMESTAMP2(internal type) and in 5.5 it will create a MYSQL_TYPE_TIMESTAMP(internal type). According to documentation, when we upgrade from 5.5 to 5.6, there is some Incomatible changes document in http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html "Due to the temporal type changes described in the previous incompatible change item above, importing pre-MySQL 5.6.4 tables that contain DATETIME and TIMESTAMP types into MySQL 5.6.4 (or later) fails." Which result that, in upgraded slave we have old TIMESTAMP type field. So when we are trying to insert a value in RBR mode, we get an error as the type mismatch happen. In replication, we are supporting MYSQL_TYPE_TIMESTAMP->MYSQL_TYPE_TIMESTAMP2 but the other way is not supported(i.e., Master with MYSQL_TYPE_TIMESTAMP2, slave with MYSQL_TYPE_TIMESTAMP), which happens in upgraded server because of the limitation of mysql_upgrade. Solution: Added code to do conversion between MYSQL_TYPE_TIMESTAMP2->MYSQL_TYPE_TIMESTAMP
Loading