CODEWITHSUNDEEP
foreign-keyssqlexceptionaudit-trailjavers
Shiva Jain
Shiva Jain

Reputation: 1

Javers - foreign key constraint fails with mysql

I am new in javers. Started a POC for my application but getting SQL_EXCEPTION while committing, due to 'last_insert_id()' function of mysql returns '0'.

What I did:

Problem:

I am using spring boot 2.0 with mysql version 5.7 I debugged the problem in deep and found that When Javers code 'org.javers.repository.sql.session.Session.java' method 'executeInsertAndGetSequence' line no 40, tries to find primary key it return as zero '0'. As per code, mysql dialect does not support sequences so it generate a value from keyGenerator using mysql funtion 'last_insert_id()' at eventually it returns zero.

I got following error: 

SQL_EXCEPTION: Cannot add or update a child row: a foreign key constraint fails (`jv_snapshot`, CONSTRAINT `jv_snapshot_commit_fk` FOREIGN KEY (`commit_fk`) REFERENCES `jv_commit` (`commit_pk`))\nwhile executing sql: INSERT INTO thinkhr_portal.jv_snapshot ( type, global_id_fk, commit_fk, version, state, changed_properties, managed_type ) VALUES  ( ?,?,?,?,?,?,? )

I wonder, if javers does not support mysql version 5.7 or there is something else I need to take care in my javers configuration.

Upvotes: 0

Views: 663

Answers (2)

Chris Par
Chris Par

Reputation: 1

Egor's answer helps.

Need to create a Custom Connection Provider to reuse existing connection.

public class JaversConnectionProvider implements ConnectionProvider  {
    private final DataSource dataSource;
    private Connection conn;

    public JaversConnectionProvider(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public Connection getConnection() throws SQLException {
        if (conn == null || conn.isClosed()) {
            conn = buildNewConnection();
        }
        return conn;
    }

    private Connection buildNewConnection() throws SQLException {
        // Obtain a new connection from the DataSource
        return dataSource.getConnection();
    }
}

and use it in Javers Configuration

@Configuration
public class JaversConfiguration {

    @Bean
    public Javers javers(DataSource dataSource) {

        JaversConnectionProvider connectionProvider = new JaversConnectionProvider(dataSource);
        JaversSqlRepository sqlRepository = SqlRepositoryBuilder
                                                .sqlRepository()
                                                .withConnectionProvider(connectionProvider)
                                                .withDialect(DialectName.MYSQL)
                                                .build();

        return JaversBuilder
            .javers()
            .registerJaversRepository(sqlRepository)
            .build();
    }
}

Upvotes: 0

Egor Gryaznov
Egor Gryaznov

Reputation: 11

I ran into the same issue, and it was due to creating a new connection every time in the ConnectionProvider. Javers was trying to get the commit id by running select last_insert_id(), and in the new connection, it always returns 0.

You want your connection provider to be something like this:

        ConnectionProvider connectionProvider = new ConnectionProvider() {
            private Connection conn;
            @Override
            public Connection getConnection() throws SQLException {
                if (conn == null) {
                    conn = buildNewConnection();
                }
                return conn;
            }
        };

Upvotes: 1

Related Questions