search

pingcap / tidb

TiDB - the open-source, cloud-native, distributed SQL database designed for modern applications.
https://pingcap.com
Apache License 2.0
37.39k stars 5.85k forks source link

Support `golang-migrate/migrate` that sets SERIALIZABLE isolation level #45487

Open yahonda opened 1 year ago

yahonda commented 1 year ago

Feature Request

Is your feature request related to a problem? Please describe: One of the database migration tool, https://github.com/golang-migrate/migrate.

Describe the feature you'd like: golang-migrate/migrate sets isolation level to SERIALIZABLE for MySQL database connections since https://github.com/golang-migrate/migrate/pull/656

This protects against other instances of migrate reading the migration version, which may later update the version.

I recall that other migration tools like Ruby on Rails, Flyway, Prisma Migrate and elixir Ecto uses advisory locks not to run multiple migration at the same time. Now TiDB supports get_lock() function this issue has been resolved. https://github.com/pingcap/tidb/pull/33947 https://github.com/pingcap/tidb/issues/14994 on the other hand, golang-migrate/migrate uses SERIALIZABLE for the same purpose.

Describe alternatives you've considered: Set tidb_skip_isolation_level_check = 1 to workaround it.

Teachability, Documentation, Adoption, Migration Strategy: Here is the steps to reproduce:

  1. Install golang-migrate

    % brew install golang-migrate

  2. Create a new migration

    % migrate create -ext sql -dir db/migrations -seq create_users_table
/Users/yahonda/db/migrations/000001_create_users_table.up.sql
/Users/yahonda/db/migrations/000001_create_users_table.down.sql

  3. Edit two migration files as follows

% more /Users/yahonda/db/migrations/000001_create_users_table.up.sql
CREATE TABLE foo (id integer)
% more /Users/yahonda/db/migrations/000001_create_users_table.down.sql
drop table foo

  1. Startup TiUP Playground

    % tiup playground

  2. Run the migration that raises Error 8048: The isolation level 'SERIALIZABLE' is not supported. Set tidb_skip_isolation_level_check=1 to skip this error

% migrate --path db/migrations --database "mysql://root:@(127.0.0.1:4000)/test" -verbose up

2023/07/21 09:46:28 Start buffering 1/u create_users_table
2023/07/21 09:46:28 error: transaction start failed in line 0:  (details: Error 8048: The isolation level 'SERIALIZABLE' is not supported. Set tidb_skip_isolation_level_check=1 to skip this error)

  1. Set tidb_skip_isolation_level_check = 1 to workaround it

    % mysql --comments --host 127.0.0.1 --port 4000 -u root
mysql> set global tidb_skip_isolation_level_check = 1;
Query OK, 0 rows affected (0.04 sec)

  2. Run the migration again with tidb_skip_isolation_level_check = 1

    
% migrate --path db/migrations --database "mysql://root:@(127.0.0.1:4000)/test" -verbose up

2023/07/21 09:48:55 Start buffering 1/u create_users_table 2023/07/21 09:48:55 Read and execute 1/u create_users_table 2023/07/21 09:48:55 Finished 1/u create_users_table (read 4.380031ms, ran 201.558974ms) 2023/07/21 09:48:55 Finished after 211.977585ms 2023/07/21 09:48:55 Closing source and database %

arthot commented 8 months ago

Can you add tidb_skip_isolation_level_check to the config https://docs.pingcap.com/tidb/stable/tidb-configuration-file? That way it could be disabled right away and there will be no need for this manual workaround. It's especially annoying in docker environment.