with-transaction MySQL

[irigarae]

version

Babashka 0.6.5
org.babashka/mysql 0.0.8

platform

Mac and Linux (tested on both)

problem

Running some query within with-transaction gives unexpected behaviour. These are some results seen after running the same query multiple times:

• clojure.lang.ExceptionInfo: Buffer length is less then expected payload length
• [{:next.jdbc/update-count 0}]
• clojure.lang.ExceptionInfo: Index 7 out of bounds for length 7
• clojure.lang.ExceptionInfo: Index 6 out of bounds for length 2
• Correct expected response (a few times)
• stuck (never returns)

repro

Having an accessible MySQL DB (tested on v5.7 and v8.0)

(require '[babashka.pods :as pods])
(pods/load-pod 'org.babashka/mysql "0.0.8")
(require '[pod.babashka.mysql :as mysql])

(def db {:dbtype "mysql"
         :host "localhost"
         :port 3306
         :dbname "test"
         :user "root"
         :password ""})

(def con (mysql/get-connection db))

(mysql/execute! con ["drop table if exists foo"])
(mysql/execute! con ["create table foo (a int)"])
(mysql/execute! con ["insert into foo values (1), (2), (3)"])
(mysql/execute! con ["select * from foo"])

(mysql/with-transaction [tx con]
  ;(Thread/sleep 200)
  (mysql/execute! tx ["select * from foo"]))

Running the transaction multiple times gives different results. Running (mysql/execute! con ["select * from foo"]) gives always the same result. Uncommenting (Thread/sleep 200) reduces the amount of erratic behaviour considerably.

expected behavior

I would expect

(mysql/with-transaction [tx con]
  (mysql/execute! tx ["select * from foo"]))

to return always the same thing as

(mysql/execute! con ["select * from foo"])

[borkdude]

@irigarae That's pretty weird. Can you confirm that this doesn't happen with a JVM setup where you use next.jdbc directly in Clojure?

[euccastro]

@borkdude @irigarae's coworker here. The same example works fine every time in the JVM (tested by just substituting (require '[next.jdbc :as mysql]) for the initial three expressions in @irigarae's description).

[borkdude]

Are you running the query in a loop, multiple times, or is the above exactly what you are running? So can I reproduce this by just invoking the above script multiple times?

[euccastro]

The above should reproduce the errors most of the times you run it, except you may need to comment out the following line the first time:

(mysql/execute! con ["drop table foo"])

[borkdude]

Thanks!

[borkdude]

Note to self, I can reproduce with the following docker invocation:

(require '[babashka.pods :as pods])
(pods/load-pod 'org.babashka/mysql "0.0.8")
(require '[pod.babashka.mysql :as mysql])

;; running mysql in docker with:
;; docker run --name=mysql-pod-repro -p3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_DATABASE=test -e MYSQL_ROOT_HOST=% -d mysql/mysql-server:8.0.20

(def db {:dbtype "mysql"
         :host "localhost"
         :port 3306
         :dbname "test"
         :user "root"
         :password "my-secret-pw"})

(def con (mysql/get-connection db))

(mysql/execute! con ["drop table if exists foo"])
(mysql/execute! con ["create table foo (a int)"])
(mysql/execute! con ["insert into foo values (1), (2), (3)"])
(mysql/execute! con ["select * from foo"])

(mysql/with-transaction [tx con]
  ;; (Thread/sleep 200)
  (mysql/execute! tx ["select * from foo"]))

[borkdude]

I added a branch called issue-47 where I added some logging to the SQL pod and, while I can reproduce the problem, it's not obvious to me where the problem lies. Sometimes it's about to do a query to mySQL but just hangs forever, or mySQL throws an exception with "Index 7 out of bounds for length 7".

[borkdude]

@john-shaffer As you contributed the MySQL support, if you have any ideas, please chime in.

[borkdude]

I added a dotimes around with-transaction in the tests for MariaDB and PostgreSQL. Both tests are still passing. But testing against a MySQL in Docker fails. Theoretically this could also be a bug in MySQL somehow?

[john-shaffer]

I see the same behavior, with MariaDB in podman working but MySQL in podman working inconsistently.

However, the code compiled as a Clojure native-image project using next.jdbc works fine with both databases.

[borkdude]

It could be a multithreading issue. The pod uses a thread for running the transaction. Perhaps MySQL connections aren’t thread safe or so.

[borkdude]

I believe I have found a fix. It was indeed a multi-threading issue but the issue was within the pod. I'll add the fix. @john-shaffer Is there a way to add "real" MySQL to the tests similar to how it's done for MariaDB?

[borkdude]

Fix pushed!

[borkdude]

@irigarae @euccastro If you could test with this updated pod:

• linux: https://1928-261980534-gh.circle-artifacts.com/0/release/pod-babashka-mysql-0.0.9-SNAPSHOT-linux-static-amd64.zip
• macos: https://1931-261980534-gh.circle-artifacts.com/0/release/pod-babashka-mysql-0.0.9-SNAPSHOT-macos-amd64.zip

then I'll make a new release after you have confirmed the bug is fixed.

You can load the pod using (babashka.pods/load-pod "./pod-babashka-mysql") after downloading and unzipping it into your local directory. Note that on macOS you can best do this in a shell using curl and tar to bypass the macOS quarantine stuff.

[irigarae]

@borkdude Testing from my side indeed the erratic behaviour is gone.

[borkdude]

Good, I will release a new version later this week.

[john-shaffer]

Great work!

I think the best option for testing against MySQL is to use a container.

[borkdude]

Released the new pod as version 0.1.0, also available via the registry.