mybatis / migrations Goto Github PK

View Code? Open in Web Editor NEW

208.0 24.0 76.0 4.54 MB

A command line Database migrations tool

Home Page: http://mybatis.github.io/migrations

License: Apache License 2.0

Java 97.43% JavaScript 2.28% Dockerfile 0.29%

java migration-tool

migrations's People

Contributors

Stargazers

Watchers

migrations's Issues

Display Result Set

Hi,
For the command "up" and "pending" in command line, executed SQL message/result is not displayed. It says only "MyBatis Migrations SUCCESS"

Feature: Allow/Disallow empty UNDO clause for DOWN

When a migration should not be undone, I typically just remove the -- //@UNDO line so that migrate down will fail for this migration. This is what you want when using the migration in production .

However, for development you'd prefer to just ignore the empty undo and execute nothing on the assumption that you'll soon perform a migrate up.

To configure this behaviour for production and development environments, I'm recommending that you add a new boolean property to that will either allow or disallow empty UNDO's for migrate down.

Bootstrap fails with more than one table

Hi! I've got a boostrap.sql that holds many table. migrate bootstrap fails while parsing the second table. This is what the error looks like:

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 IF NOT EXISTS applications (
  uuid varchar(100) NOT NULL,
  secret' at line 38[

If I do by hand mysql -u user -p table < scripts/bootstrap.sql works fine.

------------------------------------------------------------------------
-- MyBatis Migrations - info
------------------------------------------------------------------------
MyBatis Migrations 3.2.0 (${implementation.build})
Java version: 1.7.0_95, vendor: Oracle Corporation
Java home: /usr/lib/jvm/java-1.7.0-openjdk-1.7.0.95-2.6.4.0.el7_2.x86_64/jre
Default locale: en_US, platform encoding: UTF-8
OS name: "Linux", version: "3.10.0-229.el7.x86_64", arch: "amd64", family: "unix"

Any idea??
Thanks.

Support for Runtime Schema Upgrade (i.e. in-app migration)

The goal is to provide a flexible and easy-to-use API for java programs that need migrations at runtime.
The API should provide:

a way to pass DataSource for database operations.
a way to read migration scripts from inside JAR.

The targets of this issue is the following database operation commands.

bootstrap
up
down
status
version
pending

This was originally reported as mybatis/old-google-code-issues#653

Missing migrate script in the zip

I think the migrate script is missing in the zip

Environment Variables for db connection variables.

Given a user that wants to run migrations
When I specify that I want to use environment variables in my environment's setting file

envVariable.enabled=true

And I specify which environment variables to use for the connection settings:

envVariable.driver=DB_DRIVER
envVariable.url=DB_URL
envVariable.username=DB_USERNAME
envVariable.password=DB_PASSWORD

And I run mvn migration:up or mvn migration:pending
Then set up the db connection using the following:
DB_DRIVER for the driver property,
DB_URL for the url property,
DB_USERNAME for the username property,
DB_PASSWORD for the password property
And successfully run the migrations.

I'm willing to code this feature If you would accept the PR.

Better auditing

I have been evaluating several db migration tools and mybatis has been the most promising (+1)

However, one thing that I would love to see is better auditing in the change log.

Specifically:

db user executing the script
Local user connected
Client connection string
Source version tag (which could subsitute as the DB version... maybe?)

This is kinda the schema I had in mind:

CREATE TABLE IF NOT EXISTS CHANGELOG
(
  ordinal INT NOT NULL AUTO_INCREMENT,
  version varchar(50) null,
  description varchar(200) not null COMMENT 'Use _ as delimiter and use file naming convention'l,
  script varchar(1000) not null COMMENT 'Fully qualified path of the script executed',
  hash VARCHAR(64) COMMENT 'sha256 of script',
  tag VARCHAR(64) COMMENT 'current git tag. Maybe use this as the version?',
  client_id VARCHAR(50) COMMENT '$SSH_CLIENT This field should be enough to accomodate ipv6',
  installed_with varchar(100) not null DEFAULT CURRENT_USER,
  installed_by varchar(100) not null COMMENT 'this is the user on the actual localhost $USER',
  installed_on timestamp default CURRENT_TIMESTAMP not null,
  execution_time int not null
);

Based on what I am seeing from this project I can probably achieve this with hooks. It would be great to have it baked in

Stored procs

I am trying to evaluate MyBatis for migrations and it (along with all other Java based tools) fail on the below syntax because of the delimiter swap.

MyBatis at least has a call-out for this in the properties:

# A NOTE ON STORED PROCEDURES AND DELIMITERS
#
# Stored procedures and functions commonly have nested delimiters
# that conflict with the schema migration parsing.  If you tend
# to use procs, functions, triggers or anything that could create
# this situation, then you may want to experiment with
# send_full_script=true (preferred), or if you can't use
# send_full_script, then you may have to resort to a full
# line delimiter such as "GO" or "/" or "!RUN!".
#
# Also play with the autocommit settings, as some drivers
# or databases don't support creating procs, functions or
# even tables in a transaction, and others require it.   
send_full_script=false

So, I don't quite get it. What modifications to which files can I make to get this to go? One procedure per file and send whole file? Change the delimiters in the file? Does anyone have any experience on this? I have tried the mysql driver and the mariadb driver. Both choke.

    DELIMITER ||
    
    CREATE FUNCTION format_time( picoseconds BIGINT UNSIGNED )
    RETURNS varchar(16) CHARSET utf8
       DETERMINISTIC
    BEGIN
    
      IF picoseconds IS NULL THEN
    
        RETURN NULL;
    
      ELSE 
    
        RETURN CAST(CONCAT(ROUND(picoseconds / 1000000000000, 2)) AS DECIMAL(10,6));
    
      END IF;
    
    END;
    ||
    
    DELIMITER ;

Update documentation

Features such as HOOKPATH/SCRIPTPATH/DRIVERPATH etc should be documented in --help and the online docs

Pending migrations doesn't picked up by up command

Here is a little example of unexpected behavior. I use maven plugin for this, but I run into with my cli too.

git clone https://github.com/Bessonov/mybatis-migrations-up-pending.git
cd mybatis-migrations-up-pending

# edit db connection
vim migrations/environments/development.properties

# two scripts are pending
mvn migration:status

# run migrations
mvn migration:up

# simulate merge with other team member
mv migrations/scripts/20170520092916_first_migration.deactivated migrations/scripts/20170520092916_first_migration.sql

# shows pending migration in between
mvn migration:status

# nothing happens
mvn migration:up

# but now it's picked up
mvn migration:pending

script command understands parameters wrong way

MyBatis migrations version 3.1.0
Given:
Migration "1"
CREATE TABLE t1;
--//@undo
DROP TABLE t1;
Migration "2"
CREATE TABLE t2;
--//@undo
DROP TABLE t2;

run: migrate script 1 2
expected result:
CREATE TABLE t2;
actual result:
CREATE TABLE t1;
CREATE TABLE t2;

run: migrate script 2 2
expected result: error
actual result:
CREATE TABLE t2;

run: migrate script 2 1
expected result:
DROP TABLE t2;
actual result:
DROP TABLE t1;
DROP TABLE t2;

Even if my expectation are wrong - there is no way to build a script to UNDO only 1 migration.

migration succeeds even if compiling procedures failed

hi,

we are using MyBatis for applying DB changes and in 1 of the script there was a procedure which we were creating. MyBatis removed the comments while performing migraitons and that lead to procedure not compiling successfully. However Mybatis migraiton did not fail the execution but it provided Success status.

When we went to Database to check the procedure, it was broken.

Please can you confirm if MyBatis migrations can be used to Create/Replace Procedures,Functions, Packages in Oracle database.

Thanks

MySQLSyntaxErrorException On First Migration

Hello!

I'm evaluating your tool for use with our company. I'm running into an issue just running MIGRATE INIT followed immediately by MIGRATE UP. I've experienced the same error with 3.2.0, 3.2.1, and 3.3.0. I'm executing against a MySQL v5.7.12 running on a fully patched Windows 2012 R2 x64 server. the JDBC driver is mysql-connector-java-5.1.39-bin.jar.

I have copied the SQL out of the error and pasted it into MySQL Workbench and executed it against the target database with the same account MIGRATE would be running under without error:

Without --trace

PS C:\DB-Backup\mybatis-migrations-3.2.1\migrations> migrate up
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=128m; support was removed in 8.0
------------------------------------------------------------------------
-- MyBatis Migrations - up
------------------------------------------------------------------------
Fri Jun 03 12:31:21 PDT 2016 WARN: Establishing SSL connection without server's identity verification is not recommended
. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit
option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to
 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore f
or server certificate verification.
Fri Jun 03 12:31:22 PDT 2016 WARN: Establishing SSL connection without server's identity verification is not recommended
. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit
option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to
 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore f
or server certificate verification.
========== Applying: 20160603192400_create_changelog.sql =======================
--
--    Copyright 2010-2016 the original author or authors.
--
--    Licensed under the Apache License, Version 2.0 (the "License");
--    you may not use this file except in compliance with the License.
--    You may obtain a copy of the License at
--
--       http://www.apache.org/licenses/LICENSE-2.0
--
--    Unless required by applicable law or agreed to in writing, software
--    distributed under the License is distributed on an "AS IS" BASIS,
--    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--    See the License for the specific language governing permissions and
--    limitations under the License.
--

--  Create Changelog

-- Default DDL for changelog table that will keep
-- a record of the migrations that have been run.

-- You can modify this to suit your database before
-- running your first migration.

-- Be sure that ID and DESCRIPTION fields exist in
-- BigInteger and String compatible fields respectively.

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);


