Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Error Code 1292 - Truncated incorrect DOUBLE value - Mysql

I am not sure what is this error!

#1292 - Truncated incorrect DOUBLE value: 

I don't have double value field or data!

I have wasted a whole hour trying to figure this out!

here is my query

INSERT INTO call_managment_system.contact_numbers 
    (account_id, contact_number, contact_extension, main_number, created_by)
SELECT
    ac.account_id,
    REPLACE(REPLACE(REPLACE(REPLACE(ta.phone_number, '-', ''), ' ', ''), ')', ''),'(','') AS Phone,
    IFNULL(ta.ext, '') AS extention,
    '1' AS MainNumber,
    '2' AS created_by
FROM 
    cvsnumbers AS ta
    INNER JOIN accounts AS ac ON ac.company_code = ta.company_code
WHERE 
    LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(ta.phone_number, '-', ''), ' ', ''), ')', ''),'(','') ) = 10

here is my show create table for the table which the results are going into

CREATE TABLE `contact_numbers` (  
    `number_id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
    `account_id` int(10) unsigned NOT NULL DEFAULT '0',  
    `person_id` int(11) NOT NULL DEFAULT '0',  
    `contact_number` char(15) NOT NULL,  
    `contact_extension` char(10) NOT NULL DEFAULT '',  
    `contact_type` enum('Primary','Direct','Cell','Fax','Home','Reception','Office','TollFree') NOT NULL DEFAULT 'Primary',  
    `contact_link` enum('Account','PDM','Other') NOT NULL DEFAULT 'Account',  
    `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0 = inactive, 1=active', 
    `main_number` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = main phone number',  
    `created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    `created_by` int(11) NOT NULL,  
    `modified_on` datetime DEFAULT NULL,  
    `modified_by` int(11) NOT NULL DEFAULT '0',  
    PRIMARY KEY (`number_id`),  
    KEY `account_id` (`account_id`),  
    KEY `person_id` (`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=534 DEFAULT CHARSET=utf8
like image 295
Mike Avatar asked Apr 17 '13 19:04

Mike


People also ask

How do I fix error code 1292 in MySQL?

You just need to go to the Wamp panel, then to MySQL, then to settings and change the mode to sql-mode: none.

What is truncated incorrect double value?

The MySQL error Truncated incorrect DOUBLE value is one of the weirdest errors in MySQL. This is because the error can be caused by some mistakes in your SQL script that has nothing to do with a DOUBLE value. The error is mostly triggered when there's a mistake in UPDATE statements script.


4 Answers

This message means you're trying to compare a number and a string in a WHERE or ON clause. In your query, the only potential place where that could be occurring is ON ac.company_code = ta.company_code; either make sure they have similar declarations, or use an explicit CAST to convert the number to a string.

If you turn off strict mode, the error should turn into a warning.

like image 96
Barmar Avatar answered Oct 21 '22 11:10

Barmar


I corrected this error as there was a syntax error or some unwanted characters in the query, but MySQL was not able to catch it. I was using and in between multiple fields during update, e.g.

update user 
set token='lamblala', 
    accessverion='dummy' and 
    key='somekey' 
where user = 'myself'

The problem in above query can be resolved by replacing and with comma(,)

like image 23
user1926248 Avatar answered Oct 21 '22 10:10

user1926248


I was facing the same issue. Trying to compare a varchar(100) column with numeric 1. Resulted in the 1292 error. Fixed by adding single quotes around 1 ('1').

Thanks for the explanation above

like image 12
ForeverLearner Avatar answered Oct 21 '22 10:10

ForeverLearner


TL; DR

This might also be caused by applying OR to string columns / literals.

Full version

I got the same error message for a simple INSERT statement involving a view:

insert into t1 select * from v1

although all the source and target columns were of type VARCHAR. After some debugging, I found the root cause; the view contained this fragment:

string_col1 OR '_' OR string_col2 OR '_' OR string_col3

which presumably was the result of an automatic conversion of the following snippet from Oracle:

string_col1 || '_' || string_col2 || '_' || string_col3

(|| is string concatenation in Oracle). The solution was to use

concat(string_col1, '_', string_col2, '_', string_col3)

instead.

like image 5
Frank Schmitt Avatar answered Oct 21 '22 10:10

Frank Schmitt