'[jboss-user] [jBPM] - Re: JBPM4 Migrating from Oracle to Postgres,'

[prev in list] [next in list] [prev in thread] [next in thread] 

List: jboss-user
Subject: [jboss-user] [jBPM] - Re: JBPM4 Migrating from Oracle to Postgres,
From: Marco Rietveld <do-not-reply () jboss ! com>
Date: 2011-09-30 15:36:40
Message-ID: 2-629406-3-91984-1317318522476-2-629596-3-85327-1317396986225.jivesbs.jivemailuser () http://community ! jboss ! org
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]

Marco Rietveld [http://community.jboss.org/people/marco.rietveld] created the \
discussion

"Re: JBPM4 Migrating from Oracle to Postgres,  Transaction Error on JBPM4_LOB"

To view the discussion, visit: http://community.jboss.org/message/629596#629596

--------------------------------------------------------------
I have bad news, Jeff..  :( 
(Well, sort of.. there may be hope, see the end of the message). 

The problem you're running into with jbpm 4 is that a database operation is being \
attempted with an object that, indeed, has a @LOB field -- which Postgresql handles \
with it's super cool (yay.. :/ ) "Large Object" facility[1]. 

See  http://jdbc.postgresql.org/documentation/84/binary-data.html \
http://jdbc.postgresql.org/documentation/84/binary-data.html

The important sentence on that page is the following: 
You must access Large Objects within an SQL transaction block. You can start a \
transaction block by calling setAutoCommit(false). 

And somewhere deep in the jbpm 4 code, that's exactly what's +not+ happening: jbpm 4 \
is creating, modifying or accessing an entity with a LOB field "outside" of a \
transaction. The code doesn't even have to modify the object/entity containing a LOB: \
even +retrieving+ an entity with a LOB field   will cause this problem.

The reason I happen to know about this is because I've been looking at this issue in \
jbpm *+5+* (with PostgreSQL, which is the only DB that has this)[2]. In jBPM 5, we're \
close to fixing the code so that it works. It's actually not that hard, I just have \
to be careful I don't break anything else while fixing it.  ;)    :/

Short of modifying the code in jBPM 4 yourself (it +is+ open source!), I don't think \
there's a solution for this. 

Wait, I take that back -- one of the possible solutions I looked into was modifying \
the mapping of the objects in question so that the LOB was stored as a byte array and \
not using the "Large Object" facility[1]. To tell the truth, though, I unfortunately \
just deleted the (git) stash and branch containing that stuff this week, after having \
not looked at it for a month. In short, I'm only about 85% sure that what I'm \
suggesting is possible -- and can't remember all the details of the solution.   

[Oh yeah, now I remember -- the byte [] field in PostgreSQL is limited (wrt to size), \
and while the limit is pretty high, because of.. how jBPM 5 is put together, it \
wasn't the better solution. I don't think that jBPM 4 will have as large @Lob/byte [] \
fields, but I would check the maximum size of the byte [] fields in your Oracle jbpm \
4 database. If the max size of the @Lob/byte [] field in the Oracle db is larger than \
the max allowable size of the byte [] field in PostgreSQL, then at least you have a \
good reason that upper management will understand when you tell them why you can't \
migrate the system.  :)  ]

I'm not at all unfamilar with jbpm 4, but the trick is the following: 
* You need to have access (be able to modify) the persistence.xml in order to do \
this. * If necessary, you can replace the persistence.xml in the appropriate jbpm 4 \
jar. 

* You can +override+ the mappings that are present in the classes: * You will have to \
                go through all the jbpm 4 code and find which entities these are. 
* Then you will have write entity-mapping xml files for these entities. * Make sure \
to include the xml-mapping-metadata-complete element in order to disable the anno's \
                (including @Lob) in the java files. 
* This make take some trial an error: I use eclipse's debugging capabilities with a \
                good unit test for this type of thing. 
* There are probably some other descriptions of how to do this, but see Pro JPA 2, \
pp. 373-375 for the overriding anno's and pp. 385-398 for some basic entity-mapping \
xml info. * The order of elements is (bizarrely) important: make sure to verify your \
mapping (and the order of your xml elements) agains orm_1_0.xsd (or whichever schema \
you're using.)

* The mapping files you write for each entity must be referred to in your \
persistence.xml and if necessary, inserted in the appropriate jbpm 4 jar (see first \
bullet). 

Good luck, 
Marco

[1] The Large Object facility stores an id in the actual field of the entity, which \
then refers to a row/record in an +another+ table where the actual byte data is \
stored -- hence the need for transactions since multiple tables are involved. 

[2] Yes, the "Large Object" facility is unique -- unique like the way we made jokes \
about "special" people in middle school. ;D That said, there are actually worse \
databases out there with regards to (XA) transactions -- worse +commercial+ \
                databases, that make me Sy.. , I mean sigh. /:)