Error executing: --
--    Copyright 2010-2016 the original author or authors.
--
--    Licensed under the Apache License, Version 2.0 (the "License");
--    you may not use this file except in compliance with the License.
--    You may obtain a copy of the License at
--
--       http://www.apache.org/licenses/LICENSE-2.0
--
--    Unless required by applicable law or agreed to in writing, software
--    distributed under the License is distributed on an "AS IS" BASIS,
--    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--    See the License for the specific language governing permissions and
--    limitations under the License.
--

--  Create Changelog

-- Default DDL for changelog table that will keep
-- a record of the migrations that have been run.

-- You can modify this to suit your database before
-- running your first migration.

-- Be sure that ID and DESCRIPTION fields exist in
-- BigInteger and String compatible fields respectively.

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);

.  Cause: 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 'ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id)' at line 34
------------------------------------------------------------------------
-- MyBatis Migrations FAILURE
-- Total time: 1s
-- Finished at: Fri Jun 03 12:31:22 PDT 2016
-- Final Memory: 16M/483M
------------------------------------------------------------------------

ERROR: Error executing command.  Cause: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: --
--    Copyright 2010-2016 the original author or authors.
--
--    Licensed under the Apache License, Version 2.0 (the "License");
--    you may not use this file except in compliance with the License.
--    You may obtain a copy of the License at
--
--       http://www.apache.org/licenses/LICENSE-2.0
--
--    Unless required by applicable law or agreed to in writing, software
--    distributed under the License is distributed on an "AS IS" BASIS,
--    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--    See the License for the specific language governing permissions and
--    limitations under the License.
--

--  Create Changelog

-- Default DDL for changelog table that will keep
-- a record of the migrations that have been run.

-- You can modify this to suit your database before
-- running your first migration.

-- Be sure that ID and DESCRIPTION fields exist in
-- BigInteger and String compatible fields respectively.

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);

.  Cause: 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 'ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id)' at line 34
PS C:\DB-Backup\mybatis-migrations-3.2.1\migrations>

with --Trace

PS C:\DB-Backup\mybatis-migrations-3.2.1\migrations> migrate --trace up
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=128m; support was removed in 8.0
------------------------------------------------------------------------
-- MyBatis Migrations - up
------------------------------------------------------------------------
Fri Jun 03 12:34:32 PDT 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Fri Jun 03 12:34:32 PDT 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
========== Applying: 20160603192400_create_changelog.sql =======================
--
--    Copyright 2010-2016 the original author or authors.
--
--    Licensed under the Apache License, Version 2.0 (the "License");
--    you may not use this file except in compliance with the License.
--    You may obtain a copy of the License at
--
--       http://www.apache.org/licenses/LICENSE-2.0
--
--    Unless required by applicable law or agreed to in writing, software
--    distributed under the License is distributed on an "AS IS" BASIS,
--    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--    See the License for the specific language governing permissions and
--    limitations under the License.
--

--  Create Changelog

-- Default DDL for changelog table that will keep
-- a record of the migrations that have been run.

-- You can modify this to suit your database before
-- running your first migration.

-- Be sure that ID and DESCRIPTION fields exist in
-- BigInteger and String compatible fields respectively.

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);


