Medik wrote:

1. Special pages
2. File list
3. Choose one of images
4. delete all
5. Submit (delete)

Database error
Jump to: navigation, search
A database query syntax error has occurred. This may indicate a bug in the software. The last attempted database query was:
SELECT rev_id,rev_page,rev_text_id,rev_timestamp,rev_comment,rev_user_text,rev_user,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,rev_content_format,rev_content_model,page_namespace,page_title,page_id,page_latest,page_is_redirect,page_len,user_name FROM "revision" INNER JOIN "page" ON ((page_id = rev_page)) LEFT JOIN "mwuser" ON ((rev_user != 0) AND (user_id = rev_user)) WHERE page_id = '10' AND rev_id = '44' LIMIT 1 FOR UPDATE
from within function "Revision::fetchFromConds". Database returned error "0A000: ERROR: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join ".

Backtrace:

#0 /var/www/ideas/includes/db/DatabasePostgres.php(482): DatabaseBase->reportQueryError('ERROR: SELECT ...', '0A000', 'SELECT rev_id,...', 'Revision::fetch...', false)
#1 /var/www/ideas/includes/db/Database.php(983): DatabasePostgres->reportQueryError('ERROR: SELECT ...', '0A000', 'SELECT rev_id,...', 'Revision::fetch...', false)
#2 /var/www/ideas/includes/db/Database.php(1434): DatabaseBase->query('SELECT rev_id,...', 'Revision::fetch...')
#3 /var/www/ideas/includes/Revision.php(372): DatabaseBase->select(Array, Array, Array, 'Revision::fetch...', Array, Array)
#4 /var/www/ideas/includes/Revision.php(314): Revision::fetchFromConds(Object(DatabasePostgres), Array, 3)
#5 /var/www/ideas/includes/Revision.php(294): Revision::loadFromConds(Object(DatabasePostgres), Array, 3)
#6 /var/www/ideas/includes/Revision.php(143): Revision::newFromConds(Array, 3)
#7 /var/www/ideas/includes/WikiPage.php(575): Revision::newFromPageId(10, 44, 3)
#8 /var/www/ideas/includes/WikiPage.php(615): WikiPage->loadLastEdit()
#9 /var/www/ideas/includes/WikiPage.php(2543): WikiPage->getContent(3)
#10 /var/www/ideas/includes/FileDeleteForm.php(185): WikiPage->doDeleteArticleReal('', false, 0, false, '', Object(User))
#11 /var/www/ideas/includes/FileDeleteForm.php(110): FileDeleteForm::doDelete(Object(Title), Object(LocalFile), '', '', false, Object(User))
#12 /var/www/ideas/includes/ImagePage.php(875): FileDeleteForm->execute()
#13 /var/www/ideas/includes/actions/DeleteAction.php(45): ImagePage->delete()
#14 /var/www/ideas/includes/Wiki.php(439): DeleteAction->show()
#15 /var/www/ideas/includes/Wiki.php(305): MediaWiki->performAction(Object(ImagePage), Object(Title))
#16 /var/www/ideas/includes/Wiki.php(565): MediaWiki->performRequest()
#17 /var/www/ideas/includes/Wiki.php(458): MediaWiki->main()
#18 /var/www/ideas/index.php(59): MediaWiki->run()
#19 {main}

Apache/2.2.16 (Debian)
Linux 2.6.32-5-amd64
PHP Version 5.3.10-1
PostgreSQL 9.1.2

gparent wrote:

Getting the same issue here with a stock 1.21.1 install on PostgreSQL.

Could replicate locally in the current git master (1cb9da7a149166a5bbb8c07bf4a415882f716160)

• Bug 56174 has been marked as a duplicate of this bug. ***

btiqui wrote:

Same error (see below) for 1.21.2 install with PostgreSQL database.

Database errorJump to: navigation, search
A database query syntax error has occurred. This may indicate a bug in the software. The last attempted database query was:
SELECT rev_id,rev_page,rev_text_id,rev_timestamp,rev_comment,rev_user_text,rev_user,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,rev_content_format,rev_content_model,page_namespace,page_title,page_id,page_latest,page_is_redirect,page_len,user_name FROM "revision" INNER JOIN "page" ON ((page_id = rev_page)) LEFT JOIN "mwuser" ON ((rev_user != 0) AND (user_id = rev_user)) WHERE page_id = '14' AND rev_id = '60' LIMIT 1 FOR UPDATE
from within function "Revision::fetchFromConds". Database returned error "0A000: ERROR: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join ".

Since this is showing up in 1.21, I don't think we need to delay 1.22 for this. But this should be fixed ASAP.

Update per https://www.mediawiki.org/w/index.php?title=MediaWiki_1.22/Known_issues&oldid=847749 which seems to be the master list.

martin.konecny wrote:

I can confirm this bug is happening in 1.22.1.

