Opened 4 years ago

Closed 4 years ago

Last modified 3 years ago

#16481 closed Bug (fixed)

DBCacheTests.test_cull fails under Oracle

Reported by: aaugustin Owned by: aaugustin
Component: Core (Cache system) Version: 1.3
Severity: Release blocker Keywords: Oracle
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by aaugustin)

$ python runtests.py --settings=test_settings.oracle cache
Creating test database for alias 'default'...
Creating test user...
Creating test database for alias 'other'...
Creating test user...
...........................E................................................................................................................ssssssssssssssssssssssssssssssss.......
======================================================================
ERROR: test_cull (regressiontests.cache.tests.DBCacheTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/tests/regressiontests/cache/tests.py", line 750, in test_cull
    self.perform_cull_test(50, 29)
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/tests/regressiontests/cache/tests.py", line 417, in perform_cull_test
    self.cache.set('cull%d' % i, 'value', 1000)
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/django/core/cache/backends/db.py", line 65, in set
    self._base_set('set', key, value, timeout)
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/django/core/cache/backends/db.py", line 84, in _base_set
    self._cull(db, cursor, now)
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/django/core/cache/backends/db.py", line 138, in _cull
    cursor.execute("SELECT cache_key FROM %s ORDER BY cache_key LIMIT 1 OFFSET %%s" % table, [num / self._cull_frequency])
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/django/db/backends/oracle/base.py", line 638, in execute
    return self.cursor.execute(query, self._param_generator(params))
DatabaseError: ORA-00933: SQL command not properly ended


----------------------------------------------------------------------
Ran 179 tests in 26.809s

FAILED (errors=1, skipped=32)
Destroying test database for alias 'default'...
Destroying test user...
Destroying test database tables...
Destroying test database for alias 'other'...
Destroying test user...
Destroying test database tables...

Attachments (1)

16481.patch (1.3 KB) - added by aaugustin 4 years ago.

Download all attachments as: .zip

Change History (10)

comment:1 Changed 4 years ago by aaugustin

  • Keywords Oracle added
  • Owner changed from nobody to aaugustin
  • Status changed from new to assigned

comment:2 Changed 4 years ago by aaugustin

  • Owner changed from aaugustin to nobody
  • Status changed from assigned to new
  • Triage Stage changed from Unreviewed to Accepted

comment:4 Changed 4 years ago by aaugustin

  • Owner changed from nobody to aaugustin

comment:5 Changed 4 years ago by aaugustin

  • Description modified (diff)
  • Has patch set

Oracle doesn't support the combination of LIMIT and OFFSET. This is a very common source of question in forums.

I found this page which provides an analysis of this problem on several RDBMS: http://troels.arvin.dk/db/rdbms/#select-limit-offset

It says that the SQL we currently use, ORDER BY ... LIMIT ... OFFSET ..., isn't standard. But it's supported by PostgreSQL, MySQL and SQLite at least (the test passes on those engines). Also, it's the only solution supported by MySQL, and it's simple. As a consequence, I chose to add a special case for Oracle, and a comment to warn about the use of non-standard SQL.

Since the database cache backend isn't an industrial grade concept, I don't believe someone who has the resources to purchase an Oracle license will ever use it. This patch may be a bit hackish but it fixes the test suite with limited changes.

Changed 4 years ago by aaugustin

comment:6 Changed 4 years ago by aaugustin

NB: the patch is tested with Oracle 10g. I don't have Oracle 11g available, but the CI server does.

comment:7 Changed 4 years ago by ramiro

  • Resolution set to fixed
  • Status changed from new to closed

In [16635]:

Fixed #16481 -- Adapted one raw SQL query in cull immplementation of the database-based cache backend so it works with Oracle. Thanks Aymeric Augustin for the report and patch.

comment:8 Changed 4 years ago by aaugustin

This was a duplicate of #15580.

comment:9 Changed 3 years ago by aaugustin

In [17805]:

[1.3.x] Fixed #16481 -- Adapted one raw SQL query in cull implementation of the database-based cache backend so it works with Oracle. Backport of r16635 from trunk.

Note: See TracTickets for help on using tickets.
Back to Top