trac.db.tests.api test failures with sqlite

[Christian Boos]

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

@@ -323 +323 @@
 
     def drop_table(self, table):
         cursor = self.cursor()
-        cursor.execute("DROP TABLE IF EXISTS " + self.quote(table))
+        try:
+            cursor.execute("DROP TABLE" + self.quote(table))
+        except:
+            pass
 
     def get_column_names(self, table):
         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.

[Christian Boos]

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

@@ -323 +323 @@
 
     def drop_table(self, table):
         cursor = self.cursor()
-        cursor.execute("DROP TABLE IF EXISTS " + self.quote(table))
+        if sqlite_version < (3, 7, 6):
+            # SQLite versions at least between 3.6.21 and 3.7.5 have a
+            # buggy behavior with DROP TABLE IF EXISTS (#12298)
+            try:
+                cursor.execute("DROP TABLE " + self.quote(table))
+            except sqlite.OperationalError: # "no such table"
+                pass
+        else:
+            cursor.execute("DROP TABLE IF EXISTS " + self.quote(table))
 
     def get_column_names(self, table):
         cursor = self.cnx.cursor()

[Ryan J Ollos]

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.

[Ryan J Ollos]

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.

[Christian Boos]

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.

[Christian Boos]

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).

[Ryan J Ollos]

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

[Christian Boos]

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.

[Christian Boos]

Fixed in r14411, merged in trunk in r14412.