[Django] #29062: Sqlite in memory database does not work for testing.

[Django]

#29062: Sqlite in memory database does not work for testing.
-------------------------------------+-------------------------------------
Reporter: Juozas | Owner: nobody
Masiulis |
Type: Bug | Status: new
Component: Testing | Version: 2.0
framework | Keywords: sqlite, testing,
Severity: Normal | databases
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I am getting database table locked errors, when running tests which use
sqlite in memory database.
This issue was introduced with this commit:
https://github.com/django/django/commit/bece837829eafbc22f2598dadf82c9a8364b085a
I know this because reversing this commit resolves the issue for me.
If sqlite in memory testing is no longer supported, it should be mentioned
in the documentation. If it is supported, there needs to be some parameter
in the settings to use liveserver without multithreading. Or just make a
new class liveservermultithreaded.

--
Ticket URL: <https://code.djangoproject.com/ticket/29062>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#29062: Sqlite in memory database does not work for testing.
-------------------------------------+-------------------------------------

Reporter: Juozas Masiulis | Owner: nobody
Type: Bug | Status: new
Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage:
databases | Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

Please provide a sample project that reproduces the problem.

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:1>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase

-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: closed

Component: Testing framework | Version: 2.0

Severity: Normal | Resolution: needsinfo

Keywords: sqlite, testing, | Triage Stage:
databases | Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* status: new => closed
* resolution: => needsinfo

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:2>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody
Type: Bug | Status: closed
Component: Testing framework | Version: 2.0
Severity: Normal | Resolution: needsinfo
Keywords: sqlite, testing, | Triage Stage:
databases | Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Juozas Masiulis):

https://github.com/Euphorbium/django-bug-demo here is the sample project.
Sorry for the mess in the test, the issue is with multiprocessing. The
test pass with my version of django, check requirements, and fails with
the unmodified django.

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:3>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:

Keywords: sqlite, testing, | Triage Stage:
databases | Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Juozas Masiulis):

* status: closed => new
* resolution: needsinfo =>

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:4>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: closed

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:

| worksforme

Keywords: sqlite, testing, | Triage Stage:
databases | Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* status: new => closed

* resolution: => worksforme

Comment:

Is the error consistent? I ran your sample project ten times and the test
usually passes. Once in a while, I see this failure:
{{{
======================================================================
FAIL: testStuff (bugdemo.tests.TestDatabaseLocking)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/home/tim/code/django-bug-demo/bugdemo/tests.py", line 75, in
testStuff
self.assertEqual(Question.objects.count(), 30)
AssertionError: 29 != 30
}}}
I think you'll have to explain why Django is at fault to move this report
forward. Possibly the problem might be dependent on the Python version. I
tested with Python 3.4.7, 3.5.5, and 3.6.4.

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:5>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:

Keywords: sqlite, testing, | Triage Stage:
databases | Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Juozas Masiulis):

* status: closed => new

* resolution: worksforme =>

Comment:

It is a race condition. It is consistent in my system, but it gets locked
by node process, and I did not want to go outside of python to demonstrate
the bug. It needs something faster to lock the database consistently.

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:6>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage:
databases | Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

Unfortunately your comment doesn't help with my understanding of the
problem. Can explain why Django is at fault or propose a solution? If not,
I'm not sure how to move this issue forward.

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:7>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: closed

Component: Testing framework | Version: 2.0

Severity: Normal | Resolution: needsinfo

Keywords: sqlite, testing, | Triage Stage:
databases | Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* status: new => closed

* resolution: => needsinfo

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:8>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:

Keywords: sqlite, testing, | Triage Stage:
databases | Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Juozas Masiulis):

* status: closed => new

* resolution: needsinfo =>

Comment:

Have you tried running it with my version, which is in the
requirements.txt? That NEVER fails. This is proof that it is a django
problem, and it was introduced in
https://github.com/django/django/commit/bece837829eafbc22f2598dadf82c9a8364b085a

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:9>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage:
databases | Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

Just because the behavior changed doesn't mean Django is at fault. You'll
need to investigate and explain why Django is at fault. It might be that
your testing code is at fault.

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:10>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage:

databases | Someday/Maybe
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* stage: Unreviewed => Someday/Maybe

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:11>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage:
databases | Someday/Maybe

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Andrew Badr):

Yep, I'm hitting this too. See workaround at
https://stackoverflow.com/questions/48353002/sqlite-database-table-is-
locked-on-tests

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:12>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:

Keywords: sqlite, testing, | Triage Stage: Accepted
databases |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Andrew Badr):

* stage: Someday/Maybe => Accepted

Comment:

Moving to 'Accepted' -- breaking test suites is a serious enough bug, and
multiple people have hit it. Someday/Maybe is not appropriate here.

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:13>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage: Accepted
databases |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Daniel Hahler):

Just a drive-by comment since I've seen a similar issue and found this:
check/ensure that transactions are enabled (i.e. with pytest-django
`transactional_db` instead of `db` should be used).
Also check the code around
https://github.com/django/django/commit/bece837829eafbc22f2598dadf82c9a8364b085a
#diff-5d7d8ead1a907fe91ffc121f830f2a49L1287.

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:14>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage: Accepted
databases |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Chris Spencer):

