Multiple SQL statements in the same migration file leads to failure due to newlines

[soham-suki]

Describe the Bug
If a migration file contains multiple SQL statements separated by newlines, running the migration in go using m.Up() results in the migration failing and schema_migrations being dirty.
Logs show that this is because the library sends "\n" as well to the database engine, which can't interpret the newline character.

Steps to Reproduce
Steps to reproduce the behavior:

1. Create a migration file with at least two SQL statements:

CREATE INDEX idx_time ON users (time);
CREATE INDEX idx_time_project ON users (time, project_id);

2. Run migrate with the following options:

        m, err := migrate.NewWithDatabaseInstance(
		"file://db/migrations",
		"mysql",
		driver)
	if err != nil {
		return fmt.Errorf("Failed to create migration object: %v", err)
	}

	if err := m.Up(); err != nil && err != migrate.ErrNoChange {
		return fmt.Errorf("Error running db migrations for data tables: %v", err)
	}

3. Observe the error. golang-migrate sends "\n" after the first SQL statement, which creates an invalid SQL.

Expected Behavior
All the SQL statements should be run (or the library should add in the README that only single SQL statements should be written in a migration :))

Migrate Version
In go.mod: github.com/golang-migrate/migrate/v4 v4.17.1

Loaded Source Drivers
MySQL I think, but I'd love help in figuring out how this is different from "Loaded Database Drivers" below (especially in the golang library"

Loaded Database Drivers
MySQL passed in to the library:

        driver, err := mysql.WithInstance(dbConn.DB, &mysql.Config{})
	if err != nil {
		return fmt.Errorf("Failed to create MySQL client instance for migrations: %v", err)
	}

	m, err := migrate.NewWithDatabaseInstance(
		"file://db/migrations",
		"mysql",
		driver)

Go Version
go version go1.22.3 darwin/arm64

Stacktrace
N/A

Additional context
N/A

[xiezhihuan]

Your dbConn The DB object is not configured with multiStatements=true
instance must have multiStatements set to true

example：user:password@tcp(localhost:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local&multiStatements=true

ref:
database/mysql/mysql.go:208
config.MultiStatements = true

[soham-suki]

Thanks! I wasn't aware that multiStatements is a configurable option. Any reason why this is not the default behavior?