In JDBC init script with CREATE TABLE, comment with unmatched single-quote causes SQLSyntaxErrorException

[mfickett]

I have a Kotlin Micronaut gRPC app that uses testcontainers via a datasource JDBC URL in its application-test.yml:

datasources:
  default:
    url: jdbc:tc:mysql:5.6:///mydb?TC_INITSCRIPT=file:src/test/create_mydb_schema.sql
    driverClassName: org.testcontainers.jdbc.ContainerDatabaseDriver

If I use any of these contents for my create_mydb_schema.sql init script, the script is executed without error:

-- it's

SELECT 1;

-- It's fine this way.

SELECT 'string';

-- it is

CREATE TABLE t (
  v varchar(255)
) ENGINE=InnoDB;

-- it's what's

CREATE TABLE t (
  v varchar(255)
) ENGINE=InnoDB;

I confirm that the script is being read with this line:

    19:22:26.551 [kotest-engine-0 @coroutine#2] INFO  org.testcontainers.ext.ScriptUtils - Executed database script from file:src/test/create_mydb_schema.sql in 19 ms.

However, with this init script I get a syntax error:

-- it's

CREATE TABLE t (
  v varchar(255)
) ENGINE=InnoDB;

(As a sanity check, I can do source /path/to/.../src/test/create_mydb_schema.sql with that contents and it's fine.)

This also gets a syntax error:

-- it's what's
-- that's

CREATE TABLE t (
  v varchar(255)
) ENGINE=InnoDB;

    19:35:16.818 [kotest-engine-0 @coroutine#2] INFO  org.testcontainers.ext.ScriptUtils - Executing database script from file:src/test/create_mydb_schema.sql
    19:35:17.913 [kotest-engine-0 @coroutine#2] ERROR com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Exception during pool initialization.
    org.testcontainers.ext.ScriptUtils$UncategorizedScriptException: Failed to execute database script from resource [-- It's not fine.

    CREATE TABLE `t` (
      `v` varchar(255) NOT NULL DEFAULT ''
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    ]
        at org.testcontainers.ext.ScriptUtils.executeDatabaseScript(ScriptUtils.java:375)
        at org.testcontainers.ext.ScriptUtils.executeDatabaseScript(ScriptUtils.java:313)
        at org.testcontainers.jdbc.ContainerDatabaseDriver.runInitScriptIfRequired(ContainerDatabaseDriver.java:196)
        at org.testcontainers.jdbc.ContainerDatabaseDriver.connect(ContainerDatabaseDriver.java:132)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358)
        ... snip ...
        at java.base/java.lang.Thread.run(Thread.java:832)
    Caused by: org.testcontainers.ext.ScriptUtils$ScriptStatementFailedException: Script execution failed (file:src/test/create_magix_schema.sql:1): CREATE TABLE `t` (
        at org.testcontainers.jdbc.JdbcDatabaseDelegate.execute(JdbcDatabaseDelegate.java:49)
        at org.testcontainers.delegate.AbstractDatabaseDelegate.execute(AbstractDatabaseDelegate.java:34)
        at org.testcontainers.ext.ScriptUtils.executeDatabaseScript(ScriptUtils.java:362)
        ... 54 common frames omitted
    Caused by: java.sql.SQLSyntaxErrorException: 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 '' at line 1
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
        at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
        at org.testcontainers.jdbc.JdbcDatabaseDelegate.execute(JdbcDatabaseDelegate.java:42)
        ... 56 common frames omitted

[amackeyitx]

this error message, "Failed to execute database script from resource [-- It's not fine." (bolding is mine) doesn't actually match your test cases, which makes me think what you're testing isn't what you're editing ... or vice versa?

[amackeyitx]

did you try / my comments and 'stuff here / rather than -- oneliner comment style? Is it possible that commentPrefix (which defaults to "--" is being over-ridden somewhere else? Grasping at (and then throwing) straws for you ...

[haneeshr]

I suspect this is because ScriptUtils#containsSqlScriptDelimiters does not take comments into consideration. So if the scripts contains a literal ' in a comment it can potentially lead to undesired behavior.

[eddumelendez]

Nowadays, we are suggesting use withCopyFileToContainer in MySQLContainer and put the scripts under /docker-entrypoint-initdb.d as is documented here. Also, given you are using Testcontainers JDBC URL we encourage to use Flyway or Liquibase.