org.postgresql.util.PSQLException: ERROR: column "resourcecode.codeid" must appear in the GROUP BY clause or be used in an aggregate function

Liferay Issues | Albert Lee | 4 years ago
  1. 0

    When converting legacy permissions algorithm in 5.2.3 in the Control Panel in the environments listed, you get errors in the console. # Navigate to Control Panel > Server Administration # Click on the Data Migration tab # Click 'Execute' to convert legacy permissions algorithm # You get the following errors in the console *DB2 9.7* {code}18:38:27,595 DEBUG [MaintenanceUtil:75] Generating ResourceAction and ResourcePermission data 18:38:27,647 FATAL [ConvertPermissionAlgorithm:131] com.ibm.db2.jcc.a.nn: DB2 SQL Error: SQLCODE=-119, SQLSTATE=42803, SQLERRMC=CODEID, DRIVER=3.52.95 com.ibm.db2.jcc.a.nn: DB2 SQL Error: SQLCODE=-119, SQLSTATE=42803, SQLERRMC=CODEID, DRIVER=3.52.95 at com.ibm.db2.jcc.a.ad.a(ad.java:666) at com.ibm.db2.jcc.a.ad.a(ad.java:60) at com.ibm.db2.jcc.a.ad.a(ad.java:127) at com.ibm.db2.jcc.a.rl.c(rl.java:2424) at com.ibm.db2.jcc.a.rl.d(rl.java:2401) at com.ibm.db2.jcc.a.rl.a(rl.java:1902) at com.ibm.db2.jcc.t4.db.g(db.java:138) at com.ibm.db2.jcc.t4.db.a(db.java:38) at com.ibm.db2.jcc.t4.t.a(t.java:32) at com.ibm.db2.jcc.t4.sb.h(sb.java:141) at com.ibm.db2.jcc.a.rl.Y(rl.java:1873) at com.ibm.db2.jcc.a.sl.bc(sl.java:2526) at com.ibm.db2.jcc.a.sl.gc(sl.java:2632) at com.ibm.db2.jcc.a.sl.a(sl.java:443) at com.ibm.db2.jcc.a.ib.a(ib.java:2016) at com.ibm.db2.jcc.a.ib.prepareStatement(ib.java:639) at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:213) at sun.reflect.GeneratedMethodAccessor55.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.invoke(LazyConnectionDataSourceProxy.java:364) at $Proxy0.prepareStatement(Unknown Source) at com.liferay.portal.upgrade.util.Table.generateTempFile(Table.java:369) at com.liferay.portal.convert.ConvertPermissionAlgorithm._convertToBitwise(ConvertPermissionAlgorithm.java:157) at com.liferay.portal.convert.ConvertPermissionAlgorithm.doConvert(ConvertPermissionAlgorithm.java:124) at com.liferay.portal.convert.ConvertProcess.convert(ConvertProcess.java:41) at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.doReceive(ConvertProcessMessageListener.java:59) at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.receive(ConvertProcessMessageListener.java:44) at com.liferay.portal.kernel.messaging.InvokerMessageListener.receive(InvokerMessageListener.java:70) at com.liferay.portal.kernel.messaging.SerialDestination$1.run(SerialDestination.java:54) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675) at java.lang.Thread.run(Thread.java:595) 18:38:27,650 INFO [ConvertProcess:43] Conversion complete{code} *Oracle 10.2* {code}20:25:19,058 DEBUG [MaintenanceUtil:75] Generating ResourceAction and ResourcePermission data 20:25:19,064 FATAL [ConvertPermissionAlgorithm:131] java.sql.SQLException: ORA-00979: not a GROUP BY expression java.sql.SQLException: ORA-00979: not a GROUP BY expression at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1203) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at com.liferay.portal.upgrade.util.Table.generateTempFile(Table.java:371) at com.liferay.portal.convert.ConvertPermissionAlgorithm._convertToBitwise(ConvertPermissionAlgorithm.java:157) at com.liferay.portal.convert.ConvertPermissionAlgorithm.doConvert(ConvertPermissionAlgorithm.java:124) at com.liferay.portal.convert.ConvertProcess.convert(ConvertProcess.java:41) at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.doReceive(ConvertProcessMessageListener.java:59) at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.receive(ConvertProcessMessageListener.java:44) at com.liferay.portal.kernel.messaging.InvokerMessageListener.receive(InvokerMessageListener.java:70) at com.liferay.portal.kernel.messaging.SerialDestination$1.run(SerialDestination.java:54) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675) at java.lang.Thread.run(Thread.java:595) 20:25:19,066 INFO [ConvertProcess:43] Conversion complete{code} *PostgreSQL 9* {code}20:47:21,380 DEBUG [MaintenanceUtil:75] Generating ResourceAction and ResourcePermission data 20:47:21,403 FATAL [ConvertPermissionAlgorithm:131] org.postgresql.util.PSQLException: ERROR: column "resourcecode.codeid" must appear in the GROUP BY clause or be used in an aggregate function org.postgresql.util.PSQLException: ERROR: column "resourcecode.codeid" must appear in the GROUP BY clause or be used in an aggregate function at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1343) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at com.liferay.portal.upgrade.util.Table.generateTempFile(Table.java:371) at com.liferay.portal.convert.ConvertPermissionAlgorithm._convertToBitwise(ConvertPermissionAlgorithm.java:157) at com.liferay.portal.convert.ConvertPermissionAlgorithm.doConvert(ConvertPermissionAlgorithm.java:124) at com.liferay.portal.convert.ConvertProcess.convert(ConvertProcess.java:41) at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.doReceive(ConvertProcessMessageListener.java:59) at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.receive(ConvertProcessMessageListener.java:44) at com.liferay.portal.kernel.messaging.InvokerMessageListener.receive(InvokerMessageListener.java:70) at com.liferay.portal.kernel.messaging.SerialDestination$1.run(SerialDestination.java:54) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675) at java.lang.Thread.run(Thread.java:595) 20:47:21,407 INFO [ConvertProcess:43] Conversion complete{code} *SQL Server 2008* {code}20:57:35,783 DEBUG [MaintenanceUtil:75] Generating ResourceAction and ResourcePermission data 20:57:35,792 FATAL [ConvertPermissionAlgorithm:131] java.sql.SQLException: Column 'ResourceCode.codeId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. java.sql.SQLException: Column 'ResourceCode.codeId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477) at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at com.liferay.portal.upgrade.util.Table.generateTempFile(Table.java:371) at com.liferay.portal.convert.ConvertPermissionAlgorithm._convertToBitwise(ConvertPermissionAlgorithm.java:157) at com.liferay.portal.convert.ConvertPermissionAlgorithm.doConvert(ConvertPermissionAlgorithm.java:124) at com.liferay.portal.convert.ConvertProcess.convert(ConvertProcess.java:41) at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.doReceive(ConvertProcessMessageListener.java:59) at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.receive(ConvertProcessMessageListener.java:44) at com.liferay.portal.kernel.messaging.InvokerMessageListener.receive(InvokerMessageListener.java:70) at com.liferay.portal.kernel.messaging.SerialDestination$1.run(SerialDestination.java:54) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675) at java.lang.Thread.run(Thread.java:595) 20:57:35,794 INFO [ConvertProcess:43] Conversion complete{code} *Sybase 15.02* {code}19:40:04,928 DEBUG [MaintenanceUtil:75] Generating ResourceAction and ResourcePermission data 19:40:05,019 FATAL [ConvertPermissionAlgorithm:131] java.sql.SQLException: Invalid column name '100'. java.sql.SQLException: Invalid column name '100'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477) at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at com.liferay.portal.upgrade.util.Table.generateTempFile(Table.java:371) at com.liferay.portal.convert.ConvertPermissionAlgorithm._convertResourcePermission(ConvertPermissionAlgorithm.java:365) at com.liferay.portal.convert.ConvertPermissionAlgorithm._convertToBitwise(ConvertPermissionAlgorithm.java:181) at com.liferay.portal.convert.ConvertPermissionAlgorithm.doConvert(ConvertPermissionAlgorithm.java:124) at com.liferay.portal.convert.ConvertProcess.convert(ConvertProcess.java:41) at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.doReceive(ConvertProcessMessageListener.java:59) at com.liferay.portal.convert.messaging.ConvertProcessMessageListener.receive(ConvertProcessMessageListener.java:44) at com.liferay.portal.kernel.messaging.InvokerMessageListener.receive(InvokerMessageListener.java:70) at com.liferay.portal.kernel.messaging.SerialDestination$1.run(SerialDestination.java:54) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675) at java.lang.Thread.run(Thread.java:595) 19:40:05,021 INFO [ConvertProcess:43] Conversion complete{code}

    Liferay Issues | 4 years ago | Albert Lee
    org.postgresql.util.PSQLException: ERROR: column "resourcecode.codeid" must appear in the GROUP BY clause or be used in an aggregate function
  2. 0

    Error after upgrade from 2.0.4 to 2.0.6

    Google Groups | 8 years ago | Heejong Lee
    com.google.gwtorm.client.OrmException: fetch failure on trusted_external_ids
  3. 0

    PO very slow on zkwebui

    adempiere | 5 years ago | bma99
    org.postgresql.util.PSQLException: ERROR: syntax error at or near ""limit""   Position: 501; State=42601; ErrorCode=0
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Hibernate does not create table - some converters

    Stack Overflow | 2 years ago | Koziołek
    org.postgresql.util.PSQLException: ERROR: relation "prowizje_szablony" does not exist Pozycja: 273
