search

lagom / lagom-recipes

Other
62 stars 35 forks source link

Unable to modify mixed-persistence-java-sbt to use Postgres #65

Closed marvinmarnold closed 5 years ago

marvinmarnold commented 5 years ago

These are the changes I tried to make to use Postgres: https://github.com/marvinmarnold/lagom-recipes/commit/81b6f39d1becc2aa2efbdf970beb8fe5f61704b1

When I do sbt runAll, things start off nice:

16:17:04.840 [info] application [] - Creating Pool for datasource 'default'
16:17:04.851 [info] com.zaxxer.hikari.HikariDataSource [] - HikariPool-1 - Starting...
16:17:04.960 [info] com.zaxxer.hikari.HikariDataSource [] - HikariPool-1 - Start completed.
16:17:04.970 [info] play.api.db.HikariCPConnectionPool [] - datasource [default] bound to JNDI as DefaultDS
16:17:04.983 [info] play.api.db.DefaultDBApi [] - Database [default] connected at jdbc:postgresql://localhost:5431/postgres
16:17:05.659 [info] org.hibernate.jpa.internal.util.LogHelper [] - HHH000204: Processing PersistenceUnitInfo [
    name: default
    ...]

But then the Postgres connection ultimately fails

16:17:06.795 [warn] org.hibernate.engine.jdbc.spi.SqlExceptionHelper [] - SQL Error: 0, SQLState: null
16:17:06.795 [error] org.hibernate.engine.jdbc.spi.SqlExceptionHelper [] - null
16:17:06.799 [warn] com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaSessionImpl [] - Exception while initializing JPA EntityManagerFactory
javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:967)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:892)
    at org.hibernate.jpa.HibernatePersistenceProvider.createEntityManagerFactory(HibernatePersistenceProvider.java:58)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:55)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:39)
    at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaSessionImpl.lambda$createEntityManagerFactory$1(JpaSessionImpl.java:86)
    at com.lightbend.lagom.internal.javadsl.persistence.jpa.Retry.$anonfun$retry$1(Retry.scala:35)
    at scala.concurrent.Future$.$anonfun$apply$1(Future.scala:655)
    at scala.util.Success.$anonfun$map$1(Try.scala:251)
    at scala.util.Success.map(Try.scala:209)
    at scala.concurrent.Future.$anonfun$map$1(Future.scala:289)
    at scala.concurrent.impl.Promise.liftedTree1$1(Promise.scala:29)
    at scala.concurrent.impl.Promise.$anonfun$transform$1(Promise.scala:29)
    at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:60)
    at slick.util.AsyncExecutor$$anon$2$$anon$3$$anon$4.run(AsyncExecutor.scala:165)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.GenericJDBCException: Unable to open JDBC Connection for DDL execution
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
    at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:69)
    at org.hibernate.tool.schema.internal.exec.ImprovedExtractionContextImpl.getJdbcConnection(ImprovedExtractionContextImpl.java:60)
    at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:40)
    at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:65)
    at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.<init>(DatabaseInformationImpl.java:59)
    at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:132)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:96)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:313)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:452)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:889)
    ... 16 common frames omitted
Caused by: java.sql.SQLFeatureNotSupportedException: null
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:135)
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180)
    at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:43)
    ... 27 common frames omitted
16:17:06.800 [info] com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaSessionImpl [] - Will retry initializing JPA EntityManagerFactory 10 times in 5 seconds

I'm able to connect to that DB from DBeaver using the same credentials. Port 5431 is intentional.

marvinmarnold commented 5 years ago

I'm now able to get a stable connection to the DB but it fails when I try to persist a new entry.

Additional changes: https://github.com/marvinmarnold/lagom-recipes/commit/25fa4b9c8001ce785ec8a7f20aa19eb25da7b39f

Current exception

Hibernate: insert into UserGreetingRecord (message, id) values (?, ?)
Hibernate: update "read_side_offsets" set "sequence_offset"=?,"time_uuid_offset"=? where "read_side_id"=? and "tag"=?; insert into "read_side_offsets" ("read_side_id","tag","sequence_offset","time_uuid_offset") select ?,?,?,? where not exists (select 1 from "read_side_offsets" where "read_side_id"=? and "tag"=?)
22:46:13.825 [warn] org.hibernate.engine.jdbc.spi.SqlExceptionHelper [] - SQL Error: 0, SQLState: 42804
22:46:13.825 [error] org.hibernate.engine.jdbc.spi.SqlExceptionHelper [] - ERROR: column "sequence_offset" is of type bigint but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.
  Position: 50
  Location: File: parse_target.c, Routine: transformAssignedExpr, Line: 591
  Server SQLState: 42804