Error executing: --
--    Copyright 2010-2016 the original author or authors.
--
--    Licensed under the Apache License, Version 2.0 (the "License");
--    you may not use this file except in compliance with the License.
--    You may obtain a copy of the License at
--
--       http://www.apache.org/licenses/LICENSE-2.0
--
--    Unless required by applicable law or agreed to in writing, software
--    distributed under the License is distributed on an "AS IS" BASIS,
--    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--    See the License for the specific language governing permissions and
--    limitations under the License.
--

--  Create Changelog

-- Default DDL for changelog table that will keep
-- a record of the migrations that have been run.

-- You can modify this to suit your database before
-- running your first migration.

-- Be sure that ID and DESCRIPTION fields exist in
-- BigInteger and String compatible fields respectively.

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);

.  Cause: 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 'ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id)' at line 34
------------------------------------------------------------------------
-- MyBatis Migrations FAILURE
-- Total time: 1s
-- Finished at: Fri Jun 03 12:34:32 PDT 2016
-- Final Memory: 16M/483M
------------------------------------------------------------------------

ERROR: Error executing command.  Cause: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: --
--    Copyright 2010-2016 the original author or authors.
--
--    Licensed under the Apache License, Version 2.0 (the "License");
--    you may not use this file except in compliance with the License.
--    You may obtain a copy of the License at
--
--       http://www.apache.org/licenses/LICENSE-2.0
--
--    Unless required by applicable law or agreed to in writing, software
--    distributed under the License is distributed on an "AS IS" BASIS,
--    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--    See the License for the specific language governing permissions and
--    limitations under the License.
--

--  Create Changelog

-- Default DDL for changelog table that will keep
-- a record of the migrations that have been run.

-- You can modify this to suit your database before
-- running your first migration.

-- Be sure that ID and DESCRIPTION fields exist in
-- BigInteger and String compatible fields respectively.

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);

.  Cause: 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 'ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id)' at line 34org.apache.ibatis.migration.MigrationException: Error executing command.  Cause: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: --
--    Copyright 2010-2016 the original author or authors.
--
--    Licensed under the Apache License, Version 2.0 (the "License");
--    you may not use this file except in compliance with the License.
--    You may obtain a copy of the License at
--
--       http://www.apache.org/licenses/LICENSE-2.0
--
--    Unless required by applicable law or agreed to in writing, software
--    distributed under the License is distributed on an "AS IS" BASIS,
--    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--    See the License for the specific language governing permissions and
--    limitations under the License.
--

--  Create Changelog

-- Default DDL for changelog table that will keep
-- a record of the migrations that have been run.

-- You can modify this to suit your database before
-- running your first migration.

-- Be sure that ID and DESCRIPTION fields exist in
-- BigInteger and String compatible fields respectively.

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);

.  Cause: 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 'ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id)' at line 34
        at org.apache.ibatis.migration.operations.UpOperation.operate(UpOperation.java:99)
        at org.apache.ibatis.migration.commands.UpCommand.execute(UpCommand.java:37)
        at org.apache.ibatis.migration.CommandLine.runCommand(CommandLine.java:65)
        at org.apache.ibatis.migration.CommandLine.execute(CommandLine.java:42)
        at org.apache.ibatis.migration.Migrator.main(Migrator.java:20)
Caused by: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: --
--    Copyright 2010-2016 the original author or authors.
--
--    Licensed under the Apache License, Version 2.0 (the "License");
--    you may not use this file except in compliance with the License.
--    You may obtain a copy of the License at
--
--       http://www.apache.org/licenses/LICENSE-2.0
--
--    Unless required by applicable law or agreed to in writing, software
--    distributed under the License is distributed on an "AS IS" BASIS,
--    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--    See the License for the specific language governing permissions and
--    limitations under the License.
--

--  Create Changelog

-- Default DDL for changelog table that will keep
-- a record of the migrations that have been run.

-- You can modify this to suit your database before
-- running your first migration.

-- Be sure that ID and DESCRIPTION fields exist in
-- BigInteger and String compatible fields respectively.

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);

.  Cause: 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 'ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id)' at line 34
        at org.apache.ibatis.jdbc.ScriptRunner.executeFullScript(ScriptRunner.java:130)
        at org.apache.ibatis.jdbc.ScriptRunner.runScript(ScriptRunner.java:105)
        at org.apache.ibatis.migration.operations.UpOperation.operate(UpOperation.java:70)
        ... 4 more
Caused by: 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 'ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id)' at line 34
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
        at com.mysql.jdbc.Util.getInstance(Util.java:387)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503)
        at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:839)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:739)
        at org.apache.ibatis.jdbc.ScriptRunner.executeStatement(ScriptRunner.java:235)
        at org.apache.ibatis.jdbc.ScriptRunner.executeFullScript(ScriptRunner.java:125)
        ... 6 more
PS C:\DB-Backup\mybatis-migrations-3.2.1\migrations>

Problem setting the classLoader

Hi. I'm making a gradle plugin to integrate mybatis migrations. After migrating my source code to version version 3.1.0 of mybatis-migrations the same code that worked before the upgrade stopped working. It seems that the new behavior is ignoring my attempt to set the driver classloader as can be seen here

Does “migrate new” automatically generate the changes?

With the "migrate new" command, it generates a sql file:

-- // create blog table
-- Migration SQL that makes the change goes here.

-- //@UNDO
-- SQL to undo the change goes here.

Does the command detect my table and data changes and fill in with the script, such as Alter Table query, automatically? Or I have to fill in the scripts manually?

mybatis up doesn't work out-of-the-box

Using mysql:5.6 on docker
docker run -d --name mybatis -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw mysql:5.6

I follow the directions
migrate --idpattern=00000 init

Which produces:
./drivers
./environments
./scripts
./scripts/bootstrap.sql
./scripts/00001_create_changelog.sql

So far, so good.

put mysql driver in the drivers directory:

mysql-connector-java-5.1.40-bin.jar
md5sum drivers/mysql-connector-java-5.1.40-bin.jar MD5 (drivers/mysql-connector-java-5.1.40-bin.jar) = 2ad5cfbcb388bee5a64c879c208c7652

and now run:
migrate up

Produces:

$ migrate up
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=128m; support was removed in 8.0
------------------------------------------------------------------------
-- MyBatis Migrations - up
------------------------------------------------------------------------
========== Applying: 00001_create_changelog.sql ================================


CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);


Error executing: 

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);

