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.