Skip to content
  • Neeraj Bisht's avatar
    2b024759
    Bug#17532932 - TIMESTAMP AND DATETIMES SELF-INCOMPATIBLE DURING REPLICATION · 2b024759
    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 
    2b024759
    Bug#17532932 - TIMESTAMP AND DATETIMES SELF-INCOMPATIBLE DURING REPLICATION
    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 
Loading