[AMBARI-21363] ORA-00911 error during Ambari server schema upgrade due to incorrect syntax of Update statement

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 2.5.2
• Fix Version/s: 2.5.2
• Component/s: ambari-server
• Labels: regression upgrade
• Assignee: Dmytro Grinenko
• Reporter: Vivek Sharma
• Created: 28/Jun/17 12:04
• Updated: 29/Jun/17 12:54
• Resolved: 29/Jun/17 12:18

Description

Looks due to the fix of AMBARI-21291

STR

1. Deployed cluster with Ambari version: 2.2.2.0 and HDP version: 2.4.2.0-258 (Oracle DB)
2. Upgrade Ambari to target Version: 2.5.2.0-77 | Hash: 3ebf403f9d5065bfc83fae5d699bc4a88f67d179

Result

28 Jun 2017 04:42:23,733  INFO [main] DBAccessorImpl:848 - Executing query: ALTER TABLE request ADD cluster_host_info BLOB NULL
28 Jun 2017 04:42:23,763  INFO [main] DBAccessorImpl:878 - Executing prepared query: UPDATE REQUEST SET CLUSTER_HOST_INFO=? WHERE CLUSTER_HOST_INFO IS NULL;
28 Jun 2017 04:42:23,770 ERROR [main] DBAccessorImpl:895 - Error executing prepared query: UPDATE REQUEST SET CLUSTER_HOST_INFO=? WHERE CLUSTER_HOST_INFO IS NULL;
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
        at org.apache.ambari.server.orm.DBAccessorImpl.executePreparedQuery(DBAccessorImpl.java:892)
        at org.apache.ambari.server.orm.DBAccessorImpl.executePreparedUpdate(DBAccessorImpl.java:911)
        at org.apache.ambari.server.orm.DBAccessorImpl.moveColumnToAnotherTable(DBAccessorImpl.java:1428)
        at org.apache.ambari.server.upgrade.UpgradeCatalog251.moveClusterHostColumnFromStageToRequest(UpgradeCatalog251.java:131)
        at org.apache.ambari.server.upgrade.UpgradeCatalog251.executeDDLUpdates(UpgradeCatalog251.java:90)
        at org.apache.ambari.server.upgrade.AbstractUpgradeCatalog.upgradeSchema(AbstractUpgradeCatalog.java:925)
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:208)
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:426)
28 Jun 2017 04:42:23,773 ERROR [main] SchemaUpgradeHelper:210 - Upgrade failed.
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
        at org.apache.ambari.server.orm.DBAccessorImpl.executePreparedQuery(DBAccessorImpl.java:892)
        at org.apache.ambari.server.orm.DBAccessorImpl.executePreparedUpdate(DBAccessorImpl.java:911)
        at org.apache.ambari.server.orm.DBAccessorImpl.moveColumnToAnotherTable(DBAccessorImpl.java:1428)
        at org.apache.ambari.server.upgrade.UpgradeCatalog251.moveClusterHostColumnFromStageToRequest(UpgradeCatalog251.java:131)
        at org.apache.ambari.server.upgrade.UpgradeCatalog251.executeDDLUpdates(UpgradeCatalog251.java:90)
        at org.apache.ambari.server.upgrade.AbstractUpgradeCatalog.upgradeSchema(AbstractUpgradeCatalog.java:925)

Issue appears Oracle specific possibly due to missing quotes in ? of the Update SQL statement

Issue Links

• links to: Review Board