.  Cause: 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 'ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id)' at line 7
------------------------------------------------------------------------
-- MyBatis Migrations FAILURE
-- Total time: 0s
-- Finished at: Wed Oct 11 11:26:11 PDT 2017
-- Final Memory: 15M/479M
------------------------------------------------------------------------

ERROR: Error executing command.  Cause: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: 

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);

.  Cause: 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 'ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id)' at line 7

Override the System Defined JdbcType

In MyBatis it wont override the JdbcType if i specify the JdbcType in the parameter

eg, Select * from table_name where id = #{value,jdbcType=NUMERIC}

for the above statement if the type of value is string , it is set as VARCHAR data-type , Instead of Numeric because of this it impact the performance , the databas wont use the index,

Eventhough i specify as #{value,jdbcType=NUMERIC,javaType=Integer}) , it throws
java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer

bootstrap not working with more than one table

if more than one table is defined in bootstrap.sql file, the bootstrap command will throw SQLSyntaxErrorException.

Here's an example:

-- begin of the bootstrap.sql file
CREATE TABLE card_type (
ID BIGINT(20)
);

CREATE TABLE card (
ID BIGINT(20)
);

-- end of the bootstrap.sql file

Timestamp on init

Hi,

migrate init creates folders and files, including some .sql scripts with timestamps.
Problem is that those sql scripts use default timestamp (time_zone = GMT+0) and I would like to initialize those scripts with my correct timezone (GMT+2).

It should be possible to either

pass a "time_zone" parameter to the init command, so that files are created with the proper timestamp
OR
remove the check for empty directories to the "init" and allow at least a .properties to be there so I can enter the right "time_zone"

Regards,
Roberto

Hive Support

Is Hive supported?

I have added all the jar files that I thought would be needed but I am still getting an error when trying to run the migrations. I am however able to list what migrations have not been run.

List of hive jar driver files:

commons-codec-1.5.jar
commons-logging-1.1.1.jar
hadoop-common-2.6.0.jar
hive-common-0.13.0.jar
hive-jdbc-0.13.0.jar
hive-service-0.13.0.jar
httpclient-4.2.5.jar
httpcore-4.2.5.jar
libthrift-0.9.1.jar
log4j-1.2.17.jar
slf4j-api-1.7.5.jar
slf4j-log4j12-1.7.5.jar

Sample migrate commands:

[dev@sandbox hive]$ migrate status
------------------------------------------------------------------------
-- MyBatis Migrations - status
------------------------------------------------------------------------
ID             Applied At          Description
================================================================================
log4j:WARN No appenders could be found for logger (org.apache.thrift.transport.TSaslTransport).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
20141202195919    ...pending...    test

------------------------------------------------------------------------
-- MyBatis Migrations SUCCESS
-- Total time: 0s
-- Finished at: Mon Aug 03 19:52:32 UTC 2015
-- Final Memory: 22M/480M
------------------------------------------------------------------------
[dev@sandbox hive]$ migrate up
------------------------------------------------------------------------
-- MyBatis Migrations - up
------------------------------------------------------------------------
log4j:WARN No appenders could be found for logger (org.apache.thrift.transport.TSaslTransport).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
========== Applying: 20141202195919_test.sql ===================================
--  Create Changelog
CREATE TABLE dev.mybatis_test(
  source string, 
  siteid string)

Error executing: CREATE TABLE dev.mybatis_test(
  source string, 
  siteid string)
.  Cause: java.sql.SQLException: Method not supported
------------------------------------------------------------------------
-- MyBatis Migrations FAILURE
-- Total time: 1s
-- Finished at: Mon Aug 03 19:52:36 UTC 2015
-- Final Memory: 20M/480M
------------------------------------------------------------------------

ERROR: Error executing command.  Cause: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: CREATE TABLE dev.mybatis_test(
  source string, 
  siteid string)
.  Cause: java.sql.SQLException: Method not supported

migrate new doesn't correctly handle . (dot) in description

If you use dot "." in new migration description, e.g.

$ migrate new 'Make column TABLE.COL not null'

a corresponding SQL script with correct filename is created ("Make_column_TABLE.COL_not_null.sql"). However migrate status command show description trimmed before first ".", e.g.

$ migrate status
.....
20141228134105    ...pending...    Make column TABLE
....

Also, when I "migrate up", incorrect trimmed description is saved to CHANGELOG table.

Migrate fail when postgreSQL issue a Notice

Hello,

i'm currently upgrading Mybatis Migrations from 3.2.0 to last release 3.3.1 in order tu use Hooks, in combination with Java 1.8.0_73 and PostgreSQL 9.5.

I've some scripts that, in order to re-execute without problems, have some initialization SQL code like:

DROP TABLE IF EXISTS FOO

