MySQL Bugs: #70085: Error 1677 with replication, reading master log with <unknown type> column

Bug #70085	Error 1677 with replication, reading master log with <unknown type> column
Submitted:	19 Aug 2013 12:41	Modified:	21 Jul 2014 12:08
Reporter:	Goran Sumzer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.6.11-ndb-7.3.2-cluster-gpl-log	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	<unknown type>, binlog; replication; 1677, cannot be converted from type, error 1677, ndb, radius

Description:
Hi,

We`re issuing problem regarding replication between master(5.6.11-ndb-7.3.2-cluster-gpl-log) and slave(5.5.20-ndb-7.2.5-gpl-log). 
Replication is done by bin logs (binlog_format = ROW)

After upgrading master server from version 5.5.20-ndb-7.2.5-gpl-log to 5.6.11-ndb-7.3.2-cluster-gpl-log we are getting this error while try to replicate: 

Last_SQL_Errno:1677
Last_SQL_Error:Column 9 of table 'radius.radacct' cannot be converted from type '<unknown type>' to type 'datetime'
or 
Last_Errno: 1677
Last_Error: Column 4 of table 'radius.radpostauth' cannot be converted from type '<unknown type>' to type 'timestamp'

After upgrading master we had to turn on 'ALLOW_INVALID_DATES' and to turn off STRICT_TRANS_TABLES in sql_mode. 
On master we set binlog-checksum=NONE.

Why mysql sets <unknown type> on columns, instead of datetime/timestamp, or this is problem with slave mysql while reading log? Data in tables from master are ok and we do not have problem with stored data. We have problem only with replication. 
With previous versions of mysql we didn`t have that kind of problem with replication.

`Desc` for tables on master and slave are the same. 

for radius.radacct table:
+------------------------+--------------+------+-----+-------------------+-----------------------------+
| Field                  | Type         | Null | Key | Default           | Extra                       |
+------------------------+--------------+------+-----+-------------------+-----------------------------+
| radacctid              | bigint(21)   | NO   | PRI | NULL              | auto_increment              |
| acctsessionid          | varchar(64)  | NO   | MUL |                   |                             |
| acctuniqueid           | varchar(32)  | NO   | MUL |                   |                             |
| username               | varchar(64)  | NO   | MUL |                   |                             |
| groupname              | varchar(64)  | NO   |     |                   |                             |
| realm                  | varchar(64)  | YES  |     |                   |                             |
| nasipaddress           | varchar(15)  | NO   | MUL |                   |                             |
| nasportid              | varchar(15)  | YES  |     | NULL              |                             |
| nasporttype            | varchar(32)  | YES  |     | NULL              |                             |
| acctstarttime          | datetime     | YES  | MUL | NULL              |                             |
| acctupdatetime         | timestamp    | YES  | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| acctstoptime           | datetime     | YES  | MUL | NULL              |                             |
| acctsessiontime        | int(12)      | YES  | MUL | NULL              |                             |
| acctusagetime          | int(12)      | NO   |     | 0                 |                             |
| acctusageupdatetime    | datetime     | YES  |     | NULL              |                             |
| acctlastdayusagetime   | int(12)      | NO   |     | 0                 |                             |
| acctlastweekusagetime  | int(12)      | NO   |     | 0                 |                             |
| acctlastmonthusagetime | int(12)      | NO   |     | 0                 |                             |
| acctauthentic          | varchar(32)  | YES  |     | NULL              |                             |
| connectinfo_start      | varchar(50)  | YES  |     | NULL              |                             |
| connectinfo_stop       | varchar(50)  | YES  |     | NULL              |                             |
| acctinputoctets        | bigint(20)   | YES  |     | NULL              |                             |
| acctoutputoctets       | bigint(20)   | YES  |     | NULL              |                             |
| calledstationid        | varchar(50)  | NO   |     |                   |                             |
| callingstationid       | varchar(50)  | NO   |     |                   |                             |
| acctterminatecause     | varchar(32)  | NO   |     |                   |                             |
| servicetype            | varchar(32)  | YES  |     | NULL              |                             |
| framedprotocol         | varchar(32)  | YES  |     | NULL              |                             |
| framedipaddress        | varchar(15)  | NO   | MUL |                   |                             |
| acctstartdelay         | int(12)      | YES  |     | NULL              |                             |
| acctstopdelay          | int(12)      | YES  |     | NULL              |                             |
| xascendsessionsvrkey   | varchar(10)  | YES  |     | NULL              |                             |
| serviceinfo            | varchar(64)  | YES  |     | NULL              |                             |
| parentsessionid        | varchar(64)  | YES  |     | NULL              |                             |
| billfactor             | decimal(4,2) | NO   |     | 0.00              |                             |
+------------------------+--------------+------+-----+-------------------+-----------------------------+

for radius.radpostauth
+--------------+--------------+------+-----+-------------------+-----------------------------+
| Field        | Type         | Null | Key | Default           | Extra                       |
+--------------+--------------+------+-----+-------------------+-----------------------------+
| id           | bigint(11)   | NO   | PRI | NULL              | auto_increment              |
| username     | varchar(64)  | NO   | MUL |                   |                             |
| pass         | varchar(64)  | NO   |     |                   |                             |
| reply        | varchar(32)  | NO   |     |                   |                             |
| authdate     | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| reason       | varchar(128) | YES  |     |                   |                             |
| nasipaddress | varchar(15)  | YES  | MUL | NULL              |                             |
+--------------+--------------+------+-----+-------------------+-----------------------------+

Best regards
Sumi

