0

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?

8
  • A minimal reproducible example would help Jul 25 at 19:30
  • thank you @Charlieface, is there anything not clear enough on my question? Jul 26 at 12:34
  • show create table db.table and SELECT @@character_set_database, @@collation_database; on both master and slave would be a start Jul 26 at 12:44
  • I updated my question with both command, thank you for your suggestion. is it clear enough? @Charlieface Jul 26 at 13:20
  • Show us a sample of the "garbage in".
    – Rick James
    Jul 26 at 18:49
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.