SQLite: Migrations containing PRAGMA foreign_keys= should be marked as non-transactional

[ZumiKua]

Which version and edition of Flyway are you using?

Flyway 5.2.1 (with Spring)

If this is not the latest version, can you reproduce the issue with the latest one as well?

this is the latest version in maven central.

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Java API? I'm using Spring boot and it seems that Spring boot handles migration for me.

Which database are you using (type & version)?

SQLite JDBC 3.25.2

Which operating system are you using?

Windows 10

What did you do?

Execute the following migration:

PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

CREATE TABLE new_category(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    UNIQUE(name) ON CONFLICT IGNORE
    ord INTEGER
);

INSERT INTO new_category SELECT id, name, ord FROM category;

DROP TABLE category;

ALTER TABLE new_category RENAME TO category;

CREATE INDEX category_name_index ON category(name);

PRAGMA foreign_key_check;

COMMIT;

PRAGMA foreign_keys=on;

What did you expect to see?

According to flyway documentation

If Flyway detects that a specific statement cannot be run within a transaction due to technical limitations of your database, it won’t run that migration within a transaction. Instead it will be marked as non-transactional.

So I expect flyway mark this migration as non-transactional

What did you see instead?

It still wrap this migration in a transaction.

SQL State  : null
Error Code : 1
Message    : [SQLITE_ERROR] SQL error or missing database (cannot start a transaction within a transaction)
Location   : db/migration/V14__RemoveHighlightColumnInCategoryTable.sql (...\out\production\resources\db\migration\V14__RemoveHighlightColumnInCategoryTable.sql)
Line       : 3
Statement  : BEGIN TRANSACTION

[axelfontaine]

This isn't trivial to solve as SQLite only supports a single transaction, via a single connection. Marking the migration as non-transactional isn't enough as it still poses issues with our schema history table management.

The workaround is easy: don't begin or commit transaction within a migration. Flyway already wraps every migration in a transaction for you.

Documented as a limitation.

[ZumiKua]

Sorry for my late reply.
Currently I'm using Java Migration to workaround this, by create a subclass of BaseJavaMigration and override canExecuteInTransaction to return false.
Will this cause any problem? Like flyway's schema history table's management you metioned?

BTW I think execute something outside transaction is important, like the migration I wrote on original post, PRAGMA foreign_keys=OFF; must be executed outside a transaction. And this is a very common usage to modify table schema.

[axelfontaine]

Your workaround should be fine for now. It could make sense for Flyway to automatically mark a migration containing PRAGMA foreign_keys= as non-transactional (even the statement can be run within a transaction, but then it is treated as a no-op which certainly isn't the desired behavior)

[ZumiKua]

Gotcha, thanks for your help.

[axelfontaine]

Fixed. Migration containing PRAGMA foreign_keys= are now automatically marked as non-transactional.