SQL error 1677 cannot be converted from type 'datetime' to type 'varchar(255)'

All we need is an easy explanation of the problem, so here it is.

I got error 1677 on my slave.

Last_Errno: 1677
Last_Error: Column 25 of table 'db.table' cannot be converted from type 'datetime' to type 'varchar(255)'

I’m try to solved this by copy the same db from master to the slave, but after I restart the slave it still showing the same error. I’ve done too walk around to find the solution. one of them are set slave_type_conversions=all_non_lossy but, I didnt sure to take this because it running well before.

running show create table db.table\G on both master and duplicate showing same output without any different.

Master create table

*************************** 1. row ***************************
       Table: machines
Create Table: CREATE TABLE `machines` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TID` varchar(26) NOT NULL,
  `Name` varchar(100) DEFAULT NULL,
  `DeviceCode` varchar(100) DEFAULT NULL,
  `DisplayType` varchar(16) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `OnlineStatus` tinyint(1) DEFAULT NULL,
  `Latitude` decimal(11,8) DEFAULT NULL,
  `Longitude` decimal(11,8) DEFAULT NULL,
  `Temperature` float(8,2) DEFAULT NULL,
  `Signal` int(3) DEFAULT NULL,
  `SellStatus` int(2) DEFAULT NULL,
  `Volume` int(2) DEFAULT NULL,
  `DisplaySize` int(3) DEFAULT NULL,
  `DeployStatus` tinyint(1) DEFAULT 0,
  `DeployLocation` varchar(255) DEFAULT NULL,
  `ImageUrl` varchar(255) DEFAULT NULL,
  `ImageLocation` varchar(255) DEFAULT NULL,
  `City` varchar(60) DEFAULT NULL,
  `IP` varchar(16) DEFAULT NULL,
  `MAC` varchar(20) DEFAULT NULL,
  `IMEI` varchar(50) DEFAULT NULL,
  `NetworkCode` int(8) DEFAULT NULL,
  `NetworkName` varchar(16) DEFAULT NULL,
  `AppVersion` varchar(12) DEFAULT NULL,
  `FaultNotifyRuleID` varchar(255) DEFAULT NULL COMMENT 'ID Management Strategi Alarm Yopoint',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `machines_tid_unique` (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=162 DEFAULT CHARSET=utf8
1 row in set (0.000 sec)

Master Character and Collation DB

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+
1 row in set (0.000 sec)

Slave create table

*************************** 1. row ***************************
       Table: machines
Create Table: CREATE TABLE `machines` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TID` varchar(26) NOT NULL,
  `Name` varchar(100) DEFAULT NULL,
  `DeviceCode` varchar(100) DEFAULT NULL,
  `DisplayType` varchar(16) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `OnlineStatus` tinyint(1) DEFAULT NULL,
  `Latitude` decimal(11,8) DEFAULT NULL,
  `Longitude` decimal(11,8) DEFAULT NULL,
  `Temperature` float(8,2) DEFAULT NULL,
  `Signal` int(3) DEFAULT NULL,
  `SellStatus` int(2) DEFAULT NULL,
  `Volume` int(2) DEFAULT NULL,
  `DisplaySize` int(3) DEFAULT NULL,
  `DeployStatus` tinyint(1) DEFAULT 0,
  `DeployLocation` varchar(255) DEFAULT NULL,
  `ImageUrl` varchar(255) DEFAULT NULL,
  `ImageLocation` varchar(255) DEFAULT NULL,
  `City` varchar(60) DEFAULT NULL,
  `IP` varchar(16) DEFAULT NULL,
  `MAC` varchar(20) DEFAULT NULL,
  `IMEI` varchar(50) DEFAULT NULL,
  `NetworkCode` int(8) DEFAULT NULL,
  `NetworkName` varchar(16) DEFAULT NULL,
  `AppVersion` varchar(12) DEFAULT NULL,
  `FaultNotifyRuleID` varchar(255) DEFAULT NULL COMMENT 'ID Management Strategi Alarm Yopoint',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `machines_tid_unique` (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=162 DEFAULT CHARSET=utf8
1 row in set (0.000 sec)

Slave Character and Collation DB

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+
1 row in set (0.000 sec)

Questions

Column 25 of table ‘atlas.machines’ cannot be converted from type ‘datetime’ to type ‘varchar(255)’

There is anything I can do with this?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

its done by:

  1. reset the slave
  2. setting the last binlog on slave instance
  3. try to stop and start the slave
  4. (optional) delete another binlog on master except the updated one

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply