Last modified: 2014-09-24 01:27:08 UTC

Wikimedia Bugzilla is closed!

Wikimedia migrated from Bugzilla to Phabricator. Bug reports are handled in Wikimedia Phabricator.
This static website is read-only and for historical purposes. It is not possible to log in and except for displaying bug reports and their history, links might be broken. See T18794, the corresponding Phabricator task for complete and up-to-date bug report information.
Bug 16794 - $wgSharedDB PostgreSQL support
$wgSharedDB PostgreSQL support
Status: REOPENED
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
1.17.x
All All
: Low enhancement (vote)
: ---
Assigned To: Nobody - You can work on this!
: patch, patch-reviewed
Depends on:
Blocks: postgres
  Show dependency treegraph
 
Reported: 2008-12-25 22:30 UTC by OverlordQ
Modified: 2014-09-24 01:27 UTC (History)
7 users (show)

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Attachments
Completely untested patch (3.05 KB, patch)
2008-12-26 02:20 UTC, Brad Jorsch
Details
PostgreSQL sharedDB with second connection (6.97 KB, patch)
2011-05-20 17:04 UTC, Luca Fulchir
Details
PostgreSQL - $wgSharedDB - mediawiki 1.17.0beta1 (7.02 KB, patch)
2011-05-29 15:00 UTC, Luca Fulchir
Details
PostgreSQL - $wgsharedDB - mediawiki trunk (6.96 KB, patch)
2011-05-29 15:02 UTC, Luca Fulchir
Details
PostgreSQL - $wgSharedDB - mediawiki 1.17.0 (7.04 KB, patch)
2011-07-04 08:20 UTC, Luca Fulchir
Details
PostgreSql $wgSharedDB support - mwiki 1.17.0 (7.93 KB, patch)
2011-07-23 10:01 UTC, Luca Fulchir
Details
PostgreSQL wgSharedDB with schemas (1.58 KB, patch)
2011-11-30 09:00 UTC, Luca Fulchir
Details
PostgreSQL wgSharedDB with schemas (2.42 KB, patch)
2011-12-04 14:12 UTC, Luca Fulchir
Details
trunk patch to fix temporary tables (808 bytes, patch)
2011-12-27 16:02 UTC, Luca Fulchir
Details
PostgreSql wgshareddb support - trunk (2.17 KB, patch)
2012-02-06 13:50 UTC, Luca Fulchir
Details
PostgreSql $wgSharedDb support - wiki 1.20 (3.05 KB, patch)
2012-06-29 17:57 UTC, Luca Fulchir
Details
PostgreSql $wgSharedDb support - wiki 1.20 (3.40 KB, patch)
2012-06-29 20:36 UTC, Luca Fulchir
Details
PostgreSql $wgSharedDb support - wiki 1.20 (1.98 KB, patch)
2012-06-30 12:43 UTC, Luca Fulchir
Details

Description OverlordQ 2008-12-25 22:30:32 UTC
PG doesn't do cross database queries which breaks the easy route of sharing login data across wikis.

So, in a brief flash of insanity, a modification of the typical wikifarm setup.  Single database, but different schema.  Still use public for tsearch2 et al, but instead of having just mediawiki for the lone wiki schema, have multiple schemas.

So if the DB backend is PG, IMO it should treat $wgSharedDB as $wgSharedSchema.

Haven't thought much past that wrt search_path. But AFAIK if the PG DB driver was modified to explicitly use schemas when referring to tables it shouldn't matter what the search path is set to.
Comment 1 Brad Jorsch 2008-12-26 02:20:13 UTC
Created attachment 5624 [details]
Completely untested patch

It has always struck me that MySQL's "databases" are about equivalent to PG's schemas, so theoretically it should work. I wonder if this copy-paste from Database.php is enough to make it work?
Comment 2 OverlordQ 2008-12-26 02:48:27 UTC
I came up with something similar and this is what's wrong:

