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

MySQL

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?

Best Answer

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