Data Truncation Errors
Submitted by Guest on Thu, 21/03/2013 - 11:25
NOTE LOADER Linker.0 - Failed to process row N86075: Cannot resolve reference: <act.patientClinicalNote>N86075. Referenced object not mapped due to errors: Data truncation: Data too long for column 'value' at row 1
The above error is generated on both the History Note Loader and the subsequent Load linker just references the loader error...
Does Data too long for column value at ROW 1 mean the first row of my loader in this case the date or startTime for the clinicalNote.
I checked the data for this row and it was set to a date object 2013/07/02
Any ideas ?
the note field was quite long...but I doubt it exceeded the limit..
Re: Data Truncation Errors
This is another error Im now getting
What start_Time is it referring to...none of the archetypes present use that entry.
You can see the loader details above ...the table load steps actually loads the data out of a working version.
Using
MYSQL 5.6
MYSQL connector 3.1 (the latest connecter generates application context errors with Kettle)
PDI 3.2
WinXP
Java 1.7
Re: Data Truncation Errors
The first error:
is probably due to the note field being too long for the value column of act_details which is limited to 5000 characters.
The errors relating to start_time are referring to the start_time column in the product_prices table. They can be null, so if you get an empty string, replace with null.
With regards to the MySQL connector, Kettle ships with ./libext/JDBC/mysql-connector-java-3.1.14-bin.jar
I haven't had any problems with it. If you want to run a different version, you'll need to remove this JAR first.
-Tim
Re: Data Truncation Errors
The problem is I wasnt setting any value for the start_Time. The loader didnt specify the field at all..
I assumed it was using the default java.util.date.new() (or whatever the default java expression is.
Re: Data Truncation Errors
I am not exactly sure whats going on here, but here are some cut and pastes from various sources(linked)
http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html
As of MySQL 5.6.5,
TIMESTAMP
andDATETIME
columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only forTIMESTAMP
, and for at most oneTIMESTAMP
column per table. The following notes first describe automatic initialization and updating for MySQL 5.6.5 and up, then the differences for versions preceding 5.6.5.....
TIMESTAMP
andDATETIME
columns have no automatic properties unless they are specified explicitly, with this exception: By default, the firstTIMESTAMP
column has bothDEFAULT CURRENT_TIMESTAMP
andON UPDATE CURRENT_TIMESTAMP
if neither is specified explicitly. To suppress automatic properties for the firstTIMESTAMP
column, do either of the following:Define the column with a
DEFAULT
clause that specifies a constant default value.Specify the
NULL
attribute. This also causes the column to permitNULL
values, which means that you cannot assign the current timestamp by setting the column toNULL
. AssigningNULL
sets the column toNULL
http://stackoverflow.com/questions/9009329/why-not-use-java-util-date-in...
Okay, so having read all the information throughout the answers, and the others posts pointed to in comments and so forth, I've decided to summarize what I learned:
The Setup:
From what I can see, there are three layers
The First Reason
Many JDBC class wrappers, such as Spring's famous
SimpleJdbcTemplate
, allowed you give it aMap<String, Object>
as the argument map when executing a SQL statement. This is wonderfully simple, as it hands all the conversions from the objects to the properjava.sql.*
types when it uses raw JDBC under the hood. The first problem is here: what happens if you have the following:What does Spring convert it to? A
java.sql.Date
? Ajava.sql.Timestamp
? Ajava.sql.Time
? Or does it even cast it to ajava.lang.Object
? As well explained by @BalusC in this answer to a different question and by another fellow here, there are big differences between those threejava.sql
types. So, that's the first reason not to usejava.util.Date
: you can't rely on the internal convention of a framework to handle the conversion for you.The Second Reason
Now, talking about raw JDBC calls, @The Nail explained that you need these
java.sql
types to make JDBC calls, and he's absolutely right, which was news to me. However, there is still the dreadedsetObject
call. From reading the JavaDoc for that call, it seems a little ambiguous as to what it will do if give ajava.util.Date
. So, the second reason not to use it is because of the ambiguity there.The Third Reason
Finally, talking about the level of the driver. I can attest with personal experience that sometimes Spring in conjunction with the Oracle driver does work with
java.util.Date
. Sometimes. And then sometimes it doesn't. So, because I have no clue how any particular version of any particular driver will handle ajava.util.Date
, it's best to be explicit. This is the third reason.Conclusion
In general, it looks like the reason is: "JDBC is not supposed to be used with
java.util.Date
. If you do, you cannot be sure what will happen." This is a good enough reason for me :)Re: Data Truncation Errors
From the above I am starting to think that I may need to revert my mysql install back to 5.1...
So I reinstalled 5.1 and then ran the exact same transform with no changes and it ran normally
it will need confirmation but I dont think our loader/kettle is compatible with MySQL 5.6 +
I cant be sure about OPENVPMS because I am yet to get it working on 5.6 with all the data loaded.
Re: Data Truncation Errors
The product_prices table uses DATETIME columns for the start_time and end_time columns, and both have DEFAULT NULL constraints.
Did you try replacing the MySQL JDBC connector ./libext/JDBC/mysql-connector-java-3.1.14-bin.jar with the most recent version from http://dev.mysql.com/downloads/connector/j/5.1.html ?
-Tim
Re: Data Truncation Errors
I tried but the OpenVpms Loader plugin failed almost straight away with application context errors
Re: Data Truncation Errors
Can you send me the log?