Last modified: 2013-09-02 20:21:43 UTC

Wikimedia Bugzilla is closed!

Wikimedia has migrated from Bugzilla to Phabricator. Bug reports should be created and updated in Wikimedia Phabricator instead. Please create an account in Phabricator and add your Bugzilla email address to it.
Wikimedia Bugzilla is read-only. If you try to edit or create any bug report in Bugzilla you will be shown an intentional error message.
In order to access the Phabricator task corresponding to a Bugzilla report, just remove "static-" from its URL.
You could still run searches in Bugzilla or access your list of votes but bug reports will obviously not be up-to-date in Bugzilla.
Bug 27732 - Support of PostgreSQL in SocialProfile
Support of PostgreSQL in SocialProfile
Status: NEW
Product: MediaWiki extensions
Classification: Unclassified
SocialProfile (Other open bugs)
unspecified
All All
: Normal enhancement with 1 vote (vote)
: ---
Assigned To: Jack Phoenix
: patch, patch-reviewed
: 28279 (view as bug list)
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2011-02-26 02:53 UTC by Anton Kochkov
Modified: 2013-09-02 20:21 UTC (History)
5 users (show)

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


Attachments
little patch for fix postgresql offset query (1.91 KB, patch)
2011-02-26 11:37 UTC, Anton Kochkov
Details
Schema for my wiki installation, which works (partially) with SocialProfile on PostgreSQL (12.84 KB, application/x-gzip)
2011-02-26 11:39 UTC, Anton Kochkov
Details
Some functions and sequencies needed for SocialProfile (1.10 KB, application/x-gzip)
2011-02-26 11:40 UTC, Anton Kochkov
Details

Description Anton Kochkov 2011-02-26 02:53:40 UTC
Please add support of PostgreSQL database storage
Comment 1 Anton Kochkov 2011-02-26 11:37:19 UTC
Created attachment 8216 [details]
little patch for fix postgresql offset query
Comment 2 Anton Kochkov 2011-02-26 11:39:08 UTC
Created attachment 8217 [details]
Schema for my wiki installation, which works (partially) with SocialProfile on PostgreSQL