1) Wont work as-is, takes $wgSharedTables into account after the PG'ification of table names. Should be moved to after the horrible && mess.
2) Once moved, yes logging in works, reading pages works. Editing . . . is another matter. It doesn't take into account schema's so it inserts into $table, which depending on the search_path might not be valid.
Comment 3 OverlordQ 2008-12-26 03:13:18 UTC
Ok, Ignore #2. Think I figured out why the editing is snafu.

say we have 2 wikis, awesomewiki && poolwiki with matching schemas.

Poolwiki has the shared usertable so $wgSharedDB = 'poolwiki';

User logs into awesomewiki. The search path gets set to "awesomewiki", public.  Currently, it checks for $wgSharedDB, see's that user table is shared, and plops down the poolwiki schema before the queries. The lookup works since the schema is explicit.  Next said logged in user goes to submit an edit.  MW goes to insert a row into the revisions table with the user data.  There is a foreign key constraint on rev_user to the mwuser table.  However since we're sharing the poolwiki mwuser table & the searchpath is ("awesomewiki", public), it tries to lookup user in awesomewiki.mwuser which is empty and fails the foreign key constraint.

So I'm stumped on what 'Best Practices' would say on how to move on from here.
Comment 4 larson 2009-01-28 05:25:52 UTC
So...what I'm hearing is that, if you want to have a wiki farm with a shared user table right now, you have to go with MySQL.  *sigh*  This seems like the kind of thing that ought to be explicitly mentioned in the shared-user-table doco.
Comment 5 Greg Sabino Mullane 2009-02-06 15:18:36 UTC
I don't have time to dig into this much, but if it's just the user table being shared, couldn't you simply change the line in DatabasePostgres.php to something like this:

if ($wgSharedDB) {
  return "$wgSharedDB.mwuser";
}
return 'mwuser';

You'd also have to ensure that the FKs are in the right place too, of course, which means modifying tables.sql slightly for a new install, or recreating the FK constraints on an existing database.

Hmm...all in all, sounds like some search_path munging might be easier overall.
Comment 6 p858snake 2011-04-30 00:09:57 UTC
*Bulk BZ Change: +Patch to open bugs with patches attached that are missing the keyword*
Comment 7 Luca Fulchir 2011-05-20 17:04:30 UTC
Created attachment 8562 [details]
PostgreSQL sharedDB with second connection

This patch should allow you to use the $wgSharedDB normally, as you would with mysql. Basically this patch creates a second connection with the shared database and when a query is made, we check on which connection we should send it.
Comment 8 Luca Fulchir 2011-05-20 17:12:52 UTC
Forgot to say:
The patch is in a "it works for me" state, I'm actually using it with a wiki family...
More testing is appreciated, since it's the first time I write anything for mediawiki (or php in general, but it took only 2 hours to write it, it's not that complicated)

Apply as usual, on the main mediawiki dir:

patch -p0 < PostgreSQL.patch

This patch was written for the latest mediawiki ( 1.16.5 ), so it might NOT work with previous version. It should not be difficoult to port it to older version trough...
Comment 9 Luca Fulchir 2011-05-29 15:00:33 UTC
Created attachment 8598 [details]
PostgreSQL - $wgSharedDB - mediawiki 1.17.0beta1

Added patch to make this work with mediawiki 1.17.0beta1 ....
I just ported it from 1.16.5 patch, not really tested, don't have time now, but it is just a copy-paste of the 1.16.5 patch in the right places...
Comment 10 Luca Fulchir 2011-05-29 15:02:13 UTC
Created attachment 8599 [details]
PostgreSQL - $wgsharedDB - mediawiki trunk

Added patch to make this work with mediawiki trunk (29/05/11) ....
I just ported it from 1.16.5 patch, not really tested, don't have time now, but it is just a copy-paste of the 1.16.5 patch in the right places... shouldn't break anything.
Comment 11 Luca Fulchir 2011-07-04 08:20:09 UTC
Created attachment 8738 [details]
PostgreSQL - $wgSharedDB - mediawiki 1.17.0

