Bug #70124 Timestamp and datetimes self-incompatible during replication
Submitted: 22 Aug 2013 19:39 Modified: 13 Aug 2014 10:51
Reporter: Andrew McGill Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6.13 OS:Any
Assigned to: CPU Architecture:Any
Tags: converted, datetime, error 1677, replication, row based, timestamp

[22 Aug 2013 19:39] Andrew McGill
Description:
Replication actions on our InnoDB tables created with mysql 5.1.13 fail during row-based replication with error 1677, message "Column 6 of table 'yourdatabase.cooltable' cannot be converted from type 'timestamp' to type 'timestamp'"

This error affected both datetime and timestamp fields.

A workaround for this error is to dump the table, and re-import that exact same dump (on the slave):

mysqldump yourdatabase cooltable | mysql yourdatabase
mysql -e 'START SLAVE';

The newly created table does not have any difficulty converting timestamps to timestamps.  This suggests a silent change in the definition of the timestamp and datetime fields that is incompatible with the binary logging - perhaps the support of fractional time.

Master and slave are both "5.6.13-log MySQL Community Server (GPL)"

I hacked up a script to run the workaround of dumping each table that produces this error ... it seems to be working:

while true; do 
  mysql -e 'start slave; select sleep(10); show slave status\G'|
  grep Last_SQL_Error:.*cannot.be.converted | 
  cut -f 2 -d "'" | 
  while read R ; do 
    [ "$R" ] || continue; 
    echo Found error 1677 for db.table $R; 
    T=${R/*.} ; 
    D=${R/.*} ; 
    mysqldump  $D $T > dump.sql &&  mysql $D < dump.sql ; 
    mysql -e 'start slave' ; 
    sleep 1; 
    mysql -e 'show slave status\G'; 
  done ; 
  sleep 60; 
done

How to repeat:
I haven't tried to repeat this error from scratch - I expect a much neater version of this error is possible:

Install mysql 5.1.13
Set up row based replication
Create INNODB tables with datetime fields
Populate the tables
Upgrade to mysql 5.6.13, running mysql_upgrade, etc.
Make random (cough) changes.
Hope to see the problem -- I suspect that it only affects UPDATE queries.  

mysqlbinlog shows the timestamps as integers with no fractional part, for what that's worth.

Suggested fix:
Binary log replay should transparently handle the conversion between integer timetamps and fractional timestamp, since this distinction is not exposed to the front-end.
[22 Aug 2013 20:01] Andrew McGill
A correction: the mysql version on the tables were made was not 5.1.13 - sorry :(.  On looking at actual records the versions used were:

5.5.18 
5.5.25 
5.6.11 (showed error) 
5.6.13 (showed error)

Additionally, this server uses innodb_file_per_table, and was formerly the master in the replication arrangement in which it is now slave.
[24 Aug 2013 12:15] Leandro Morgado
Hello Andrew,  can you clarify what version the original master server was running before being upgraded to 5.6.13? And the original slave?

The original test case is specified as:
=====
Install mysql 5.1.13
Set up row based replication
Create INNODB tables with datetime fields
Populate the tables
Upgrade to mysql 5.6.13, running mysql_upgrade, etc.
======= 

But then you say:
======= 
A correction: the mysql version on the tables were made was not 5.1.13 - sorry :(.  On looking at actual records the versions used were:

5.5.18 
5.5.25 
5.6.11 (showed error) 
5.6.13 (showed error)
======= 

Please send detailed information about the original versions of the master and slave and how they were upgraded. Include information like which one was upgraded first, what versions they were running and if a binary upgrade (same datadir with mysql_upgrade)  was used.

Also please include my.cnf for both master and slave. 

I suspect the problem might have been caused by the binary upgrade (with mysql_upgrade) as mysqldump/reload seems to resolve the problem.

Additional information:
 http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html
 http://dev.mysql.com/doc/refman/5.6/en/replication-upgrade.html
[9 Sep 2013 14:35] Andrew McGill
Hi Leandro, 
Sorry about the delay here: it's pretty much impossible to give you a history of this installation - it's been in place for years.  I do not have an archive copy of the my.cnf at the time that the tables were created - although I do know that tables created directly before the update from 5.5.25 to 5.6.11 exhibited the bug.

The my.cnf as it currently stands, which is largely the same as before the update, is below.  

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip_name_resolve
max_heap_table_size=4096M
tmp_table_size=4096M
expire_logs_days = 7
innodb_file_per_table
max_connect_errors=10000
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=clientssqlha1-bin
server-id=11
user=mysql
log-slave-updates=1
binlog-format=ROW
old_passwords=0
max_allowed_packet=6400M
innodb_buffer_pool_size=16G
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit = 0
max_connections = 800
interactive_timeout = 3600
query_cache_limit = 40M
query_cache_size = 128M
query_cache_type = 1
slave_skip_errors=1032,1062
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-key=/etc/mysql/ssl/clientssql-key.pem
ssl-cert=/etc/mysql/ssl/clientssql-cert.pem
[25 Sep 2013 9:22] Simon Mudd
A similar issue happened to me with a 5.5.23 master replicating to a 5.6.13 intermediate master with 5.6.13 slaves underneath. Cause _may_ be an incomplete mysql_upgrade on all tables on the 5.6 server (that is using --upgrade-system-tables). I found that doing an ALTER TABLE XXX modify ...... (same column definition as before); actually then allowed replication to work. That's lighter than doing a load and dump, and can if necessary be replicated.
[29 Sep 2013 10:56] Andrii Nikitin
Verified in following steps:

1. Server1 5.6.12 : 
create table test.a(d timestamp) engine=InnoDB;
2. Server2 5.5.30 : 
create table test.a(d timestamp) engine=InnoDB;
3. Server2: binary upgrade to 5.6.14
4. Setup replication Server1->Server2
5. Server1: 
insert into test.a values (now());
6. Server2:
Last_Error: Column 0 of table 'test.a' cannot be converted from type 'timestamp' to type 'timestamp'

In my understanding related source code is following:

rpl_utility.cc : can_convert_field_to

  else if (metadata == 0 &&
           ((field->real_type() == MYSQL_TYPE_TIMESTAMP2 &&
             source_type == MYSQL_TYPE_TIMESTAMP) ||
            (field->real_type() == MYSQL_TYPE_TIME2 &&
             source_type == MYSQL_TYPE_TIME) ||
            (field->real_type() == MYSQL_TYPE_DATETIME2 &&
             source_type == MYSQL_TYPE_DATETIME)))

Not sure how it was designed, but it assumes replication only for case
MYSQL_TYPE_TIMESTAMP->MYSQL_TYPE_TIMESTAMP2 
but replication the other way must be supported too for binary upgraded slaves:
MYSQL_TYPE_TIMESTAMP2->MYSQL_TYPE_TIMESTAMP
[26 Feb 2014 21:51] martin fuxa
Hi guys, I have similar experience on 5.6.15 and 5.6.16 too.

some Mysql server conf:
binlog_format=ROW 
gtid-mode=ON
enforce-gtid-consistency=true
slave_sql_verify_checksum = ON
Master and slave have same conf.

Error
Slave SQL: Column 11 of table 'x.y' cannot be converted from type 'datetime' to type 'datetime', Error_code: 1677

query by relay log
INSERT INTO `x`.`y`
values (
  96,
  24346,
  ...
  '2014-02-21 09:01:08',
  1392969668);

and later for update too (from relay log again)
UPDATE `x`.`y`
WHERE ...
SET
   @1=96
   @2=24346
   @12='2014-02-21 09:01:08'
   @13=1392969760

running query in mysql client
Warning (Code 1265): Data truncated for column 'updated' at row 1
Warning (Code 1264): Out of range value for column 'updated' at row 1

table
CREATE TABLE `y` (
  `id` int(11) NOT NULL DEFAULT '1',
  `id_customer` int(11) NOT NULL DEFAULT '1',
  ...
  `created` datetime NOT NULL,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_customer`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and true
alter table `x`.`y` modify updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Note (Code 1880): TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.

update history
grep MySQL-server /var/log/yum.log
Jan 01 15:35:44 Updated: Percona-Server-server-55-5.5.28-rel29.2.360.rhel5.x86_64
May 03 21:25:28 Updated: Percona-Server-server-55-5.5.30-rel30.2.508.rhel5.x86_64
Aug 12 22:26:08 Installed: MySQL-server-5.6.13-1.rhel5.x86_64
Oct 11 06:38:03 Updated: MySQL-server-5.6.14-1.rhel5.x86_64
Jan 22 15:29:56 Updated: MySQL-server-5.6.15-1.rhel5.x86_64
Feb 26 21:16:24 Updated: MySQL-server-5.6.16-1.rhel5.x86_64

mysql_upgrade was executed without error, verified from linux history
mysql_upgrade -u root -p
[24 Apr 2014 12:27] Simon Mudd
Bitten again by this, and this time I'm pretty sure that the 5.6 slaves did have mysql_upgrade run on them.

Note: the problem materialised after modifying the servers in a replication chain, that is previously I had (pending an upgrade):

mysql 5.5.23 (master) -> mysql-5.6.15 (slaves A) 
   +-----> mysql 5.6.15 (master) ----> mysql 5.6.15 (slaves B)

and this seemed to be working fine (for a few weeks). Not sure if it was related but I modified the topology to:

mysql 5.5.23 (master) --> mysql-5.6.15 (to be master) ---> mysql-5.6.15 (slaves A)
                                                +-----------> mysql 5.6.15 (master) ----> mysql 5.6.15 (slaves B)

and shortly after this replication broke on the slaves A.

In my opinion 3 things are needed:
(1) better logging to indicate there is a conversion of timestamp [5.5] to timestamp(0) [5.6+]
(2) conversion of the types takes place automatically
(3) checkmysql tells you more than OK, and gives a clear indication if a table "change" is needed, as otherwise it's not possible to see if a missing conversion is needed.  This point (3) has been made on several occasions due to similar issues like this which have come up before and has not been acted upon. It is quite frustrating to not have a way to verify if a table on a server really is in need of a "mysql_upgrade" / "rebuild" or not, especially when suddenly a replication chain breaks and rebuilding all servers is the last thing you want to do.
[25 Apr 2014 6:11] Simon Mudd
I've upgraded the chain to be completely 5.6 and the problem (which came back) has gone away completely. It's worth noting that the chain was running RBR.

The table concerned had this "simple structure":

 CREATE TABLE `SiteVars` (
  `key` varchar(64) NOT NULL DEFAULT '',
  `value` varchar(255) NOT NULL,
  `last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and only 41 rows.

The the only thing we noticed if it makes any difference is:
(1) the developers assure me they do not change the last_change column, so it is theory changed by MySQL (on the 5.5 master)
(2) some of the rows on the upstream slaves had values such as '2096-07-04 04:13:07' and '2056-01-31 14:42:27' which are out of range of a normal timestamp value and given (I checked the binlogs) we're using RBR it seems impossible to send these values from the 5.5 master to the 5.6 downstream master and below.
(3) the 5.6 master did not show any issues, only the 5.6 slaves which were downstream to it.
(4) I am going through the process of upgrading several replication chains from 5.5 to 5.6 and have not seen this happen except on a previously reported problem several months ago. Some of those chains use RBR, so this seems to be some sort of weird edge case.
(5) I have requested in another FR that information on the mysql_upgrade runs is stored locally (without replication) in a mysql.* (MyISAM) table (so it can not be rolled back if there are errors) so that there is a history on the server of the upgrade process, whether it worked or not and the tables concerned. If this needs to be another sort of log file so be it, but this sort of information is invaluable for diagnosing the cause of issues like this, and in addition to the lack of clear information provided by mysqlcheck /mysql_upgrade about whether it actually changes a table, or the table requires a change, means that a potentially broken system is invisible to the DBA and that is not helpful.

So I'm hoping that some of the information provided here might help diagnose this specific issue and improvements suggested in this bug report may get pushed out to the code soon to better help diagnosis of problems like this which may happen in the future (as they no doubt will from time to time).
[23 May 2014 19:39] Brandon Johnson
FWIW, I'm having this exact same issue on a set of servers that's running 5.6.12-log across the board.

It happens rarely and I've found a simpler solution is to simply ALTER TABLE and redefine the column as it's own type. e.g. ALTER TABLE table_name CHANGE column column DATETIME NOT NULL;

Some interesting things to note:
a.) It's never repeated on the same table. ( don't know if that's true in your scenario)
b.) This only affects InnoDB tables.
c.) it's very random over time. It doesn't seem to follow any set order. It makes me wonder if it's an innodb tablespace problem.
[6 Aug 2014 17:16] Laurynas Biveinis
Should this be closed?

$ bzr log -n0 -r 5929
------------------------------------------------------------
revno: 5929
committer: Neeraj Bisht <neeraj.x.bisht@oracle.com>
branch nick: 5.6
timestamp: Mon 2014-05-12 12:18:27 +0530
message:
  Bug#17532932 - TIMESTAMP AND DATETIMES SELF-INCOMPATIBLE DURING REPLICATION
  
  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
[13 Aug 2014 10:51] David Moss
The following has been added to the 5.7.5 and 5.6.21 changelog with commit 3992:

Replication of tables that contained temporal type fields (such as TIMESTAMP, DATETIME, and TIME) from different MySQL versions failed due to incompatible TIMESTAMP types. This was caused by the fractional TIMESTAMP format added in MySQL 5.6.4 not being supported in earlier versions. You can now replicate a TIMESTAMP in either format correctly.
[13 Aug 2014 10:54] David Moss
Posted by developer:
 
The following has been added to the 5.7.5 and 5.6.21 changelog with commit 3992:
Replication of tables that contained temporal type fields (such as TIMESTAMP, DATETIME, and TIME) from different MySQL versions failed due to incompatible TIMESTAMP types. This was caused by the fractional TIMESTAMP format added in MySQL 5.6.4 not being supported in earlier versions. You can now replicate a TIMESTAMP in either format correctly.
[21 Sep 2014 21:34] Inaam Rana
So was this resolved by the commit mentioned by Laurynas or a new commit went into 5.6.21?
[21 Sep 2014 22:24] Calvin Sun
This following is in 5.6.20 changes:

Replication: Replication of tables that contained temporal type fields (such as TIMESTAMP, DATETIME, and TIME) from different MySQL versions failed due to incompatible TIMESTAMP types. The fractional TIMESTAMP format added in MySQL 5.6.4 was not being correctly converted. You can now replicate a TIMESTAMP in either format correctly according to the slave_type_conversions variable. (Bug #70124, Bug #17532932)

so, should be fixed in 5.6.20. Dev, please confirm!
[14 Oct 2014 16:17] Andrii Nikitin
I've checked rpl_utility.cc in 5.6.20 and can see new function timestamp_cross_check() instead of problem comparisson. I don't see anything related in 5.6.21 so far, so I would assume that the bug was "fixed" in 5.6.20
[15 Oct 2014 15:45] David Moss
I have double-checked with the developers and this was fixed in 5.6.20, not 5.6.21.
[16 Oct 2014 12:39] David Moss
Posted by developer:
 
Fixing development status back to closed.