Not finding the right solution?
Take a tour to get the most out of Samebug.

Tired of useless tips?

Automated exception search integrated into your IDE

Root Cause Analysis

  1. org.postgresql.util.PSQLException

    ERROR: column "resourcecode.codeid" must appear in the GROUP BY clause or be used in an aggregate function

    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse()
  2. PostgreSQL JDBC Driver
    AbstractJdbc2Statement.executeQuery
    1. org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608)
    2. org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1343)
    3. org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194)
    4. org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
    5. org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
    6. org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
    6 frames
  3. c3p0:JDBC DataSources/Resource Pools
    NewProxyPreparedStatement.executeQuery
    1. com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    1 frame
  4. Liferay Portal Impl
    ConvertProcessMessageListener.receive
    1. com.liferay.portal.upgrade.util.Table.generateTempFile(Table.java:371)
    2. com.liferay.portal.convert.ConvertPermissionAlgorithm._convertToBitwise(ConvertPermissionAlgorithm.java:157)
    3. com.liferay.portal.convert.ConvertPermissionAlgorithm.doConvert(ConvertPermissionAlgorithm.java:124)
    4. com.liferay.portal.convert.ConvertProcess.convert(ConvertProcess.java:41)
    5. com.liferay.portal.convert.messaging.ConvertProcessMessageListener.doReceive(ConvertProcessMessageListener.java:59)
    6. com.liferay.portal.convert.messaging.ConvertProcessMessageListener.receive(ConvertProcessMessageListener.java:44)
    6 frames
  5. Liferay Portal Service
    SerialDestination$1.run
    1. com.liferay.portal.kernel.messaging.InvokerMessageListener.receive(InvokerMessageListener.java:70)
    2. com.liferay.portal.kernel.messaging.SerialDestination$1.run(SerialDestination.java:54)
    2 frames
  6. Java RT
    Thread.run
    1. java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
    2. java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
    3. java.lang.Thread.run(Thread.java:595)
    3 frames