Migrations table creation and insert fails on MySQL during transaction

[JuozasVainauskas]

MySQL does not support transactions on CREATE operation:

• https://dev.mysql.com/doc/refman/8.4/en/atomic-ddl.html
• https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

goose/migrate.go

Lines 249 to 263 in 1984573

	func createVersionTable(ctx context.Context, db *sql.DB) error {
	txn, err := db.BeginTx(ctx, nil)
	if err != nil {
	return err
	}
	if err := store.CreateVersionTable(ctx, txn, TableName()); err != nil {
	_ = txn.Rollback()
	return err
	}
	if err := store.InsertVersion(ctx, txn, TableName(), 0); err != nil {
	_ = txn.Rollback()
	return err
	}
	return txn.Commit()
	}

part fails because transactions are used. When this code is invoked, the table is created successfully but insert fails and we get the state where we have empty goose_db_version table since transaction does not rollback table creation. Later, following migrations fail with error:

2024/09/09 13:01:19 database migration failed: no next version found

Would it be possible to run createVersionTable function without transaction for MySQL dialect? This solution would work and fix our problem. I could implement this but need some guidance and ideas how I could code it up.

[mfridman]

I'll need to investigate this a bit further, but I'm quite surprised as we test goose against a number of databases and afaik many users use MySQL or MariaDB already.

[jrozner]

We recently ran into this problem as well. Wondering if there are any possible solutions short of taking these limitations into account as we write further migrations (eg. each atomic statement gets it's own migration)?