I was getting the same error above when enabling showing SQL errors in the browser. But when I look into my postgresql log in Linux, I see the following:

<code>
2014-01-27 04:21:19 UTC ERROR: column "rev_id" does not exist at character 49
2014-01-27 04:21:19 UTC STATEMENT: SELECT /* Revision::fetchFromConds Mkonecny */ rev_id,rev_page,rev_text_id,rev_timestamp,rev_comment,rev_user_text,rev_user,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,rev_content_format,rev_content_model,page_namespace,page_title,page_id,page_latest,page_is_redirect,page_len,user_name FROM "mwuser" WHERE page_id = '10' AND rev_id = '280' LIMIT 1 FOR UPDATE OF page
</code>

1.22.0 was a fresh install (first time I ever used mediawiki). I upgraded to 1.22.1 in an attempt to solve this problem (which it obviously didn't).

That's really strange. It looks like it's trying to delete a page from the _user_ table (and failing, obviously, since the user table has none of the columns specified). I can confirm this with a clean install of 1.22.1 with PostgreSQL, although it's strange because it doesn't happen with MySQL (I don't see any reason why the table name would change based on the database system in use, particularly the revision table).

martin.konecny wrote:

I definitely don't have those columns:

wikidocs=# \d mediawiki.mwuser

                                      Table "mediawiki.mwuser"
Column          |           Type           |                            Modifiers

--------------------------+--------------------------+------------------------------------------------------------------
user_id | integer | not null default nextval('mediawiki.user_user_id_seq'::regclass)
user_name | text | not null
user_real_name | text |
user_password | text |
user_newpassword | text |
user_newpass_time | timestamp with time zone |
user_token | text |
user_email | text |
user_email_token | text |
user_email_token_expires | timestamp with time zone |
user_email_authenticated | timestamp with time zone |
user_touched | timestamp with time zone |
user_registration | timestamp with time zone |
user_editcount | integer |
Indexes:

"mwuser_pkey" PRIMARY KEY, btree (user_id)
"mwuser_user_name_key" UNIQUE CONSTRAINT, btree (user_name)
"user_email_token_idx" btree (user_email_token)

Referenced by:

TABLE "mediawiki.archive" CONSTRAINT "archive_ar_user_fkey" FOREIGN KEY (ar_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.filearchive" CONSTRAINT "filearchive_fa_deleted_user_fkey" FOREIGN KEY (fa_deleted_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.filearchive" CONSTRAINT "filearchive_fa_user_fkey" FOREIGN KEY (fa_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.image" CONSTRAINT "image_img_user_fkey" FOREIGN KEY (img_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.ipblocks" CONSTRAINT "ipblocks_ipb_by_fkey" FOREIGN KEY (ipb_by) REFERENCES mediawiki.mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.ipblocks" CONSTRAINT "ipblocks_ipb_user_fkey" FOREIGN KEY (ipb_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.logging" CONSTRAINT "logging_log_user_fkey" FOREIGN KEY (log_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.oldimage" CONSTRAINT "oldimage_oi_user_fkey" FOREIGN KEY (oi_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.protected_titles" CONSTRAINT "protected_titles_pt_user_fkey" FOREIGN KEY (pt_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.recentchanges" CONSTRAINT "recentchanges_rc_user_fkey" FOREIGN KEY (rc_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.revision" CONSTRAINT "revision_rev_user_fkey" FOREIGN KEY (rev_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.user_former_groups" CONSTRAINT "user_former_groups_ufg_user_fkey" FOREIGN KEY (ufg_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.user_groups" CONSTRAINT "user_groups_ug_user_fkey" FOREIGN KEY (ug_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.user_newtalk" CONSTRAINT "user_newtalk_user_id_fkey" FOREIGN KEY (user_id) REFERENCES mediawiki.mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.user_properties" CONSTRAINT "user_properties_up_user_fkey" FOREIGN KEY (up_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
TABLE "mediawiki.watchlist" CONSTRAINT "watchlist_wl_user_fkey" FOREIGN KEY (wl_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED

Sounds like a duplicate of fixed bug 47055, because there also the FOR UPDATE was a problem.

martin.konecny wrote:

@Umherirrender

I saw that ticket (bug 47055) and thought the same thing. Unfortunately 1.22.1 does not address this problem.

IMO there's something wrong with the 1.22.0 installer for Postgresql seeing as the tables generated during install do not have the required columns.

The rev_id column is part of the revision table, but the join to that table is missing (sql example in comment 5 has it), which should be there, but the missing join condition in the sql of comment 8 sounds more like bug 60031 or is a problem with the FOR UPDATE fix, see bug 60531. I have no postgres to test this.

martin.konecny wrote:

I can confirm bug 60531 represents the issue, manually applying the patch in that ticket (https://gerrit.wikimedia.org/r/109914) solved the issue.

The sql error of comment 0 should be fixed with bug 47055, needs testing.

Works now

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