Edgewall Software
Modify

Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#12298 closed defect (fixed)

trac.db.tests.api test failures with sqlite

Reported by: Christian Boos Owned by: Christian Boos
Priority: normal Milestone: 1.0.10
Component: database backend Version: 1.0dev
Severity: minor Keywords: sqlite
Cc: Branch:
Release Notes:

Fixed test failures with SQLite version 3.6.21 to 3.7 due to unreliable behavior of DROP TABLE IF EXIST.

API Changes:
Internal Changes:

Description

I'm seeing some test failures on Windows (x64), Python 2.7.10 with sqlite3 module 2.6.0 (SQLite 3.6.21).

On 1.0-stable, the failure looks like this:

$ make test=trac/db/tests/api.py
[...]
python setup.py -q test -s trac.db.tests.api.suite
SKIP: fine-grained permission tests (ConfigObj not installed)
.........................E.............
======================================================================
ERROR: test_table_names (trac.db.tests.api.ConnectionTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "d:\trac\repos\1.0-stable\trac\db\tests\api.py", line 432, in setUp
    self.env.global_databasemanager.create_tables(self.schema)
  File "d:\trac\repos\1.0-stable\trac\db\api.py", line 266, in create_tables
    db(sql)
  File "d:\trac\repos\1.0-stable\trac\db\util.py", line 128, in execute
    cursor.execute(query, params if params is not None else [])
  File "d:\trac\repos\1.0-stable\trac\db\util.py", line 63, in execute
    r = self.cursor.execute(sql)
  File "d:\trac\repos\1.0-stable\trac\db\sqlite_backend.py", line 82, in execute
    result = PyFormatCursor.execute(self, *args)
  File "d:\trac\repos\1.0-stable\trac\db\sqlite_backend.py", line 60, in execute
    args or [])
  File "d:\trac\repos\1.0-stable\trac\db\sqlite_backend.py", line 52, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: table HOURS already exists

It's as if the DROP TABLE IF EXISTS statement wouldn't work reliably.

The following patch makes the tests pass again:

  • trac/db/sqlite_backend.py

     
    323323
    324324    def drop_table(self, table):
    325325        cursor = self.cursor()
    326         cursor.execute("DROP TABLE IF EXISTS " + self.quote(table))
     326        try:
     327            cursor.execute("DROP TABLE" + self.quote(table))
     328        except:
     329            pass
    327330
    328331    def get_column_names(self, table):
    329332        cursor = self.cnx.cursor()

However, it's not really satisfying…

Note that on Linux with Python 2.7.9 and sqlite3 2.6.0 as well, the error doesn't happen, but in this case the version of SQLite is 3.8.10.2.

So I built PySqlite 2.8.1 with latest SQLite (3.9.2), and there the above error doesn't happen either. PySqlite 2.8.1 no longer builds with SQLite 3.6.21, but PySqlite 2.7.0 does, and it triggers the error like sqlite3 did.

I'm now trying to pinpoint this problem to a specific version of SQLite.

Attachments (0)

Change History (9)

comment:1 by Christian Boos, 5 years ago

This issue no longer happens starting with SQLite 3.7.6.

Don't know yet what the problem was exactly, but regardless of that, the proposed fix would be:

  • trac/db/sqlite_backend.py

     
    323323
    324324    def drop_table(self, table):
    325325        cursor = self.cursor()
    326         cursor.execute("DROP TABLE IF EXISTS " + self.quote(table))
     326        if sqlite_version < (3, 7, 6):
     327            # SQLite versions at least between 3.6.21 and 3.7.5 have a
     328            # buggy behavior with DROP TABLE IF EXISTS (#12298)
     329            try:
     330                cursor.execute("DROP TABLE " + self.quote(table))
     331            except sqlite.OperationalError: # "no such table"
     332                pass
     333        else:
     334            cursor.execute("DROP TABLE IF EXISTS " + self.quote(table))
    327335
    328336    def get_column_names(self, table):
    329337        cursor = self.cnx.cursor()

comment:2 by Ryan J Ollos, 5 years ago

Just curious, do you get the same error with make db=sqlite test=trac/db/tests/api.py? That command would use a real SQLite database rather than an in-memory database.

comment:3 by Ryan J Ollos, 5 years ago

Related note, I've seen other projects using appveyor for testing in Windows, which would be a nice complement to Travis CI.

I've seen Jun running tests on Windows, so he might have some input.

in reply to:  2 comment:4 by Christian Boos, 5 years ago

Replying to rjollos:

Just curious, do you get the same error with make db=sqlite test=trac/db/tests/api.py? That command would use a real SQLite database rather than an in-memory database.

Yes, same results: the error also happens with SQLite 3.7.5 but not with SQLite 3.7.6.

in reply to:  3 comment:5 by Christian Boos, 5 years ago

Replying to rjollos:

Related note, I've seen other projects using appveyor for testing in Windows, which would be a nice complement to Travis CI.

Great! Thanks for the suggestion.

I'm currently experimenting, it looks quite promising (see e.g. build 1.0.3).

comment:6 by Ryan J Ollos, 5 years ago

Travis CI build break is probably due to new Babel release. I recall seeing a similar issue the other night but didn't investigate.

in reply to:  6 comment:7 by Christian Boos, 5 years ago

Owner: set to Christian Boos
Status: newassigned

Replying to rjollos:

Travis CI build break is probably due to new Babel release. I recall seeing a similar issue the other night but didn't investigate.

Yes, the failures were unrelated to the actual changes.

comment:8 by Christian Boos, 5 years ago

Resolution: fixed
Status: assignedclosed

Fixed in r14411, merged in trunk in r14412.

comment:9 by Ryan J Ollos, 5 years ago

Release Notes: modified (diff)

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Christian Boos.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Christian Boos to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.