Page MenuHomePhabricator

MediaWiki should support SQLite 3.8 (or later)
Open, LowPublic

Description

For more information about the issues with database locking, see T89180: Jenkins jobs using MediaWiki frequently fail due to database locks

Summary:

Other distros and versions:

Event Timeline

Krinkle created this task.Mar 18 2015, 4:36 PM
Krinkle raised the priority of this task from to Needs Triage.
Krinkle updated the task description. (Show Details)
Krinkle added a project: Mediawiki-Rdbms.
Krinkle added subscribers: Krinkle, aaron, greg and 2 others.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMar 18 2015, 4:36 PM

@Krinkle should this be closed as obsolete now? Trusty shipped PHP 5.5, so it wouldn't be able to run MediaWiki today anyways.

Krinkle added a comment.EditedMay 29 2018, 12:38 PM

@Legoktm If I recall correctly, this task was filed with "Trusty" being "new" not "old". In other words, it wasn't about primary versions working and other ones not.

It was about the then next/primary version not working. Unless this has been fixed in newer versions of Ubuntu, Debian or SQLite, it may still be broken and might still cause issues with N>1 requests to MediaWiki via Apache when using SQLite.

More details at T89180#1045226

Krinkle renamed this task from MediaWiki should support SQlite on Ubuntu Trusty to MediaWiki should support SQlite 3.8 (or later).May 29 2018, 12:42 PM
Krinkle renamed this task from MediaWiki should support SQlite 3.8 (or later) to MediaWiki should support SQLite 3.8 (or later).
Krinkle updated the task description. (Show Details)

To recap, SQLite 3.8+ generates an error when a read transaction is upgraded to a write transaction, if another thread holds the write lock. The solution to this is to use BEGIN IMMEDIATE instead of BEGIN in order to force the transaction to be a write transaction from the start. As a quick hack, a constructor parameter trxMode was added in 22508bec75fb05546520398ac0a195c7c79ef87e , which can be used in CI to force all transactions to be write transactions, at the expense of performance. The transactions would all be serialized instead of allowing parallel reads.

Ideally, Database::begin() would take a trxMode parameter, allowing calling code to select the transaction mode when starting the problematic transaction.

To avoid deadlocks, there can be only one connection to SQLite in a given request, so the alternate solution of using DB_MASTER/DB_REPLICA to select the transaction mode would be difficult, perhaps requiring a new kind of connection wrapper class.

Ideally, Database::begin() would take a trxMode parameter, allowing calling code to select the transaction mode when starting the problematic transaction.

Database::begin() would not be too hard. The problem with this is startAtomic() and doAtomicSection(), which can be called with a transaction already open. For example with LCStoreDB, a write may be needed on the first call to wfMessage(). It's hard to see how that could work. Easier to recommend that SQLite not be used in production.

aaron added a comment.Jul 5 2018, 11:14 AM

LCStoreDB should use a separate DB file, as does objectcache (by default, via the installer), if it is to be used at all. Better yet, is that $wgCacheDirectory can be set so that LCStoreCDB can be used.

I suppose LCStoreDB can be setup by the installer similar to the job queue for sqlite.

Change 443951 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/core@master] [WIP] Move l10n_cache table to a separate DB for sqlite via the installer

https://gerrit.wikimedia.org/r/443951

Change 443951 merged by jenkins-bot:
[mediawiki/core@master] Move l10n_cache table to a separate DB for sqlite via the installer

https://gerrit.wikimedia.org/r/443951

Change 463597 had a related patch set uploaded (by Krinkle; owner: MaxSem):
[mediawiki/core@master] Bump minimum SQLite version to 3.8.0

https://gerrit.wikimedia.org/r/463597

Change 495413 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/core@master] Use HTTP method as hint for the locking mode of sqlite

https://gerrit.wikimedia.org/r/495413

Change 495418 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/core@master] installer: make sqlite installer move the job queue to another DB

https://gerrit.wikimedia.org/r/495418

Change 495418 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/core@master] installer: make sqlite installer move the job queue to another DB

https://gerrit.wikimedia.org/r/495418

greg removed a subscriber: greg.Apr 2 2019, 8:32 PM

Change 495418 merged by jenkins-bot:
[mediawiki/core@master] installer: make sqlite installer move the job queue to another DB

https://gerrit.wikimedia.org/r/495418

Change 495413 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/core@master] Use HTTP method as hint for the locking mode of sqlite

https://gerrit.wikimedia.org/r/495413

Change 495413 merged by jenkins-bot:
[mediawiki/core@master] Use HTTP method as hint for the locking mode of sqlite

https://gerrit.wikimedia.org/r/495413

Core Platform: I know this was in your backlog for later, so feel free to defer to later. But, Aaron has made some progress on this task, possibly resolving it. I'm signing it over to you to validate whether the expected levels of support are now reached, and if not to identify any remaining work that could be done before this task can be resolved.