According to PostgreSQL documentation (https://www.postgresql.org/docs/9.5/static/sql-droptable.html) IF EXISTS not throw an error if the table does not exist, throw a Notice (similar to warning), like this:

NOTICE: table "Foo" does not exist, skipping

With migrations version 3.3.1, when database raise this Notice, mybatis abort the execution and no execute scripts.

With migrations 3.2.0 the scripts runs well, but i cant use Hooks.

Any notice about this?

Thanks, best regards.

ERROR: Error copying org/apache/ibatis/migration/template_README on Mac

Steps:

mkdir db
mybatis-migrations-3.3.0/bin/migrate --path="$(pwd)/db" init

Result:
ERROR: Error copying org/apache/ibatis/migration/template_README to /some/path/db/README. Cause: java.io.FileNotFoundException: file:/some/path/mybatis-migrations-3.3.0/lib/mybatis-migrations-3.3.0.jar!/org/apache/ibatis/migration/template_README (No such file or directory)

Feature request: expand the usage of hooks (JSR-223 and SQL) to all migration commands

In my search for a solution to manage test data using MyBatis Migrations I stumbled upon the hooks feature.

I managed to implement a solution by which I use the [before|after]each[up|down] hooks to temporary replace a script which contains test data insert statements with an script containing no sql statements.
This implementation works great if you use the up and down commands to manage each environment.

Because of certain regulations the production environment needs to be maintained using plain old sql files.
Unfortunately the hooks feature only works for the commands up and down.
This means, in my case, that a script, generated by the script command on the same set of migration scripts for the same environment, after execution, will result in a different database compared to using the up command.

Therefore I would like to ask if the hooks feature could also be expanded to the other commands: script, version, pending.

The solution is simple and just needs 3 files (do not forget to change the file extension to .sql and .js)
empty_migration_script.txt
post-process.txt
pre-process.txt

Regards,
Steven Janssens.

Provide pre/post-migration hooks

This is a draft and obsolete!

Please see the documentation. http://www.mybatis.org/migrations/hooks.html

Migration hook

Overview

You can write scripts that are executed before or after up/down operation.
The following hooks are supported.

before up/down : executed before the first migration
before each up/down : executed before each migration
after each up/down : executed after each migration
after up/down : executed after the last migration

SQL and JSR-223 compliant scripting languages are supported for writing hook scripts.

Quick start

Here are what you need to do to use hook scripts.

Create hooks directory.
Create a hook script in hooks directory.
Add settings to the environment properties file.

1. Create hooks directory.

Create a directory named hooks in the base directory.

2. Create a hook script in hooks directory.

The following script outputs the famous string to the log.
Save this script in the hooks directory as hello.js.

// hello.js
print('Hello, World!');

The example is written in JavaScript because JavaScript script engine is shipped with JDK.

3. Add settings to the environment properties file.

To configure Migrations, add the following line to the development.properties in the environment directory.

# development.properties
hook_before_up=JavaScript:hello.js

The details will be explained in the later section, but the above line tells Migrations to execute hello.js at the beginning of migrate up operation.

Now, if you run migrate up, you will find the following lines in the log.

========== Applying JSR-223 hook : hello.js ==========
Hello, World!

NOTE: The hook script will not be executed if there was no pending migration.

Configuration

Keys for available hooks

As shown in the Quick Start section, the key of a hook setting specifies when to execute the script. Here is the list of available hooks.

hook_before_up
hook_before_each_up
hook_after_each_up
hook_after_up
hook_before_down
hook_before_each_down
hook_after_each_down
hook_after_down

Minimum setting : language and file name

The value part of the setting line consists of two or more segments separated with a colon :.
The first segment is the language name (e.g. SQL, JavaScript, Groovy, etc.). The second segment is the file name. These two segments are required.
Here are some examples:

hook_before_up=SQL:insert_log.sql
hook_after_up=JavaScript:restart_server.js

Constant variables

The other segments are used to define constant variables specific to this particular hook script. These variables can have arbitrary names, but there also are some special variable names for JSR-223 hooks that are explained in the later section.

The following settings reference the same hook script printvar.js with different variable values.

# development.properties
hook_before_up=JavaScript:printvar.js:when=before:what=up
hook_after_down=JavaScript:printvar.js:when=after:what=down

Constant variables can be referenced as global variables in JavaScript.

// printvar.js
print('This is ' + when + ' ' + what + ' hook.');

The above script will print This is before up hook. on migrate up and This is after down hook. on migrate down.

The below is an example for SQL hook scripts.

# development.properties
hook_before_up=SQL:update_timestamp.sql:col=before
hook_after_up=SQL:update_timestamp.sql:col=after

// update_timestamp.sql
update worklog set ${col} = current_date();

If there are global variables defined in the environment properties file, they can be used in hook scripts in the same manner.

foo=bar

Advanced usage of JSR-223 scripts

Get paths to the directories

An instance of SelectedPaths object is accessible as a global variable migrationPaths.

print(migrationPaths.getBasePath());
print(migrationPaths.getEnvPath());
print(migrationPaths.getScriptPath());
print(migrationPaths.getDriverPath());
print(migrationPaths.getHookPath());

Accessing Change object (each hook only)

In an each hook script, an instance of Change object is accessible via the global variable migrationContext.

print(migrationContext.getChange().getId());
print(migrationContext.getChange().getFilename());

NOTE: The Change instance is a clone and will be discarded after each exection, so modifying it would be meaningless.

Execute SQL statement

You can execute arbitrary SQL statement via the built-in global object migrationContext.

migrationContext.executeSql("insert into worklog (str1) values ('done!');");

If you need more than just executing SQL statement, you can get an instance of java.sql.Connection from migrationContext.

con = migrationContext.getConnection();
try {
  stmt = con.createStatement();
  rs = stmt.executeQuery("select * from changelog");
  while (rs.next()) {
    print("id = " + rs.getString("id"));
  }
} finally {
  con.close();
  con = null;
  rs = null;
  stmt = null;
}

Invoking function

When configuring JSR-223 hook scripts, it is possible to specify a top level function to invoke. The following script contains two functions foo and bar.

// foobar.js
function foo() {
  print('foo');
}

function bar(id, name) {
  print(id + ':' + name);
}

To invoke a function, you need to specify the function name using a special variable name _function.

hook_before_up=js:foobar.js:_function=foo
hook_after_up=js:foobar.js:_function=bar:_arg=100:_arg=John

Notice that the two arguments passed to bar() function are specified with another special variable name _arg.

NOTE: Some JSR-223 implementation may not support function invocation.

Invoking method

Similar to function invocation, it is also possible to invoke a method of an top level object.

doggy.js
var dog = new Object();
dog.bark = function(who, times) {
  print('bow-wow ' + times + ' times at ' + who);
}

In the hook setting, use _object to specify the object name and _method for the method name.

hook_before_up=js:doggy.js:_object=dog:_method=bark:_arg=Lucy:_arg=128

NOTE: Some JSR-223 implementation may not support method invocation.

Retain variable value throughout operation

When single up/down operation executes multiple migrations, variables are reset on each migration.
There are two ways to retain variable value throughout the operation.

Initialize the variable in before_up/down script and use it in before/after_each_up/down script.
```
// before_up hook script
var counter = 1
```
```
// before_each_up hook script
print(counter++);
```

Initialize only when it is undefined.

if (typeof counter == 'undefined') this.counter = 1; 
println(counter++);

Use other languages than JavaScript

To use other JSR-223 compliant scripting language than JavaScript, you need to copy required .jar files to $MIGRATIONS_HOME/lib directory.

To write hook scripts in Groovy, for example, you will need groovy.jar and groovy-jsr223.jar.
Once the JARs are placed in $MIGRATIONS_HOME/lib directory, the rest is pretty much the same as JavaScript.
Save hello.groovy in hooks directory with the following content...

// hello.groovy
println('Hello groovy!')

...and add the setting to the environment file.

hook_before_up=groovy:hello.groovy

non-timestamp sequence

we use migrations, but with manually sequenced files instead of the timestamps generated by migrate new. i have some code to introduce a property to switch between sequence types for the new command - would you be willing to accept this upstream? if so, i'll submit a pr to include it.

Migration to Oracle DB executes but fails with RuntimeSqlException and java.sql.SQLWarning: Warning: execution completed with warning

I am using mybatis-migrations 3.3.1 and Oracle DB.

Besides changelog.sql I have one migration file with a one-liner. If I run migrate up the log shows
"failure" while SQL is actually being executed.

20171013000000_test.sql

-- // test 

CREATE TABLE test AS (SELECT * FROM parameters);

-- //@UNDO

DROP TABLE test;

migrate up

~/mybatis/mybatis-migrations-3.3.1> migrate up
------------------------------------------------------------------------
-- MyBatis Migrations - up
------------------------------------------------------------------------
========== Applying: 20170101000000_create_changelog.sql =======================
--  Create Changelog
CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
)
ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id)
========== Applying: 20171013000000_test.sql ======================
--  test
CREATE TABLE test AS (SELECT * FROM parameters)
Error executing: CREATE TABLE test AS (SELECT * FROM parameters)
.  Cause: java.sql.SQLWarning: Warning: execution completed with warning
------------------------------------------------------------------------
-- MyBatis Migrations FAILURE
-- Total time: 12s
-- Finished at: Mon Oct 16 12:53:01 CEST 2017
-- Final Memory: 18M/483M
------------------------------------------------------------------------
ERROR: Error executing command.  Cause: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: CREATE TABLE test AS (SELECT * FROM parameters)
.  Cause: java.sql.SQLWarning: Warning: execution completed with warning

