Err 1292 - Truncated incorrect DOUBLE value

Question

I have this two tables:

OLD:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `crdate` int(11) DEFAULT NULL,
  `fb_id` text,
  `email` varchar(64) DEFAULT NULL,
  `fb_access_token` varchar(256) DEFAULT NULL,
  `display_name` varchar(128) DEFAULT NULL,
  `first_name` varchar(128) DEFAULT NULL,
  `middle_name` varchar(128) DEFAULT NULL,
  `last_name` varchar(128) DEFAULT NULL,
  `gender` varchar(128) DEFAULT NULL,
  `timezone` tinyint(4) DEFAULT NULL,
  `locale` varchar(16) DEFAULT NULL,
  `fb_profile_url` text,
  `balanced_id` text,
  `token` varchar(100) DEFAULT NULL,
  `address_id` int(11) DEFAULT NULL,
  `admin` tinyint(1) DEFAULT '0',
  `zip` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

NEW:

CREATE TABLE IF NOT EXISTS `friendzy`.`users_new` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(64) NULL DEFAULT NULL,
  `display_name` VARCHAR(128) NULL DEFAULT NULL,
  `first_name` VARCHAR(128) NULL DEFAULT NULL,
  `middle_name` VARCHAR(128) NULL DEFAULT NULL,
  `last_name` VARCHAR(128) NULL DEFAULT NULL,
  `gender` VARCHAR(128) NULL DEFAULT NULL,
  `redis_token` VARCHAR(100) NULL DEFAULT NULL,
  `admin` TINYINT(1) NULL DEFAULT '0',
  `api_token` VARCHAR(255) NULL DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `salt` VARCHAR(255) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT '0',
  `last_login` TIMESTAMP NULL DEFAULT NULL,
  `locked` TINYINT(1) NULL DEFAULT NULL,
  `expired` TINYINT(1) NOT NULL DEFAULT '0',
  `expired_at` TIMESTAMP NULL DEFAULT NULL,
  `confirmation_token` VARCHAR(255) NULL DEFAULT NULL,
  `password_requested_at` TIMESTAMP NULL DEFAULT NULL,
  `credentials_expired` TINYINT(1) NULL DEFAULT NULL,
  `credentials_expire_at` TIMESTAMP NULL DEFAULT NULL,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` TIMESTAMP NULL DEFAULT NULL,
  `modified_by` VARCHAR(255) NULL DEFAULT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 164
DEFAULT CHARACTER SET = latin1;

And im trying yo copy the data from the old one to the new one with this sentence:

INSERT INTO `users_new` (`id`, `email`, `display_name`, `first_name`, `middle_name`, `last_name`, `gender`, `redis_token`, `admin`, `api_token`, `password`, `salt`, `enabled`, `last_login`, `locked`, `expired`, `expired_at`, `confirmation_token`, `password_requested_at`, `credentials_expired`, `credentials_expire_at`, `created`, `modified`, `modified_by`, `deleted_at`)
  SELECT `users`.`id`, `users`.`email`, `users`.`display_name`, `users`.`first_name`, `users`.`middle_name`, `users`.`last_name`, `users`.`gender`, if(`users`.`token`, `users`.`token`, md5(1)), `users`.`admin`, md5(1), md5(1), md5(1), 1, NULL, 0, 0, NULL, md5(1), NULL, 0, NULL, NOW(), NOW(), 'UPDATE SCRIPT', NULL
  FROM users;

But i get this error:

[Err] 1292 - Truncated incorrect DOUBLE value: '4043a809f18ffc8be824064934797d3a'

Answer 1

This is one of those really crappy errors that gets thrown that might not have anything to do with the actual problem. My suggestion would be to change your

if(`users`.`token`, `users`.`token`, md5(1)) 

to

 COALESCE(`users`.`token`, md5(1))

And see if it doesn't fix itself.