[HIVE-11628] DB type detection code is failing on Oracle 12

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Won't Fix
• Fix Version/s: 2.0.0
• Component/s: Metastore

Environment

Oracle 12

• Assignee: Deepesh Khandelwal
• Reporter: Deepesh Khandelwal
• Created: 24/Aug/15 19:31
• Updated: 16/Feb/16 23:50
• Resolved: 27/Aug/15 17:40

Description

DB type detection code is failing when using Oracle 12 as backing store.

When determining qualification for direct SQL, in the logs following message is seen:

2015-08-14 01:15:16,020 INFO  [pool-6-thread-109]: metastore.MetaStoreDirectSql (MetaStoreDirectSql.java:<init>(131)) - Using direct SQL, underlying DB is OTHER

Currently in org/apache/hadoop/hive/metastore/MetaStoreDirectSql, there is a code snippet:

  private DB determineDbType() {
    DB dbType = DB.OTHER;
    if (runDbCheck("SET @@session.sql_mode=ANSI_QUOTES", "MySql")) {
      dbType = DB.MYSQL;
    } else if (runDbCheck("SELECT version from v$instance", "Oracle")) {
      dbType = DB.ORACLE;
    } else if (runDbCheck("SELECT @@version", "MSSQL")) {
      dbType = DB.MSSQL;
    } else {
      // TODO: maybe we should use getProductName to identify all the DBs
      String productName = getProductName();
      if (productName != null && productName.toLowerCase().contains("derby")) {
        dbType = DB.DERBY;
      }
    }
    return dbType;
  }

The code relies on access to v$instance in order to identify the backend DB as Oracle, but this can fail if users are not granted select privileges on v$ tables. An alternate way is specified on Oracle Database Reference pages works.
I will attach a potential patch that should work.

Without the patch the workaround here would be to grant select privileges on v$ tables.