categories don't work with the sqlite backend?
Closed, DeclinedPublic


Author: phil

I made a clean 1.16.0 install using an sqlite backend, then created a page that contained only:


The "Category: Tests" link appeared correctly at the bottom of the page, but the Category:Tests page itself says only "This category currently contains no pages or media."

A repeat of the same test on a MySQL installation behaves correctly.

From my quick, ignorant poking at the DB, it looks ok -- both the categories and categorylinks tables have sensible-looking rows in them -- but I really have no experience here.

Interestingly, sub-categories do work correctly -- which makes me wonder if it's a side-effect of whatever was done to fix bug 16966?

Version: 1.16.x
Severity: major


bzimport raised the priority of this task from to Normal.
bzimport set Reference to bz25746.
bzimport created this task.Nov 1 2010, 11:13 PM
MaxSem added a comment.Nov 2 2010, 6:33 PM

Hi, I was unable to reproduce this on both 1.16 and trunk (the latter had lots of category-related changes though). Could you specify which SQLite version do you have (from Special:Version) and attach a sample database file?

phil wrote:

sqlite database that reproduces the bug (for me)

sqlite version 3.3.6

This a pristine just-installed database, with the single change of adding Main_Page to [[Category:Test]] and confirming that it doesn't appear (for me)


MaxSem added a comment.Nov 4 2010, 3:24 PM

I managed to find 3.3.7, and everything still worked for me. The only visible difference between your database and databases created by later versions of SQLite is PRAGMA legacy_file_format that is off for you and on for newer versions (this is a documented change in SQLite behaviour).

Can you try the following SQL:

SELECT page_title,page_namespace,page_len,page_is_redirect,cl_sortkey,cat_id,cat_title,cat_subcats,cat_pages,cat_files FROM page INNER JOIN categorylinks ON ((cl_from = page_id)) LEFT JOIN category ON ((cat_title = page_title AND page_namespace = 14)) WHERE (1 = 1) AND cl_to = 'Test' ORDER BY cl_sortkey LIMIT 201

To ensure that it's being run in the same environment, it should be executed via maintenance/sql.php

If everything works, you should receive one row of results. The behaviour you're observing indicates that it returns no rows for you.

On a side note, SQLite 3.3.6 is more than 4 years old, has bugs that were fixed in later versions and has no full-text search support. I wonder if we should drop support for it completely. Are your packages up to date? Or you're using something in the line of Debian Lenny, RHEL and XAMPP?

Phil, did you try Max's select query?

No response, assuming it's due to antiquated SQLite version. I'll consider adding a warning when installing on anything less than 3.6.