--------------------------------------------------------------

Reply to this message by going to Community
[http://community.jboss.org/message/629596#629596]

Start a new discussion in jBPM at Community
[http://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[Attachment #5 (text/html)]

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<body link="#355491" alink="#4262a1" vlink="#355491" style="background: #e2e2e2; \
margin: 0; padding: 20px;">

<div>
	<table cellpadding="0" bgcolor="#FFFFFF" border="0" cellspacing="0" style="border: \
1px solid #dadada; margin-bottom: 30px; width: 100%; -moz-border-radius: 6px; \
-webkit-border-radius: 6px;">  <tbody>
			<tr>

				<td>

					<table border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" \
style="border: solid 2px #ccc; background: #dadada; width: 100%; -moz-border-radius: \
6px; -webkit-border-radius: 6px;">  <tbody>
							<tr>
								<td bgcolor="#000000" valign="middle" height="58px" style="border-bottom: 1px \
solid #ccc; padding: 20px; -moz-border-radius-topleft: 3px; \
-moz-border-radius-topright: 3px; -webkit-border-top-right-radius: 5px; \
                -webkit-border-top-left-radius: 5px;">
									<h1 style="color: #333333; font: bold 22px Arial, Helvetica, sans-serif; \
                margin: 0; display: block !important;">
									<!-- To have a header image/logo replace the name below with your img tag \
                -->
									<!-- Email clients will render the images when the message is read so any \
                image -->
									<!-- must be made available on a public server, so that all recipients can \
                load the image. -->
									<a href="http://community.jboss.org/index.jspa" style="text-decoration: \
none; color: #E1E1E1">JBoss Community</a></h1>  </td>

							</tr>
							<tr>
								<td bgcolor="#FFFFFF" style="font: normal 12px Arial, Helvetica, sans-serif; \
color:#333333; padding: 20px;  -moz-border-radius-bottomleft: 4px; \
-moz-border-radius-bottomright: 4px; -webkit-border-bottom-right-radius: 5px; \
-webkit-border-bottom-left-radius: 5px;"><h3 style="margin: 10px 0 5px; font-size: \
17px; font-weight: normal;">  Re: JBPM4 Migrating from Oracle to Postgres,  \
Transaction Error on JBPM4_LOB </h3>
<span style="margin-bottom: 10px;">
    created by <a href="http://community.jboss.org/people/marco.rietveld">Marco \
Rietveld</a> in <i>jBPM</i> - <a \
href="http://community.jboss.org/message/629596#629596">View the full discussion</a> \
</span> <hr style="margin: 20px 0; border: none; background-color: #dadada; height: \
1px;">

<div class="jive-rendered-content">I have bad news, Jeff.. :( \
(Well, sort of.. there may be hope, see the end of the message). &#160;The problem you're \
running into with jbpm 4 is that a database operation is being attempted with an \
object that, indeed, has a @LOB field -- which Postgresql handles with it's super \
cool (yay.. :/ ) "Large Object" facility[1]. &#160;See <a class="jive-link-external-small" \
href="http://jdbc.postgresql.org/documentation/84/binary-data.html" \
target="_blank">http://jdbc.postgresql.org/documentation/84/binary-data.html</a>&#160;The important \
sentence on that page is the following: <pre>You must access Large Objects within \
an SQL transaction block. You can start a transaction block by calling \
setAutoCommit(false). </pre>&#160;And somewhere deep in the jbpm 4 code, that's exactly what's \
not happening: jbpm 4 is creating, modifying or accessing an entity with a \
LOB field "outside" of a transaction. The code doesn't even have to modify the \
object/entity containing a LOB: even retrieving an entity with a LOB \
field&#160; will cause this problem.&#160;The reason I happen to know about this is because I've \
been looking at this issue in jbpm 5 (with PostgreSQL, \
which is the only DB that has this)[2]. In jBPM 5, we're close to fixing the code so \
that it works. It's actually not that hard, I just have to be careful I don't break \
anything else while fixing it. ;) &#160; :/&#160;Short of modifying the code in jBPM 4 \
yourself (it is open source!), I don't think there's a solution for this. \
&#160;Wait, I take \
that back -- one of the possible solutions I looked into was modifying the mapping of \
the objects in question so that the LOB was stored as a byte array and not using the \
"Large Object" facility[1]. To tell the truth, though, I unfortunately just deleted \
the (git) stash and branch containing that stuff this week, after having not looked \
at it for a month. In short, I'm only about 85% sure that what I'm suggesting is \
possible -- and can't remember all the details of the solution.&#160; &#160;[Oh yeah, now I \
remember -- the byte [] field in PostgreSQL is limited (wrt to size), and while the \
limit is pretty high, because of.. how jBPM 5 is put together, it wasn't the better \
solution. I don't think that jBPM 4 will have as large @Lob/byte [] fields, but I \
would check the maximum size of the byte [] fields in your Oracle jbpm 4 database. If \
the max size of the @Lob/byte [] field in the Oracle db is larger than the max \
allowable size of the byte [] field in PostgreSQL, then at least you have a good \
reason that upper management will understand when you tell them why you can't migrate \
the system. :) ]&#160;I'm not at all unfamilar with jbpm 4, but the trick is the \
following: <ul><li style="text-align: start;">You need to have access (be able to \
modify) the persistence.xml in order to do this. <ul><li style="text-align: \
start;">If necessary, you can replace the persistence.xml in the appropriate jbpm 4 \
jar. </li></ul></li><li style="text-align: start;">You can override the \
mappings that are present in the classes: <ul><li style="text-align: start;">You will \
have to go through all the jbpm 4 code and find which entities these are. </li><li \
style="text-align: start;">Then you will have write entity-mapping xml files for \
these entities. <ul><li style="text-align: start;">Make sure to include the \
xml-mapping-metadata-complete element in order to disable the anno's (including @Lob) \
in the java files. </li><li style="text-align: start;">This make take some trial an \
error: I use eclipse's debugging capabilities with a good unit test for this type of \
thing. </li><li style="text-align: start;">There are probably some other descriptions \
of how to do this, but see Pro JPA 2, pp. 373-375 for the overriding anno's and pp. \
385-398 for some basic entity-mapping xml info. <ul><li style="text-align: \
start;">The order of elements is (bizarrely) important: make sure to verify your \
mapping (and the order of your xml elements) agains orm_1_0.xsd (or whichever schema \
you're using.)</li></ul></li></ul></li></ul></li><li style="text-align: start;">The \
mapping files you write for each entity must be referred to in your persistence.xml \
and if necessary, inserted in the appropriate jbpm 4 jar (see first bullet). \
</li></ul>&#160;&#160;Good luck, \
Marco&#160;[1] The Large Object facility stores an id in the actual field of \
the entity, which then refers to a row/record in an another table where the \
actual byte data is stored -- hence the need for transactions since multiple tables \
are involved. &#160;[2] Yes, the "Large Object" facility is unique -- unique like the \
way we made jokes about "special" people in middle school. ;D That said, there are \
actually worse databases out there with regards to (XA) transactions -- worse \
commercial databases, that make me Sy.. , I mean sigh. /:) </div>

<div style="background-color: #f4f4f4; padding: 10px; margin-top: 20px;">
    <p style="margin: 0;">Reply to this message by <a \
href="http://community.jboss.org/message/629596#629596">going to Community</a></p>  \
<p style="margin: 0;">Start a new discussion in jBPM at <a \
href="http://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034">Community</a></p>
 </div></td>
                        </tr>
                    </tbody>
                </table>

                </td>
            </tr>
        </tbody>
    </table>

</div>

</body>
</html>

_______________________________________________
jboss-user mailing list
jboss-user@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/jboss-user

[prev in list] [next in list] [prev in thread] [next in thread] 