User Board/User properties works ok.
Comment 3 Anton Kochkov 2011-02-26 11:40:35 UTC
Created attachment 8218 [details]
Some functions and sequencies needed for SocialProfile
Comment 4 Anton Kochkov 2011-02-26 11:41:44 UTC
These schemas for my wiki and this small little patch works ok on some parts of SocialProfile features, but do crash for others. Hope you can fix that in future :)
Comment 5 Jack Phoenix 2011-02-26 15:50:59 UTC
SocialProfile has had (theoretical) PostgreSQL support for a while already, see the appropriate .postgres.sql files in the SocialProfile subdirectories (http://svn.wikimedia.org/viewvc/mediawiki/trunk/extensions/SocialProfile/UserBoard/user_board.postgres.sql?view=markup for example).

As for your first patch, it looks OK but I'm not sure, and I certainly don't want to break MySQL compatibility (although if I did, I'm sure someone would spot it quickly and report back). In any case, I'd like to get rid of that raw SQL construction altogether instead of just patching the existing raw SQL. That way maybe one day SocialProfile will work with Oracle, too. :)

Mark commented on SocialProfile's MySQL-isms on http://www.mediawiki.org/wiki/Special:Code/MediaWiki/75007#c11339; I implemented one of his suggestions on r77339 but later had to revert it in r79748 because the Database::unixTimestamp function was removed.

Long story short, there is some support for PostgreSQL already, but there are plenty of code-related issues that should be fixed in order to support PostgreSQL and other DBMSes properly. I'd like to get rid of all the raw SQL queries first; IIRC there are also some rather nasty subqueries somewhere, too.
I unfortunately don't know much about non-MySQL DBMSes, but I'm interested in building PostgreSQL support. Feel free to submit patches or even apply for commit access (see http://www.mediawiki.org/wiki/Commit_access for information about that).
Comment 6 Anton Kochkov 2011-02-26 16:26:47 UTC
Hm. main problems was with postgresql creating tables - your postgresql files are not work on postgresql. (at least on 9.0 and later). So, I'm attached my tables, functions (in SQL unixtime, etc), and sequences for PostgreSQL, which works ok.
Comment 7 Anton Kochkov 2011-02-26 16:29:32 UTC
And here an example of some error with LIMIT and OFFSET:

A database error has occurred
Query: SELECT UNIX_TIMESTAMP(rc_timestamp) AS item_date, rc_title,
rc_user, rc_user_text, rc_comment, rc_id, rc_minor, rc_new,
rc_namespace, rc_cur_id, rc_this_oldid, rc_last_oldid,
rc_log_action
FROM recentchanges

ORDER BY rc_id DESC LIMIT 0,50
Function: UserActivity::setEdits
Error: 1 ERROR: LIMIT #,# syntax is not supported
LINE 7: ORDER BY rc_id DESC LIMIT 0,50
^
HINT: Use separate LIMIT and OFFSET clauses.
Backtrace:

#0 /usr/home/droid-dev/www/includes/db/Database.php(538): DatabasePostgres->reportQueryError('ERROR: LIMIT #...', 1, 'SELECT UNIX_TIM...', 'UserActivity::s...', false)
#1 /usr/home/droid-dev/www/extensions/SocialProfile/UserActivity/UserActivityClass.php(85): DatabaseBase->query('SELECT UNIX_TIM...', 'UserActivity::s...')
#2 /usr/home/droid-dev/www/extensions/SocialProfile/UserActivity/UserActivityClass.php(605): UserActivity->setEdits()
#3 /usr/home/droid-dev/www/extensions/SocialProfile/UserActivity/UserActivityClass.php(639): UserActivity->getActivityList()
#4 /usr/home/droid-dev/www/extensions/SocialProfile/UserActivity/UserActivity.body.php(75): UserActivity->getActivityListGrouped()
#5 /usr/home/droid-dev/www/includes/SpecialPage.php(559): UserHome->execute(NULL)
#6 /usr/home/droid-dev/www/includes/Wiki.php(254): SpecialPage::executePath(Object(Title))
#7 /usr/home/droid-dev/www/includes/Wiki.php(64): MediaWiki->handleSpecialCases(Object(Title), Object(OutputPage), Object(WebRequest))
#8 /usr/home/droid-dev/www/index.php(117): MediaWiki->performRequestForTitle(Object(Title), NULL, Object(OutputPage), Object(User), Object(WebRequest))
#9 {main}
Comment 8 Jack Phoenix 2011-03-26 16:27:25 UTC
I've rewritten the SQL queries used by UserActivity to use MediaWiki's Database class in r84804; can you please try updating your SocialProfile to that revision and see if it helps? If that doesn't fix the problem(s), then a PostgreSQL guru should take a look at this bug; I'm not familiar with PGSQL and how it works.
Comment 9 Anton Yackushev 2011-04-10 11:45:27 UTC
(In reply to comment #8)
> I've rewritten the SQL queries used by UserActivity to use MediaWiki's Database
> class in r84804; can you please try updating your SocialProfile to that
> revision and see if it helps? If that doesn't fix the problem(s), then a
> PostgreSQL guru should take a look at this bug; I'm not familiar with PGSQL and
> how it works.

It hasn't helped me
Comment 10 p858snake 2011-04-30 00:09:41 UTC
*Bulk BZ Change: +Patch to open bugs with patches attached that are missing the keyword*
Comment 11 Anton Kochkov 2011-07-04 14:48:22 UTC
MW: trunk, SocialProfile: trunk

Warning: pg_query() [function.pg-query]: Query failed: ERROR: function unix_timestamp(timestamp without time zone) does not exist LINE 1: ...tatus,gift_name,gift_description,gift_given_count,UNIX_TIMES... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253

Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253

Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253

Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253

Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253

Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253

Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253

Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253

Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253

Warning: Invalid argument supplied for foreach() in /home/droid-dev/www/includes/resourceloader/ResourceLoaderWikiModule.php on line 176

Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253

Warning: Invalid argument supplied for foreach() in /home/droid-dev/www/includes/resourceloader/ResourceLoaderWikiModule.php on line 176

Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253

MediaWiki internal error.

 Original exception: exception 'DBQueryError' with message 'A database error has occurred. Did you forget to run maintenance/update.php after upgrading? See: http://www.mediawiki.org/wiki/Manual:Upgrading#Run_the_update_script
 Query: SELECT ug_id,ug_user_id_from,ug_user_name_from,ug_gift_id,ug_date,ug_status,gift_name,gift_description,gift_given_count,UNIX_TIMESTAMP(ug_date) AS unix_time FROM "user_gift" INNER JOIN "gift" ON ((ug_gift_id = gift_id)) WHERE (ug_user_id_to = 4) ORDER BY ug_id DESC LIMIT 4 OFFSET 0 
 Function: UserGifts::getUserGiftList
 Error: 1 ERROR: function unix_timestamp(timestamp without time zone) does not exist
 LINE 1: ...tatus,gift_name,gift_description,gift_given_count,UNIX_TIMES...
 ^
 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
 ' in /home/droid-dev/www/includes/db/Database.php:845
 Stack trace:
 #0 /home/droid-dev/www/includes/db/Database.php(812): DatabaseBase->reportQueryError('ERROR: functio...', 1, 'SELECT ug_id,u...', 'UserGifts::getU...', false)
 #1 /home/droid-dev/www/includes/db/Database.php(1281): DatabaseBase->query('SELECT ug_id,u...', 'UserGifts::getU...')
 #2 /home/droid-dev/www/extensions/SocialProfile/UserGifts/UserGiftsClass.php(332): DatabaseBase->select(Array, Array, Array, 'UserGifts::getU...', Array, Array)
 #3 /home/droid-dev/www/extensions/SocialProfile/UserProfile/UserProfilePage.php(1043): UserGifts->getUserGiftList(0, 4)
 #4 /home/droid-dev/www/extensions/SocialProfile/UserProfile/UserProfilePage.php(73): UserProfilePage->getGifts('XVilka')
 #5 /home/droid-dev/www/includes/Wiki.php(436): UserProfilePage->view()
 #6 /home/droid-dev/www/includes/Wiki.php(211): MediaWiki->performAction(Object(UserProfilePage))
 #7 /home/droid-dev/www/includes/Wiki.php(586): MediaWiki->performRequest()
 #8 /home/droid-dev/www/includes/Wiki.php(497): MediaWiki->main()
 #9 /home/droid-dev/www/index.php(74): MediaWiki->run()
 #10 {main}

 Exception caught inside exception handler: exception 'DBUnexpectedError' with message 'SQL error: ERROR: current transaction is aborted, commands ignored until end of transaction block' in /home/droid-dev/www/includes/db/DatabasePostgres.php:303
 Stack trace:
 #0 /home/droid-dev/www/includes/WatchedItem.php(45): DatabasePostgres->numRows(false)
 #1 /home/droid-dev/www/includes/User.php(2408): WatchedItem->isWatched()
 #2 /home/droid-dev/www/includes/Title.php(1107): User->isWatched(Object(Title))
 #3 /home/droid-dev/www/includes/SkinTemplate.php(1026): Title->userIsWatching()
 #4 /home/droid-dev/www/includes/SkinTemplate.php(501): SkinTemplate->buildContentNavigationUrls(Object(OutputPage))
 #5 /home/droid-dev/www/includes/OutputPage.php(1929): SkinTemplate->outputPage(Object(OutputPage))
 #6 /home/droid-dev/www/includes/Exception.php(183): OutputPage->output()
 #7 /home/droid-dev/www/includes/Exception.php(213): MWException->reportHTML()
 #8 /home/droid-dev/www/includes/Exception.php(413): MWException->report()
 #9 /home/droid-dev/www/includes/Exception.php(481): MWExceptionHandler::report(Object(DBQueryError))
 #10 /home/droid-dev/www/includes/Wiki.php(500): MWExceptionHandler::handle(Object(DBQueryError))
 #11 /home/droid-dev/www/index.php(74): MediaWiki->run()
 #12 {main}
Comment 12 Anton Kochkov 2011-09-13 02:33:49 UTC
btw, PostgreSQL 8.* series is now deprecated.
Comment 13 Anton Yackushev 2012-04-22 09:02:29 UTC
When installing SocialProfile get the error:

...
Creating user_status table...Could not open"/var/www/test/extensions/SocialProfile/UserStatus/userstatus.postgres.sql".

Backtrace:
#0 /var/www/test/includes/installer/DatabaseUpdater.php(362): DatabaseBase->sourceFile('/var/www/test/e...')
#1 /var/www/test/includes/installer/DatabaseUpdater.php(379): DatabaseUpdater->applyPatch('/var/www/test/e...', true)
#2 [internal function]: DatabaseUpdater->addTable('user_status', '/var/www/test/e...', true)
#3 /var/www/test/includes/installer/DatabaseUpdater.php(233): call_user_func_array(Array, Array)
#4 /var/www/test/includes/installer/DatabaseUpdater.php(201): DatabaseUpdater->runUpdates(Array, true)
#5 /var/www/test/maintenance/update.php(119): DatabaseUpdater->doUpdates(Array)
#6 /var/www/test/maintenance/doMaintenance.php(105): UpdateMediaWiki->execute()
#7 /var/www/test/maintenance/update.php(145): require_once('/var/www/test/m...')
#8 {main}

P.S. MediaWiki 1.18.2; PHP 5.3.3-7+squeeze8 (apache2handler); PostgreSQL 8.4.11; SocialProfile r92462
Comment 14 Jack Phoenix 2013-09-02 20:21:43 UTC
*** Bug 28279 has been marked as a duplicate of this bug. ***

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


Navigation
Links