One Star

[resolved] [RESOLVED] - Data tuncatin: Out f ang au

Hello I don't understand, I have this message :
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column 'nb_entrees' at row 1
But it arrives after 54700 lines loaded (on a theoric 95000)...
This job was working good yesterday... Is it possible to identify the input line which could provoque this error ?
this input data is a double 17,17 and the output is like that too...
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

Re: [resolved] [RESOLVED] - Data tuncatin: Out f ang au

OH
MY
GOD
!!!!
the problem that I had defined the double (17,17)... that mean 17 digits and 17 décimals so it rests 0 for the interger!!!!!!
with double(24;12) everuthing is OK
I was getting really crazy... Now I'm crying... I've destroyed 17523 walls with my head just for that... I'm stupid!
Sorry!
11 REPLIES
One Star

Re: [resolved] [RESOLVED] - Data tuncatin: Out f ang au

Incredible, today the job is OK, no error... what could have provoque the error ?
One Star

Re: [resolved] [RESOLVED] - Data tuncatin: Out f ang au

hi guy
u can increase datatype SIZE of this COLUMN "nb_entrees" .
and try to execute........

Regards
One Star

Re: [resolved] [RESOLVED] - Data tuncatin: Out f ang au

ever tried...
I have again this message in another job... I really don't understand why ?
In fact, this time, 125000 lines are loaded correctly but each time the job stops with this error :
Data truncation: Out of range value adjusted for column 'nb_entrees' at row 1
'nb_entrees' is defined as a double unsigned in the mysql table and I've checked, the maximum of 'nb_entrees' is 4873563, so I don't know why there is this problem ??
If I skip 'nb_entrees' for the export, I still have an error on another double unsigned...
But something is sure, the firsts 125000 lines are loaded correctly!! so why not the others ?
One Star

Re: [resolved] [RESOLVED] - Data tuncatin: Out f ang au

Hello everybody,
2 NEws :
1 - If I export everything as string, it's OK... but that's not what I want to do. :-)
2 - It seems that the problem regard the schéma I've defined for the tmysqlinput. I've defined all my 'double' as 'double unsigned' and I've seen that the double unsigned don't accept the negatives values!!!
OK, so I decide to set up all my double like that :
type : Double
Type DB : DOUBLE
précision : 17 (I've checkek with a len method and the max caractere I've seen for my doubles is 9!!)
longueur : 17
That should be OK but I still have an error at the first line on the first colonne double : 'com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column...'
Even if the table is created well and the double correctly set up (double(17,17))
So I really don't know what I must do? I become crazy for that! all the job is OK, I've test on exporting in excel file too and it was OK...
The problem is only on these double in Mysql...
Who can help me ? HELP!!! 8O
Community Manager

Re: [resolved] [RESOLVED] - Data tuncatin: Out f ang au

Hello guy
What's data type of nb_entrees field in real table? How do you define it?
Link a reject flow from tMysqlOuput to tLogRow to see the rejects rows.

Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Seventeen Stars

Re: [resolved] [RESOLVED] - Data tuncatin: Out f ang au

hi,
try those topics on mysql forum :
verifiy quotes
jdbcCompliantTruncation
it could be help you ... hope Smiley Happy
regards
laurent
One Star

Re: [resolved] [RESOLVED] - Data tuncatin: Out f ang au

Hello shong,
What's data type of nb_entrees field in real table? How do you define it?
-> the data source is a "double unsigned" even if when I guest schéma, talend define it as "string" (why, I don't know) but using tConverttypes I can use it as double...
In fact in all my Job I have always specify the double as double unsigned to write the data in mysql, each time I've tried to set ip up like that :
type : Double
Type DB : DOUBLE
précision : 17
longueur : 17
I've always had the error : 'com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column...'
I'm really getting crazy...
One Star

Re: [resolved] [RESOLVED] - Data tuncatin: Out f ang au

other precision.
I did a test : the same export but in a postgres database, and it's OK with the same double! So only with Mysql I have problems with the double"
One Star

Re: [resolved] [RESOLVED] - Data tuncatin: Out f ang au

thank you kzone for your links, there are insteresting and I've thought this would have solved my problem... But it didn't.
I've set up my mysql connection in talend with this option : jdbcCompliantTruncation=false
-> but I still have 'com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column...'
I've also tired to erase 'STRICT_TRANS_TABLES' and/or 'STRICT_ALL_TABLES' from the SQL type of 'sql_mode' in the table "proc" of "mysql database"...
-> but I still have 'com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column...'
I've also tried to replace the mysql connector driver in talend, I tried the 3 MySQL Connector/J purposed here : http://downloads.mysql.com/snapshots.php#connector-j
I really don't know what to do... and I'm getting more nad more crazy...
One Star

Re: [resolved] [RESOLVED] - Data tuncatin: Out f ang au

OH
MY
GOD
!!!!
the problem that I had defined the double (17,17)... that mean 17 digits and 17 décimals so it rests 0 for the interger!!!!!!
with double(24;12) everuthing is OK
I was getting really crazy... Now I'm crying... I've destroyed 17523 walls with my head just for that... I'm stupid!
Sorry!
Community Manager

Re: [resolved] [RESOLVED] - Data tuncatin: Out f ang au

I was getting really crazy... Now I'm crying... I've destroyed 17523 walls with my head just for that... I'm stupid!

:lol: :mad: :rolleyes:
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business