Topic on Project:Support desk

Search function in File list doesn't work properly

36
Summary by Ciencia Al Poder
Zioalex (talkcontribs)
Fereal (talkcontribs)

I've always thought the File List search needed some sort of option to activate since it never worked for me.

Checking the file, the query generated attempts to convert both the image names and the search query to lowercase before using a LIKE comparison. I've checked the database and apparently the image name column is set as VARBINARY, preventing the lowercase conversion of the image name thus botching the comparison.

TL;DR Go to includes\specials\SpecialListfiles.php. On Line 84, change:

$this->mQueryConds[] = 'LOWER(img_name)' .

to

$this->mQueryConds[] = 'CONVERT(img_name USING latin1)' .

Necrobumping since this problem still persists in 1.19.

kthxbye.

165.212.186.27 (talkcontribs)

It appears that somewhere around v1.22 they introduced a new function to replace the mQueryConds member variable. When they did this, the patch above recommended stopped working. I believe this is because the new function, "protected fuction buildQueryConds" handles this task now.

TL;DR Go to includes/specials/SpecialListfiles.php, Line 138:

 $conds[] = 'LOWER(' . $prefix . '_name)' .

I'm not sure what to change this to.

For the full function, see here:

 protected function buildQueryConds( $table ) {
               $prefix = $table === 'image' ? 'img' : 'oi';
               $conds = array();

               if ( !is_null( $this->mUserName ) ) {
                       $conds[ $prefix . '_user_text' ] = $this->mUserName;
               }

               if ( $this->mSearch !==  ) {
                       $nt = Title::newFromURL( $this->mSearch );
                       if ( $nt ) {
                               $dbr = wfGetDB( DB_SLAVE );
                               $conds[] = 'LOWER(' . $prefix . '_name)' .
                                       $dbr->buildLike( $dbr->anyString(),
                                               strtolower( $nt->getDBkey() ), $dbr->anyString() );
                       }
               }

               if ( $table === 'oldimage' ) {
                       // Don't want to deal with revdel.
                       // Future fixme: Show partial information as appropriate.
                       // Would have to be careful about filtering by username when username is deleted.
                       $conds['oi_deleted'] = 0;
               }

               // Add mQueryConds in case anyone was subclassing and using the old variable.
               return $conds + $this->mQueryConds;
       }
165.212.186.27 (talkcontribs)

Nevermind, I figured it out.

TL;DR Go to includes/specials/SpecialListfiles.php, Line 138 change:

$conds[] = 'LOWER(' . $prefix . '_name)' .

to

$conds[] = 'CONVERT(' . $prefix . '_name USING latin1)' .
Ciencia Al Poder (talkcontribs)
165.212.186.27 (talkcontribs)

Unless i'm reading it wrong, that bug seems to be more based on an error in handling non standard characters. They couldn't search because the db table was not accepting UTF8.

With the issue described above, its not a special character but just a capital letter. I didn't need to change the db table format, just how the query was being submitted to the db.

165.212.186.27 (talkcontribs)

nevermind, both issues are related to the VARBINARY column type. In the resolution above we changed the query, in the resolution in the bug, they changed the column type. either way would resolve it I guess.

Ciencia Al Poder (talkcontribs)

The efficient way is to convert the database field, specially if it's part of an index, because searching for that column would use the index. If you need to convert the value for searching, then the index won't be used and the database engine would need to do a full table scan instead.

212.149.48.42 (talkcontribs)

I am A newbie here. I accidently changed the subject of the thread. I actually wanted to start a new one. Please change it back. Sorry.

212.149.48.42 (talkcontribs)
Loman87 (talkcontribs)

Hi everybody,

sorry but I am facing this same issue in 1.27.1. In Special:ListFiles I can't retrieve any file using letters (either upper and lower case) in the query; if I use only numbers I get results.

The solution proposed in this thread, seems not to be still valid for newer versions of MW. Maybe is this another bug?

Thanks for your collaboration!

Ciencia Al Poder (talkcontribs)