22:46:13.836 [warn] akka.stream.scaladsl.RestartWithBackoffSource [sourceThread=hello-impl-application-akka.actor.default-dispatcher-17, akkaTimestamp=04:46:13.836UTC, akkaSource=RestartWithBackoffSource(akka://hello-impl-application), sourceActorSystem=hello-impl-application] - Restarting graph due to failure. stack_trace: 
java.util.concurrent.CompletionException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
    at java.util.concurrent.CompletableFuture.encodeRelay(CompletableFuture.java:326)
    at java.util.concurrent.CompletableFuture.completeRelay(CompletableFuture.java:338)
    at java.util.concurrent.CompletableFuture.uniRelay(CompletableFuture.java:911)
    at java.util.concurrent.CompletableFuture$UniRelay.tryFire(CompletableFuture.java:899)
    at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:474)
    at java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:1977)
    at scala.concurrent.java8.FuturesConvertersImpl$CF.apply(FutureConvertersImpl.scala:21)
    at scala.concurrent.java8.FuturesConvertersImpl$CF.apply(FutureConvertersImpl.scala:18)
    at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:60)
    at scala.concurrent.BatchingExecutor$Batch.processBatch$1(BatchingExecutor.scala:63)
    at scala.concurrent.BatchingExecutor$Batch.$anonfun$run$1(BatchingExecutor.scala:78)
    at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:12)
    at scala.concurrent.BlockContext$.withBlockContext(BlockContext.scala:81)
    at scala.concurrent.BatchingExecutor$Batch.run(BatchingExecutor.scala:55)
    at scala.concurrent.Future$InternalCallbackExecutor$.unbatchedExecute(Future.scala:866)
    at scala.concurrent.BatchingExecutor.execute(BatchingExecutor.scala:106)
    at scala.concurrent.BatchingExecutor.execute$(BatchingExecutor.scala:103)
    at scala.concurrent.Future$InternalCallbackExecutor$.execute(Future.scala:864)
    at scala.concurrent.impl.CallbackRunnable.executeWithValue(Promise.scala:68)
    at scala.concurrent.impl.Promise$DefaultPromise.$anonfun$tryComplete$1(Promise.scala:284)
    at scala.concurrent.impl.Promise$DefaultPromise.$anonfun$tryComplete$1$adapted(Promise.scala:284)
    at scala.concurrent.impl.Promise$DefaultPromise.tryComplete(Promise.scala:284)
    at scala.concurrent.Promise.complete(Promise.scala:49)
    at scala.concurrent.Promise.complete$(Promise.scala:48)
    at scala.concurrent.impl.Promise$DefaultPromise.complete(Promise.scala:183)
    at scala.concurrent.impl.Promise.$anonfun$transform$1(Promise.scala:29)
    at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:60)
    at slick.util.AsyncExecutor$$anon$2$$anon$3$$anon$4.run(AsyncExecutor.scala:165)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
    at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1514)
    at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaReadSideImpl$JpaReadSideHandler.updateOffset(JpaReadSideImpl.java:186)
    at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaReadSideImpl$JpaReadSideHandler.lambda$null$3(JpaReadSideImpl.java:148)
    at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaSessionImpl.lambda$withTransaction$0(JpaSessionImpl.java:63)
    at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaSessionImpl.lambda$null$cb7af80$1(JpaSessionImpl.java:102)
    at play.utils.Threads$.withContextClassLoader(Threads.scala:21)
    at play.utils.Threads.withContextClassLoader(Threads.scala)
    at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaSessionImpl.lambda$wrapWithContextClassLoader$100ad7b7$1(JpaSessionImpl.java:114)
    at scala.concurrent.Future$.$anonfun$apply$1(Future.scala:655)
    at scala.util.Success.$anonfun$map$1(Try.scala:251)
    at scala.util.Success.map(Try.scala:209)
    at scala.concurrent.Future.$anonfun$map$1(Future.scala:289)
    at scala.concurrent.impl.Promise.liftedTree1$1(Promise.scala:29)
    ... 6 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
    at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:199)
    at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1537)
    at org.hibernate.query.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:274)
    at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1504)
    ... 18 more
Caused by: org.postgresql.util.PSQLException: ERROR: column "sequence_offset" is of type bigint but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.
  Position: 50
  Location: File: parse_target.c, Routine: transformAssignedExpr, Line: 591
  Server SQLState: 42804
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
    ... 22 more
ignasi35 commented 5 years ago

Hi @marvinmarnold the recipes here are not always maintained. If you need help adapting some of the examples you may have a wider audience (and potential help) at http://discuss.lagomframework.com

marvinmarnold commented 5 years ago

@ignasi35 I got this working now. Let me know if you would like me to clean up my commits and make a pull request for a new PostgreSQL recipe for this repo.

https://github.com/marvinmarnold/lagom-recipes/commit/3e67f0c08f860b2819116e7ba355318d6052e72e

ignasi35 commented 5 years ago

Let me know if you would like me to clean up my commits and make a pull request for a new PostgreSQL recipe for this repo.

@marvinmarnold that'd be great! Thanks