PostgreSQL/MySQL need extra options to do insert-multi! with columns as a single operation

Description

java.jdbc uses a single prepared statement invocation for insert-multi! (columns) and provides each param group as a batch. By default, both PostgreSQL and MySQL run this as multiple statements, one for each batch of parameters (which is counter-intuitive, certainly as far as the JDBC docs are concerned).

However, you can provide options in your db-spec map to address this: PostgreSQL requires :reWriteBatchedInserts true and My SQL requires :rewriteBatchedStatement true (both non-standard JDBC options, of course!). This should cause the drivers to rewrite the SQL to a single insert with repeated values groups, and then it will do a single insert as "expected".

At a minimum, update the docstring for insert-multi! and the clojure-doc documentation.

But also consider actually producing that style of multiple insert SQL for databases known to support it (not all of them do). Perhaps introduce a new option on insert-multi! itself?

Environment

None

Activity

Show:
import
February 7, 2019, 8:40 PM

Comment made by: joellew

I am trying to do a batch insert using insert-multi!, but it is writing multiple insert statements and not one single insert statement. The output of Mysql is showing multiple insert when I was expecting to see single insert with multiple values.

This is the output of Mysql log file showing multiple inserts:
551 Connect prod@localhost as anonymous on ikte_prod
551 Query select @@sql_mode
551 Query select @@autocommit
551 Query SELECT @@tx_isolation
551 Query set autocommit=0
551 Query INSERT INTO ik_kt_port_kvs ( id, eid, prop, val, process, tx ) VALUES ( '4ffc', '4ffc', 'ID', '4ffc', 'KTIER', '4173' )
551 Query INSERT INTO ik_kt_port_kvs ( id, eid, prop, val, process, tx ) VALUES ( '46de', '4ffc', 'TYPE', 'DATA', 'KTIER', '4173' )
551 Query INSERT INTO ik_kt_port_kvs ( id, eid, prop, val, process, tx ) VALUES ( '4af4', '4ffc', 'SOURCE_ID', '4416', 'KTIER', '4173' )
...........................
551 Query COMMIT
551 Query set autocommit=1
551 Quit

This is the codej/insert-multi! db (keyword (rule :table)) (conj [] :id :eid rop :val rocess :tx) @records)
@records is a vector of values:
[[4ffc 4ffc ID 4ffc KTIER 4173]
[46de 4ffc TYPE DATA KTIER 4173]
[4af4 4ffc SOURCE_ID 4416 KTIER 4173]
.....]

This is the db specification:
{:classname com.mysql.jdbc.Driver, :subprotocol mysql, :subname //127.0.0.1:3306/
ikte_prod, :user xxx, assword xxx, :rewriteBatchedStatements true}

This is the db version:
mysql Ver 15.1 Distrib 10.3.9-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

This is the jdbc version:
[org.clojure/java.jdbc "0.7.8"]

These are the dependencies of the project:
[org.clojure/clojure "1.7.0"]
[com.stuartsierra/component "0.2.3"]
[compojure "1.3.3"]
[duct "0.1.1"]
[environ "1.0.0"]
[meta-merge "0.1.1"]
[dire "0.5.4"]
[ring/ring "1.6.0-RC1"]
[ring/ring-core "1.6.0-RC1"]
[ring/ring-defaults "0.2.3"]
[ring-jetty-component "0.3.1"]
[ring-webjars "0.1.1"]
[ring-basic-authentication "1.0.5"]
[ring-cors "0.1.10"]
[org.webjars/normalize.css "3.0.2"]
[liberator "0.13"]
[clj-http "3.3.0"]
[com.taoensso/timbre "3.1.6"]
[com.unboundid/unboundid-ldapsdk "2.3.3"]
[org.clojars.pntblnk/clj-ldap "0.0.9"]
[org.clojure/java.jdbc "0.7.8"]
[com.taoensso/timbre "3.1.6"]
[org.jumpmind.symmetric.jdbc/mariadb-java-client "1.1.1"]
[cheshire "5.5.0"]
[clj-quartz "0.0.3"]
[clj-time "0.12.0"]
[dk.ative/docjure "1.11.0"]
[org.clojure/core.match "0.3.0-alpha4"]
[com.cemerick/pomegranate "0.3.1"]
[buddy/buddy-auth "1.4.1"]
[org.clojure/data.csv "0.1.4"]
[com.bhauman/rebel-readline "0.1.2"]

Thank you for your help.

Sean Corfield
February 21, 2019, 6:15 PM

Joelle got that working after updating the MariaDB driver to a more recent version (it then supported the MySQL-specific rewrite option).

Since this involves non-standard driver options, I am addressing this via a note in the docstring for insert-multi!.

Sean Corfield
February 21, 2019, 6:35 PM

Release 0.7.9 is heading to Maven Central!

Completed

Assignee

Sean Corfield

Reporter

Sean Corfield

Labels

None

Approval

None

Patch

None

Priority

Major
Configure