One of the ways I'm trying to work around Flyway issue 156 (see here) is to rely on Java Migrations. To that end, I've written one based on the sample in order to establish the initial state of the database.
First, I grab the initial state of the database (mysql 5.5) and put it into a text file for the migration to read:
mysqldump -u root -p myProject > db/migration/_V1__baseline.sql
Then, I write a Java Migration to load that state into a new database when it's first created:
package com.myCompany.myProject.migration;
import com.googlecode.flyway.core.migration.java.JavaMigration;
import org.springframework.jdbc.core.JdbcTemplate;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import org.springframework.dao.DataAccessException;
public class V1__baseline implements JavaMigration {
@Override
public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
BufferedReader stream = null;
String sqlCode = "";
String line;
try {
stream = new BufferedReader(new FileReader("db/migration/_V1__baseline.sql"));
while ((line = stream.readLine()) != null) {
sqlCode += line + '\n';
}
jdbcTemplate.execute(sqlCode);
}catch(IOException e){
System.out.println("File error: "+ e.getMessage());
}catch(DataAccessException e){
System.out.println("Data access exception: "+ e.getMessage());
}catch(Exception e){
System.out.println("Generic Exception: "+ e.getMessage());
} finally {
if (stream != null) {
stream.close();
}
}
}
}
Then, to try it out:
mvn compile flyway:clean flyway:init flyway:migrate
And the result:
[INFO] --- flyway-maven-plugin:1.5:migrate (default-cli) @ elysium-unity-server ---
[WARNING] Unable to find path for sql migrations: db/migration
[WARNING] Unable to find path for sql migrations: db/migration
[WARNING] Unable to find path for sql migrations: db/migration
[INFO] Validated 0 migrations (mode: ALL) (execution time 00:00.002s)
[INFO] Current schema version: 0
[INFO] Migrating to version 1
Data access exception: StatementCallback; bad SQL grammar [-- MySQL dump 10.13 Distrib 5.5.19, for Win64 (x86)
--
-- Host: localhost Database: myProject
-- ------------------------------------------------------
-- Server version 5.5.19
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
... lots more lines ...
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2012-02-03 12:51:33
]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COL' at line 8
So it looks like mysql syntax error. But the syntax was generated by the same database that I'm trying to feed it to. So I'm guessing there must be something in the middle that is getting in the way. Any suggestions? I'd like to understand why this exact method isn't working, but I will also gratefully accept suggestions for other ways of accomplishing my ultimate goal.
