Saving db object while iterating over a queryset larger than ITER_CHUNK_SIZE breaks with sqlite

[Jenan Wise]

Example

simpleapp/models.py:

from django.db import models

class SimpleModel(models.Model):
    num = models.IntegerField()

bugtest.py:

from django.db.models import query
from simpleapp.models import SimpleModel

# fill the db
for i in range(query.ITER_CHUNK_SIZE + 1): 
    SimpleModel(num=i).save()

for item in SimpleModel.objects.all():
    item.save() # breaks on *first* iteration of loop

Traceback:

Traceback (most recent call last):
  File "bugtest.py", line 19, in <module>
    item.save() # breaks on *first* iteration of loop
  File "/Library/Python/2.5/site-packages/django/db/models/base.py", line 272, in save
    self.save_base()
  File "/Library/Python/2.5/site-packages/django/db/models/base.py", line 341, in save_base
    transaction.commit_unless_managed()
  File "/Library/Python/2.5/site-packages/django/db/transaction.py", line 140, in commit_unless_managed
    connection._commit()
  File "/Library/Python/2.5/site-packages/django/db/backends/__init__.py", line 20, in _commit
    return self.connection.commit()
sqlite3.OperationalError: SQL logic error or missing database

Details

• When using just django.db.models.query.ITER_CHUNK_SIZE objects or fewer, no exception is raised.
• When forcing evaluation of the queryset, e.g. wrapping it in list(), no exception is raised.
• It doesn't matter whether the item being saved is one from the queryset. Any database save will break.

Affected DBs

• Both in-memory SQLite and SQLite using a file database have the same problem.
• PostgreSQL is unaffected
• MySQL untested

[Chris Beaven]

MySQL is unaffected (but I can confirm the sqlite error...)

[Ramiro Morales]

#7636 was a duplicate.

[Mikhail]

I confirm this error. Additionaly, I can report that error appears not only with saving, but with deleting objects too.

[Malcolm Tredinnick]

(In [7926]) Fixed #7411 -- worked around some possible transaction conflicts in SQLite.

[Dave Smith <davewsmith@…>]

On OSX Leopard (Python 2.5.1, SQLite3 3.4), r7926 introduces the following failure not present in r7925:

Updated to revision 7926.
Macintosh-8:tests dws$ python runtests.py --settings=test-settings
======================================================================
FAIL: Doctest: regressiontests.queries.models.__test__.API_TESTS
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Library/Python/2.5/site-packages/django/test/_doctest.py", line 2180, in runTest
    raise self.failureException(self.format_failure(new.getvalue()))
AssertionError: Failed doctest test for regressiontests.queries.models.__test__.API_TESTS
  File "/Users/dws/src/django_dev/tests/regressiontests/queries/models.py", line unknown line number, in API_TESTS

----------------------------------------------------------------------
File "/Users/dws/src/django_dev/tests/regressiontests/queries/models.py", line ?, in regressiontests.queries.models.__test__.API_TESTS
Failed example:
    for i, obj in enumerate(Number.objects.all()):
        obj.save()
        if i > 10: break
Exception raised:
    Traceback (most recent call last):
      File "/Library/Python/2.5/site-packages/django/test/_doctest.py", line 1267, in __run
        compileflags, 1) in test.globs
      File "<doctest regressiontests.queries.models.__test__.API_TESTS[217]>", line 2, in <module>
        obj.save()
      File "/Library/Python/2.5/site-packages/django/db/models/base.py", line 278, in save
        self.save_base()
      File "/Library/Python/2.5/site-packages/django/db/models/base.py", line 347, in save_base
        transaction.commit_unless_managed()
      File "/Library/Python/2.5/site-packages/django/db/transaction.py", line 140, in commit_unless_managed
        connection._commit()
      File "/Library/Python/2.5/site-packages/django/db/backends/__init__.py", line 20, in _commit
        return self.connection.commit()
    OperationalError: SQL logic error or missing database


----------------------------------------------------------------------
Ran 363 tests in 220.245s

FAILED (failures=1)

My test-settings.py is

DATABASE_ENGINE = 'sqlite3'
DATABASE_NAME = '/tmp/django-test'
ROOT_URLCONF = ''

[Malcolm Tredinnick]

Are you sure you have a fully consistent checkout? Because that's exactly the test failure you would see if you included the test change from [7926] and not the core change.

In any case, I can't do anything here until somebody on a similar system can confirm this. That test fails before [7926] is applied and passes afterwards (it was the whole point of the change!), so there's something subtle going on here if it's not just an incomplete checkout.

[Dave Smith <davewsmith@…>]

I did a fresh cvs co (to r7943) and still see the test failures.

The only thing out of the ordinary with my setup is that I upgraded from Tiger to Leopard. AFAICT, that's not an issue here, but I may be missing something. I'll poke further in that direction.

[Malcolm Tredinnick]

Can you also verify that the test itself still fails in [7925] (which I suspect it will)? Just patch the test file and run runtests.py --settings=settings queries (modify for the right settings file, etc). It sounds like there's something "special" (which isn't quite the expletive I'd like to use, but this is a family show) about SQLite on your system -- and possibly on all Macs everywhere -- that is causing even more problems than in the normal case. There are a few different build options for sqlite itself (not just the Python bindings), so strangeness is possible.

Are you using the system-installed SQLite? Or a version you found somewhere else (you shouldn't bring home strange copies of SQLite that you found in a bar, you know)?

[James Bennett]

Running r7943 on Mac OS X Leopard 10.5.4, SQLite 3.4.0, Python 2.5.1, the above-mentioned test passes.

[Dave Smith <davewsmith@…>]

Tests pass on a clean Leopard install. Must be something funky on my laptop. I'll investigate. If it looks like something that might affect more people (e.g., just people who've upgraded from Tiger to Leopard), I'll submit a new bug.

[Dave Smith <davewsmith@…>]

Discovered moments later to be a PYTHONPATH issue.