Wikimedia Bugzilla is closed!

We're experiencing same problem with version 1.11.0 install from tar.gz using Postgresql 8.1.9 too.

Warning: pg_query() [function.pg-query]: Query failed: ERROR: function concat(text, "unknown") does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. in /opt/mediawiki-1.11.0/includes/DatabasePostgres.php on line 515
Erreur interne

A database error has occurred Query: INSERT INTO filearchive (fa_storage_group,fa_storage_key,fa_deleted_user,fa_deleted_timestamp,fa_deleted_reason,fa_deleted,fa_name,fa_archive_name,fa_size,fa_width,fa_height,fa_metadata,fa_bits,fa_media_type,fa_major_mime,fa_minor_mime,fa_description,fa_user,fa_user_text,fa_timestamp) SELECT 'deleted',IF(img_sha1='', '', CONCAT(img_sha1,'.jpg')),'2','2007-11-14 11:18:58 GMT','erreur de nom',0,img_name,NULL,img_size,img_width,img_height,img_metadata,img_bits,img_media_type,img_major_mime,img_minor_mime,img_description,img_user,img_user_text,img_timestamp FROM image WHERE img_name = 'Eclipse_use_source01.jpg' Function: LocalFileDeleteBatch::doDBInserts Error: 1 ERROR: function concat(text, "unknown") does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts.

Backtrace:

#0 /opt/mediawiki-1.11.0/includes/Database.php(795): DatabasePostgres->reportQueryError('ERROR: functio...', 1, 'INSERT INTO fi...', 'LocalFileDelete...', false)
#1 /opt/mediawiki-1.11.0/includes/Database.php(1861): Database->query('INSERT INTO fi...', 'LocalFileDelete...')
#2 /opt/mediawiki-1.11.0/includes/filerepo/LocalFile.php(1216): Database->insertSelect('filearchive', 'image', Array, Array, 'LocalFileDelete...')
#3 /opt/mediawiki-1.11.0/includes/filerepo/LocalFile.php(1297): LocalFileDeleteBatch->doDBInserts()
#4 /opt/mediawiki-1.11.0/includes/filerepo/LocalFile.php(873): LocalFileDeleteBatch->execute()
#5 /opt/mediawiki-1.11.0/includes/FileDeleteForm.php(75): LocalFile->delete('erreur de nom')
#6 /opt/mediawiki-1.11.0/includes/ImagePage.php(491): FileDeleteForm->execute()
#7 /opt/mediawiki-1.11.0/includes/Wiki.php(397): ImagePage->delete()
#8 /opt/mediawiki-1.11.0/includes/Wiki.php(48): MediaWiki->performAction(Object(OutputPage), Object(ImagePage), Object(Title), Object(User), Object(WebRequest))
#9 /opt/mediawiki-1.11.0/index.php(89): MediaWiki->initialize(Object(Title), Object(OutputPage), Object(User), Object(WebRequest))
#10 {main}

I found how to patch the bug :

In includes/filerepo/LocalFile.php :
Line 1195 : 'fa_storage_key'   => "IF(img_sha1='', '', CONCAT(img_sha1,$encExt))",
Should be : 'fa_storage_key'   => $dbw->conditional("img_sha1=''", "''", $dbw->concat("img_sha1",$encExt)),
Line 1227 : 'fa_storage_key'   => "IF(oi_sha1='', '', CONCAT(oi_sha1,$encExt))",
Should be : 'fa_storage_key'   => $dbw->conditional("oi_sha1=''","''",$dbw->concat("oi_sha1",$encExt)),

But it also needs a creation of a concat function in includes/Database.php and DatabasePostgres.php
I place thoses few lines in Databases.php after line 1899 :
/**
* Returns an SQL expression for a simple concat.
* Uses CONCAT on MySQL
*
*  (really useful since PostgreSQL uses || for concatenation)
*
* @param string $arg1 first SQL fragment
* @param string $arg2 second SQL fragment
*/
function concat($arg1,$arg2) {
        return " CONCAT($arg1,$arg2) ";
}

And those few lines after line 892 in DatabasePostgres.php
/**
* Returns an SQL expression for a simple concat.
* Uses CONCAT on MySQL but || on PostgreSQL
*
* @param string $arg1 first SQL fragment
* @param string $arg2 second SQL fragment
*/
function concat($arg1,$arg2) {
        return " ($arg1||$arg2) ";
}

After that I had problems with samllint and executed these sql requests :
ALTER TABLE filearchive ALTER fa_size TYPE int4;
ALTER TABLE filearchive ALTER fa_width TYPE int4;
ALTER TABLE filearchive ALTER fa_height TYPE int4;
ALTER TABLE filearchive ALTER fa_bits TYPE int4;
ALTER TABLE filearchive ALTER COLUMN fa_size SET STATISTICS -1;
ALTER TABLE filearchive ALTER COLUMN fa_width SET STATISTICS -1;
ALTER TABLE filearchive ALTER COLUMN fa_height SET STATISTICS -1;
ALTER TABLE filearchive ALTER COLUMN fa_bits SET STATISTICS -1;
I only had problem with fa_size but change more smallint to int by "precaution".

