[mysql] 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

When I received this error I believe it was a bug, however you should keep in mind that if you do a separate query with a SELECT statement and the same WHERE clause, then you can grab the primary ID's from that SELECT: SELECT CONCAT(primary_id, ',')) statement and insert them into the failed UPDATE query with conditions -> "WHERE [primary_id] IN ([list of comma-separated primary ID's from the SELECT statement)" which allows you to alleviate any issues being caused by the original (failed) query's WHERE clause.

For me, personally, when I was using quotes for the values in the "WHERE ____ IN ([values here])", only 10 of the 300 expected entries were being affected which, in my opinion, seems like a bug.

If you have used CHECK CONSTRAINT on table for string field length

e.g: to check username length >= 8

use:

CHECK (CHAR_LENGTH(username)>=8)

instead of

CHECK (username>=8)

fix the check constraint if any have wrong datatype comparison

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.

In my case it was a view (highly nested, view in view) insertion causing the error in mysql-5.6:

CREATE TABLE tablename AS
  SELECT * FROM highly_nested_viewname
;

The workaround we ended up doing was simulating a materialized view (which is really a table) and periodically insert/update it using stored procedures.

Had this issue with ES6 and TypeORM while trying to pass .where("order.id IN (:orders)", { orders }), where orders was a comma separated string of numbers. When I converted to a template literal, the problem was resolved.

.where(`order.id IN (${orders})`);

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

If you don't have double value field or data, maybe you should try to disable sql strict mode.

To do that you have to edit "my.ini" file located in MySQL installation folder, find "Set the SQL mode to strict" line and change the below line:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

to this, deleting "STRICT_TRANS_TABLES"

# Set the SQL mode to strict
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

After that, you have to restart MySQL service to enable this change.

To check the change, open the editor an execute this sql sentence:

SHOW VARIABLES LIKE 'sql_mode';

Very Important: Be careful of the file format after saving. Save it as "UTF8" and don't as "TFT8 with BOM" because the service will not restart.

I've seen a couple cases where this error occurs:

1. using the not equals operator != in a where clause with a list of multiple or values

such as:

where columnName !=('A'||'B')

This can be resolved by using

where columnName not in ('A','B')

2. missing a comparison operator in an if() function:

select if(col1,col1,col2);

in order to select the value in col1 if it exists and otherwise show the value in col2...this throws the error; it can be resolved by using:

select if(col1!='',col1,col2);

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(,)