Case insensitive query possible?

I was thinking about changing the collation, but I know very little about mysql. In phpmyadmin I found a table called smw_ids, but not sure if this is where to make the change. Clicking on structure, there was no collation shown on any of the rows. I tried changing the title and sort_key to utf8-general-ci, but it still didn't show anything set for collation in these rows. Anyone know where/how to make the change? Thanks

D3mon187 (talk)

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

Cavila (talk)

I've managed to get case insensitive ask queries in MW 1.21.1, SMW 1.8.0.5, 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) ;
COMMIT;

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.

Renae (talk)

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.

Renae (talk)