search

axonivy-market / excel-importer

Apache License 2.0
0 stars 0 forks source link

Import of Data to Postgres fails due to primary key auto-increment strategy #43

Open ivy-rew opened 3 months ago

ivy-rew commented 3 months ago

The excel import always fails using 'postgres' as backend. importArzneimittelPg

reproduce: import the xls in our test-suite to postgres: https://github.com/axonivy-market/excel-importer/blob/master/excel-importer-test/src_test/com/axonivy/util/excel/test/ArzneimittelLight.xlsx

analysis: it's seems that a primary key generator strategy is missing. https://github.com/axonivy-market/excel-importer/issues/24#issuecomment-2014811547

error:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1613)
    at org.hibernate.query.Query.getResultList(Query.java:165)
    at ch.ivyteam.ivy.process.data.persistence.internal.IvyEntityManager.lambda$1(IvyEntityManager.java:65)
    at ch.ivyteam.ivy.process.data.persistence.internal.IvyEntityManager$1.call(IvyEntityManager.java:201)
    at ch.ivyteam.util.callable.AbstractExecutionContext.callInContext(AbstractExecutionContext.java:10)
    at ch.ivyteam.ivy.security.internal.SecurityManager.executeAsSystem(SecurityManager.java:481)
    at ch.ivyteam.ivy.process.data.persistence.internal.IvyEntityManager.execute(IvyEntityManager.java:193)
    at ch.ivyteam.ivy.process.data.persistence.internal.IvyEntityManager.execute(IvyEntityManager.java:176)
    at ch.ivyteam.ivy.process.data.persistence.internal.IvyEntityManager.findAll(IvyEntityManager.java:63)
    at com.axonivy.util.excel.importer.EntityDataLoader.createTable(EntityDataLoader.java:123)
    at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.lambda$3(ExcelImportProcessor.java:151)
    at ch.ivyteam.util.callable.AbstractExecutionContext.callInContext(AbstractExecutionContext.java:10)
    at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.importData(ExcelImportProcessor.java:150)
    at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.importExcel(ExcelImportProcessor.java:126)
    at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.lambda$0(ExcelImportProcessor.java:97)
    at org.eclipse.core.internal.resources.Workspace.run(Workspace.java:2315)
    at org.eclipse.core.internal.resources.Workspace.run(Workspace.java:2340)
    at com.axonivy.util.excel.importer.wizard.ExcelImportProcessor.run(ExcelImportProcessor.java:94)
    at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:122)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2297)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2050)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2012)
    at org.hibernate.loader.Loader.doQuery(Loader.java:948)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
    at org.hibernate.loader.Loader.doList(Loader.java:2843)
    at org.hibernate.loader.Loader.doList(Loader.java:2825)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2657)
    at org.hibernate.loader.Loader.list(Loader.java:2652)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1414)
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1636)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1604)
    ... 18 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "arzneimittellight" does not exist
  Position: 957
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
    at ch.ivyteam.db.jdbc.proxy.PreparedStatementProxy.executeQuery(PreparedStatementProxy.java:39)
    at ch.ivyteam.ivy.db.internal.monitor.ExternalDatabasePreparedStatementProxy.access$2(ExternalDatabasePreparedStatementProxy.java:1)
    at ch.ivyteam.ivy.db.internal.monitor.ExternalDatabasePreparedStatementProxy$3.executeQuery(ExternalDatabasePreparedStatementProxy.java:134)
    at ch.ivyteam.ivy.db.internal.monitor.StatementExecutorAndReportToMonitor.executeQuery(StatementExecutorAndReportToMonitor.java:238)
    at ch.ivyteam.ivy.db.internal.monitor.ExternalDatabasePreparedStatementProxy.executeQuery(ExternalDatabasePreparedStatementProxy.java:130)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
    ... 33 more
linhpd-axonivy commented 2 months ago

Hi @ivy-rew , I have encountered this bug before. To fix it, you just need to set hibernate.hbm2ddl.auto=update (to allow schema changes) hibernate.id.new_generator_mappings=false (to use classic sequence) to persistence of postgres