Page MenuHomePhabricator

Orphan files and postgresql in 1.22.1: ERROR: column "img_name" doesn't exist
Closed, DeclinedPublic

Description

Author: l.wandrebeck

Description:
special pages, orphan files brings up an error with PostgreSQL 9.3:
< 2014-01-14 08:47:08.118 CET >INSTRUCTION : SELECT /* UnusedimagesPage::reallyDoQuery Low */ 6 AS namespace,img_name AS title,img_timestamp AS value,img_user,img_user_text,img_description FROM "imagelinks" WHERE (il_to IS NULL) ORDER BY value LIMIT 51
< 2014-01-14 08:47:26.556 CET >ERROR: column « img_name » doesn’t exist at caracter 66


Version: 1.22.1
Severity: critical

Details

Reference
bz60031

Related Objects

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.

Event Timeline

bzimport raised the priority of this task from to High.Nov 22 2014, 2:52 AM
bzimport set Reference to bz60031.
bzimport added a subscriber: Unknown Object (MLST).

l.wandrebeck wrote:

Same regression pops up for several categories in special pages:
< 2014-01-14 09:02:52.565 CET >INSTRUCTION : SELECT /* UncategorizedImagesPage::reallyDoQuery Low */ page_namespace AS namespace,page_title AS title,page_title AS value FROM "categorylinks" WHERE (cl_from IS NULL) AND page_namespace = '6' AND page_is_redirect = '0' ORDER BY value LIMIT 51
< 2014-01-14 09:02:56.881 CET >ERROR: column « tl_namespace » doesn’t exist at character 54
< 2014-01-14 09:02:56.881 CET >INSTRUCTION : SELECT /* WantedTemplatesPage::reallyDoQuery Low */ tl_namespace AS namespace,tl_title AS title,COUNT(*) AS value FROM "page" WHERE (page_title IS NULL) AND tl_namespace = '10' GROUP BY tl_namespace,tl_title ORDER BY value DESC LIMIT 51
< 2014-01-14 09:03:02.828 CET >ERROR: column « page_namespace » doesn’t exist at character 54
< 2014-01-14 09:03:02.828 CET >INSTRUCTION : SELECT /* UnusedtemplatesPage::reallyDoQuery Low */ page_namespace AS namespace,page_title AS title,page_title AS value FROM "templatelinks" WHERE page_namespace = '10' AND (tl_from IS NULL) AND page_is_redirect = '0' ORDER BY value LIMIT 51
< 2014-01-14 09:09:07.225 CET >ERROR: column « page_namespace » doesn’t exist at character 61

l.wandrebeck wrote:

