Error Code 1292 - Truncated incorrect DOUBLE value - Mysql

Question

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

Crapshooter

According to this bug report the message comes from comparing a string column with an integer, because they both get converted to double for the comparison. How are ac.company_code and ta.company_code declared? – Vanscoy

See also bugs.mysql.com/bug.php?id=46641 where a poster suggested that this error message be reworded to "WHERE comparisons between numerical and non-numerical columns are not allowed" – Vanscoy

Both of those columns are int(11) and not strings! – Crapshooter

Can you make a sqlfiddle with some sample data? – Vanscoy

Answer 1

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.

Vanscoy

Wow, what a misleading error message. Thanks for helping. You were right. I needed to change DB::table('contacts')->where('attendance', $int) ->update(["attendance" => $string]); to DB::table('contacts')->where('attendance', '' . $int) ->update(["attendance" => $string]); – Offend

Thanks for this. To expand: there are all sorts of ways in which this can be triggered. In my case, it was using a regexp to extract an integer from a string, and comparing this against an integer. Weirdly, when I just used the SELECT statement, it was all fine: select xxxx from t1 inner join t2 on t1.id=substr(value,locate(':',tagvalue)+1) Once I turned that into an INSERT...SELECT, the error was triggered. – Polyvinyl

I encountered this issues when using ON DUPLICATE KEY UPDATE pk1=pk1 AND pk2=pk2. Replacing AND with , fixed my issue. Ref: #3456758 – Antifederalist

Convert Where clause values to string, did the job thanks – Addition

Thanks. Turns out I was sending a color as a string rather than as an integer. Worked for years, new server and it started barking. No doubles in sight! – Howl

@LorenPechtel You probably upgraded the MySQL version, which changed the default SQL_MODE. – Vanscoy

@Vanscoy Probably, but I had nothing to do with the server, I just had to hunt the error. – Howl

Answer 2

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

Incognito

Thanks this is not a big issue but sometime small problem becomes big issue – Fruitcake

Thank you so much!!!! This happened to me in the context of an update statement – Heinrich

Answer 3

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

Epimenides

Answer 4

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.

Inattentive

Thank-you! New to MySQL, I was concatenating using the SQL Server allowed way, something like string1 + string2 + string3. Your suggestion of the concat function fixed this error for me. – Improvident

Answer 5

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);

Tiaratibbetts

Answer 6

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.

Templas

Answer 7

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.

Rattlepate

It's more safe to do this per session basis, or even better only in the specific script that's modifying the 'troublesome tables': $pdo->query('SET SESSION SQL_MODE = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"') and after your script magic, turn strict back on same way:$pdo->query('SET SESSION SQL_MODE = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"') – Lippi

Answer 8

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.

Nosh

Answer 9

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})`);

Frail

Answer 10

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

Ailina