Data Truncation Errors

 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..

 

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Data Truncation Errors

This is another error Im now getting

 2013/03/21 15:59:32 - General - Initialising application context, using database URL: jdbc:mysql://10.1.3.50:3306/openvpms
2013/03/21 15:59:59 - General - Application context initialised
2013/03/21 15:59:59 - Load Price Templates - This transformation can be replayed with replay date: 2013/03/21 15:59:59
2013/03/21 15:59:59 - Load Templates.0 - Starting...
2013/03/21 15:59:59 - Load Templates from Kalinga.0 - Finished reading query, closing connection.
2013/03/21 15:59:59 - Load Templates from Kalinga.0 - Finished processing (I=6, O=0, R=0, W=6, U=0, E=0)
2013/03/21 15:59:59 - Price Template Tx.0 - Finished processing (I=0, O=7, R=6, W=6, U=0, E=0)
2013/03/21 16:00:00 - Load Templates.0 - Failed to process: Data truncation: Incorrect datetime value: '' for column 'start_time' at row 1
2013/03/21 16:00:00 - Load Templates.0 - Failed to process row 90: Data truncation: Incorrect datetime value: '' for column 'start_time' at row 1
2013/03/21 16:00:00 - Load Templates.0 - Failed to process row 5682: Data truncation: Incorrect datetime value: '' for column 'start_time' at row 1
2013/03/21 16:00:00 - Load Templates.0 - Failed to process row 6453: Data truncation: Incorrect datetime value: '' for column 'start_time' at row 1
2013/03/21 16:00:00 - Load Templates.0 - Failed to process row 6584: Data truncation: Incorrect datetime value: '' for column 'start_time' at row 1
2013/03/21 16:00:00 - Load Templates.0 - Processed 6 rows in 1.0 seconds (   6.0 rows/sec), generating 12 objects (12.0 objects/sec)
2013/03/21 16:00:00 - Spoon - The transformation has finished!! 

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:

NOTE LOADER Linker.0 - Failed to process row N86075: Cannot resolve reference: <act.patientClinicalNote>N86075

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 and DATETIME 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 for TIMESTAMP, and for at most one TIMESTAMP 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 and DATETIME columns have no automatic properties unless they are specified explicitly, with this exception: By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly. To suppress automatic properties for the first TIMESTAMP 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 permit NULL values, which means that you cannot assign the current timestamp by setting the column to NULL. Assigning NULL sets the column to NULL

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

JDBC wrapper calls (e.g. Spring's SimpleJdbcTemplate)
|
|
Raw JDBC calls (e.g. PreparedStatement)
|
|
JDBC driver (e.g. Oracle)						

The First Reason

Many JDBC class wrappers, such as Spring's famous SimpleJdbcTemplate, allowed you give it a Map<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 proper java.sql.*types when it uses raw JDBC under the hood. The first problem is here: what happens if you have the following:

Map<String, Object> paramMap = new HashMap<String,Object>();
paramMap.put("p_some_data", new java.util.Date());						

What does Spring convert it to? A java.sql.Date? A java.sql.Timestamp? A java.sql.Time? Or does it even cast it to a java.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 three java.sqltypes. So, that's the first reason not to use java.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.sqltypes to make JDBC calls, and he's absolutely right, which was news to me. However, there is still the dreaded setObjectcall. From reading the JavaDoc for that call, it seems a little ambiguous as to what it will do if give a java.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 a java.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?

Syndicate content