2

I have a MySQL table with three fields:

fname   varchar(20) 
ftype   varchar(10) 
value   varchar(30)

as you can see "value" is a varchar, but I use it to store differente kind of value (entered by users), so I can do:

UPDATE table SET value = 1 + value WHERE ftype='COUNTER';

it works as intended, doing an implicit conversion and then update and store, so:

 fname  ftype   value   
 f1     COUNTER     100
 f2     COUNTER     100 dǵ#$vferv  <-- this value contains an input error

become:

 fname  ftype   value
 f1     COUNTER     101
 f2     COUNTER     101

I'm perfectly fine with it. The point is that if I execute the query directly, it works (and eventually MySQL return some warnings if I run show warnings), but if I run in inside a Java EE app (running under glassfish) those warnings are treated like errors so I'll get an exception (ie: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '100 dǵ#$vferv').

Is there any option in the JDBC driver to tell it not throw exception in this case?

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
moretti.fabio
  • 1,128
  • 13
  • 32
  • Did you try rewriting your query to handle the data type conversions properly? `UPDATE table SET value = CAST(1 + CAST(value as CHAR(30)) as CHAR(30)) WHERE ftype='COUNTER';` – Sarath Chandra Jan 12 '18 at 17:57
  • The warning is generated during the implicit conversion from varchar to unsigned done by the sum operator, I don't see how casting varchar to char before the implicit conversion can solve the problem. – moretti.fabio Jan 12 '18 at 18:09

1 Answers1

1

The answer for your question is here : https://bugs.mysql.com/bug.php?id=24546

Basically, add "jdbcCompliantTruncation=false" as a URL configuration parameter

orujoman
  • 11
  • 2