Updated to patch smoothly to mediawiki 1.17.0

still working for me, i'm waiting for comments... :p
Comment 12 OverlordQ 2011-07-04 09:27:38 UTC
Doesn't apply to trunk and unless it was significantly different then was was tried last time[1], it will likely be broken as well.

1 - http://www.mediawiki.org/wiki/Special:Code/MediaWiki/89393#code-comments
Comment 13 Luca Fulchir 2011-07-04 11:57:33 UTC
(In reply to comment #12)
> Doesn't apply to trunk and unless it was significantly different then was was
> tried last time[1], it will likely be broken as well.
> 
> 1 - http://www.mediawiki.org/wiki/Special:Code/MediaWiki/89393#code-comments
sorry, didn't see it. i'll post back when i'll resolve it
Comment 14 Luca Fulchir 2011-07-23 10:01:39 UTC
Created attachment 8818 [details]
PostgreSql $wgSharedDB support - mwiki 1.17.0

Updated patch: doesn't break update.php anymore.
Again, it works for me, but testing is appreciated.
Comment 15 Luca Fulchir 2011-11-30 09:00:00 UTC
Created attachment 9575 [details]
PostgreSQL wgSharedDB with schemas

This patch (only for trunk) is totally different from the other ones: instead of having multiple connections to a single database, we use postgresql schemas.

we use $wgSharedDB as it was an other schema name.

tested with new wikis installations.
didn't test with update.php as right now in trunk it's broken even without my patch :p

should work trough...
Comment 16 Luca Fulchir 2011-12-04 14:12:20 UTC
Created attachment 9606 [details]
PostgreSQL wgSharedDB with schemas

The patch was breaking update.php since it was still calling the parent in some cases.

fixed it, update.php works, multiple wiki seems to work.

testing is appreciated (patch only for trunk)
Comment 17 Mark A. Hershberger 2011-12-13 15:07:11 UTC
r106025
Comment 18 Mark A. Hershberger 2011-12-27 13:53:42 UTC
Luca, OverlordQ made this comment on that code:

  "In Postgres temporary tables are put in to their own special schema,
   explicitly prefixing all table names with mediawiki's default schema breaks
   any usage of temporary tables.

  "Either this needs reverted, or have to do some ugly hacks to the testing
   framework."

Could you look at what changes need to be made, either here or in the testing framework, to make temporary tables work properly?
Comment 19 Luca Fulchir 2011-12-27 16:02:15 UTC
Created attachment 9765 [details]
trunk patch to fix temporary tables

this patch (trunk) should put the schema name in front of the table only if the requested table is a shared table.
Otherwise you get only the quoted table name.

This should fix the temporary tables problem. (test, please?)
Since the temp tables are per-connection sharing the same db should not pose any problem.
Comment 20 Mark A. Hershberger 2011-12-27 17:13:02 UTC
You should be able to test using phpunit.  Have you tried that yet?
Comment 21 Luca Fulchir 2011-12-27 18:57:53 UTC
I have a couple of problems with phpunit now... i'll test it tomorrow.

right now i only tested install and basic functionality with wgshareddb,
Comment 22 Luca Fulchir 2011-12-28 16:01:19 UTC
tested, seems ok.
since it doesn't force the main schema phpunit is now back to using temporary tables.

If you run phpunit with $wgshareddb set it still gives you the shared table instead of the temporary.
The tableName() for mysql seems to force the database if it's a shared table, so even for mysql phpunit + $wgshareddb gives you the shared table... which means it should now work as intended ( I hope :p)

I haven't tested it with mysql, trough, just looked at the code.
Comment 23 Luca Fulchir 2012-02-06 13:50:39 UTC
Created attachment 9959 [details]
PostgreSql wgshareddb support - trunk

the patch has been reverted, but I didn't hear any negative comments, so this update is to keep it here for anyone who needs it or wants to work on it
Comment 24 Sumana Harihareswara 2012-02-08 19:22:15 UTC
Luca, if you need any help running PHPUnit, or want to improve your patch to the point where it can be committed, we can help you in the IRC channel: https://www.mediawiki.org/wiki/MediaWiki_on_IRC  Thanks!
Comment 25 Marcin Cieślak 2012-03-09 11:49:01 UTC
You have a comment that worries me a bit (it's not your fault though):

# Lets test for any bits of text that should never show up in a table 
# name. Basically anything like JOIN or ON which are actually part of
# SQL queries, but may end up inside of the table value to combine 
# sql. Such as how the API is doing. 	 

# Note that we use a whitespace test rather than a \b test to avoid
# any remote case where a word like on may be inside of a table name
# surrounded by symbols which may be considered word breaks

and then you resort to a regex to catch them:

'/(^|\s)(DISTINCT|JOIN|ON|AS)(\s|$)/i'

Could you comment a bit more on this? (Probably in a separate bug
that blocks this one)?

It's some time since I looked at the API code but I understand that
our current SQL query infrastructure is not sufficient for all API
needs...
Comment 26 Daniel Friesen 2012-03-10 00:22:09 UTC
That's part of the MySQL code. It dates back to when I implemented shared prefix and shared tables. After implementing it I got bug reports on it breaking and found out that various bits of code had awfully being passing full joins into the list of tables because we didn't have a join syntax back then. I had to add that junk into tableName in order for it to work.
Comment 27 Luca Fulchir 2012-06-29 17:57:04 UTC
Created attachment 10807 [details]
PostgreSql $wgSharedDb support - wiki 1.20

Patch updated to work against latest git (29/06).

Works also with phpunit tests (before it didn't apply the table prefix)

I hope it will be included in 1.20...
Comment 28 Marcin Cieślak 2012-06-29 18:19:34 UTC
Thanks for the patch! 

Unfortunately, I cannot install mediawiki with this patch:


[29-Jun-2012 18:15:15 UTC] PHP Fatal error:  Call to undefined method DatabasePo
stgres::realTableName() in /usr/home/saper/public_html/pg/w/includes/db/Database
Postgres.php on line 1233

I think we should re-think what realTableName and tableName should be... realTableName is needed in some places where we know it's PostgreSQL
Comment 29 Luca Fulchir 2012-06-29 20:36:12 UTC
Created attachment 10808 [details]
PostgreSql $wgSharedDb support - wiki 1.20

*Really* sorry, forgot to change one instance of realTableName() into tableName().... my bad... altrough the phpunit test passed...

a quick "grep -r" tells me there are no other realTableName(),and I tested this with raw installation and phpunit.

realTableName is not needed anymore. The distinction was created (I think) since tablename() didn't do proper escaping/quoting on all cases.

Now tableName() handles every escaping/quoting/schema/prefix you should need, Much of it is done by mimicking Database->tableName(), which was called before by realTableName()
Comment 30 Marcin Cieślak 2012-06-29 21:42:17 UTC
Luca, can you check

https://gerrit.wikimedia.org/r/#/c/3323/
(or https://gerrit.wikimedia.org/r/gitweb?p=mediawiki%2Fcore.git;a=commit;h=556c5cf464b9103b04b247ed7dd7ee3051e9aef6)

I believe need real table names for the updater... we shouldn't cheat with renaming "user" and "text" tables to the PostgreSQL-aware parts of code...
Comment 31 Luca Fulchir 2012-06-30 12:43:57 UTC
Created attachment 10809 [details]
PostgreSql $wgSharedDb support - wiki 1.20

You're right, I didn't understand the purpose of realTableName.

Corrected, now realTableName does the quoting/schema handling, tableName is untouched.

I also cleand up a little bit the patch, tested with multiple installation/shared tables/phpunit

Note You need to log in before you can comment on or make changes to this bug.


Navigation
Links