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'

there is non double or numeric field on the tables, and the only data that can be that hash kind its generated by the md5 function neither of they its converted to a number, i cant find the error — bitgandtter
Can you please list your fields that are assigned md5(1) values? — PM 77-1
redis_token VARCHAR(100) NULL DEFAULT NULL, api_token VARCHAR(255) NULL DEFAULT NULL, password VARCHAR(255) NOT NULL, salt VARCHAR(255) NOT NULL, confirmation_token VARCHAR(255) NULL DEFAULT NULL, — bitgandtter

JNevill JNevill · Accepted Answer · 2014-11-04T19:31:12

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.

Err 1292 - Truncated incorrect DOUBLE value

1 Answers