SchemaMigrator explodes on .sql files containing multiple statements

[Ubehebe]

The SchemaMigrator docs state:

misk/misk-jdbc/src/main/kotlin/misk/jdbc/SchemaMigrator.kt

Line 83 in c3869b5

* Each file should contain SQL statements terminated by a `;`. The files should be named like

This is not accurate. In reality, each file must contain exactly one SQL statement. When a migration file contains multiple SQL statements, running the schema migrator results in an exception like this:

java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET GLOBAL explicit_defaults_for_timestamp = FALSE' at line 8
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
	at com.mysql.cj.util.Util.handleNewInstance(Util.java:192)
	at com.mysql.cj.util.Util.getInstance(Util.java:167)
	at com.mysql.cj.util.Util.getInstance(Util.java:174)
	at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)
	at com.mysql.cj.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:893)
	at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:796)
	at io.opentracing.contrib.jdbc.JdbcTracingUtils.call(JdbcTracingUtils.java:104)
	at io.opentracing.contrib.jdbc.TracingStatement.executeBatch(TracingStatement.java:107)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at io.opentracing.contrib.common.WrapperProxy$1.invoke(WrapperProxy.java:73)
	at com.sun.proxy.$Proxy75.executeBatch(Unknown Source)
	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeBatch(HikariProxyStatement.java)
	at misk.jdbc.SchemaMigrator$applyAll$1$1.invoke(SchemaMigrator.kt:217)

It's pretty clear why this is happening. SchemaMigrator reads the entire contents of a migration file into a single string:

misk/misk-jdbc/src/main/kotlin/misk/jdbc/SchemaMigrator.kt

Line 210 in c3869b5

val migrationSql = resourceLoader.utf8(migration.path)

then passes that string to Statement.addBatch:

misk/misk-jdbc/src/main/kotlin/misk/jdbc/SchemaMigrator.kt

Line 216 in c3869b5

migrationStatement.addBatch(migrationSql)

But the addBatch documentation expects the argument to be a single command:

Adds the given SQL command to the current list of commands for this Statement object.

As far as I can tell via go/codesearch, no service that uses SchemaMigrator has any migration file containing more than 1 statement.

This is quite an unusual restriction. Our service uses other tools that read .sql files (namely jooq), and they work fine with multiple statements.

[Ubehebe]

https://github.com/squareup/cash-socialite/pull/1470