in-memory test database does not work with threads

[bluebird75]

When using the test configuration of the DB with XXX and accessing the DB from another thread, it fails miserably.

Using this example script on the Poll tutorial:

import os                                                                       
os.environ[ 'DJANGO_SETTINGS_MODULE' ] = 'settings'                             
import settings                                                                 
import datetime, threading                                                      
                                                                                
#django  stuff                                                                  
from polls.models import *                                                      
from django.core.mail import mail_admins                                        
from django.test.utils import *                                                 
from django.db import connection                                                
                                                                                
                                                                                
def create_object():                                                            
    print 'Creating Poll'                                                       
    p = Poll()                                                                  
    p.question = "What's up doc ?"                                              
    p.pub_date = datetime.date.today()                                          
    p.save()                                                                    
    print 'Poll object saved. Id: %d' % p.id                                    
                                                                                
                                                                                
WITH_THREAD = False                                                             
                                                                                
if __name__ == '__main__':                                                      
    setup_test_environment()                                                    
    old_db_name = settings.DATABASE_NAME                                        
    new_db_name = connection.creation.create_test_db(verbosity=1)               
    print 'New DATABASE:', new_db_name                                          
                                                                                
    if WITH_THREAD:                                                             
        t = threading.Thread( target=create_object )                            
        t.start()                                                               
        t.join()                                                                
    else:                                                                       
        create_object()                                                         
                                                                                
    teardown_test_environment()                                                 
    connection.creation.destroy_test_db( old_db_name )             

If I run it with WITH_THREADS set to False:

Philippe@pc-philippe /cygdrive/d/work/django/django-tuto/mysite $ python run_wi 
th_threads.py                                                                   
Creating test database...                                                       
Creating table auth_permission                                                  
Creating table auth_group                                                       
Creating table auth_user                                                        
Creating table auth_message                                                     
Creating table django_admin_log                                                 
Creating table django_content_type                                              
Creating table django_session                                                   
Creating table django_site                                                      
Creating table polls_poll                                                       
Creating table polls_choice                                                     
Installing index for auth.Permission model                                      
Installing index for auth.Message model                                         
Installing index for admin.LogEntry model                                       
Installing index for polls.Choice model                                         
New DATABASE: :memory:                                                          
Creating Poll                                                                   
Poll object saved. Id: 1                                                        
Destroying test database...   

If I run it with WITH_THREADS set to True:

Philippe@pc-philippe /cygdrive/d/work/django/django-tuto/mysite $ python run_wi 
th_threads.py                                                                   
Creating test database...                                                       
Creating table auth_permission                                                  
Creating table auth_group                                                       
Creating table auth_user                                                        
Creating table auth_message                                                     
Creating table django_admin_log                                                 
Creating table django_content_type                                              
Creating table django_session                                                   
Creating table django_site                                                      
Creating table polls_poll                                                       
Creating table polls_choice                                                     
Installing index for auth.Permission model                                      
Installing index for auth.Message model                                         
Installing index for admin.LogEntry model                                       
Installing index for polls.Choice model                                         
New DATABASE: :memory:                                                          
Creating Poll                                                                   
Exception in thread Thread-1:                                                   
Traceback (most recent call last):                                              
  File "c:\Python26\lib\threading.py", line 522, in __bootstrap_inner           
    self.run()                                                                  
  File "c:\Python26\lib\threading.py", line 477, in run                         
    self.__target(*self.__args, **self.__kwargs)                                
  File "run_with_threads.py", line 19, in create_object                         
    p.save()                                                                    
  File "c:\Python26\lib\site-packages\django\db\models\base.py", line 410, in save                                                                              
    self.save_base(force_insert=force_insert, force_update=force_update)        
  File "c:\Python26\lib\site-packages\django\db\models\base.py", line 495, in save_base                                                                         
    result = manager._insert(values, return_id=update_pk)                       
  File "c:\Python26\lib\site-packages\django\db\models\manager.py", line 177, in _insert                                                                        
    return insert_query(self.model, values, **kwargs)                           
  File "c:\Python26\lib\site-packages\django\db\models\query.py", line 1087, in 
insert_query                                                                    
    return query.execute_sql(return_id)                                         
  File "c:\Python26\lib\site-packages\django\db\models\sql\subqueries.py", line 
320, in execute_sql                                                             
    cursor = super(InsertQuery, self).execute_sql(None)                         
  File "c:\Python26\lib\site-packages\django\db\models\sql\query.py", line 2369, in execute_sql                                                                 
    cursor.execute(sql, params)                                                 
  File "c:\Python26\lib\site-packages\django\db\backends\util.py", line 19, in execute                                                                          
    return self.cursor.execute(sql, params)                                     
  File "c:\Python26\lib\site-packages\django\db\backends\sqlite3\base.py", line 
