Re: [Django] #30023: SQLite schema editor can cause table corruption if used within a transaction since Django 2.0

[Django]

#30023: SQLite schema editor can cause table corruption if used within a
transaction since Django 2.0
-+-
 Reporter:  Simon Charette   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  closed
Component:  Migrations   |  Version:  2.0
 Severity:  Release blocker  |   Resolution:  fixed
 Keywords:   | Triage Stage:  Ready for
 |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette ):

 In [changeset:"ecece1b22e35865cee429e239bcdd19f01925c9d" ecece1b2]:
 {{{
 #!CommitTicketReference repository=""
 revision="ecece1b22e35865cee429e239bcdd19f01925c9d"
 [2.0.x] Fixed #30023 -- Prevented SQLite schema alterations while foreign
 key checks are enabled.

 Prior to this change foreign key constraint references could be left
 pointing
 at tables dropped during operations simulating unsupported table
 alterations
 because of an unexpected failure to disable foreign key constraint checks.

 SQLite3 does not allow disabling such checks while in a transaction so
 they
 must be disabled beforehand.

 Thanks ezaquarii for the report and Carlton and Tim for the review.

 Backport of 315357ad25a6590e7f4564ec2e56a22132b09001 from master.
 }}}

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.59fdf9eafdbd3fe9ab7a0f84365b24a8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30023: SQLite schema editor can cause table corruption if used within a transaction since Django 2.0

[Django]

#30023: SQLite schema editor can cause table corruption if used within a
transaction since Django 2.0
-+-
 Reporter:  Simon Charette   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  closed
Component:  Migrations   |  Version:  2.0
 Severity:  Release blocker  |   Resolution:  fixed
 Keywords:   | Triage Stage:  Ready for
 |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette ):

 In [changeset:"7cf9d15cf80df4ed9a1179bc457ff2d03b5e5cb3" 7cf9d15c]:
 {{{
 #!CommitTicketReference repository=""
 revision="7cf9d15cf80df4ed9a1179bc457ff2d03b5e5cb3"
 [2.1.x] Fixed #30023 -- Prevented SQLite schema alterations while foreign
 key checks are enabled.

 Prior to this change foreign key constraint references could be left
 pointing
 at tables dropped during operations simulating unsupported table
 alterations
 because of an unexpected failure to disable foreign key constraint checks.

 SQLite3 does not allow disabling such checks while in a transaction so
 they
 must be disabled beforehand.

 Thanks ezaquarii for the report and Carlton and Tim for the review.

 Backport of 315357ad25a6590e7f4564ec2e56a22132b09001 from master.
 }}}

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.a3df67c7022fe32395b9f9ae8239de17%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30023: SQLite schema editor can cause table corruption if used within a transaction since Django 2.0

[Django]

#30023: SQLite schema editor can cause table corruption if used within a
transaction since Django 2.0
-+-
 Reporter:  Simon Charette   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  closed
Component:  Migrations   |  Version:  2.0
 Severity:  Release blocker  |   Resolution:  fixed
 Keywords:   | Triage Stage:  Ready for
 |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Simon Charette ):

 * status:  assigned => closed
 * resolution:   => fixed


Comment:

 In [changeset:"315357ad25a6590e7f4564ec2e56a22132b09001" 315357ad]:
 {{{
 #!CommitTicketReference repository=""
 revision="315357ad25a6590e7f4564ec2e56a22132b09001"
 Fixed #30023 -- Prevented SQLite schema alterations while foreign key
 checks are enabled.

 Prior to this change foreign key constraint references could be left
 pointing
 at tables dropped during operations simulating unsupported table
 alterations
 because of an unexpected failure to disable foreign key constraint checks.

 SQLite3 does not allow disabling such checks while in a transaction so
 they
 must be disabled beforehand.

 Thanks ezaquarii for the report and Carlton and Tim for the review.
 }}}

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.e0853d5debc5daa3dd34bbd2bcadf498%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30023: SQLite schema editor can cause table corruption if used within a transaction since Django 2.0

[Django]

#30023: SQLite schema editor can cause table corruption if used within a
transaction since Django 2.0
-+-
 Reporter:  Simon Charette   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  assigned
Component:  Migrations   |  Version:  2.0
 Severity:  Release blocker  |   Resolution:
 Keywords:   | Triage Stage:  Ready for
 |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Carlton Gibson):

 * stage:  Accepted => Ready for checkin


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.9f5dda84498e43002711c466d78a8749%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30023: SQLite schema editor can cause table corruption if used within a transaction since Django 2.0

