Javers giving Duplicate key name 'jv_global_id_owner_id_fk_idx' when starting from Spring Boot with mySQL

Question

I'm trying to integrate Javers 5.9.0 with my Spring Boot 2.1.13 application with mySQL.

As soon I run my application I'm getting

java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: Duplicate key name 'jv_global_id_owner_id_fk_idx'

Please find the full error stack

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'JaversFromStarter' defined in class path resource [org/javers/spring/boot/sql/JaversSqlAutoConfiguration.class]: Invocation of init method failed; nested exception is java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: Duplicate key name 'jv_global_id_owner_id_fk_idx' at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1771) ~[spring-beans-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:593) ~[spring-beans-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:515) ~[spring-beans-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320) ~[spring-beans-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318) ~[spring-beans-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:847) ~[spring-beans-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:877) ~[spring-context-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549) ~[spring-context-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141) ~[spring-boot-2.1.13.RELEASE.jar:2.1.13.RELEASE] at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:744) [spring-boot-2.1.13.RELEASE.jar:2.1.13.RELEASE] at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:391) [spring-boot-2.1.13.RELEASE.jar:2.1.13.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:312) [spring-boot-2.1.13.RELEASE.jar:2.1.13.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) [spring-boot-2.1.13.RELEASE.jar:2.1.13.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1204) [spring-boot-2.1.13.RELEASE.jar:2.1.13.RELEASE] at [classes/:na] Caused by: java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: Duplicate key name 'jv_global_id_owner_id_fk_idx' at org.javers.repository.sql.schema.JaversSchemaManager.executeSQL(JaversSchemaManager.java:165) ~[javers-persistence-sql-5.9.0.jar:na] at org.javers.repository.sql.schema.JaversSchemaManager.addIndex(JaversSchemaManager.java:343) ~[javers-persistence-sql-5.9.0.jar:na] at org.javers.repository.sql.schema.JaversSchemaManager.addDbIndexOnOwnerId(JaversSchemaManager.java:102) ~[javers-persistence-sql-5.9.0.jar:na] at org.javers.repository.sql.schema.JaversSchemaManager.ensureSchema(JaversSchemaManager.java:61) ~[javers-persistence-sql-5.9.0.jar:na] at org.javers.repository.sql.JaversSqlRepository.ensureSchema(JaversSqlRepository.java:186) ~[javers-persistence-sql-5.9.0.jar:na] at org.javers.spring.jpa.JaversTransactionalDecorator$1.doInTransactionWithoutResult(JaversTransactionalDecorator.java:186) ~[javers-spring-jpa-5.9.0.jar:na] at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:36) ~[spring-tx-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.javers.spring.jpa.JaversTransactionalDecorator.ensureSchema(JaversTransactionalDecorator.java:183) ~[javers-spring-jpa-5.9.0.jar:na] at org.javers.spring.jpa.JaversTransactionalDecorator.afterPropertiesSet(JaversTransactionalDecorator.java:177) ~[javers-spring-jpa-5.9.0.jar:na] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1830) ~[spring-beans-5.1.14.RELEASE.jar:5.1.14.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1767) ~[spring-beans-5.1.14.RELEASE.jar:5.1.14.RELEASE] ... 16 common frames omitted Caused by: java.sql.SQLSyntaxErrorException: Duplicate key name 'jv_global_id_owner_id_fk_idx' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.19.jar:8.0.19] at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.19.jar:8.0.19] at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.19.jar:8.0.19] at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764) ~[mysql-connector-java-8.0.19.jar:8.0.19] at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648) ~[mysql-connector-java-8.0.19.jar:8.0.19] at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-3.2.0.jar:na] at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-3.2.0.jar:na] at org.javers.repository.sql.schema.JaversSchemaManager.executeSQL(JaversSchemaManager.java:160) ~[javers-persistence-sql-5.9.0.jar:na] ... 27 common frames omitted

Answer 1

Found a work around

1. in application.yaml make following changes sqlSchemaManagementEnabled: false. This will stop creating tables at application start up
2. Create the table/indexes manually. Something like

CREATE TABLE javers.jv_commit ( commit_pk BIGINT NOT NULL AUTO_INCREMENT, author VARCHAR(200), commit_date TIMESTAMP(3), commit_date_instant VARCHAR(30), commit_id NUMERIC(22,2), CONSTRAINT jv_commit_pk PRIMARY KEY(commit_pk) ) ENGINE = InnoDB; CREATE INDEX jv_commit_commit_id_idx ON gears_javers.jv_commit(commit_id)

CREATE TABLE javers.jv_commit_property ( property_name VARCHAR(191) NOT NULL, property_value VARCHAR(600), commit_fk BIGINT, CONSTRAINT jv_commit_property_pk PRIMARY KEY(commit_fk, property_name), CONSTRAINT jv_commit_property_commit_fk FOREIGN KEY(commit_fk) REFERENCES gears_javers.jv_commit(commit_pk) ) ENGINE = InnoDB

CREATE INDEX jv_commit_property_commit_fk_idx ON gears_javers.jv_commit_property(commit_fk) CREATE INDEX jv_commit_property_property_name_property_value_idx ON gears_javers.jv_commit_property(property_name,property_value(191))

CREATE TABLE javers.jv_global_id ( global_id_pk BIGINT NOT NULL AUTO_INCREMENT, local_id VARCHAR(191), fragment VARCHAR(200), type_name VARCHAR(200), owner_id_fk BIGINT, CONSTRAINT jv_global_id_pk PRIMARY KEY(global_id_pk), CONSTRAINT jv_global_id_owner_id_fk FOREIGN KEY(owner_id_fk) REFERENCES gears_javers.jv_global_id(global_id_pk) ) ENGINE = InnoDB

CREATE INDEX jv_global_id_local_id_idx ON gears_javers.jv_global_id(local_id); CREATE INDEX jv_global_id_owner_id_fk_idx ON gears_javers.jv_global_id(owner_id_fk);

CREATE TABLE javers.jv_snapshot ( snapshot_pk BIGINT NOT NULL AUTO_INCREMENT, type VARCHAR(200), version BIGINT, state TEXT, changed_properties TEXT, managed_type VARCHAR(200), global_id_fk BIGINT, commit_fk BIGINT, CONSTRAINT jv_snapshot_pk PRIMARY KEY(snapshot_pk), CONSTRAINT jv_snapshot_global_id_fk FOREIGN KEY(global_id_fk) REFERENCES gears_javers.jv_global_id(global_id_pk), CONSTRAINT jv_snapshot_commit_fk FOREIGN KEY(commit_fk) REFERENCES gears_javers.jv_commit(commit_pk) ) ENGINE = InnoDB

CREATE INDEX jv_snapshot_global_id_fk_idx ON gears_javers.jv_snapshot(global_id_fk); CREATE INDEX jv_snapshot_commit_fk_idx ON gears_javers.jv_snapshot(commit_fk); CREATE INDEX jv_snapshot_owner_id_fk_idx ON gears_javers.jv_global_id(owner_id_fk);

Answer 2

Try to reproduce the issue int this test https://github.com/javers/javers/blob/master/javers-persistence-sql/src/test/groovy/org/javers/repository/sql/integration/docker/MySqlDockerIntegrationTest.groovy Then we will fix it