OK, I’ve checked every links in special pages, here’s the whole error log (in french sorry, but meaning is obvious).
< 2014-01-14 08:47:08.118 CET >ERREUR: la colonne « img_name » n'existe pas au caractère 66
< 2014-01-14 08:47:08.118 CET >INSTRUCTION : SELECT /* UnusedimagesPage::reallyDoQuery Low */ 6 AS namespace,img_name AS title,img_timestamp AS value,img_user,img_user_text,img_description FROM "imagelinks" WHERE (il_to IS NULL) ORDER BY value LIMIT 51
< 2014-01-14 08:47:26.556 CET >ERREUR: la colonne « img_name » n'existe pas au caractère 66
< 2014-01-14 08:47:26.556 CET >INSTRUCTION : SELECT /* UnusedimagesPage::reallyDoQuery Low */ 6 AS namespace,img_name AS title,img_timestamp AS value,img_user,img_user_text,img_description FROM "imagelinks" WHERE (il_to IS NULL) ORDER BY value LIMIT 51
< 2014-01-14 09:02:52.565 CET >ERREUR: la colonne « page_namespace » n'existe pas au caractère 58
< 2014-01-14 09:02:52.565 CET >INSTRUCTION : SELECT /* UncategorizedImagesPage::reallyDoQuery Low */ page_namespace AS namespace,page_title AS title,page_title AS value FROM "categorylinks" WHERE (cl_from IS NULL) AND page_namespace = '6' AND page_is_redirect = '0' ORDER BY value LIMIT 51
< 2014-01-14 09:02:56.881 CET >ERREUR: la colonne « tl_namespace » n'existe pas au caractère 54
< 2014-01-14 09:02:56.881 CET >INSTRUCTION : SELECT /* WantedTemplatesPage::reallyDoQuery Low */ tl_namespace AS namespace,tl_title AS title,COUNT(*) AS value FROM "page" WHERE (page_title IS NULL) AND tl_namespace = '10' GROUP BY tl_namespace,tl_title ORDER BY value DESC LIMIT 51
< 2014-01-14 09:03:02.828 CET >ERREUR: la colonne « page_namespace » n'existe pas au caractère 54
< 2014-01-14 09:03:02.828 CET >INSTRUCTION : SELECT /* UnusedtemplatesPage::reallyDoQuery Low */ page_namespace AS namespace,page_title AS title,page_title AS value FROM "templatelinks" WHERE page_namespace = '10' AND (tl_from IS NULL) AND page_is_redirect = '0' ORDER BY value LIMIT 51
< 2014-01-14 09:09:07.225 CET >ERREUR: la colonne « page_namespace » n'existe pas au caractère 61
< 2014-01-14 09:09:07.225 CET >INSTRUCTION : SELECT /* UncategorizedTemplatesPage::reallyDoQuery Low */ page_namespace AS namespace,page_title AS title,page_title AS value FROM "categorylinks" WHERE (cl_from IS NULL) AND page_namespace = '10' AND page_is_redirect = '0' ORDER BY page_title LIMIT 51
< 2014-01-14 09:23:52.778 CET >ERREUR: la relation « pg2 » n'existe pas au caractère 118
< 2014-01-14 09:23:52.778 CET >INSTRUCTION : SELECT /* WantedPagesPage::reallyDoQuery Low */ pl_namespace AS namespace,pl_title AS title,COUNT(*) AS value FROM "pg2" WHERE (pg1.page_namespace IS NULL) AND (pl_namespace NOT IN ( '2', '3' )) AND (pg2.page_namespace != '8') GROUP BY pl_namespace,pl_title HAVING COUNT(*) > 0 ORDER BY value DESC LIMIT 51
< 2014-01-14 09:27:08.785 CET >ERREUR: la colonne « page_namespace » n'existe pas au caractère 53
< 2014-01-14 09:27:08.785 CET >INSTRUCTION : SELECT /* UnwatchedpagesPage::reallyDoQuery Low */ page_namespace AS namespace,page_title AS title,page_namespace AS value FROM "watchlist" WHERE (wl_title IS NULL) AND page_is_redirect = '0' AND (page_namespace != '8') ORDER BY page_namespace,page_title LIMIT 51
< 2014-01-14 09:29:31.165 CET >ERREUR: la colonne « page_namespace » n'existe pas au caractère 50
< 2014-01-14 09:29:31.165 CET >INSTRUCTION : SELECT /* LonelyPagesPage::reallyDoQuery Low */ page_namespace AS namespace,page_title AS title,page_title AS value FROM "templatelinks" WHERE (pl_namespace IS NULL) AND page_namespace = '0' AND page_is_redirect = '0' AND (tl_namespace IS NULL) ORDER BY page_title LIMIT 51
< 2014-01-14 09:29:40.511 CET >ERREUR: la colonne « page_namespace » n'existe pas au caractère 57
< 2014-01-14 09:29:40.511 CET >INSTRUCTION : SELECT /* UncategorizedPagesPage::reallyDoQuery Low */ page_namespace AS namespace,page_title AS title,page_title AS value FROM "categorylinks" WHERE (cl_from IS NULL) AND page_namespace = '0' AND page_is_redirect = '0' ORDER BY page_title LIMIT 51
< 2014-01-14 09:29:44.301 CET >ERREUR: la colonne « page_namespace » n'existe pas au caractère 55
< 2014-01-14 09:29:44.301 CET >INSTRUCTION : SELECT /* WithoutInterwikiPage::reallyDoQuery Low */ page_namespace AS namespace,page_title AS title,page_title AS value FROM "langlinks" WHERE (ll_title IS NULL) AND page_namespace = '0' AND page_is_redirect = '0' ORDER BY page_namespace,page_title LIMIT 51
< 2014-01-14 09:29:48.105 CET >ERREUR: la relation « p2 » n'existe pas au caractère 159
< 2014-01-14 09:29:48.105 CET >INSTRUCTION : SELECT /* BrokenRedirectsPage::reallyDoQuery Low */ p1.page_namespace AS namespace,p1.page_title AS title,p1.page_title AS value,rd_namespace,rd_title FROM "p2" WHERE (rd_namespace >= 0) AND (rd_interwiki IS NULL OR rd_interwiki = '') AND (p2.page_namespace IS NULL) ORDER BY rd_namespace,rd_title,rd_from LIMIT 51 FOR UPDATE OF p1
< 2014-01-14 09:33:04.904 CET >ERREUR: la relation « p2 » n'existe pas au caractère 204
< 2014-01-14 09:33:04.904 CET >INSTRUCTION : SELECT /* ListredirectsPage::reallyDoQuery Low */ p1.page_namespace AS namespace,p1.page_title AS title,p1.page_title AS value,rd_namespace,rd_title,rd_fragment,rd_interwiki,p2.page_id AS redirid FROM "p2" WHERE p1.page_is_redirect = '1' ORDER BY p1.page_namespace,p1.page_title LIMIT 51
< 2014-01-14 09:34:19.441 CET >ERREUR: la colonne « cl_to » n'existe pas au caractère 71
< 2014-01-14 09:34:19.441 CET >INSTRUCTION : SELECT /* WantedCategoriesPage::reallyDoQuery Low */ 14 AS namespace,cl_to AS title,COUNT(*) AS value FROM "page" WHERE (page_title IS NULL) GROUP BY cl_to ORDER BY value DESC LIMIT 51
< 2014-01-14 09:34:26.579 CET >ERREUR: la colonne « il_to » n'existe pas au caractère 65
< 2014-01-14 09:34:26.579 CET >INSTRUCTION : SELECT /* WantedFilesPage::reallyDoQuery Low */ 6 AS namespace,il_to AS title,COUNT(*) AS value FROM "image" WHERE (img_name IS NULL) GROUP BY il_to ORDER BY value DESC LIMIT 51
< 2014-01-14 09:34:29.776 CET >ERREUR: la colonne « page_namespace » n'existe pas au caractère 51
< 2014-01-14 09:34:29.776 CET >INSTRUCTION : SELECT /* DeadendPagesPage::reallyDoQuery Low */ page_namespace AS namespace,page_title AS title,page_title AS value FROM "pagelinks" WHERE (pl_from IS NULL) AND page_namespace = '0' AND page_is_redirect = '0' ORDER BY page_title LIMIT 51
< 2014-01-14 09:36:05.846 CET >ERREUR: la colonne « pl_namespace » n'existe pas au caractère 49
< 2014-01-14 09:36:05.846 CET >INSTRUCTION : SELECT /* MostlinkedPage::reallyDoQuery Low */ pl_namespace AS namespace,pl_title AS title,COUNT(*) AS value,page_namespace FROM "page" GROUP BY pl_namespace,pl_title,page_namespace HAVING COUNT(*) > 1 ORDER BY value DESC LIMIT 51

