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

User · Answer

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

User · Answer

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

User · Answer

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  quot my ini quot  file located in MySQL installation folder  find  quot Set the SQL mode to strict quot  line and change the below line    Set the SQL mode to strict sql-mode  quot STRICT TRANS TABLES NO AUTO CREATE USER NO ENGINE SUBSTITUTION quot   to this  deleting  quot STRICT TRANS TABLES quot    Set the SQL mode to strict sql-mode  quot NO AUTO CREATE USER NO ENGINE SUBSTITUTION quot   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  quot UTF8 quot  and don t as  quot TFT8 with BOM quot  because the service will not restart

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If you have used CHECK CONSTRAINT on table for string field length  e g  to check username length    8  use   CHECK  CHAR LENGTH username  gt  8    instead of   CHECK  username gt  8    fix the check constraint if any have wrong datatype comparison

User · Answer

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

User · Answer

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

[mysql] Error Code 1292 - Truncated incorrect DOUBLE value - Mysql

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

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

Examples related to mysql

Examples related to truncated

Examples related to mysql-error-1292