MIgration fails, when add column and drop column happens in one pass

[wahur666]

Description

Migrator fails when applying add column and drop column on the same attribute in one pass.

Steps to Reproduce

1. Create a table in the first migration

 manager
            .create_table(
                Table::create()
                    .table(BenchmarkResult::Table)
                    .if_not_exists()
                    .col(
                        ColumnDef::new(BenchmarkResult::Id)
                            .integer()
                            .not_null()
                            .auto_increment()
                            .primary_key(),
                    )
            )
            .await?;

2. Create a second migration

manager
            .alter_table(
                Table::alter()
                    .table(BenchmarkResult::Table)
                    .add_column(
                        ColumnDef::new(BenchmarkResult::Status)
                            .string()
                            .not_null()
                            .default("Pending"),
                    )
                    .to_owned(),
            )
            .await?;

3. Create a third migration

manager
            .alter_table(
                Table::alter()
                    .table(BenchmarkResult::Table)
                    .drop_column(BenchmarkResult::Status)
                    .to_owned(),
            )
            .await?;

4. SQLite backend, on first run will fail with the following error.

        Migrator::up(&db, None)
            .await
            .expect("Failed to run migrations for tests");

error

'Failed to run migrations for tests: Exec(SqlxError(Database(SqliteError { code: 1, message: "no such column: \"\"status\"\"" })))', 

5. Restart the program and will pass correctly.

Expected Behavior

It migrates everything once, and not fail.

Actual Behavior

'Failed to run migrations for tests: Exec(SqlxError(Database(SqliteError { code: 1, message: "no such column: ""status""" })))',

Reproduces How Often

Always

Workarounds

Run migration in 2 passes

        Migrator::up(&db, Some(2))
            .await
            .expect("Failed to run migrations for tests");
        Migrator::up(&db, None)
            .await
            .expect("Failed to run migrations for tests");

Reproducible Example

Versions

cargo tree | Select-String "sea-(.*)" | ForEach-Object { $_.Matches.Value }

sea-orm v0.12.2
sea-orm-macros v0.12.2 (proc-macro)
sea-bae v0.2.0 (proc-macro)
sea-query v0.30.0
sea-query-derive v0.4.0 (proc-macro)
sea-query-binder v0.5.0
sea-query v0.30.0 ()
sea-orm-migration v0.12.2
sea-orm v0.12.2 ()
sea-orm-cli v0.12.2
sea-schema v0.14.0
sea-query v0.30.0 ()
sea-schema-derive v0.2.0 (proc-macro)
sea-schema v0.14.0 ()
sea-orm v0.12.2 (*)

Windows 11, SQLite

[tyt2y3]

The migrations should run serially. I don't understand why would there be a problem. Can you dig deeper and investigate a bit?

[wahur666]

I will try to provide a reproducible build

[amtoaer]

@wahur666 Hi, have you solved this problem? I just encountered it too.

[wahur666]

I did solve it with the workaround I provided in the code, but not fixed it correctly, sadly

        Migrator::up(&db, Some(2))
            .await
            .expect("Failed to run migrations for tests");
        Migrator::up(&db, None)
            .await
            .expect("Failed to run migrations for tests");

[amtoaer]

Thank you! I'll try it tomorrow and see if it works. If not, I'll look into it more.

[amtoaer]

@wahur666 I realized the issue was due to changing the ConnectOptions. Previously, the DatabaseConnection for migrations was the same as for the app:

pub async fn database_connection() -> Result<DatabaseConnection> {
    let mut option = ConnectOptions::new(database_url());
    option
        .max_connections(100)
        .min_connections(5)
        .acquire_timeout(std::time::Duration::from_secs(90));
    Ok(Database::connect(option).await?)
}

This caused migration failures, showing a "no such column" error. But using the default ConnectOptions fixes it:

pub async fn migrate_database() -> Result<()> {
    let connection = Database::connect(database_url()).await?;
    Ok(Migrator::up(&connection, None).await?)
}

Hope this information could be helpful to you.

[wahur666]

Its a nice observation. Thank you for the help. I'm no longer accessing the code where this happened. But the next person who reads it, will find it helpful for sure. :)