Wikimedia Bugzilla is closed!

23:07:05Fri 29 Nov 13 23:07:07



The the fields to be use in outer joins need to be defined as not null. Then  PostgreSQL can enforce data consistency without falling the outer left join transaction. 

The root of the problem  can be found in the the PostgreSQL log.

The test from where the fail arises is: public function testOnArticlePurgeOnDatabase()
in line 121 of  
SemanticMediaWiki/tests/phpunit/integration/MediaWikiFunctionHookIntegrationTest.php.


GMT ERROR:  
SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join.

This means in this context that the data fields used in the left join must be not nullable:
{
LEFT JOIN "unittest_mwuser" ON ((rev_user != 0) AND (user_id = rev_user))  WHERE page_id = '612' AND rev_id = '892'  LIMIT 1   FOR UPDATE
}

ref: http://www.postgresql.org/message-id/flat/98882B9F-5C37-4C5B-9B15-5E51D8767CF2@socialserve.com
http://postgresql.1045698.n5.nabble.com/outer-joins-and-for-update-td1937029.html


GMT ERROR:  
SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join.

GMT STATEMENT:  
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 "unittest_revision" INNER JOIN "unittest_page" ON ((page_id = rev_page)) 



It seems that unit test reporting hides the real root of the problem that can be identified in the PostgreSQL log .Then in future It seems that it would very useful to include this log.
On a debian system the standard location is in:  /var/log/postgresql/postgresql-9.1-main.log. Thus perhaps including the output of 
tail -20  /var/log/postgresql/postgresql-9.1-main.log  with unit test fails report could be something easy to achieve.

From above analysis it looks to be a dupe of bug 47055, adding dependency for now.

Be also aware of a quite nasty bug 37702, but I suppose it affects SMW to the lesser extent.

Thanks for the link to 47055 and pointing to 37702.

It seems that all the problems related to postgresql are rooted on   creating  data sets that are not compatible with the operations that smw wants to excute on them.

Indeed in this case mysql seems to enable outer joins on nullables.

Here It seems that the most  more straight approach would be set “not null”  for fields that are used in outer joins.

Regards.

J.A.

Hi,

According to my understanding the root of this bug is in
is in function selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds ); starting in line 1453 of file  includes/db/Database.php.

In my understanding this function should  check if  an update on a select with outer joint is going to be requested . Thus avoiding the” select for update/share cannot be applied to the nullable side of an outer join.”  exception  thrown by postgresql.

This the secuence of calls that it seems to happen from 
/
SemanticMediaWiki/tests/phpunit/integration/MediaWikiFunctionHookIntegrationTest.php
 testOnArticlePurgeOnDatabase();

$this->deletePage( $wikiPage, __METHOD__ )  starts an “forupdate” process.
$wikiPage->doDeleteArticle( 
doDeleteArticleReal

[file includes/Hooks.php]
wfRunHooks( 'ArticleDelete',    array( &$this wikipage,
                 &$user, &$reason, &$error, &$status ) ) 
$hooks = self::getHandlers( ArticleDelete );

[file includes/wikipage.php]
loadPageData( 'forupdate' );
pageDataFromTitle( wfGetDB( DB_MASTER ), $this->mTitle, array( 'FOR UPDATE' ) );

[includes/db/LoadBalancer.php]
connection -->db->last query lef joint → reused for update

[file includes/wikipage.php]

 pageDataFromTitle
 pageData

[file includes/db/Database.php]
selectRow( 'page', $fields, $conditions, __METHOD__, $options );
selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds );

Regards.

J.A.

(In reply to comment #3)
> Thanks for the link to 47055 and pointing to 37702.
> 
> It seems that all the problems related to postgresql are rooted on  
> creating 
> data sets that are not compatible with the operations that smw wants to
> excute
> on them.
> 
> Indeed in this case mysql seems to enable outer joins on nullables.
> 
> Here It seems that the most  more straight approach would be set “not null” 
> for fields that are used in outer joins.
> 
> Regards.
> 
> J.A.


Setting a non null  constraint for all the fields of the tables  involved in the outer joint does not solve the problem.

(In reply to comment #4)
> Hi,
> 
> According to my understanding the root of this bug is in
> is in function selectSQLText( $table, $vars, $conds, $fname, $options,
> $join_conds ); starting in line 1453 of file  includes/db/Database.php.
> 
> In my understanding this function should  check if  an update on a select
> with
> outer joint is going to be requested . Thus avoiding the” select for
> update/share cannot be applied to the nullable side of an outer join.” 
> exception  thrown by postgresql.
> 
> This the secuence of calls that it seems to happen from 
> /
> SemanticMediaWiki/tests/phpunit/integration/
> MediaWikiFunctionHookIntegrationTest.php
>  testOnArticlePurgeOnDatabase();
> 
> $this->deletePage( $wikiPage, __METHOD__ )  starts an “forupdate” process.
> $wikiPage->doDeleteArticle( 
> doDeleteArticleReal
> 
> [file includes/Hooks.php]
> wfRunHooks( 'ArticleDelete',    array( &$this wikipage,
>                  &$user, &$reason, &$error, &$status ) ) 
> $hooks = self::getHandlers( ArticleDelete );
> 
> [file includes/wikipage.php]
> loadPageData( 'forupdate' );
> pageDataFromTitle( wfGetDB( DB_MASTER ), $this->mTitle, array( 'FOR UPDATE' )
> );
> 
> [includes/db/LoadBalancer.php]
> connection -->db->last query lef joint → reused for update
> 
> [file includes/wikipage.php]
> 
>  pageDataFromTitle
>  pageData
> 
> [file includes/db/Database.php]
> selectRow( 'page', $fields, $conditions, __METHOD__, $options );
> selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds );
> 
> Regards.
> 
> J.A.

If the real problem is in function  selectSQLText then perhaps a bug should be raised   in mediawiki...

Looks like related to bug 36759, bug 37600 and unfortunately bug 37702 to me.

Can you run the test again with 4b291909e0e91ad4e8ed98030c1312a872ca3bd4 reverted and post the error message again? (see bug 58095)

I can't because the current MW master is even more broken for PostgerSQL because of [1]. 

[1] Could not insert main page: A database error has occurred. Did you forget to run maintenance/update.php after upgrading?  See: https://www.mediawiki.org/wiki/Manual:Upgrading#Run_the_update_script
Query: INSERT INTO "recentchanges" (rc_timestamp,rc_namespace,rc_title,rc_type,rc_source,rc_minor,rc_cur_id,rc_user,rc_user_text,rc_comment,rc_this_oldid,rc_last_oldid,rc_bot,rc_ip,rc_patrolled,rc_new,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_id) VALUES ('2013-12-05 20:33:02 GMT','0','Main_Page','1','mw.new','0','1','0','MediaWiki default',,'1','0','0','127.0.0.1','0','1','0','524','0','0',NULL,,,'1')
Function: RecentChange::save
Error: 23502 ERROR:  null value in column "rc_cur_time" violates not-null constraint

[2] https://s3.amazonaws.com/archive.travis-ci.org/jobs/15003959/log.txt

PS: I do wonder if someone on the WMF site really runs tests (it's the second time this week Travis fails because of deployed core incompatibilities).

Yes, this is bug 40744 gone bad. The fix is in gerrit change I66034fad8a1cf5485b5daf0421378a28ca58beab but somehow having pains in review :)


I have some more interesting PostgreSQL fixes in the pipeline:

Gerrit change #99349
Gerrit change #99640
Gerrit change #99648
Gerrit change #99676  
Gerrit change #100141
Gerrit change #100154

feel free to try them as all of them are still in the works.