[
https://issues.apache.org/jira/browse/OOZIE-1674?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13878240#comment-13878240
]
Robert Kanter commented on OOZIE-1674:
--------------------------------------
Nevermind my last comment; Oozie 4 to trunk should be fine.
----
I'm seeing multiple issues here:
1)
The offending line:
{code:java}
String updateQuery = "update " + tableName + " set " + TEMP_COLUMN_PREFIX +
column + "=(decode(replace("
+ column + ", '\\', '\\\\'), 'escape'))";
{code}
should be this:
{code:java}
String updateQuery = "update " + tableName + " set " + TEMP_COLUMN_PREFIX +
column + "=(decode(replace("
+ column + ", E'\\\\', E'\\\\\\\\'), 'escape'))";
{code}
2)
When going directly from Oozie 3 (DB version 1) to trunk (DB version 3) it
first does the upgrade to version 2 and then that to version 3. However,
columns that were added in version 2 but changed in version 3 actually get
added as if they were added in version 3 instead of in version 2. For example,
we add a TEXT column named {{push_missing_dependencies}} to the
{{coord_actions}} table in version 2. We then convert it to a BYTEA column in
version 3 with some functions (it doesn't directly cast). This works fine if
you use Oozie 3 to create the database, then use Oozie 4, and then use trunk.
But if you use Oozie 3 and then trunk, you run into the below error because the
column type for {{push_missing_dependencies}} is incorrectly already BYTEA
instead of TEXT.
{noformat}
org.postgresql.util.PSQLException: ERROR: function replace(bytea, unknown,
unknown) does not exist
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Position: 65
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302)
at
org.apache.oozie.tools.OozieDBCLI.convertClobToBlobInPostgres(OozieDBCLI.java:533)
at org.apache.oozie.tools.OozieDBCLI.ddlTweaksFor50(OozieDBCLI.java:621)
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)
{noformat}
And this is because when creating the new {{push_missing_dependencies}} column
for version 2 in Oozie 4, we have:
{code:java}
@Column(name = "missing_dependencies")
@Lob
private String missingDependencies;
{code}
but in trunk, we have:
{code:java}
@Basic
@Column(name = "push_missing_dependencies")
@Lob
@Strategy("org.apache.oozie.executor.jpa.StringBlobValueHandler")
private StringBlob pushMissingDependencies;
{code}
This causes OpenJPA to create the columns with different types during the
version 2 database process:
{noformat}
oozie=# \d coord_actions;
Table "public.coord_actions"
Column | Type | Modifiers
---------------------------+-----------------------------+-----------
...
push_missing_dependencies | text |
{noformat}
{noformat}
oozie=# \d coord_actions;
Table "public.coord_actions"
Column | Type | Modifiers
---------------------------+-----------------------------+-----------
...
push_missing_dependencies | bytea |
{noformat}
I'll look at this some more soon, but I think we're going to have to add some
special cases for columns that are in similar situations.
[~virag], according to https://reviews.apache.org/r/14220/ it looks like you
only tested Oozie 3 to trunk on derby, is that correct? If so, there may be
similar problems with the other databases?
> DB upgrade from 3.3.0 to trunk fails on postgres
> ------------------------------------------------
>
> Key: OOZIE-1674
> URL: https://issues.apache.org/jira/browse/OOZIE-1674
> Project: Oozie
> Issue Type: Bug
> Affects Versions: trunk
> Reporter: Robert Kanter
> Assignee: Robert Kanter
> Priority: Critical
>
> Using a Postgres database created by Oozie 3.3.0 (DB version 1) and upgrading
> it to the current trunk (DB version 3) fails, even with no jobs in it.
> Creating the 3.3.0 database:
> {noformat}
> [oozie@localhost ~]$ oozie-3.3.0/bin/ooziedb.sh create -run
> Validate DB Connection
> DONE
> Check DB schema does not exist
> DONE
> Check OOZIE_SYS table does not exist
> DONE
> Create SQL schema
> DONE
> Create OOZIE_SYS table
> DONE
> Oozie DB has been created for Oozie version '3.3.0'
> The SQL commands have been written to: /tmp/ooziedb-4337942956770953237.sql
> {noformat}
> Upgrading to current trunk:
> {noformat}
> [oozie@localhost ~]$ oozie-4.1.0-SNAPSHOT/bin/ooziedb.sh upgrade -run
> setting CATALINA_OPTS="$CATALINA_OPTS -Xmx1024m"
> Validate DB Connection
> DONE
> Check DB schema exists
> DONE
> Verify there are not active Workflow Jobs
> DONE
> Check OOZIE_SYS table does not exist
> DONE
> Get Oozie DB version
> DONE
> Upgrade SQL schema
> DONE
> Upgrading to db schema for Oozie 4.0
> Update db.version in OOZIE_SYS table to 2
> DONE
> Post-upgrade COORD_JOBS new columns default values
> DONE
> Post-upgrade COORD_JOBS & COORD_ACTIONS status values
> DONE
> Post-upgrade MISSING_DEPENDENCIES column
> DONE
> Table 'WF_ACTIONS' column 'execution_path', length changed to 1024
> Table 'WF_ACTIONS, column 'error_message', changed to varchar/varchar2
> Table 'COORD_JOB' column 'frequency' changed to varchar/varchar2
> DONE
> Post-upgrade BUNDLE_JOBS, COORD_JOBS, WF_JOBS to drop AUTH_TOKEN column
> DONE
> Get Oozie DB version
> DONE
> Upgrading to db schema for Oozie 4.1.0-SNAPSHOT
> Update db.version in OOZIE_SYS table to 3
> DONE
> Converting text columns to bytea for all tables
> Error: ERROR: syntax error at or near "\"
> Position: 61
> Stack trace for the error was (for debug purposes):
> --------------------------------------
> org.postgresql.util.PSQLException: ERROR: syntax error at or near "\"
> Position: 61
> at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302)
> at
> org.apache.oozie.tools.OozieDBCLI.convertClobToBlobInPostgres(OozieDBCLI.java:533)
> at org.apache.oozie.tools.OozieDBCLI.ddlTweaksFor50(OozieDBCLI.java:621)
> 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)
> --------------------------------------
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.1.5#6160)