[Django]

#30023: SQLite schema editor can cause table corruption if used within a
transaction since Django 2.0
-+-
 Reporter:  Simon Charette   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  assigned
Component:  Migrations   |  Version:  2.0
 Severity:  Release blocker  |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Muflone):

 * cc: Muflone (added)


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.637fe6fc901c6214271ddccec100d15c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30023: SQLite schema editor can cause table corruption if used within a transaction since Django 2.0

[Django]

#30023: SQLite schema editor can cause table corruption if used within a
transaction since Django 2.0
-+-
 Reporter:  Simon Charette   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  assigned
Component:  Migrations   |  Version:  2.0
 Severity:  Release blocker  |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Description changed by Simon Charette:

Old description:

> From Django 2.0+ SQLite schema editor requires foreign key constraint
> checking to be disabled before performing any operations to make sure
> foreign keys are not left pointing at `__old` tables when performing
> operations requiring table rebuilds.
>
> Since [https://sqlite.org/foreignkeys.html#fk_enable SQLite prevents
> foreign key constraint checking from being disabled within a transaction]
> `SchemaEditor(atomic=True).__enter__()` has no choice but to disable
> foreign key constraints **before** opening the transaction meant to
> ensure atomic DDL operations.
>
> One edge case that `SchemaEditor` doesn't account for though is that the
> it might contextually be used within an already opened transaction that
> would prevent foreign key constraints from being effectively disabled and
> result in silent referent table corruption when an operation requiring a
> table is rebuild id performed.
>
> In order to prevent this from happening `SchemaEditor().__enter__()`
> should ensure foreign key constraint checks are effectively disabled
> after requesting it and error out if it's not the case.
>
> This assertion should be more adequate than preventing schema editor from
> being used in a transaction altogether as disabling constraint checks
> before opening a transaction works just fine as well.
>
> For example
>
> {{{#!python
> with transaction.atomic():
> call_command('migrate')
> }}}
>
> Just has to be converted to
>
> {{{#!python
> with connection.constraint_checks_disabled(), transaction.atomic():
> call_command('migrate')
> }}}
>
> And work just fine.
>
> ''This is was originally reported in #29182 but was hijacked to deal with
> a SQLite 3.26 issue with similar symptoms and can be reproduced in a
> [https://github.com/ezaquarii/django-sqlite-migration-bug test project].

New description:

 From Django 2.0+ SQLite schema editor requires foreign key constraint
 checking to be disabled to make sure foreign keys are not left pointing at
 `__old` tables when performing operations requiring table rebuilds.

 Since [https://sqlite.org/foreignkeys.html#fk_enable SQLite prevents
 foreign key constraint checking from being disabled within a transaction]
 `SchemaEditor(atomic=True).__enter__()` has no choice but to disable
 foreign key constraints **before** opening the transaction meant to ensure
 atomic DDL operations.

 One edge case that `SchemaEditor` doesn't account for though is that the
 it might contextually be used within an already opened transaction that
 would prevent foreign key constraints from being effectively disabled and
 result in silent referent table corruption when an operation requiring a
 table is rebuild id performed.

 In order to prevent this from happening `SchemaEditor().__enter__()`
 should ensure foreign key constraint checks are effectively disabled after
 requesting it and error out if it's not the case.

 This assertion should be more adequate than preventing schema editor from
 being used in a transaction altogether as disabling constraint checks
 before opening a transaction works just fine as well.

 For example

 {{{#!python
 with transaction.atomic():
 call_command('migrate')
 }}}

 Just has to be converted to

 {{{#!python
 with connection.constraint_checks_disabled(), transaction.atomic():
 call_command('migrate')
 }}}

 And work just fine.

 ''This is was originally reported in #29182 but was hijacked to deal with
 a SQLite 3.26 issue with similar symptoms and can be reproduced in a
 [https://github.com/ezaquarii/django-sqlite-migration-bug test project].

--

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion 

Re: [Django] #30023: SQLite schema editor can cause table corruption if used within a transaction since Django 2.0 (was: SQLite schema editor can cause table corruption if unsed within a transaction s

[Django]

#30023: SQLite schema editor can cause table corruption if used within a
transaction since Django 2.0
-+-
 Reporter:  Simon Charette   |Owner:  Simon
 |  Charette
 Type:  Bug  |   Status:  assigned
Component:  Migrations   |  Version:  2.0
 Severity:  Release blocker  |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.03bf62c0ed67b02f1e19fb05797038cf%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.