OperationalError: cannot commit transaction - SQL statements in progress

[PBruin]

I have noticed that I get these errors every now and then when I try to view the timeline. This seems always seems some how related to a previous commit action. This time I was unable to get past the error even with restarting my server. I only managed to get it working again when I went to browse the latest changeset via Trac. Is this a ​th:wiki:TagsPlugin issue?

Wiki changes event provider (TagsWikiModule) failed:

OperationalError: cannot commit transaction - SQL statements in progress

[PBruin]

I had the error again after my next commit. This time the error came from the Ticket Module. After this I disabled the tags module to be sure but it did not work. Only when I browsed the latest changeset the error would go away.

Ticket changes, Ticket details event provider (TicketModule) failed:

OperationalError: cannot commit transaction - SQL statements in progress

[PBruin]

Log file

[PBruin]

I can pretty much reproduce it. If I go to the timeline after a commit I will get an error on one of the modules changes event provider. The log I have attached show me restarting the server, checking the timeline resulting in error, going to WikiStart (you'll see some errors caused by me having disabled the tags plugin), going back to the timeline and still having an error, going to Browse Source, and then at last a successful visit to the timeline.

Wiki changes event provider (WikiModule) failed:

OperationalError: cannot commit transaction - SQL statements in progress

[Christian Boos]

Yes, this is a known issue… I plan to fix that in the coming days for trunk (see #2902) but it seems that this also happens on 0.10-stable, because of r3972.

For 0.10-stable, the approach suggested in #2902 won't work, of course.

Some possible solutions:

• add a flag to get_changeset() for skipping sync()
• in sync(), catch the OperationalError and ignore it

[Christian Boos]

add a flag to get_changeset() for skipping sync()

Well, that would contradict the purpose of r3971, introduced to fix #3387. It's the same get_changeset() call (sourcetrunk/trac/versioncontrol/web_ui/changeset.py@4255#L676) which is concerned, so we can't say sometimes "sync", sometimes not.

in sync(), catch the OperationalError and ignore it

Probably the best compromise. I think we should only catch the commit() failure, and ignore if it fails. The tables will still "look" ok for the request currently processed, and the sync will be retried in subsequent requests, possibly in a better context (i.e. no statement in progress).

cache.py

@@ -110 +110 @@
                                    (str(current_rev), path, kind, action,
                                    base_path, base_rev))
                 current_rev = self.repos.next_rev(current_rev)
-            self.db.commit()
+            try:
+                self.db.commit()
+            except:
+                pass
             self.repos.authz = authz # restore permission checking
 
     def get_node(self, path, rev=None):

Yet another approach would be to have a startup(tid) phase in the environment, symmetrical to the shutdown(tid). There, it would be safe to attempt a sync(), but this would introduce an additional cost for requests which would not have normally something to do with the versioncontrol subsystem…

Later, in the VcRefactoring, I'd like to do this with a dedicated thread. This would have some additional positive side-effects, like getting rid of the trac-post-commit-hook, no wait for the unfortunate user triggering a sync after a big commit, …

[PBruin]

I think the solution with the startup(tid) would be the cleanest. This will always work and can later be re-factored to the dedicated thread. It will also solve the small problem of links to change set showing as non-existing. This is caused I guess by the post-commit-hook adding references to change sets in tickets that have not been imported by the sync.
For now I resolved my problem by creating a work-around and adding a simple perl script to my post-commit-hook. Quick but dirty…

$inet = Win32::Internet->new();
$file = $inet->FetchURL( "http://localhost/trac/browser" );

[Christopher Lenz]

Patch by cboos applied in [4265]. IIUC, we've solved (are are going to solve) this in a better way on trunk.

[Christian Boos]

See also #2902 for a follow-up.