How to repeat:
Do sql dump on some database/tables with datetime/timestamp columns(5.5.20-ndb-7.2.5-gpl-log), create tables from dump on master(5.6.11-ndb-7.3.2-cluster-gpl-log). Set log_bin_use_v1_row_events=ON, turn on 'ALLOW_INVALID_DATES', turn off 'STRICT_TRANS_TABLES'. 

Set master 5.6.11-ndb-7.3.2-cluster-gpl-log, set slave 5.5.20-ndb-7.2.5-gpl-log. Try to start replication.

Try to update date(datetime, timestamp) columns with '0' value. Try to update also with regular value.

I`m adding some new information

This is from binlog from slave relay-bin-log

### INSERT INTO radius.radpostauth
### SET
###   @1=6499634 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='aleksandar.rodic@bitinfo.rs' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @3='54321' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @4='Access-Reject' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
###   @5=!! Don't know how to handle column type=17 meta=0 (0000)3/oRUhj5AwAAMAoAANjjoxMAAFIAAAAAAAEAIwH8YxL4AfxjEvgE4AHnEQoAAAAAUhH6sjuZBQDA
pgIAmZBmyINshAAA1KIBAMCmAgD+pR8DAAAAAOCU2kkAAAAAAA4xNzguMjUzLjI0NC44NwTgAecR

So you are trying to replicate from 5.6.x to 5.5.x? 

Replication from older master to newer slave is usually supported (and needed as a possible upgrade path), but replication from newer master to older slave isn't as the newer master have new features that the older slave simply won't know or understand.

In your case the problem looks as if on the new master tables were created that use the new datetime/time types with microsecond support while the older slave only knows about the older types that have a one second resolution only.

So most likely: not a bug

I have a similar error replicating from 5.1.48 to 5.1.48. The only difference between the tables is InnoDB(master) to MyISAM(slave).

5.1.48-enterprise-gpl-advanced-log MySQL Enterprise Server - Advanced Edition (GPL)

#130905 16:02:47 server id 450  end_log_pos 41152973    Write_rows: table id 11126022 flags: STMT_END_F
### INSERT INTO au_op.SubCampaign
### SET
###   @1=771381
###   @2=0
###   @3=152258
###   @4=0
###   @5=9
###   @6=000000000
###   @7=!! Don't know how to handle column type=0 meta=0 (0000)# at 41152973

CREATE TABLE SubCampaign (
  idSubCampaign int(10) unsigned NOT NULL AUTO_INCREMENT,
  CreativeType_idCreativeType_FK int(10) unsigned NOT NULL DEFAULT '0',
  Campaign_idCampaign_FK int(10) unsigned NOT NULL DEFAULT '0',
  Creative_idCreative_FK int(10) unsigned NOT NULL DEFAULT '0',
  WebPublisher_idWebPublisher_FK int(10) unsigned NOT NULL DEFAULT '0',
  sc_budget decimal(13,2) DEFAULT '0.00',
  sc_percent decimal(5,2) DEFAULT NULL,
...
) ENGINE=InnoDB AUTO_INCREMENT=771414 DEFAULT CHARSET=latin1

--

CREATE TABLE SubCampaign (
  idSubCampaign int(10) unsigned NOT NULL AUTO_INCREMENT,
  CreativeType_idCreativeType_FK int(10) unsigned NOT NULL DEFAULT '0',
  Campaign_idCampaign_FK int(10) unsigned NOT NULL DEFAULT '0',
  Creative_idCreative_FK int(10) unsigned NOT NULL DEFAULT '0',
  WebPublisher_idWebPublisher_FK int(10) unsigned NOT NULL DEFAULT '0',
  sc_budget decimal(13,2) DEFAULT '0.00',
  sc_percent decimal(5,2) DEFAULT NULL,
...
) ENGINE=MyISAM AUTO_INCREMENT=771380 DEFAULT CHARSET=latin1

NDB in the version string and a tag might be misleading, since this is about binlog replication. Reproduced on regular MySQL 5.6.15 replicating to MySQL 5.5.35:

log-bin = mysql-bin
log-bin-use-v1-row-events = on
binlog-format = row
binlog-checksum = none

Set up replication, verify it works. Then on the master:

create table t1 (a int primary key, b datetime, c timestamp) engine=innodb;
insert into t1 values (1, '2012-01-02 14:23:45.123456', '2013-02-03 15:45:46.789012');

and on the slave:

show slave status\G
...
                   Last_Errno: 1677
                   Last_Error: Column 1 of table 'test.t1' cannot be converted from type '<unknown type>' to type 'datetime'

Even if this might be considered to be a not a bug due to replication to a lower version being unsupported, it might be worth to at least document it.

Has anyone documented what is causing this DDL to be created in the first place?

I am experiencing the same issue, I understand that ALL_NON_LOSSY default value is causing this to be noticed, but what is the underlying reason for the DDL?  My environment is Ruby which I am only passingly familiar with, but I have added tables that the developers have no knowledge of and these tables are continually affected by this error, leading me to conclude that this DDL is NOT coming from the codebase, but from the database itself.  The tables are read only to the world, and fully privileged only to a trigger user.

Master:
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| version       | 5.5.28-29.2-log |
+---------------+-----------------+

Slave:
mysql> show variables like 'version';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| version       | 5.5.27-28.1-log |
+---------------+-----------------+
1 row in set (0.00 sec)

this can be happen in situations like this:
5.6.xx is master while the slave version is lower, like 5.5.18 
Has anyone pay attention to this problem ?

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

> Set master 5.6.11-ndb-7.3.2-cluster-gpl-log, set slave 5.5.20-ndb-7.2.5-gpl-log. Try to start replication.

This is not supported configuration. MySQL only supports replication from master which is older or same version as slave. Using master which is newer than slave is not recommended and can lead to situations like this one. This is not a bug.