j/with-transaction does not rollback transaction on Exception

[theronic]

Describe the bug

j/with-transaction does not rollback on Exception

To Reproduce

Create a table in a transaction, throw in the middle of the tx. Tx does not rollback.

Steps to reproduce:

(ns myproject.data
  (:require [next.jdbc :as j]))

(def spec {:dbtype   "mysql"
           :user     "root"
           :password "<password>"
           :dbname   "mydb"})

(def conn (j/get-connection spec))

(defn ensure-schema! [conn]
  (j/with-transaction [tx conn]
    (j/execute! tx
      ["create table stockists (
        id int not null auto_increment,
        name varchar(255),
        phone varchar(255),
        email varchar(255),
        street varchar(255),
        suburb varchar(255),
        country varchar(255),
        image_url text,
        PRIMARY KEY (id)
      )"])
    (throw (Exception. "this should rollback creation of stockists table"))))

(ensure-schema! conn)  ;; this throws, so should rollback, but does not.
=> Execution error at olarm.data/ensure-schema!$fn (data.clj:3).
test
(ensure-schema! conn)  ;;now this fails because stockists exists.
=> Execution error (SQLSyntaxErrorException) at com.mysql.cj.jdbc.exceptions.SQLError/createSQLException (SQLError.java:120).
Table 'stockists' already exists

;; don't expect table to exist, but it does:
(j/execute! conn ["describe stockists;"])
=>
[{:Field "id", :columns/Type "int", :Null "NO", :columns/Key "PRI", :columns/Default nil, :Extra "auto_increment"}
 {:Field "name", :columns/Type "varchar(255)", :Null "YES", :columns/Key "", :columns/Default nil, :Extra ""} ...]

Expected behavior Bad syntax in a transaction should rollback the transaction.

Stacktraces If applicable, add stack traces to help explain your problem.

project.clj/deps.edn

com.github.seancorfield/next.jdbc                   {:mvn/version "1.3.883"}
             mysql/mysql-connector-java                          {:mvn/version "8.0.23"}

Environment (please complete the following information):

• OS: macOS
• Java Version: OpenJDK 20
• Clojure Version: 1.11.1
• Database: MySQL 8.0
• Driver Library Version: mysql/mysql-connector-java {:mvn/version "8.0.23"}

Additional context Add any other context about the problem here.

[theronic]

omw. CREATE TABLE in mySQL causes in implicit commit. INSANITY.

[seancorfield]

FWIW, it's fairly common for databases to auto-commit DDL and for transactions to only work with SQL.