Migrate: Migration failed with multiple statements wrapped in BEGIN/COMMIT

[fharding1]

Describe the Bug
I get an error when I try to run a migration with multiple statements wrapped in a BEGIN/COMMIT

Steps to Reproduce
Steps to reproduce the behavior:

1. My migrations look like '...'

BEGIN;
ALTER TABLE sharedcampaigns CHANGE COLUMN `uuid` `id` CHAR(36);
ALTER TABLE sharedresearch CHANGE COLUMN `uuid` `id` CHAR(36);
COMMIT;

1. I ran migrate with the following options '....'
   parseTime=true
2. See error

panic: migration failed in line 0: BEGIN;
ALTER TABLE sharedcampaigns CHANGE COLUMN `uuid` `id` CHAR(36);
ALTER TABLE sharedresearch CHANGE COLUMN `uuid` `id` CHAR(36);
COMMIT;
 (details: Error 1064: 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 'ALTER TABLE sharedcampaigns CHANGE COLUMN `uuid` `id` CHAR(36);
ALTER TABLE shar' at line 2)

goroutine 1 [running]:
main.doe(...)
        /home/franklin/go/src/github.com/Billups/snapshot-rewrite/cmd/migrate/migrate.go:26
main.main()
        /home/franklin/go/src/github.com/Billups/snapshot-rewrite/cmd/migrate/migrate.go:69 +0x4a1

Expected Behavior
The migrations should run fine.

Migrate Version
93d53a5ae84d81945eedadfe8f6865530d61d51c

Loaded Source Drivers
file

Loaded Database Drivers
mysql

Go Version
go version go1.11 linux/amd64

[dhui]

Are you able to run the migration w/o migrate? e.g. manually using the mysql cli client
Generally, that error message means there's an issue w/ your migration as migrate doesn't modify the migration files before running them

[fharding1]

@dhui thanks for the response, they run fine manually in the MySQL cli.

[dhui]

This is probably an error in your migration and not with migrate.
When you ran the migration with the mysql cli client, did you use the same migration file?
Are you intentionally missing BEGIN/START TRANSACTION in your migration file?

Also, you could test the migration with a Go program using the mysql driver with the same DSN/connection string.

[fharding1]

I copied and pasted directly from the saved migration file to the mysql cli and it executed fine. My migrations started with BEGIN;, I just messed up the markdown so the first lines weren't showing up. Fixed. I'll get around to testing those migrations in a go program with the mysql driver.

[dhui]

I'm not sure how this worked for you in the mysql cli since DDL changes can't be used in a transaction in MySQL (or at least they won't work as you expect them to).

https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

[babygoat]

@fharding1 @dhui I ran into a similar issue recently. My migrations might contain multiple statements within a single file. At first, I could perform successful migrations UP, DOWN,DROP through golang-migrate CLI (my routine usage) but could not be able to upgrade through library calls in my project. After digging into the problems, I found out that the DSN of my mysql client was lack of &multiStatements=true option so that I was not able to perform multiple statements in one single query. You can refer more details in this PR in go-sql-driver. Hope that helps!