Skip to content

[#55866] Fix SQLite3 data loss during table alterations with CASCADE foreign keys.#55907

Merged
byroot merged 1 commit intorails:mainfrom
ruyrocha:fix/sqlite3-data-loss
Oct 17, 2025
Merged

[#55866] Fix SQLite3 data loss during table alterations with CASCADE foreign keys.#55907
byroot merged 1 commit intorails:mainfrom
ruyrocha:fix/sqlite3-data-loss

Conversation

@ruyrocha
Copy link
Copy Markdown
Contributor

@ruyrocha ruyrocha commented Oct 15, 2025 •
edited
Loading

Motivation / Background

This Pull Request has been created because #55866.

Detail

Fix SQLite3 data loss during table alterations with CASCADE foreign keys

When altering a table in SQLite3 that is referenced by child tables with ON DELETE CASCADE foreign keys, ActiveRecord would silently delete all data from the child tables. This occurred because SQLite requires table recreation for schema changes, and during this process:

  1. The original table is temporarily dropped
  2. CASCADE deletes fire immediately on child tables
  3. The table is recreated, but child data is already gone

This issue could result in catastrophic production data loss when adding columns, renaming tables, or adding foreign keys to tables that are referenced by other tables with CASCADE constraints.

The fix simply reverses the order to follow the official SQLite 12-step ALTER TABLE procedure.

This Pull Request fixes #55866.

Additional information

Checklist

Before submitting the PR make sure the following are checked:

  • This Pull Request is related to one change. Unrelated changes should be opened in separate PRs.
  • Commit message has a detailed description of what changed and why. If this PR fixes a related issue include it in the commit message. Ex: [Fix #issue-number]
  • Tests are added or updated if you fix a bug or add a feature.
  • CHANGELOG files are updated for the changed libraries if there is a behavior change or additional feature. Minor bug fixes and documentation changes should not be included.

@ruyrocha ruyrocha force-pushed the fix/sqlite3-data-loss branch 3 times, most recently from 7f90343 to fafa7fe Compare October 15, 2025 16:57
@ruyrocha ruyrocha marked this pull request as ready for review October 15, 2025 16:57
@ruyrocha ruyrocha force-pushed the fix/sqlite3-data-loss branch 2 times, most recently from 032fe51 to 20d69d5 Compare October 15, 2025 17:44
@ruyrocha ruyrocha force-pushed the fix/sqlite3-data-loss branch from 20d69d5 to 9764553 Compare October 15, 2025 23:26
@ruyrocha ruyrocha force-pushed the fix/sqlite3-data-loss branch from 9764553 to ffd25a5 Compare October 15, 2025 23:41
@byroot byroot merged commit 906d834 into rails:main Oct 17, 2025
3 checks passed
byroot added a commit that referenced this pull request Oct 17, 2025
@byroot
Merge pull request #55907 from ruyrocha/fix/sqlite3-data-loss
f0890c4 
[#55866] Fix SQLite3 data loss during table alterations with CASCADE foreign keys.
byroot added a commit that referenced this pull request Oct 17, 2025
@byroot
Merge pull request #55907 from ruyrocha/fix/sqlite3-data-loss
511dbf2 
[#55866] Fix SQLite3 data loss during table alterations with CASCADE foreign keys.
byroot added a commit that referenced this pull request Oct 17, 2025
@byroot
Merge pull request #55907 from ruyrocha/fix/sqlite3-data-loss
31217f6 
[#55866] Fix SQLite3 data loss during table alterations with CASCADE foreign keys.
@byroot
Copy link
Copy Markdown
Member

byroot commented Oct 17, 2025

Thank you. Backported to 8.1, 8.0 and 7.2

@ruyrocha ruyrocha deleted the fix/sqlite3-data-loss branch October 17, 2025 17:04
emirn added a commit to emirn/rails that referenced this pull request Apr 2, 2026
@emirn
[rails#55866] Fix SQLite3 data loss when altering tables with CASCADE…
4e61a8e 
… foreign keys inside `ActiveRecord::Base.connection.transaction`.

Silent cascade removal of child records was fixed in rails#55907 but when table alteration code was wrapped by `ActiveRecord::Base.connection.transaction`, SQLite3 ignored `PRAGMA foreign_keys = OFF`.

This fix ensures that the transaction (if exists) is now temporarily committed before `disable_referential_integrity` runs for the `PRAGMA` to take effect.

Fixes rails#55866
@emirn emirn mentioned this pull request Apr 2, 2026
Open
4 tasks
emirn added a commit to emirn/rails that referenced this pull request Apr 2, 2026
@emirn
[rails#55866] Fix SQLite3 data loss when altering tables with CASCADE…
1c0f3e4 
… foreign keys inside `ActiveRecord::Base.connection.transaction`.

Silent cascade removal of child records was fixed in rails#55907 but when table alteration code was wrapped by `ActiveRecord::Base.connection.transaction`, SQLite3 ignored `PRAGMA foreign_keys = OFF`.

This fix ensures that the transaction (if exists) is now temporarily committed before `disable_referential_integrity` runs for the `PRAGMA` to take effect.

Fixes rails#55866
emirn added a commit to emirn/rails that referenced this pull request Apr 2, 2026
@emirn
[rails#55866] Fix SQLite3 data loss when altering tables with CASCADE…
439d282 
… foreign keys inside `ActiveRecord::Base.connection.transaction`.

Silent cascade removal of child records was fixed in rails#55907 but when table alteration code was wrapped by `ActiveRecord::Base.connection.transaction`, SQLite3 ignored `PRAGMA foreign_keys = OFF`.

This fix ensures that the transaction (if exists) is now temporarily committed before `disable_referential_integrity` runs for the `PRAGMA` to take effect.

Fixes rails#55866
emirn added a commit to emirn/rails that referenced this pull request Apr 2, 2026
@emirn
[rails#55866] Fix SQLite3 data loss when altering tables with CASCADE…
23485b6 
… foreign keys inside `ActiveRecord::Base.connection.transaction`.

Silent cascade removal of child records was fixed in rails#55907 but when table alteration code was wrapped by `ActiveRecord::Base.connection.transaction`, SQLite3 ignored `PRAGMA foreign_keys = OFF`.

This fix ensures that the transaction (if exists) is now temporarily committed before `disable_referential_integrity` runs for the `PRAGMA` to take effect.

Fixes rails#55866
emirn added a commit to emirn/rails that referenced this pull request Apr 2, 2026
@emirn
[rails#55866] Fix SQLite3 data loss when altering tables with CASCADE…
64a3209 
… foreign keys inside `ActiveRecord::Base.connection.transaction`.

Silent cascade removal of child records was fixed in rails#55907 but when table alteration code was wrapped by `ActiveRecord::Base.connection.transaction`, SQLite3 ignored `PRAGMA foreign_keys = OFF`.

This fix ensures that the transaction (if exists) is now temporarily committed before `disable_referential_integrity` runs for the `PRAGMA` to take effect.

Fixes rails#55866
emirn added a commit to emirn/rails that referenced this pull request Apr 2, 2026
@emirn
[rails#55866] Fix SQLite3 data loss when altering tables with CASCADE…
24250bb 
… foreign keys inside `ActiveRecord::Base.connection.transaction`.

Silent cascade removal of child records was fixed in rails#55907 but when table alteration code was wrapped by `ActiveRecord::Base.connection.transaction`, SQLite3 ignored `PRAGMA foreign_keys = OFF`.

This fix ensures that the transaction (if exists) is now temporarily committed before `disable_referential_integrity` runs for the `PRAGMA` to take effect.

Fixes rails#55866
emirn added a commit to emirn/rails that referenced this pull request Apr 2, 2026
@emirn
[rails#55866] Fix SQLite3 data loss when altering tables with CASCADE…
290732e 
… foreign keys inside `ActiveRecord::Base.connection.transaction`.

Silent cascade removal of child records was fixed in rails#55907 but when table alteration code was wrapped by `ActiveRecord::Base.connection.transaction`, SQLite3 ignored `PRAGMA foreign_keys = OFF`.

This fix ensures that the transaction (if exists) is now temporarily committed before `disable_referential_integrity` runs for the `PRAGMA` to take effect.

Fixes rails#55866
emirn added a commit to emirn/rails that referenced this pull request Apr 2, 2026
@emirn
[rails#55866] Fix SQLite3 data loss when altering tables with CASCADE…
60d0b10 
… foreign keys inside `ActiveRecord::Base.connection.transaction`.

Silent cascade removal of child records was fixed in rails#55907 but when table alteration code was wrapped by `ActiveRecord::Base.connection.transaction`, SQLite3 ignored `PRAGMA foreign_keys = OFF`.

This fix ensures that the transaction (if exists) is now temporarily committed before `disable_referential_integrity` runs for the `PRAGMA` to take effect.

Fixes rails#55866
emirn added a commit to emirn/rails that referenced this pull request Apr 2, 2026
@emirn
[rails#55866] Fix SQLite3 data loss when altering tables with CASCADE…
dfec2ed 
… foreign keys inside `ActiveRecord::Base.connection.transaction`.

Silent cascade removal of child records was fixed in rails#55907 but when table alteration code was wrapped by `ActiveRecord::Base.connection.transaction`, SQLite3 ignored `PRAGMA foreign_keys = OFF`.

This fix ensures that the transaction (if exists) is now temporarily committed before `disable_referential_integrity` runs for the `PRAGMA` to take effect.

Fixes rails#55866
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@skipkayhil skipkayhil Awaiting requested review from skipkayhil

@rafaelfranca rafaelfranca Awaiting requested review from rafaelfranca

Assignees

No one assigned

Labels

activerecord

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

Migrations that alter SQLite table structure can cause data loss

4 participants

@ruyrocha @byroot @rafaelfranca @skipkayhil