Case insensitive query possible?


This is a long-standing feature request, but yes, the answer tends to be "change your collation". Unfortunately, no one has been able to tell how to do so in a safe way (e.g. in phpmyadmin).

19:42, 30 April 2013

I've managed to get case insensitive ask queries in MW 1.21.1, SMW, by setting the character set and collation, and changing 3 columns from varbinary to varchar.

  1. During the database step in the mediawiki installation, choose UTF8 as the database character set. (If binary was chosen the DB needs to be recreated and pages migrated). Note that issues have been noted by other users with UTF8 and some languages, so test this thoroughly if using a language containing special characters.
  2. Backup database using mysqldump
  3. Open MySQL Workbench as the root (or other authorised) user
  4. Run the following script:
use [Your_Wiki_DB];

/* The following show the character set (expecting utf8) and the collation (expecting utf8_general_ci) of the database, respectively.  
 * Uncomment and run against the relevant database to show the default settings. 
 * Note that changing these does not affect existing tables. 
--show variables like "character_set_database";
--show variables like "collation_database";

/* The following alter the relevant columns in the SMW tables to get case insensitive searching working */
-- smw_title
ALTER TABLE [Your_Wiki_DB].smw_object_ids change smw_title smw_title_bak varbinary(255);
ALTER TABLE [Your_Wiki_DB].smw_object_ids ADD smw_title VARCHAR(255) AFTER smw_namespace;
update [Your_Wiki_DB].smw_object_ids set smw_title = cast(smw_title_bak as CHAR) ;

-- smw_sort_key
ALTER TABLE [Your_Wiki_DB].smw_object_ids change smw_sortkey smw_sortkey_bak varbinary(255);
ALTER TABLE [Your_Wiki_DB].smw_object_ids ADD smw_sortkey VARCHAR(255) AFTER smw_subobject;
update [Your_Wiki_DB].smw_object_ids set smw_sortkey = cast(smw_sortkey_bak as CHAR) ;

-- smw_di_blob
ALTER TABLE [Your_Wiki_DB].smw_di_blob change o_hash o_hash_bak varbinary(255);
ALTER TABLE [Your_Wiki_DB].smw_di_blob ADD o_hash VARCHAR(255) AFTER o_blob;
update [Your_Wiki_DB].smw_di_blob set o_hash = cast(o_hash_bak as CHAR) ;
5. Case insensitive searching should now work

Note that you may get an error with the UPDATE statements if your preferences in MySQL Workbench are set with "Safe Update" on. This can be turned off by going to Edit>Preferences...>SQL Queries tab. Then uncheck "Safe Updates"... and reconnect to the DB.

01:15, 10 October 2013

Note that the SQL table changes will be reverted if you upgrade your SMW version, so would need to be reapplied. Painful I know, but for us case insensitivity is mandatory.

23:09, 29 October 2013