Replying to [comment:12 Andrew Badr]:

> Yep, I'm hitting this too. See workaround at
https://stackoverflow.com/questions/48353002/sqlite-database-table-is-
locked-on-tests

This workaround does not work for me. I'm using the LiveServerTestCase to
run a series of Selenium tests checking Ajax functionality, so the result
of switching the LiveServerThread back to single-threaded WSGIServer only
replaces the deadlock error with a timeout error. The Ajax calls timeout
waiting for the main server thread to exit, which it never does.

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:15>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage: Accepted
databases |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Matthijs Kooijman):

What seems to be relevant here (might be obvious for others, but was not
for me) is that Sqlite does not support a "wait until the lock is
released" flow with the way Django implements transactions on Sqlite. This
means that whenever you try to write to a database that already holds a
write (`EXCLUSIVE` in Sqlite terms I think) lock, it will immediately fail
rather than wait for the lock to be released. The underlying reason for
this is described in https://code.djangoproject.com/ticket/29280

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:16>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage: Accepted
databases |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Chris Jerdonek):

FYI, yesterday I found and described the root cause of this issue on
#32416. (There I was interested in a different issue that affected non-
SQLite databases, though, namely database connections not being closed
when `LiveServerTestCase` is used with the new threading behavior.)

If you read my first comments on that issue, you can see that a possible
fix is to define suitable method overrides in `ThreadedWSGIServer`.
Basically the same SQLite-specific connection-sharing logic that
`LiveServerTestCase.LiveServerThread()` and `LiveServerThread.run()` have
needs to be added to `ThreadedWSGIServer`. The `LiveServerTestCase` and
`LiveServerThread` logic can be found more or less here:
https://github.com/django/django/blob/63d239db037f02d98b7771c90422840bbb4a319a/django/test/testcases.py#L1524-L1537
https://github.com/django/django/blob/63d239db037f02d98b7771c90422840bbb4a319a/django/test/testcases.py#L1465-L1469

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:17>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage: Accepted
databases |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Chris Jerdonek):

I posted a PR ([https://github.com/django/django/pull/14011 PR #14011]) on
ticket #32416 that, once merged, I believe should allow this ticket to be
fixed with a one-line change (not counting any test(s)):

{{{
diff --git a/django/test/testcases.py b/django/test/testcases.py
index cc2f24dd5d..8717eec1c5 100644
--- a/django/test/testcases.py
+++ b/django/test/testcases.py
@@ -1472,7 +1472,9 @@ class LiveServerThread(threading.Thread):
try:
# Create the handler for serving static and media files
handler =
self.static_handler(_MediaFilesHandler(WSGIHandler()))
- self.httpd = self._create_server()
+ self.httpd = self._create_server(
+ connections_override=self.connections_override
+ )
# If binding to port zero, assign the port allocated by the
OS.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:18>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner: nobody

Type: Bug | Status: new

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage: Accepted
databases |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Chris Jerdonek):

FYI, #32416 has now been fixed and
[https://github.com/django/django/pull/14011 PR #14011] merged (thanks,
Mariusz!), which should make this ticket a lot easier to resolve (see
comment above).

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:19>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner:

| Christophe Baldy
Type: Bug | Status: assigned

Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage: Accepted
databases |

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Christophe Baldy):

* owner: nobody => Christophe Baldy
* status: new => assigned

Comment:

I posted a PR for this ticket
(https://github.com/django/django/pull/16309) following Chris Jerdonek
previous fix

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:20>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner:
| Christophe Baldy
Type: Bug | Status: assigned
Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage: Accepted
databases |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:21>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner:
| Christophe Baldy
Type: Bug | Status: assigned
Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:
Keywords: sqlite, testing, | Triage Stage: Accepted
databases |

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:22>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner:
| Christophe Baldy
Type: Bug | Status: assigned
Component: Testing framework | Version: 2.0
Severity: Normal | Resolution:

Keywords: sqlite, testing, | Triage Stage: Ready for
databases | checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:23>

[Django]

#29062: "database table locked errors" when using sqlite in-memory database with
LiveServerTestCase
-------------------------------------+-------------------------------------
Reporter: Juozas Masiulis | Owner:
| Christophe Baldy

Type: Bug | Status: closed

Component: Testing framework | Version: 2.0

Severity: Normal | Resolution: fixed

Keywords: sqlite, testing, | Triage Stage: Ready for
databases | checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

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

Comment:

In [changeset:"855f5a36e7c8e7a8ce3f62d6ef8c9ae3e073ae3d" 855f5a36]:
{{{
#!CommitTicketReference repository=""
revision="855f5a36e7c8e7a8ce3f62d6ef8c9ae3e073ae3d"
Fixed #29062 -- Prevented possibility of database lock when using
LiveServerTestCase with in-memory SQLite database.

Thanks Chris Jerdonek for the implementation idea.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29062#comment:24>