MySQL autocommit always is true and I can't use rollback()

[norappmob]

Hi. I'm using: HikariCP 5.0.1 Kotlin 1.9.0 mysql-connector-java:8.0.33

I'm learning to use it and in a test of inserting 2 records into a table and then doing rollback(), it gives the following error:

2023-11-02 12.27:53.727 [Test worker] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting... 2023-11-02 12.27:53.969 [Test worker] INFO com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@35e478f 2023-11-02 12.27:53.970 [Test worker] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed. 2023-11-02 12.27:53.992 [Test worker] WARN c.zaxxer.hikari.pool.ProxyConnection - HikariPool-1 - Connection com.mysql.cj.jdbc.ConnectionImpl@35e478f marked as broken because of SQLSTATE(08003), ErrorCode(0) java.sql.SQLNonTransientConnectionException: Can't call rollback when autocommit=true at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:111) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:98) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:90) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:64) at com.mysql.cj.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:1792) at com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:385) at com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java) at kotliquery.Connection.rollback(Connection.kt:27) ... It seems that the solution is to set autocommit to 0 or false but I have tried several ways and it never works. How should I do it? Greetings.

[norappmob]

I have changed this line and now it works: mySesion.connection.rollback() for this new one: mySesion.connection.underlying.rollback()

[lfbayer]

You say that you set autocommit to true, but the error indicates it is not true. So there must be some configuration problem. Can you show us your pool configuration properties to see how you are setting autocommit?

[norappmob]

It took me a while to respond. Now it works fine, but the project has undergone many changes so I don't know how I did it at the beginning. I send a test that I just carried out. Thanks for answering.

get("/pruebas_insert") {
        sessionOf(dataSource).use { dbSess ->
            dbSess.connection.underlying.autoCommit = false
            dbSess.update( queryOf("INSERT INTO pruebas VALUES (1)") )
            log.debug("insert 1")
            dbSess.update( queryOf("INSERT INTO pruebas VALUES (2)") )
            log.debug("insert 2")
            dbSess.connection.underlying.rollback()
            log.debug("insert rollback")
            log.debug("autocommit= ${dbSess.connection.underlying.autoCommit}")
            dbSess.connection.underlying.autoCommit = true
        }
}

Log output:

...Main - insert 1
...Main - insert 2
...Main - insert rollback
...Main - autocommit= false