Can you check the datatype of the column img_name from the image table? It should be varbinary.

Loman87 (talkcontribs)

Hi,

I am not sure if the following is what you asked, however this is what I find in tables.sql for the image table:

CREATE TABLE /*_*/image (

  -- Filename.

  -- This is also the title of the associated description page,

  -- which will be in namespace 6 (NS_FILE).

  img_name varchar(255) binary NOT NULL default '' PRIMARY KEY,

  -- File size in bytes.

  img_size int unsigned NOT NULL default 0,

  -- For images, size in pixels.

  img_width int NOT NULL default 0,

  img_height int NOT NULL default 0,

  -- Extracted Exif metadata stored as a serialized PHP array.

  img_metadata mediumblob NOT NULL,

  -- For images, bits per pixel if known.

  img_bits int NOT NULL default 0,

  -- Media type as defined by the MEDIATYPE_xxx constants

  img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,

  -- major part of a MIME media type as defined by IANA

  -- see http://www.iana.org/assignments/media-types/

  -- for "chemical" cf. http://dx.doi.org/10.1021/ci9803233 by the ACS

  img_major_mime ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown",

  -- minor part of a MIME media type as defined by IANA

  -- the minor parts are not required to adher to any standard

  -- but should be consistent throughout the database

  -- see http://www.iana.org/assignments/media-types/

  img_minor_mime varbinary(100) NOT NULL default "unknown",

 -- Description field as entered by the uploader.

  -- This is displayed in image upload history and logs.

  img_description varbinary(767) NOT NULL,

  -- user_id and user_name of uploader.

  img_user int unsigned NOT NULL default 0,

  img_user_text varchar(255) binary NOT NULL,

  -- Time of the upload.

  img_timestamp varbinary(14) NOT NULL default '',

  -- SHA-1 content hash in base-36

  img_sha1 varbinary(32) NOT NULL default ''

) /*$wgDBTableOptions*/;

Loman87 (talkcontribs)

Ok, maybe I understand now. I have to change  

img_name varchar(255) binary NOT NULL default '' PRIMARY KEY,

to

  img_name varbinary(255) NOT NULL default '' PRIMARY KEY,

is that right?

Loman87 (talkcontribs)

I made this change but the result is the same...any other idea?

Ciencia Al Poder (talkcontribs)

tables.sql is how the tables are created, and they should already exist on your installation. Changing the script will do nothing unless you install MediaWiki on a new database. Also, you should not be editing MediaWiki files directly.

What I asked is to check the type and collation of the actual tables on your database. The tables.sql may be changed on each upgrade, and if your wiki is old it may have the wrong datatype.

In any case, varchar(X) binary is not equivalent to varbinary(X) (see this) and I didn't check the actual type of those columns in tables.sql, so this were mostly instructions for you to check rather than changes to do to your database.

https://stackoverflow.com/questions/7617412/discover-collation-of-a-mysql-column

Collations other than *_bin are probably wrong. This feature is probably still broken since task T34207 is still open. I wonder why using this instead of Special:Search.

Loman87 (talkcontribs)

Good morning and thanks for you patience.

I check the actual database and this is the result:

SHOW FULL COLUMNS FROM image;

stdClass Object

