Wikimedia Bugzilla is closed!

Created attachment 4937 [details]
Fix for non-standard SQL in  query

Deleting images fails with this error:

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,'.pdf')),
        '1',
        '2008-05-29 18:28:36 GMT',
        'This file was a test.',
        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 = 'Testpdf.pdf'

Function: LocalFileDeleteBatch::doDBInserts Error:
1 ERROR:  function concat(text, "unknown") does not exist

[Error message reformatted]

The problem is the query uses the non-standard MySQL function
CONCAT to concatenate stings. 

Additionaly, the code uses the MySQL IF function as a
conditional. This is also non-standard SQL.

I've attached a patch for mediawiki/includes/filerepo/LocalFile.php
that fixes the non-standard query.  Unfortunately, this will
break sites using a MySQL backend, as MySQL does not support the
'||' ANSI standard string concatenation operator. It is possible
that the correct thing to do is move the concatenation out of the
query and into the php code. 

The patch may help other postgres users in the meantime.

MySQL does understand the standard CASE WHEN THEN ELSE END
construction.