Execution error while trying to migrate perfectly fine SQL script using ragtime

Question

A simple migration attempt:

(ns sindu.migration
  (:require [ragtime.jdbc :as jdbc]
            [ragtime.repl :as repl]))

(def ^:dynamic *db-host* "localhost")
(def ^:dynamic *db-port* 3306)
(def ^:dynamic *db-name* "sindu")
(def ^:dynamic *db-user* "sindu")
(def ^:dynamic *db-user-pass* "sindu")

(def db-config {:classname "com.mysql.jdbc.Driver"
                :subprotocol "mysql"
                :subname (str "//" *db-host* ":" *db-port* "/" *db-name*)
                :user *db-user*
                :password *db-user-pass*})

(def migration-config
  {:datastore  (jdbc/sql-database db-config)
   :migrations (jdbc/load-resources "migrations")})

(repl/migrate migration-config)

The last line fails with the following error:

Applying 001-site
Execution error (SQLSyntaxErrorException) at com.mysql.cj.jdbc.exceptions.SQLError/createSQLException (SQLError.java:120).
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE site_translation (site_id INT(11) NOT NULL,
                       ' at line 7

And the relevant SQL script in question (001-site.up.sql):

-- Create site and site_translation tables

CREATE TABLE site (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                   name VARCHAR(255) NOT NULL,
                   base_url VARCHAR(511) NOT NULL);

CREATE TABLE site_translation (site_id INT(11) NOT NULL,
                               language VARCHAR(5),
                               display_name VARCHAR(255) NOT NULL,
                               FOREIGN KEY (site_id) REFERENCES site(id));

Which works perfectly fine when I do a source 001-site.up.sql from the MySQL console.

What is the reason for this error?

Answer 1

Turns out that we need to insert --;; between separate statements.

-- Create site and site_translation tables

CREATE TABLE site (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                   name VARCHAR(255) NOT NULL,
                   base_url VARCHAR(511) NOT NULL);

--;;

CREATE TABLE site_translation (site_id INT(11) NOT NULL,
                               language VARCHAR(5),
                               display_name VARCHAR(255) NOT NULL,
                               FOREIGN KEY (site_id) REFERENCES site(id));