Why is that?

Runtime error when migrate up for the default create_changelog.sql

I setup the Mybatis migration by following the documentation. After run migrate up there is a com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException.

I found this stackoverflow topic: http://stackoverflow.com/questions/23000085/mybatis-migrations-migrate-up-causes-org-apache-ibatis-jdbc-runtimesqlexception

The solution is change the default environment setting of send_full_script from

send_full_script=true

send_full_script=false

My question is: If the default migration script do not work with the default setting, why set the send_full_script to true by default?

Need a way to configure multiple Database schemas

Hi,

We have 1 database with multiple schema (~250) and we would be tracking and deploying changes to all the 250 schema. Since the 250 schema have different credentials, how do we setup environment properties to enable mybatis migrations to work.

Could not find or load main class org.apache.ibatis.migration.Migrator

Hi @harawata @kazuki43zoo @hazendaz,

I am trying to use the tool (MyBatis Migrations) 3.2.1 to automate DB changes for my Oracle instance. Almost done that (99% of the DB initiation) and Friday ran into the issue that is (what I think) is described here: #46

CREATE OR REPLACE EDITIONABLE TRIGGER "TST_TRGR"
BEFORE INSERT ON sometable 
FOR EACH ROW

BEGIN
  SELECT runid.NEXTVAL
  INTO    :new.runid
  FROM   dual;
END;

ALTER TRIGGER "TST_TRGR" ENABLE;

Then I went to #46 and there it was mentioned to use -- @DELIMITER. It is available in 3.3.0 but I haven't found any downloadable 3.3.0-SNAPSHOT archive that I can use to proceed further.

If I use something that is in master right now (builded it with maven) - I am getting the below error just trying to run migrate info:

Could not find or load main class org.apache.ibatis.migration.Migrator

Can you support/check on this?

If statement in migration scripts?

Is there any way to use some conditional logic in a migration script? I have 2 databases (hsqlb and postgres) and it seems sometimes their syntax differ. I'd like to run specific parts of migration scripts based on the selected environment.

Feature: Execute a directory tree of "code migration" scripts for each hook.

Database migrations can be split into two types ...

Schema Changes: Table, Index, Foreign Keys, Constraints
Code Changes: Stored Procedures, Functions, Triggers, Views

Example for Code migrations see Flyway's Repeatable Scripts.

Schema migrations should be executed once and then never changed (they are idempotent). They are well supported by Batis. Thanks!
Code migrations should be able to be ALL re-executed after ALL up migrations. This is not yet well supported by Batis.
In development, Code migrations should ideally only be executed if they are updated .
In pre-release testing and production, Code migrations should ALL be re-execute after ALL schema changes have been applied. This is an important step that ensures that no code is broken by the schema change. It's synonymous with compiler type checking in other languages. If we have many code objects then this can be slow so we ideally don't wish to force this on the developer every time. Developers won't want this happening when testing up or down but you want the option to perform this to check that no code is broken by the change.

To execute these code scripts, the simplest way is to just place them all in a directory and add a feature to execute a directory tree containing SQL scripts to drop/create each code object. To ensure consistent state, execution must be in sorted file name order. This is my feature request.

Code migrations must also be executed in dependency order (assuming we have one script per object). This is a partial ordering so we don't need to number every code script to do this. We simply create numbered sub-directories that correspond to their dependency level. So for example ...

code/views/01/myview1.sql - Level 01 objects don't have any dependencies.
code/views/01/myview2.sql
code/views/02/myview3.sql - Level 02 objects depend on Level 01 objects.
code/views/03/myview4.sql - Level 03 objects depend on Level 01 & 02 objects.
etc.

I've implemented this for another migration tool FluentMigrator.

So what am I asking for?

v1 Solution

This the simplest "available now" solution.

Provide a Javascript or Groovy code sample to execute a directory of SQL using the existing hooks feature
Document the dependency method I've described above.

v2 Solution

Allow ALL hooks to look for a folder corresponding to the hook name under the scripts folder and execute all SQL in the folder's sub-directories in path name order .

When performing up and down and when generating scripts using the script command, provide an option to execute all the hooked code scripts.
Add a command line option to enable All/Updated code scripts to run. I think the default should be Updated. We'd need a means track which are updated.

version NullPointerException when changelog table is empty

When executing migrate version command, NullPointerException is thrown if changlog table is empty. If I add any record to the table everything starts working fine.

mybatis-migrations-3.2.1
mysql-connector-java-5.1.39-bin.jar

migrate --env=ci version 20161014040630 --trace
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=128m; support was removed in 8.0
Picked up _JAVA_OPTIONS: -Djava.net.preferIPv4Stack=true
------------------------------------------------------------------------
-- MyBatis Migrations - version
------------------------------------------------------------------------
Upgrading to: 20161014040630
------------------------------------------------------------------------
-- MyBatis Migrations FAILURE
-- Total time: 0s
-- Finished at: Thu Oct 13 21:09:04 PDT 2016
-- Final Memory: 17M/479M
------------------------------------------------------------------------


