[SENTRY-691] upgrade schema tool: when upgrade oracle backed db from 1.4.0 - 1.5.0, gets syntax error

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.5.0
• Fix Version/s: 1.5.0

Environment

[root@anneyu-sentry-ha-10-1 incubator-sentry]# hostname
anneyu-sentry-ha-10-1.ent.cloudera.com
[root@anneyu-sentry-ha-10-1 incubator-sentry]# pwd
/root/apache-sentry/1.5.0/incubator-sentry

• Assignee: shenguoquan
• Reporter: Anne Yu
• Created: 01/Apr/15 21:26
• Updated: 03/Apr/15 00:46
• Resolved: 02/Apr/15 21:47

Description

Sentry store connection URL:	 jdbc:oracle:thin:@//anneyu-sentry-cdh60-3.ent.cloudera.com:1521/xe
Sentry store Connection Driver :	 oracle.jdbc.OracleDriver
Sentry store connection User:	 sentryserver3ed186ace216a5ba8c
Starting upgrade sentry store schema from version 1.4.0 to 1.5.0
Upgrade script sentry-upgrade-oracle-1.4.0-to-1.5.0.sql
Connecting to jdbc:oracle:thin:@//anneyu-sentry-cdh60-3.ent.cloudera.com:1521/xe
Connected to: Oracle (version Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production)
Driver: Oracle JDBC driver (version 11.2.0.3.0)
Error: READ_COMMITTED and SERIALIZABLE are the only valid transaction levels (state=99999,code=17030)
...

./sentry-provider/sentry-provider-db/src/main/resources/005-SENTRY-398.oracle.sql

Error:

0: jdbc:oracle:thin:@//anneyu-sentry-cdh60-3.> CREATE TABLE "SENTRY_GM_PRIVILEGE" ( "GM_PRIVILEGE_ID" NUMBER NOT NULL, "COMPONENT_NAME" VARCHAR2(32) NOT NULL, "CREATE_TIME" NUMBER NOT NULL, "WITH_GRANT_OPTION" CHAR(1) NOT NULL DEFAULT 'N' "RESOURCE_NAME_0" VARCHAR2(64) DEFAULT '__NULL__', "RESOURCE_NAME_1" VARCHAR2(64) DEFAULT '__NULL__', "RESOURCE_NAME_2" VARCHAR2(64) DEFAULT '__NULL__', "RESOURCE_NAME_3" VARCHAR2(64) DEFAULT '__NULL__', "RESOURCE_TYPE_0" VARCHAR2(64) DEFAULT '__NULL__', "RESOURCE_TYPE_1" VARCHAR2(64) DEFAULT '__NULL__', "RESOURCE_TYPE_2" VARCHAR2(64) DEFAULT '__NULL__', "RESOURCE_TYPE_3" VARCHAR2(64) DEFAULT '__NULL__', "ACTION" VARCHAR2(32) NOT NULL, "scope" VARCHAR2(128) NOT NULL, "SERVICE_NAME" VARCHAR2(64) NOT NULL, )
Error: ORA-00907: missing right parenthesis (state=42000,code=907)

Issue Links

• breaks: SENTRY-500 1.4 to 1.5 upgrade needs to handle empty strings with __NULL__