193, in execute                                                                 
    return Database.Cursor.execute(self, query, params)                         
OperationalError: no such table: polls_poll                                     
                                                                                
Destroying test database...  

[Karen Tracey]

Near as I can tell this is an sqlite or pysqlite restriction, though I haven't been able to find a definitive statement in sqlite or pysqlite doc. SQLAlchemy, however, appears to have come to the conclusion that you simply cannot share :memory databases between threads, see: ​http://www.sqlalchemy.org/docs/06/reference/dialects/sqlite.html#threading-behavior. Thus I think there is no bug in Django here, the answer is don't do that and if you really want to do it you'll need to take it up with the maintainers of the lower level code.

[anonymous]

I vote to reopen this.

sqlite, as of version 3.7.13 (released 2012-06-11) has the ability to share an in-memory database between multiple connections and threads.

See: ​http://www.sqlite.org/releaselog/3_7_13.html

Making this work with the Django testing framework should be pretty easy:

In the sqlite database backend, instead of using the database name :memory:, we should use a name such as file:testdb?mode=memory&cache=shared (where "testdb" can be anything and ideally should be unique so that multiple tests can run concurrently, each with its own individual database).

As a bonus, doing this should allow removing the hacky sqlite-specific code from "LiveServerTestCase" (It contains a messy workaround for exactly the issue of this bug report)

The only thing that might be a little tricky is making this update in the Django code to still support older vesions of sqlite by falling back to the current behavior (:memory:), but I imagine that this shouldn't be too difficult.

[Andriy Sokolovskiy]

Pull request here: ​https://github.com/django/django/pull/3677

[Tim Graham <timograham@…>]

In 8c99b7920e8187f98cf4d7dbd9918bd6c6da1238:

Fixed #12118 -- Added shared cache support to SQLite in-memory testing.

[Harry Percival]

Incidentally, in case it's of use to anyone else that's temporarily stuck on an earlier versions of django, you can hack a workaround for this by using a path in /dev/shm in TEST_NAME:

DATABASES['default']['TEST_NAME'] = '/dev/shm/myproject-djangotestdb.sqlite'

[Tim Graham <timograham@…>]

In 3543fec3:

Refs #12118 -- Allowed "mode=memory" in SQLite test database names.

[Tim Graham <timograham@…>]

In 6f653f75:

[1.9.x] Refs #12118 -- Allowed "mode=memory" in SQLite test database names.

Backport of 3543fec3b739864c52de0a116dde3b0e5e885799 from master

[Tim Graham <timograham@…>]

In e2db171e:

[1.8.x] Refs #12118 -- Allowed "mode=memory" in SQLite test database names.

Backport of 3543fec3b739864c52de0a116dde3b0e5e885799 from master

[Tata Krushna Chaitanya]

Hi,

I am using Django 1.11 (Default on Ubuntu 18.04), but I guess the issue is still applicable in the latest as well. The above example works but if I replace threading with multiprocessing.apply_async it doesn't work. Here is my sample, Looking at the Django code I have made sure that all requirements are met, I am using py3.6 and the sqlite3 version is also met.

$ python3 -c "from sqlite3 import dbapi2 as Database; print
(Database.__name__, Database.sqlite_version__info)"

sqlite3.dbapi2 (3, 22, 0)

import os

os.environ["DJANGO_SETTINGS_MODULE"] = "settings"
import datetime, threading
from multiprocessing import Pool

# django  stuff
import django

django.setup()
from polls.models import *
from django.core.mail import mail_admins
from django.test.utils import *
from django.db import connection


def create_object():
    print("Creating Poll")
    p = Question()
    p.question_text = "What's up doc ?"
    p.pub_date = datetime.date.today()
    p.save()
    print("Poll object saved. Id: %d" % p.id)


MODULE = 1

if __name__ == "__main__":
    setup_test_environment()
    old_db_name = "db.sqlite3"
    new_db_name = connection.creation.create_test_db(verbosity=1)
    print("New DATABASE:", new_db_name)

    if MODULE == 0:
        t = threading.Thread(target=create_object)
        t.start()
        t.join()
    elif MODULE == 1:
        with Pool(2) as p:
            p.apply_async(create_object)
    else:
        create_object()
    obj = Question.objects.get()
    print(obj.question_text)
    teardown_test_environment()
    connection.creation.destroy_test_db(old_db_name)

[Mariusz Felisiak]

Please don't reopen old tickets. You can create a new ticket, but Django 1.11 is no longer supported so you need to first confirm this issue in Django 3.1+.