Page MenuHomePhabricator

PostgreSQL 9.0 default 'mediawiki' schema causes failure, use 'public' instead.
Closed, ResolvedPublic

Description

Author: brad.lhotsky

Description:
Installing database to a Postgresql 9.0 Server, remotely.

MediaWiki running on CentOS 6, PHP 5.3.2

During the install the DB Schema defaulted to 'mediawiki'. The install completed successfully, but upon viewing the site, there was an exception. Enabling exceptions, I saw that all the SQL statements that were tried were failing. None of the queries specified the schema.

IE:

SELECT * FROM table where column='value';

There should be an option in the PG DB connection to set the default schema, but that may not be working on CentOS 6's php-pgsql.

Without the proper setting of default schema, all table, view, and procedure names would need to specify their schema expressly, as in:

SELECT * FROM mediawiki.table where column='value';

Going to test against SVN HEAD tomorrow.


Version: 1.17.x
Severity: major

Details

Reference
bz30787

Event Timeline

bzimport raised the priority of this task from to Medium.Nov 21 2014, 11:48 PM
bzimport set Reference to bz30787.
bzimport added a subscriber: Unknown Object (MLST).

sumanah wrote:

Brad, please let us know whether you've reproduced this with SVN HEAD.

Also, we're wondering what specific steps you followed when this error occurred.

Andy Lester says, "I don't think there's a way to set a default schema, other than search_path.... are you on the wrong user?"

Greg Sabino Mullane said: "The user they use should already have the search_path changed. I'm going to guess that they are connecting with the wrong Postgres user in their LocalSettings.php . The installer should be doing an ALTER USER to set the default search_path ..."

Chad Horohoe responded: "ALTER ROLE $safeuser SET search_path = $safeschema, public" ... Line 469 of PostgresInstaller."

So, we think this might have been an error in your LocalSettings.php, so please check what Postgres user you're using, and let us know.

M8R-utuxah wrote:

I ran into the same problem with Mediawiki-1.17.0 (release) and PostgreSQL 9.1. Apache 2.2.16 and 5.3.3 are installed on an Ubuntu 10.10 machine, with Postgres installed on a different Ubuntu 10.04.03 box.

I ran the installer script, changing the database name and username to my Postgres credentials (I left the schema alone). The install script ran successfully (no errors, no obvious errors in the Postgres logs) and I copied the new LocalSettings.php file to my web root.

However, attempting to access <addr>/wiki threw an exception, with mediawiki not being able to find the right tables. I checked my postgres account, and the tables and schema were created, but my user was not altered to include "mediawiki" in the search_path variable.

I manually added "mediawiki" to my my search_path (running the ALTER ROLE command from the command line) and I was able to get my wiki running successfully.

In short, the install script apparently has issues with the ALTER ROLE command in the current version of posgres.

(I did not test the current SVN build, but since I had the same issue as Brad with an official release, I thought I'd chime in.)

Rather than changing the default schema this sounds like a bug that will crop up whenever someone 'chooses' to use a non-default schema of their own choice. In that case it sounds like what we should really do is not necessarily change our default schema (though we always could do that too) but ensure that our PostgreSQL code always uses sql that will work with the configured schema.

Krinkle subscribed.

If I understand correctly, refactoring in MW 1.27 and more recently has resolved this by making the schema configurable.

Krinkle moved this task from Untriaged to Rdbms library on the MediaWiki-libs-Rdbms board.
Krinkle edited subscribers, added: aaron; removed: wikibugs-l-list.