(

    [Field] => img_name

    [Type] => varbinary(255)

    [Collation] =>

    [Null] => NO

    [Key] => PRI

    [Default] =>

    [Extra] =>

    [Privileges] => select,insert,update,references

    [Comment] =>

The datatype seems ok; the collation instead is not *_bin as you stated. Do I have to change it? How?

I use Special:ListFiles because the results presentation is cleaner and also because the results have a chronological order (for some purposes pertinence is not always the best choice). For me it is easier to work here, but now I have a lot of files and I need to use the search function.

Thanks again for your collaboration!

Ciencia Al Poder (talkcontribs)

Looks like, in order to be able to search, the field mist be varchar(255) binary, not varbinary(255)

Cascosoft (talkcontribs)

Hi,

I am experiencing this issue with the following configuration:

MediaWiki 1.30.0
PHP 5.6.29-1+deb.sury.org~xenial+1 (apache2handler)
MySQL 5.7.22-0ubuntu0.16.04.1

Search in the Special:ListFiles only returns results when using numbers in the search query (and these numbers are present in a file name).

Is this a bug or something broke in my wiki?

Most comments (up) seem to indicate is a known issue from previous versions. If so, is there a patch?

PorkCharSui79 (talkcontribs)

I'm also experiencing this problem with the following configuration:

MediaWiki 1.31.0
PHP 7.0.33 (apache2handler)
MySQL 5.6.43-84.3

If I omit the first letter of the file I can find them. It doesn't apply to numbers though, they are found without a problem.

Jamiehutber (talkcontribs)

Yep, also having the exact same problem... sickening :( Exactly as described above PorkCharSui79

Cornulio (talkcontribs)

We have the same Problem with


mediawiki 1.31.0

PHP 7.2.16

MariaDB: 5.5.60


but I found a wierd little workaround:

e.g. I search for a file named Start

I can find it if I just type S

I can find it if I type art or tart

BUT I can't find it when I type Start or even just St

Haakmak (talkcontribs)

Same Problem.


MediaWiki     1.31.0

PHP     7.2.20-2+ubuntu18.04.1+deb.sury.org+1 (fpm-fcgi)

MySQL     5.7.27-0ubuntu0.18.04.1


Does anyone have a solution? Thank you

Fereal (talkcontribs)

Can't believe a problem I've encountered from basically fresh MediaWiki installations has an answer from myself from 8 years ago.

Go to /includes/specials/pagers/. Open ImageListPager.php. Find the line:

$conds[] = 'LOWER(' . $prefix . '_name)' .

And replace it with:

$conds[] = 'CONVERT(' . $prefix . '_name USING utf8)' .

This fix is only for Special:Listfiles. Don't expect fixes for vanilla search from devs since Wikimedia projects use their own search functions.

Cornulio (talkcontribs)

Thank you! works fine!

165.225.76.91 (talkcontribs)

@Fereal Thank you very much!!!!

Bruceillest (talkcontribs)

@Fereal Thanks this worked for my Wiki version 1.32.0. The Fix is still going strong!

S0ring (talkcontribs)

This workaround is working, why isn't patched and deployed into the next MW version?

Ciencia Al Poder (talkcontribs)
S0ring (talkcontribs)

The patch is addressed to fix the file SpecialNewimages.php, while the workaround from this topic fixes the file ImageListPager.php (!)

2001:420:C0C0:1002:0:0:0:35D (talkcontribs)

I also found this patch worked. Is there any way we can promote that patch for review again?

Gruniversal (talkcontribs)
Ernstkm (talkcontribs)

Those wishing to automate the process can apply Gruniversal's patch directly onto your 1.35.x installation with

cd /path/to/your/mediawiki/installation
curl https://gerrit.wikimedia.org/r/changes/mediawiki%2Fcore~630340/revisions/2/patch?download \
  | base64 -d | patch -p1 --dry-run

Use base64 -D on macOS (and maybe BSD) and remove the --dry-run to actually do the thing if you get no complaints from patch --dry-run.

This will not apply cleanly to 1.36 and above, so you'll want to make the changes by hand, based on some variation of Fereal's suggestions above, or the patch from phab:T34207.

It might help to make backups of the core files that are about to be modified to something like .factory first, so you can more easily diff to see what changed, and go back if everything else goes disastrously, although patch has a -R ("reverse") option, if that's any consolation.

2600:1700:4BA0:8430:9836:C644:BD8:87C3 (talkcontribs)

Still works, still ridiculous that a one-line fix has persisted in a talk page for 10 years without promotion.

45.45.58.50 (talkcontribs)

Anyone found the solution for the 1.38x ?

Those two files arent the same anymore, and i'm a bit lost.

Reply to "Search function in File list doesn't work properly"