Page MenuHomePhabricator

uploaded files cannot be updated or deleted (PostgreSQL)
Closed, ResolvedPublic

Assigned To
None
Authored By
bzimport
Nov 8 2007, 4:37 PM
Referenced Files
F4309: trunk_patch.txt
Nov 21 2014, 9:55 PM
F4308: patch.txt
Nov 21 2014, 9:55 PM
F4306: DatabasePostgres.php
Nov 21 2014, 9:55 PM
F4305: Database.php
Nov 21 2014, 9:55 PM
F4304: LocalFile.php
Nov 21 2014, 9:55 PM

Description

Author: peter.scheie

Description:
We're running MW version 1.11.1 (according to the RELEASE-NOTES file) as installed via SVN on CentOS 5, using Postgresql 8.1.9 as installed via yum from the CentOS repos. Uploading files works, and users are able to subsequently download those files. However, attempting to update or remove an existing file, whether as a regular logged-in user or as the wiki sysop, results in the following error:

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,'.doc')),'1','2007-11-07 20:39:47 GMT','',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 = 'JMS_Messaging_Problem_Within_Dual.doc' 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 /var/www/ctswiki/includes/Database.php(779): DatabasePostgres->reportQueryError('ERROR: functio...', 1, 'INSERT INTO fi...', 'LocalFileDelete...', false)
#1 /var/www/ctswiki/includes/Database.php(1845): Database->query('INSERT INTO fi...', 'LocalFileDelete...')
#2 /var/www/ctswiki/includes/filerepo/LocalFile.php(1216): Database->insertSelect('filearchive', 'image', Array, Array, 'LocalFileDelete...')
#3 /var/www/ctswiki/includes/filerepo/LocalFile.php(1297): LocalFileDeleteBatch->doDBInserts()
#4 /var/www/ctswiki/includes/filerepo/LocalFile.php(873): LocalFileDeleteBatch->execute()
#5 /var/www/ctswiki/includes/FileDeleteForm.php(75): LocalFile->delete('')
#6 /var/www/ctswiki/includes/ImagePage.php(491): FileDeleteForm->execute()
#7 /var/www/ctswiki/includes/Wiki.php(397): ImagePage->delete()
#8 /var/www/ctswiki/includes/Wiki.php(48): MediaWiki->performAction(Object(OutputPage), Object(ImagePage), Object(Title), Object(User), Object(WebRequest))
#9 /var/www/ctswiki/index.php(89): MediaWiki->initialize(Object(Title), Object(OutputPage), Object(User), Object(WebRequest))
#10 {main


Version: 1.11.x
Severity: critical
OS: Linux
Platform: PC

Details

Reference
bz11905

Related Objects

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.
StatusSubtypeAssignedTask
InvalidNone
ResolvedNone

Event Timeline

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

ecornely wrote:

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}

ecornely wrote:

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

ecornely wrote:

The LocalFile.php as I changed it

Attached:

ecornely wrote:

The Database.php as I changed it

Attached:

ecornely wrote:

The DatabasePostgres.php as I changed it

Attached:

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

ecornely wrote:

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.

Attached:

ecornely wrote:

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()

Attached:

km4hr wrote:

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}

kstauffer wrote:

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.

isaka wrote:

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

isaka wrote:

*** 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.

Jdforrester-WMF subscribed.

Migrating from the old tracking task to a tag for PostgreSQL-related tasks.