Migrating from MySQL to PostgreSQL - having issues
shakmirzakhalov opened this issue · 1 comments
I am migrating from Maria DB to PostgreSQL, currently using the keywhiz-server-0.8.0-shaded.jar. I have completed the DB data migration and applied the schema changes using an external tools. When starting keywhiz with the new DB facing the below errors, can someone please help.
database:
driverClass: com.impossibl.postgres.jdbc.PGDriver
url: jdbc:pgsql://IP:5432/keywhiz_db_stg?ssl=false
user:
properties:
charSet: UTF-8
initialSize: 32
minSize: 32
maxSize: 32
There is explicitly no password. Do not uncomment.
password:
<=================================================>
! Causing: org.jooq.exception.DataAccessException: SQL [insert into clients (name, updatedat, createdat, description, createdby, updatedby, enabled, automationallowed, lastseen) values (?, ?, ?, ?, ?, ?, ?, ?, ?) returning clients.id]; null value in column "id" of relation "clients" violates not-null constraint
! at org.jooq_3.9.1.POSTGRES.debug(Unknown Source) ~[na:na]
! at org.jooq.impl.Tools.translate(Tools.java:1983) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:676) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:363) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:198) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:169) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:165) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.jooq.impl.UpdatableRecordImpl.store0(UpdatableRecordImpl.java:196) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.jooq.impl.UpdatableRecordImpl.access$000(UpdatableRecordImpl.java:86) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.jooq.impl.UpdatableRecordImpl$1.operate(UpdatableRecordImpl.java:136) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:132) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:124) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at keywhiz.service.daos.ClientDAO.createClient(ClientDAO.java:67) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at keywhiz.service.providers.ClientAuthFactory$MyAuthenticator.authenticate(ClientAuthFactory.java:122) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at keywhiz.service.providers.ClientAuthFactory$MyAuthenticator.authenticate(ClientAuthFactory.java:91) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at keywhiz.service.providers.ClientAuthFactory.provide(ClientAuthFactory.java:68) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at keywhiz.service.providers.AuthResolver$AuthValueFactoryProvider$1.provide(AuthResolver.java:78) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at keywhiz.service.providers.AuthResolver$AuthValueFactoryProvider$1.provide(AuthResolver.java:76) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.glassfish.jersey.server.spi.internal.ParamValueFactoryWithSource.provide(ParamValueFactoryWithSource.java:71) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.glassfish.jersey.server.spi.internal.ParameterValueHelper.getParameterValues(ParameterValueHelper.java:89) ~[keywhiz-server-0.8.0-shaded.jar:na]
! at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$AbstractMethodParamInvoker.getParamValues(JavaResourceMethodDispatcherProvider.java:127) ~[keywhiz-server-0.8.0-shaded.jar:na]
<===========================================>
Running the keywhiz migrate command:
java -jar /opt/keywhiz-master/server/target/keywhiz-server-0.8.0-shaded.jar migrate /opt/keywhiz-master/server/target/classes/test-db
Output:
INFO [2022-07-18 21:21:27,962] org.flywaydb.core.internal.dbsupport.DbSupportFactory: Database: jdbc:pgsql://10.51.181.93/keywhiz_db_stg (PostgreSQL 13.6)
INFO [2022-07-18 21:21:28,379] org.flywaydb.core.internal.command.DbValidate: Validated 11 migrations (execution time 00:00.303s)
INFO [2022-07-18 21:21:29,166] org.flywaydb.core.internal.command.DbMigrate: Current version of schema "public": 6
INFO [2022-07-18 21:21:29,196] org.flywaydb.core.internal.command.DbMigrate: Schema "public" is up to date. No migration necessary.
For anyone who is migration from MYSQL to PostgreSQL, the issue above was resolved by altering the table clients column id using the pgadmin tool.
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 2 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),