Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rock (SOTR) 2010 (Brussels) with:

Data Truncation: Truncated Incorrect DOUBLE Value When Updating Timestamp

By Ben Nadel on
Tags: ColdFusion, SQL

I just spent the last hour trying to debug the smallest SQL problem! I have a datatable of Contracts and I was building a feature in our client software where we could end all contracts of a certain type (SLA) at the same time (trust me, there is a business reason behind this). To do this, I was executing the following MySQL Cross-Table UPDATE statement:

  • UPDATE
  • contract c
  • INNER JOIN
  • contract_type t
  • ON
  • (
  • t.reference_key = 'SLA'
  • AND
  • c.contract_type_id = t.id
  • )
  • SET
  • c.date_updated = <cfqueryparam value="#Now()#" cfsqltype="cf_sql_timestamp" />
  • AND
  • c.date_ended = <cfqueryparam value="#Now()#" cfsqltype="cf_sql_timestamp" />
  • WHERE
  • c.date_ended IS NULL

I'm trying to set the date_ended field to Now() where ever it is currently NULL and of the correct contract type (SLA). However, when I ran this query, MySQL kept throwing this error:

Data truncation: Truncated incorrect DOUBLE value: '2009-02-18 13:43:35'

The error certainly wants you to believe that this is a data problem. Specifically, the error wants you to believe that the date/time value you are using in the query is somehow being converted to a double and that the converted value is too big for a double. So naturally, that's what I was trying to debug. But, after a solid hour, no joke, I came to realize that this error has nothing to do with data at all and is, in fact, a syntax error!

Look at my SET statement:

  • SET
  • c.date_updated = <cfqueryparam value="#Now()#" cfsqltype="cf_sql_timestamp" />
  • AND
  • c.date_ended = <cfqueryparam value="#Now()#" cfsqltype="cf_sql_timestamp" />

See the problem now? I have it written out like a WHERE statement; a proper SET statement uses commas, not ANDs. Rewritten properly, the query is:

  • UPDATE
  • contract c
  • INNER JOIN
  • contract_type t
  • ON
  • (
  • t.reference_key = 'SLA'
  • AND
  • c.contract_type_id = t.id
  • )
  • SET
  • c.date_updated = <cfqueryparam value="#Now()#" cfsqltype="cf_sql_timestamp" />,
  • c.date_ended = <cfqueryparam value="#Now()#" cfsqltype="cf_sql_timestamp" />
  • WHERE
  • c.date_ended IS NULL

So yeah, that's an hour of my life I won't get back. That's a really poor error that MySQL is throwing. Not that I'm blaming MySQL - I was the one who wrote the crappy statement - but, it did sort of lead me on a wild goose chase. Next time, thought, I'll be prepared!



Reader Comments

Good thing that you have your own company. If you worked for someone they might have ripped you for "wasting" an hour.

Those bugs are so much fun.

Reply to this Comment

Just curious. Why did you write out:
c.date_ended = <cfqueryparam value="#Now()#" cfsqltype="cf_sql_timestamp" />
When MySQL has a built in "now()" function. Would it be cleaner (and a mirco-fraction faster) if you did this:
c.date_ended = now()

I imagine you have your reasons.
(Like:
To keep the SQL cross DB compliant
DB server isn't in same timezone as Webserver
)

Reply to this Comment

@Tim,

I just use it out of habit. I like to keep the SQL as consistent as possible and so I use CFQueryParam for ally my dynamic value. Plus, sometimes I have dates that have to be null based on other values and CFQueryParam makes that wicked easy:

<cfqueryparam value="#FORM.date_started#" type="cf_sql_timestamp" null="#(NOT IsDate( FORM.date_started ))#" />

With this, I can put my null-ify logic right into the CFQueryParam tag and keep my SQL free of CFIF statements regarding NULL inserts.

Reply to this Comment

WOW. I just did the same thing! I actually spent a couple hours trying to figure this out :(

Reply to this Comment

GOD!

same mistake here.

the error message REALLY HELPED(in making debugging more complex I mean:P)

Reply to this Comment

MySQL raised same crappy error on the following syntax:

select IFNULL(id,0)+1 into nextval from nextids where UPPER(tablename)=UPPER('''' + tbname + '''')

The correct syntax should be:

select IFNULL(id,0)+1 into nextval from nextids where UPPER(tablename)=UPPER(CONCAT('''',tbname,''''));

Thank you,Ben. It is your post that enlightened me to stop searching for errors in places where there aren't chances to find one :))

Reply to this Comment

@Lucian,

Going from MS SQL to MySQL, it took me a while to get in the mindset of CONCAT(). In MS SQL, using the "+" is so easy.

Reply to this Comment

@Ben

Yes, Ben, couldn't they just have used that tiny "+" sign? :))

Oh, another thing!
After using the concat, i kept receiving this error: "No data fetched and-some-other-thing-bla-bla" Said to myself: "Bugger! What could be wrong this time?"

Well, since I am a Delphi developer, at my origins, i always add the quotes when comparing a constant-value string to a variable string/a string-type parameter.
Well (again), it seems that MySQL doesn't need the quotes, it has it's own. Very ashamed of not knowing this, stop "boo"-ing me.

So, finally, the winning syntax was:

select IFNULL(id,0)+1 into nextval from nextids where UPPER(tablename)=UPPER(tbname)

It was right there, in front of me, all the time :)

Nice night to you, Ben, and thanks again.

Lucian

Reply to this Comment

@Lucian,

Sometimes, error just seem to do more harm than good :) They almost act as misdirection.

Reply to this Comment

Similar issue: appear to have a problem with a double, but that is not the cause....

create table test (col1 varchar(10), col2 varchar(10), col3 double);

insert into test values ("a","b",0);
update test set col3=1.2 where col1+col2="a"+"b";

Query OK, 1 row affected, 2 warnings (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'b' |
+---------+------+---------------------------------------+

update test set col3=1.2 where concat(col1,col2)="a"+"b";

Query OK, 0 rows affected, 2 warnings (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

To work without warnings,

update test set col3=1.2 where col1+col2=concat("a","b");

or

update test set col3=1.2 where concat(col1,col2)=concat("a","b");

Reply to this Comment

@Adam,

Not that this is related to the problem at all, but don't you need single quotes when dealing with SQL string values?

Reply to this Comment

@Ben:

No, double quotes work as well. He just has to pay attention to the letter case.

Greetings,
Lucian

Reply to this Comment

@Ben
Weeeeelll ... it did apply to MySQL and SQL Server and Firebird.

Can't be sure about Interbase, though, it's been a long time.

Lucian

Reply to this Comment

Hey mate,

Thanks to Google and your blog I now know why I have been getting this error for the past 3 months. Now I don't have to update fields one by one like a sucker.

Nate
Adelaide, Australia

Reply to this Comment

Commas, not ANDs in MySQL UPDATE statements. Just what I needed - thanks for posting those comments - you rock.

Reply to this Comment

I have created a procedure in mysql to create a dynamic table ,although the procedure is prepared successfully but by a call myp(args);
Its giving an error:Truncated incorrect Double value:'CREATE TABLE'
The code is written below
please help !

delimiter//
Create Procedure myp
(IN t_name VARCHAR(25) )

Begin
declare sqlString VARCHAR(150) ;

declare tableName VARCHAR(30);
set tableName :=t_name;
set sqlString :='CREATE TABLE '|| tableName || '(order_id VARCHAR NOT NULL, item_id VARCHAR NOT NULL)';

End;
//

Reply to this Comment

Thanks Ben,
I had the same problem. And thanks to you, you saved me from wasting an hour.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.