Hive
  1. Hive
  2. HIVE-3994

Hive metastore is not working on PostgreSQL 9.2 (most likely on anything 9.0+)

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Not a Problem
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      I'm getting following exception when running metastore on PostgreSQL 9.2:

      Caused by: javax.jdo.JDODataStoreException: Error executing JDOQL query "SELECT "THIS"."TBL_NAME" AS NUCORDER0 FROM "TBLS" "THIS" LEFT OUTER JOIN "DBS" "THIS_DATABASE_NAME" ON "THIS"."DB_ID" = "THIS_DATABASE_NAME"."DB_ID" WHERE "THIS_DATABASE_NAME"."NAME" = ? AND (LOWER("THIS"."TBL_NAME") LIKE ? ESCAPE '\\' ) ORDER BY NUCORDER0 " : ERROR: invalid escape string
        Hint: Escape string must be empty or one character..
      NestedThrowables:
      org.postgresql.util.PSQLException: ERROR: invalid escape string
        Hint: Escape string must be empty or one character.
              at org.datanucleus.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:313)
              at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:252)
              at org.apache.hadoop.hive.metastore.ObjectStore.getTables(ObjectStore.java:759)
              ... 28 more
      Caused by: org.postgresql.util.PSQLException: ERROR: invalid escape string
        Hint: Escape string must be empty or one character.
              at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2096)
              at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1829)
              at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
              at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
              at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
              at org.datanucleus.store.rdbms.SQLController.executeStatementQuery(SQLController.java:457)
              at org.datanucleus.store.rdbms.query.legacy.SQLEvaluator.evaluate(SQLEvaluator.java:123)
              at org.datanucleus.store.rdbms.query.legacy.JDOQLQuery.performExecute(JDOQLQuery.java:288)
              at org.datanucleus.store.query.Query.executeQuery(Query.java:1657)
              at org.datanucleus.store.rdbms.query.legacy.JDOQLQuery.executeQuery(JDOQLQuery.java:245)
              at org.datanucleus.store.query.Query.executeWithArray(Query.java:1499)
              at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:243)
              ... 29 more
      

      I've google a bit about that and I found a lot of similar issues in different projects thus I'm assuming that this might be some backward compatibility issue on PostgreSQL side.

        Activity

        Hide
        Shreepadma Venugopalan added a comment -

        This problem appears in postgres 9.x because standard conforming strings were turned on by default starting 9.x. More here - http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.1#Backward_compatibility_issues. One fix for this issue is to set standard_conforming_string to off when setting up hive metastore on postgres.

        Show
        Shreepadma Venugopalan added a comment - This problem appears in postgres 9.x because standard conforming strings were turned on by default starting 9.x. More here - http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.1#Backward_compatibility_issues . One fix for this issue is to set standard_conforming_string to off when setting up hive metastore on postgres.
        Hide
        Jarek Jarcec Cecho added a comment -

        Och thank you Shreepadma!

        I've reconfigured my PostgreSQL 9.2 by turning standard_conforming_string property to off and hive metastore is working correctly again!

        I'm closing this issue as "not a problem".

        Jarcec

        Show
        Jarek Jarcec Cecho added a comment - Och thank you Shreepadma! I've reconfigured my PostgreSQL 9.2 by turning standard_conforming_string property to off and hive metastore is working correctly again! I'm closing this issue as "not a problem". Jarcec
        Hide
        Andy Jefferson added a comment -

        Obviously DataNucleus 3.x supports this new Postgresql syntax, but sadly you keep on using an ancient version

        Show
        Andy Jefferson added a comment - Obviously DataNucleus 3.x supports this new Postgresql syntax, but sadly you keep on using an ancient version

          People

          • Assignee:
            Unassigned
            Reporter:
            Jarek Jarcec Cecho
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development