ERROR: java.lang.NullPointerExceptionorg.apache.ibatis.migration.MigrationException: java.lang.NullPointerException
        at org.apache.ibatis.migration.CommandLine.runCommand(CommandLine.java:71)
        at org.apache.ibatis.migration.CommandLine.execute(CommandLine.java:42)
        at org.apache.ibatis.migration.Migrator.main(Migrator.java:20)
Caused by: java.lang.NullPointerException
        at org.apache.ibatis.migration.operations.VersionOperation.operate(VersionOperation.java:49)
        at org.apache.ibatis.migration.commands.VersionCommand.execute(VersionCommand.java:35)
        at org.apache.ibatis.migration.CommandLine.runCommand(CommandLine.java:65)
        ... 2 more

Errors During Bootstrap Process

So I'm posting this here, I didn't see a forum or anything on StackOverflow for this...

I'm attempting to execute the bootstrap step. I've exported the schema from our production database using both Workbench and MySQLdump. I'm getting similar errors on both of them when executing MIGRATIS BOOTSTRAP:

Error 1

ERROR: Error running bootstrapper.  Cause: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: DELIMITER ;
.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manua
l that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1

Error 2

ERROR: Error running bootstrapper.  Cause: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: /*!40101 SET @OLD_
SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
.  Cause: java.sql.SQLWarning: Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future relea
se.

These errors persist regardless of the combination i use for full_line_delimiter or auto_commit

Publish latest version to Maven Central?

The most recent version of mybatis-migrations on Maven Central is from 2014: http://search.maven.org/#search%7Cga%7C1%7Ca%3A%22mybatis-migrations%22 and is not compatible w/ MyBatis 3.3. Would it be possible to publish a more recent version of mybatis-migrations?

`version` subcommand fails to stop at the specified version

I ran the version subcommand today (e.g. migrate version 20150818150900) and Migrate did not stop at the specified version (20150818150900). Instead it just kept rolling back older changes. It appears that it would have continued all the way to the start if I had not killed the command.

I have successfully reproduced this behavior.

Version Info:

MyBatis Migrations 3.2.0 (${implementation.build})
Java version: 1.6.0_65, vendor: Apple Inc.

Variable substitution does not work in custom templates

I'm trying to configure my own template for new migrations but I don't see how to include a placeholder for the description.
In the file

migrations/src/main/java/org/apache/ibatis/migration/template_migration.sql

Line 17 in b4e2f98

-- // ${description}

there is a ${description} parameter but it doesn't work for custom templates.

mybatis-migrations-3.2.0 scripts contain references to wrong jars

In bin/migrate:

80 CLASSPATH=$CLASSPATH_PREFIX:"$BASEDIR"/etc:"$REPO"/mybatis-3.1.1.jar:"$REPO"/mybatis-migrations-3.1.0.jar

In bin/migrate.cmd:

72 set CLASSPATH="%BASEDIR%"\etc;"%REPO%"\mybatis-3.1.1.jar;"%REPO%"\mybatis-migrations-3.1.0.jar

I would expect that mybatis-3.2.4.jar and mybatis-migrations-3.2.0.jar are referenced.

Pre/Post condition hooks

We provide scripts that execute SQL queries that act as Pre/Post conditions on the data. Each script contains a query. If the query returns any data then the Pre or Post condition has failed. The query output should identify the cause.

These query scripts are not migrations but very useful in the data migration process. You want another directory / hook for each of these.

They can simplify complex migration cases by making data value assumptions that need to be asserted on production data prior to being applied. They can also keep the data much cleaner by post checking that data migrations were correctly applied or that the database remains in a "clean and valid " state.

Does MyBatis Migrations work with ParAccel?

Hi,

I'm using ParAccel and I cannot run: migrate status

-- MyBatis Migrations FAILURE
-- Total time: 0s
-- Finished at: Wed Jul 22 17:15:03 BST 2015
-- Final Memory: 5M/480M

ERROR: Could not create SqlRunner. Cause: java.sql.SQLException: Error setting driver on UnpooledDataSource. Cause: java.lang.ClassNotFoundException: paraccel-jdbc.jar

Environment File contains
JDBC connection properties.
driver=paraccel-jdbc.jar

I'm using Actian's ParAccel JDBC driver:
http://esd.actian.com/product/Matrix_Drivers/JDBC/java/

Does MyBatis Migrations support ParAccel?

SQL Warning becomes exception becomes error and exit?

========== Applying: 00045_DBA-3350_recreate_DHS_VendorReference.sql ===========
-- ========== ========== ========== ========== ========== ========== ==========
-- DB1
--
-- DBA-3350  DHS_VendorReference table creation
-- ========== ========== ========== ========== ========== ========== ==========
-- ---------- ---------- ----------
DROP TABLE IF EXISTS Company.DHS_VendorReference


Error executing: DROP TABLE IF EXISTS Company.DHS_VendorReference
.  Cause: java.sql.SQLWarning: Unknown table 'Company.DHS_VendorReference'

The whole point of a DROP TABLE IF EXISTS is specifically to make creating a table safe.

I see the flag for --force but that forces all errors to continue. Frankly, this shouldn't even be a warning. The code very explicitly says it was an expected situation. How can I say "Ignore warnings" or "Don't treat warnings as errors"

extend script command

Enhancement proposal:

New mode for command "script" to generate a script for all pending migrations (similar to up)

Could be "migrate script" without version given or "migrate script up"

MyBatis Migrations: migrate up causes org.apache.ibatis.jdbc.RuntimeSqlException

I just installed Mybatis Migration and followed their instructions. The init, new, status commands worked but "migrate up" causes the below error:

------------------------------------------------------------------------
-- MyBatis Migrations - up
------------------------------------------------------------------------
========== Applying: 20140410190604_create_changelog.sql =======================
Error executing: --  Create Changelog

-- Default DDL for changelog table that will keep
-- a record of the migrations that have been run.

-- You can modify this to suit your database before
-- running your first migration.

-- Be sure that ID and DESCRIPTION fields exist in
-- BigInteger and String compatible fields respectively.

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);

.  Cause: 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 'ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id)' at line 18
------------------------------------------------------------------------
-- MyBatis Migrations FAILURE
-- Total time: 0s
-- Finished at: Thu Apr 10 15:08:51 PDT 2014
-- Final Memory: 12M/479M
------------------------------------------------------------------------

ERROR: Error executing command.  Cause: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: --  Create Changelog

-- Default DDL for changelog table that will keep
-- a record of the migrations that have been run.

-- You can modify this to suit your database before
-- running your first migration.

-- Be sure that ID and DESCRIPTION fields exist in
-- BigInteger and String compatible fields respectively.

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);

.  Cause: 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 'ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id)' at line 18

The two mysql DDL (create table and alter table) were generated by migrations in the file 20140410190604_create_changelog.sql. However, if I manually ran the sql it didn't complain any syntax error.

What might causes this "migrate up" problem?

The script is like:

CREATE TABLE CHANGELOG (
ID NUMERIC(20,0) NOT NULL,
APPLIED_AT VARCHAR(25) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL
);

ALTER TABLE CHANGELOG
ADD CONSTRAINT PK_CHANGELOG
PRIMARY KEY (id);

ClassNotFoundException: Cannot find class "oracle.jdbc.OracleDriver"/"oracle.jdbc.driver.OracleDriver"

I am using mybatis-migrations 3.1.1 (3.3.1 same behavior) and have followed installation instructions. I am using Oracle DB and trying to use ojdbc7 (or 8) driver to connect to my database. I have JDK8 installed

java version "1.8.0_102"

ls -la:

svscorp@vm:~/mybatis$ pwd
/home/svscorp/mybatis
svscorp@vm:~/mybatis$ ls -la
drwxr-xr-x 8 svscorp svscorp 4096 Oct  4 16:59 .
drwxr-xr-x 8 svscorp svscorp 4096 Oct  4 16:50 ..
drwxr-xr-x 2 svscorp svscorp 4096 May  2 00:35 bin
drwxr-xr-x 2 svscorp svscorp 4096 Oct  4 16:54 drivers
drwxr-xr-x 2 svscorp svscorp 4096 Oct  4 16:50 environments
drwxr-xr-x 2 svscorp svscorp 4096 Oct  4 16:55 lib
-rw-rw-rw- 1 svscorp svscorp 11358 Apr 25  2016 LICENSE
-rw-rw-rw- 1 svscorp svscorp 2599 Apr 25  2016 NOTICE
-rw-rw-rw- 1 svscorp svscorp 3042 Apr 25  2016 README.md
drwxr-xr-x 2 svscorp svscorp 4096 Oct  4 16:59 scripts

more info on ENV VARS

svscorp@vm:~/mybatis$ echo $MIGRATIONS
/home/svscorp/mybatis/bin
svscorp@vm:~/mybatis$ echo $MIGRATIONS_HOME
/home/svscorp/mybatis
svscorp@vm:~/mybatis$ echo $PATH
/home/svscorp/mybatis/bin:<other-stuff>

development.properties

time_zone=GMT+0:00
driver="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@//[IP]:[PORT]/[DB]"
username=[USERNAME]
password=[USERNAME]
full_line_delimiter=false
auto_commit=false

In the drivers I have ojdbc8.jar (tried with ojdbc7.jar - same result).

Error message (--trace):

Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=128m; support was removed in 8.0
------------------------------------------------------------------------
-- MyBatis Migrations - status
------------------------------------------------------------------------
ID             Applied At          Description
================================================================================
------------------------------------------------------------------------
-- MyBatis Migrations FAILURE
-- Total time: 0s
-- Finished at: Wed Oct 04 17:04:15 CEST 2017
-- Final Memory: 10M/483M
------------------------------------------------------------------------

ERROR: Could not create SqlRunner. Cause: java.sql.SQLException: Error setting driver on UnpooledDataSource. Cause: java.lang.ClassNotFoundException: Cannot find class: "oracle.jdbc.OracleDriver"org.apache.ibatis.migration.MigrationException: Could not create SqlRunner. Cause: java.sql.SQLException: Error setting driver on UnpooledDataSource. Cause: java.lang.ClassNotFoundException: Cannot find class: "oracle.jdbc.OracleDriver"
        at org.apache.ibatis.migration.operations.DatabaseOperation.getSqlRunner(DatabaseOperation.java:88)
        at org.apache.ibatis.migration.operations.DatabaseOperation.changelogExists(DatabaseOperation.java:73)
        at org.apache.ibatis.migration.operations.StatusOperation.operate(StatusOperation.java:44)
        at org.apache.ibatis.migration.commands.StatusCommand.execute(StatusCommand.java:30)
        at org.apache.ibatis.migration.CommandLine.runCommand(CommandLine.java:65)
        at org.apache.ibatis.migration.CommandLine.execute(CommandLine.java:42)
        at org.apache.ibatis.migration.Migrator.main(Migrator.java:20)
Caused by: java.sql.SQLException: Error setting driver on UnpooledDataSource. Cause: java.lang.ClassNotFoundException: Cannot find class: "oracle.jdbc.OracleDriver"
        at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.initializeDriver(UnpooledDataSource.java:221)
        at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.doGetConnection(UnpooledDataSource.java:200)
        at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.doGetConnection(UnpooledDataSource.java:196)
        at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.getConnection(UnpooledDataSource.java:93)
        at org.apache.ibatis.migration.DataSourceConnectionProvider.getConnection(DataSourceConnectionProvider.java:34)
        at org.apache.ibatis.migration.operations.DatabaseOperation.getSqlRunner(DatabaseOperation.java:86)
        ... 6 more

Same goes if I try older format specifying the driver: oracle.jdbc.driver.OracleDriver

Where did it go wrong?

Down command does not work with case sensitive column names.

whe nthe changelog table created it is created with upper case column names, but when the down command runs it queries with lover case "id=" can it be consistent?

$MIGRATION_HOME or $MIGRATIONS_HOME?

The installation instructions tell the environment variable for the installation folder is MIGRATIONS_HOME but migrate init looks for $MIGRATION_HOME/migration.properties.

When I tried to use a custom prefix I realized that I had to create a new environment variable. Is there a reason for that? Could it be unified into a single variable?

Spring Boot Autoconfiguration and CLI for MyBatis Migrations

Hi,

thank you for your work. For my spring application I created an autoconfigration with cli support. I have a lot of questions now.

Before I push it to maven central I would like to ask you what do you think about move it to mybatis github organisation?
Is there a plan for high level API for commands like script and new?
It's an error that pending command can't run migrations without changelog table (even if first script creates the table) and up doesn't pick up old pending scripts?

would be much more inuitive as YAML:

events:
  hook_after_down:
    hooks:
    - script: printvars.js
      type: javascript
      properties:
        when: after
        what: down

Also, declaring JavaScript is redundant if you go by convention: .js or Groovy as .groovy