No joins are added to the sql.

You wrote:
< 2014-01-14 09:02:56.881 CET >ERROR: column « tl_namespace » doesn’t exist at
character 54
< 2014-01-14 09:02:56.881 CET >INSTRUCTION : SELECT /*
WantedTemplatesPage::reallyDoQuery Low */ tl_namespace AS namespace,tl_title
AS title,COUNT(*) AS value FROM "page" WHERE (page_title IS NULL) AND
tl_namespace = '10' GROUP BY tl_namespace,tl_title ORDER BY value DESC LIMIT
51

Reformatted is that:
SELECT /* WantedTemplatesPage::reallyDoQuery Low */

    tl_namespace AS namespace,
    tl_title AS title,
    COUNT(*) AS value
FROM "page"

WHERE (page_title IS NULL)

AND tl_namespace = '10'

GROUP BY tl_namespace,tl_title
ORDER BY value DESC LIMIT 51

But the correct sql would be: (example from mysql)
SELECT

    tl_namespace AS namespace,
    tl_title AS title,
    COUNT(*) AS value  
FROM `templatelinks`
    LEFT JOIN `page`
        ON ((page_namespace = tl_namespace) AND (page_title = tl_title))

WHERE (page_title IS NULL)

AND tl_namespace = '10'

GROUP BY tl_namespace,tl_title
ORDER BY value DESC LIMIT 51

There is the templatelinks table and with that table the column tl_namespace is known.

I cannot seem to reproduce this. I ran the following code:

$x = new WantedTemplatesPage;
$info = $x->getQueryInfo();
$db = DatabaseBase::factory( 'postgres' );
echo $db->selectSQLText($info['tables'], $info['fields'], $info['conds'], '', $info['options'], $info['join_conds']);

And it outputs the same exact SQL, except with different quotes, as in comment 3's MySQL (i.e., it outputs the correct SQL). I've tested this both on master and in 1.22.1 (the indicated version).

l.wandrebeck wrote:

Whick version of PG do you run ?
Here, CentOS 6.5 x86_64, php 5.3.3, PG 9.3.2 with pgbouncer 1.5.4 in session mode.
Will try to back out pg changes from 1.22.0-1.22.1 patch now and get back to you.

I'm running on MediaWiki-Vagrant, which is Ubuntu 11.04? with php 5.4. Although that really shouldn't matter, since it's code logic at fault here, not platform error.

l.wandrebeck wrote:

Hmmmm strange,

I get via maintenance/eval.php a correct query too:

SELECT tl_namespace AS namespace,tl_title AS title,COUNT(*) AS value FROM "templatelinks" LEFT JOIN "page" ON ((page_namespace = tl_namespace) AND (page_title = tl_title)) WHERE (page_title IS NULL) AND tl_namespace = '' GROUP BY tl_namespace,tl_title

/me scratches head

l.wandrebeck wrote:

Updated to 1.22.3, couldn’t get these errors to be reproduced. Really wondering what happened, because I don’t use any cache system, patches always applied without a single problem…
Marking that one as resolved. Sorry for the noise.

Jdforrester-WMF subscribed.

Migrating from the old tracking task to a tag for PostgreSQL-related tasks.