Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-63680

Upgrading postgreSQL database that uses schema other than default fails.

    Details

      Description

      Description
      When upgrading to master with a PostgreSQL database that uses a schema other than the "public" schema, the upgrade fails.

      Steps to Reproduce - Upgrade from 6.2 to 7.0

      1. Create new login role in Postgres with the following sql statement "ALTER ROLE testRole SET search_path = testSchema;"
      2. Create new database "lportal".
      3. Create new schema in the lportal database named "testSchema"
      4. Configure Liferay 6.2 to use the new database and login role.
      5. Start Liferay 6.2
      6. Shutdown Liferay
      7. Upgrade to Liferay 7.0

      Expected Results
      Liferay upgrades without error

      Actual Results
      While upgrading PostgreSQL the upgrade fails with the following error.

      15:51:13,344 ERROR [localhost-startStop-1][MainServlet:237] com.liferay.portal.kernel.events.ActionException: com.liferay.portal.kernel.upgrade.UpgradeException: com.liferay.portal.kernel.upgrade.UpgradeException: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
      com.liferay.portal.kernel.events.ActionException: com.liferay.portal.kernel.upgrade.UpgradeException: com.liferay.portal.kernel.upgrade.UpgradeException: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
      	at com.liferay.portal.events.StartupAction.run(StartupAction.java:82)
      	at com.liferay.portal.servlet.MainServlet.processStartupEvents(MainServlet.java:1289)
      	at com.liferay.portal.servlet.MainServlet.init(MainServlet.java:234)
      	at javax.servlet.GenericServlet.init(GenericServlet.java:160)
      	at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1280)
      	at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1193)
      	at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1088)
      	at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:5176)
      	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5460)
      	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
      	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
      	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
      	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:633)
      	at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:656)
      	at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1635)
      	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
      	at java.util.concurrent.FutureTask.run(FutureTask.java:262)
      	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
      	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
      	at java.lang.Thread.run(Thread.java:745)
      Caused by: com.liferay.portal.kernel.upgrade.UpgradeException: com.liferay.portal.kernel.upgrade.UpgradeException: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:59)
      	at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil._upgradeProcess(UpgradeProcessUtil.java:184)
      	at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:152)
      	at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:134)
      	at com.liferay.portal.events.StartupHelper.upgradeProcess(StartupHelper.java:164)
      	at com.liferay.portal.events.StartupHelperUtil.upgradeProcess(StartupHelperUtil.java:81)
      	at com.liferay.portal.tools.DBUpgrader.upgrade(DBUpgrader.java:145)
      	at com.liferay.portal.events.StartupAction.doRun(StartupAction.java:171)
      	at com.liferay.portal.events.StartupAction.run(StartupAction.java:76)
      	... 19 more
      Caused by: com.liferay.portal.kernel.upgrade.UpgradeException: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:59)
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:83)
      	at com.liferay.portal.upgrade.UpgradeProcess_7_0_0.doUpgrade(UpgradeProcess_7_0_0.java:92)
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:56)
      	... 27 more
      Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
      	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
      	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:413)
      	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
      	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
      	at com.liferay.portal.upgrade.v7_0_0.UpgradePostgreSQL.updateOrphanedLargeObjects(UpgradePostgreSQL.java:116)
      	at com.liferay.portal.upgrade.v7_0_0.UpgradePostgreSQL.doUpgrade(UpgradePostgreSQL.java:48)
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:56)
      	... 30 more
      Stopping the server due to unexpected startup errors
      
      

      Steps to reproduce - 6.2

      1. Create a new login role in PostgreSQL 9.4 and set a search_path for "testschema":
        ALTER ROLE <username> SET search_path = testschema; 
        

        OR edit the user in pgAdmin and add it in "variables" tab.

      2. Create a new database in PostgreSQL 9.4 (encoding: UTF8) called "lportal"
      3. Create a new Schema inside the new database called "testschema"
      4. Get a 6.2 bundle
      5. Connect the bundle to the database using properties in portal-ext.properties:
        	jdbc.default.driverClassName=org.postgresql.Driver
        	jdbc.default.url=jdbc:postgresql://localhost/lportal?currentSchema=testschema
        	jdbc.default.username=<username>
        	jdbc.default.password=password
        
      6. Start the portal
        Result:
        14:22:48,803 INFO  [localhost-startStop-1][DialectDetector:71] Determine dialect for PostgreSQL 9
        14:22:48,834 INFO  [localhost-startStop-1][DialectDetector:136] Found dialect org.hibernate.dialect.PostgreSQLDialect
        14:23:11,597 INFO  [localhost-startStop-1][StartupAction:103] The following patches are installed: portal-138-6210
        14:23:11,668 ERROR [localhost-startStop-1][JDBCExceptionReporter:82] ERROR: relation "lock_" does not exist_  Position: 414 [Sanitized]
        14:23:11,684 WARN  [localhost-startStop-1][StartupAction:150] Unable to clear locks because Lock table does not exist
        14:23:12,025 WARN  [localhost-startStop-1][ReleaseLocalServiceImpl:171] ERROR: relation "release_" does not exist_  Position: 25 [Sanitized]
        14:23:12,025 INFO  [localhost-startStop-1][ReleaseLocalServiceImpl:84] Create tables and populate with default data
        14:23:19,931 INFO  [localhost-startStop-1][BaseDB:486] Database supports case sensitive queries
        14:23:20,794 INFO  [localhost-startStop-1][VerifyProcess:65] Verifying com.liferay.portal.verify.VerifyProcessSuite
        14:23:20,795 INFO  [localhost-startStop-1][VerifyProcess:65] Verifying com.liferay.portal.verify.VerifyProperties
        14:23:20,819 INFO  [localhost-startStop-1][VerifyProcess:65] Verifying com.liferay.portal.verify.VerifyDB2
        14:23:20,820 INFO  [localhost-startStop-1][VerifyProcess:65] Verifying com.liferay.portal.verify.VerifyMySQL
        14:23:20,821 INFO  [localhost-startStop-1][VerifyProcess:65] Verifying com.liferay.portal.verify.VerifyOracle
        14:23:20,823 INFO  [localhost-startStop-1][VerifyProcess:65] Verifying com.liferay.portal.verify.VerifyPostgreSQL
        14:23:20,843 ERROR [localhost-startStop-1][DBUpgrader:247] Unable to execute verify process: com.liferay.portal.verify.VerifyException: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
        com.liferay.portal.verify.VerifyException: com.liferay.portal.verify.VerifyException: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
        	at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:71)
        	at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:111)
        	at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:76)
        	at com.liferay.portal.verify.VerifyProcessUtil.verifyProcess(VerifyProcessUtil.java:46)
        	at com.liferay.portal.events.StartupHelper.verifyProcess(StartupHelper.java:140)
        	at com.liferay.portal.events.StartupHelperUtil.verifyProcess(StartupHelperUtil.java:67)
        	at com.liferay.portal.tools.DBUpgrader.verify(DBUpgrader.java:240)
        	at com.liferay.portal.events.StartupAction.doRun(StartupAction.java:239)
        	at com.liferay.portal.ee.license.StartupAction.doRun(Unknown Source)
        	at com.liferay.portal.events.StartupAction.run(StartupAction.java:77)
        	at com.liferay.portal.servlet.MainServlet.processStartupEvents(MainServlet.java:1290)
        	at com.liferay.portal.servlet.MainServlet.init(MainServlet.java:264)
        	at javax.servlet.GenericServlet.init(GenericServlet.java:158)
        	at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1284)
        	at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1197)
        	at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1087)
        	at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:5266)
        	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5554)
        	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
        	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
        	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
        	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:652)
        	at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:677)
        	at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1912)
        	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        	at java.util.concurrent.FutureTask.run(FutureTask.java:262)
        	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        	at java.lang.Thread.run(Thread.java:745)
        Caused by: com.liferay.portal.verify.VerifyException: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
        	at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:71)
        	at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:76)
        	at com.liferay.portal.verify.VerifyProcessSuite.doVerify(VerifyProcessSuite.java:29)
        	at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:68)
        	... 28 more
        Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
        	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
        	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
        	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
        	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
        	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)
        	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418)
        	at com.liferay.portal.verify.VerifyPostgreSQL.deleteOrphanedLargeObjects(VerifyPostgreSQL.java:71)
        	at com.liferay.portal.verify.VerifyPostgreSQL.doVerify(VerifyPostgreSQL.java:92)
        	at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:68)
        	... 31 more
        14:23:20,846 ERROR [localhost-startStop-1][MainServlet:267] com.liferay.portal.kernel.events.ActionException: com.liferay.portal.verify.VerifyException: com.liferay.portal.verify.VerifyException: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
        com.liferay.portal.kernel.events.ActionException: com.liferay.portal.verify.VerifyException: com.liferay.portal.verify.VerifyException: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
        	at com.liferay.portal.events.StartupAction.run(StartupAction.java:83)
        	at com.liferay.portal.servlet.MainServlet.processStartupEvents(MainServlet.java:1290)
        	at com.liferay.portal.servlet.MainServlet.init(MainServlet.java:264)
        	at javax.servlet.GenericServlet.init(GenericServlet.java:158)
        	at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1284)
        	at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1197)
        	at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1087)
        	at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:5266)
        	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5554)
        	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
        	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
        	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
        	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:652)
        	at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:677)
        	at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1912)
        	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        	at java.util.concurrent.FutureTask.run(FutureTask.java:262)
        	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        	at java.lang.Thread.run(Thread.java:745)
        Caused by: com.liferay.portal.verify.VerifyException: com.liferay.portal.verify.VerifyException: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
        	at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:71)
        	at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:111)
        	at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:76)
        	at com.liferay.portal.verify.VerifyProcessUtil.verifyProcess(VerifyProcessUtil.java:46)
        	at com.liferay.portal.events.StartupHelper.verifyProcess(StartupHelper.java:140)
        	at com.liferay.portal.events.StartupHelperUtil.verifyProcess(StartupHelperUtil.java:67)
        	at com.liferay.portal.tools.DBUpgrader.verify(DBUpgrader.java:240)
        	at com.liferay.portal.events.StartupAction.doRun(StartupAction.java:239)
        	at com.liferay.portal.ee.license.StartupAction.doRun(Unknown Source)
        	at com.liferay.portal.events.StartupAction.run(StartupAction.java:77)
        	... 19 more
        Caused by: com.liferay.portal.verify.VerifyException: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
        	at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:71)
        	at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:76)
        	at com.liferay.portal.verify.VerifyProcessSuite.doVerify(VerifyProcessSuite.java:29)
        	at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:68)
        	... 28 more
        Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at end of input_  Position: 62 [Sanitized]
        	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
        	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
        	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
        	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
        	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)
        	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418)
        	at com.liferay.portal.verify.VerifyPostgreSQL.deleteOrphanedLargeObjects(VerifyPostgreSQL.java:71)
        	at com.liferay.portal.verify.VerifyPostgreSQL.doVerify(VerifyPostgreSQL.java:92)
        	at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:68)
        	... 31 more
        

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              shitian.zhang Shitian "Shelton" Zhang (Inactive)
              Reporter:
              samuel.ziemer Sam Ziemer
              Participants of an Issue:
              Recent user:
              Esther Sanz
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since last comment:
                3 years, 31 weeks, 1 day ago

                  Packages

                  Version Package
                  6.2.X EE
                  7.0.0 CE RC1