I hope this would help.
If anybody can make a patch... I suppose it would help

Created attachment 4336 [details]
The LocalFile.php as I changed it

Created attachment 4337 [details]
The Database.php as I changed it

Created attachment 4338 [details]
The DatabasePostgres.php as I changed it

Please submit in diff format. And use one diff only.

Created attachment 4339 [details]
patch from version http://svn.wikimedia.org/svnroot/mediawiki/branches/REL1_11/phase3

Sorry I'm new to open-source software development.
I hope I was right using that branch (branches/REL1_11/phase3) to create my patch.

But googling the web i also added those files a few days ago http://svn.wikimedia.org/viewvc/mediawiki?view=rev&revision=26040 to fix an other problem.

So my patch fixed two problems i had with mediawiki-1.11.0 using postgresql.

As i added a concat function in Database.php and DatabasePostgres.php, i suppose this function should also be defined in DatabaseOracle.php but i don"t know how concat is made in oracle SQL.

Created attachment 4340 [details]
Patch for trunk

I checkouted trunk version and realized it was already changed but somebody changed DBMS dependend code into an other DBMS dependend code.
I prefered replacing "IF() mysql syntax" not with "CASE WHEN postgresql syntax" but with $dbw->conditional()

I meticulously implemented all the recommend changes. Not only did it not fix the problem, it made it worse. Not only can I not now delete or upload existing files, but I can't upload any files at all. I think it would be fitting to warn users on the MediaWiki web site that some users are having trouble with PostgreSQL installations. I wish I had been warned.

My Environment:
MediaWiki: 1.11.0
OS: Centos5
php: 5.1.6
postgresql: 8.1.9

===============================================================================
Internal error

A database error has occurred Query: INSERT INTO oldimage (oi_name,oi_archive_name,oi_size,oi_width,oi_height,oi_bits,oi_timestamp,oi_description,oi_user,oi_user_text,oi_metadata,oi_media_type,oi_major_mime,oi_minor_mime,oi_sha1) SELECT img_name,'20071121191208!Image006.jpg',img_size,img_width,img_height,img_bits,img_timestamp,img_description,img_user,img_user_text,img_metadata,img_media_type,img_major_mime,img_minor_mime,img_sha1 FROM image WHERE img_name = 'Image006.jpg' Function: LocalFile::recordUpload2 Error: 1 ERROR: column "oi_metadata" is of type bytea but expression is of type text HINT: You will need to rewrite or cast the expression.

Backtrace:

#0 /opt/mediawiki-1.11.0/includes/Database.php(796): DatabasePostgres->reportQueryError('ERROR: column ...', 1, 'INSERT INTO ol...', 'LocalFile::reco...', false)
#1 /opt/mediawiki-1.11.0/includes/Database.php(1862): Database->query('INSERT INTO ol...', 'LocalFile::reco...')
#2 /opt/mediawiki-1.11.0/includes/filerepo/LocalFile.php(746): Database->insertSelect('oldimage', 'image', Array, Array, 'LocalFile::reco...')
#3 /opt/mediawiki-1.11.0/includes/filerepo/LocalFile.php(642): LocalFile->recordUpload2('20071121191208!...', '', '', Array, false)
#4 /opt/mediawiki-1.11.0/includes/SpecialUpload.php(436): LocalFile->upload('mwrepo://local/...', '', '', 1, Array)
#5 /opt/mediawiki-1.11.0/includes/SpecialUpload.php(248): UploadForm->processUpload()
#6 /opt/mediawiki-1.11.0/includes/SpecialUpload.php(14): UploadForm->execute()
#7 /opt/mediawiki-1.11.0/includes/SpecialPage.php(653): wfSpecialUpload(NULL, Object(SpecialPage))
#8 /opt/mediawiki-1.11.0/includes/SpecialPage.php(459): SpecialPage->execute(NULL)
#9 /opt/mediawiki-1.11.0/includes/Wiki.php(201): SpecialPage::executePath(Object(Title))
#10 /opt/mediawiki-1.11.0/includes/Wiki.php(45): MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage), Object(WebRequest))
#11 /opt/mediawiki-1.11.0/index.php(89): MediaWiki->initialize(Object(Title), Object(OutputPage), Object(User), Object(WebRequest))
#12 {main}

Hi, I had the same problem. I checked the changelog and found that r26043 should have all the required fixes for this.

I have now been using following combination for few weeks without any delete or other problems:
MediaWiki: 1.12alpha (r26043)
PHP: 5.1.6
PostgreSQL: PostgreSQL 8.1.9 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)

http://svn.wikimedia.org/viewvc/mediawiki?view=rev&revision=26043

-Kristian

Is anyone still having problems with this using the latest subversion revision? For the record, CASE..WHEN is standard SQL that both MySQL and Postgres support.

Perhaps I think same case but different solution.
Please test it.
http://bugzilla.wikimedia.org/show_bug.cgi?id=12365

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

Marking as fixed for now, please reopen if further problems found in subversion version.