search

golang-migrate / migrate

Database migrations. CLI and Golang library.
Other
14.99k stars 1.38k forks source link

[BUG] MYSQL driver no longer respects safe updates #864

Open ping-localhost opened 1 year ago

ping-localhost commented 1 year ago

Describe the Bug

golang-migrate no longer respects safe updates within MySQL.

Steps to Reproduce

  1. Create a database that has SQL_SAFE_UPDATES enabled.
  2. Run any migration which causes the migrations table to be updated.
  3. query := "DELETE FROM `" + m.config.MigrationsTable + "`" will be executed and golang-migrate will error out:

Error 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column in line 0: DELETE FROM schema_migrations

Expected Behavior

I would expect golang-migrate to respect this flag by using an additional WHERE-statement (or by using TRUNCATE).

Migrate Version

github.com/golang-migrate/migrate/v4 v4.15.2

Loaded Source Drivers

iofs (so I can use go:embed)

Loaded Database Drivers

mysql

Go Version

go version go1.19.3 darwin/amd64

Additional context

This does not happen in v4.15.1 as it used TRUNCATE (changed with https://github.com/golang-migrate/migrate/pull/656), which is acceptable for safe updating. Might be nice if I, as a user, could select what I want to use?

ping-localhost commented 9 months ago

This is still an issue in v4.16.2, is there anything that can be done so safe updates work again?

emeka commented 5 months ago

Could this be solved by adding a LIMIT clause to the delete instead of moving back to truncate which is not transactional?

maxmati commented 5 months ago

I made a PR that should solve this issue #1070 while still keeping the DELETE. I've also created tag v4.17.0-safe in our fork which incorporates this change already. It can be used like this: replace github.com/golang-migrate/migrate/v4 v4.17.0 => github.com/certifaction/migrate/v4 v4.17.0-safe

ping-localhost commented 5 months ago

Could this be solved by adding a LIMIT clause to the delete instead of moving back to truncate which is not transactional?

I've not yet had time to test the proposed change, but if @maxmati can confirm this works, I'll gladly close my MR in favor of #1070 👌

maxmati commented 5 months ago

Yes this works for me :)

ping-localhost commented 3 months ago

I made a PR that should solve this issue #1070 while still keeping the DELETE. I've also created tag v4.17.0-safe in our fork which incorporates this change already. It can be used like this: replace github.com/golang-migrate/migrate/v4 v4.17.0 => github.com/certifaction/migrate/v4 v4.17.0-safe

Just tried this locally and it did not actually resolve my issue:

2024-06-12T15:30:24.396+0200    INFO    migrations      loggers/migration.go:21 Start buffering 1/u initialize_schema
2024/06/12 15:30:24 Caught panic:
        Error 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column in line 0: DELETE FROM `schema_migrations` LIMIT 1