[OOZIE-1907] DB upgrade from 3.3.0 to trunk fails on derby

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: trunk
• Fix Version/s: 4.1.0
• Assignee: Robert Kanter
• Reporter: Robert Kanter
• Created: 27/Jun/14 23:38
• Updated: 09/Dec/14 00:45
• Resolved: 30/Jun/14 16:29

Description

In OOZIE-1674, we saw that there were a few problems upgrading the database from DB version 1 (Oozie 3) to DB version 3 (trunk) with Postgres. It looks like a problem similar to the second problem described in this comment on OOZIE-1674 also applies to Derby when the database has content in it. I must have previously tested this with an empty database, because that doesn't run into the problem.

Here's the error you get:

...
Update db.version in OOZIE_SYS table to 3
DONE
Converting clob columns to blob for all tables

Error: An attempt was made to get a data value of type 'java.sql.Clob' from a data value of type 'BLOB'.

Stack trace for the error was (for debug purposes):
--------------------------------------
java.sql.SQLDataException: An attempt was made to get a data value of type 'java.sql.Clob' from a data value of type 'BLOB'.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedResultSet.dataTypeConversion(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedResultSet.getClob(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedResultSet.getClob(Unknown Source)
	at org.apache.oozie.tools.OozieDBCLI.convertClobToBlobinDerby(OozieDBCLI.java:616)
	at org.apache.oozie.tools.OozieDBCLI.ddlTweaksFor50(OozieDBCLI.java:679)
	at org.apache.oozie.tools.OozieDBCLI.upgradeDBto50(OozieDBCLI.java:239)
	at org.apache.oozie.tools.OozieDBCLI.upgradeDB(OozieDBCLI.java:219)
	at org.apache.oozie.tools.OozieDBCLI.run(OozieDBCLI.java:128)
	at org.apache.oozie.tools.OozieDBCLI.main(OozieDBCLI.java:76)
Caused by: java.sql.SQLException: An attempt was made to get a data value of type 'java.sql.Clob' from a data value of type 'BLOB'.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
	... 15 more
--------------------------------------

This happens due to the push_missing_dependencies column already being a BLOB when the update tool is expecting it to be a CLOB. When you upgrade from Oozie 3 (DB version 1) to Oozie 4 (DB version 2), the Oozie 4 db tool creates the column as a CLOB; then when you upgrade to trunk (DB version 3), the trunk db tool converts it to a BLOB. However, when you upgrade directly from Oozie 3 (DB version 1) to trunk (DB version 3), the trunk db tool runs through both upgrade procedures but during the "Oozie 4 (DB version 2)" upgrade, the column is created as a BLOB already.

We can fix this in the same way we fixed in it OOZIE-1674.

Issue Links

• is broken by: OOZIE-1548 OozieDBCLI changes to convert clob to blob and remove the discriminator column
• is related to: OOZIE-1674 DB upgrade from 3.3.0 to trunk fails on postgres
• is related to: OOZIE-1684 DB upgrade from 3.3.0 to trunk fails on Oracle