Item13082: Cache fetch is case insensitive in MySQL, but topic names are case sensitive

I had a user attempt to go to a topic named NoEthernetLIght, which generated a 404 (properly). He corrected the typo (NoEthernetLight) and was given the same 404, including a "Similar page" result of NoEthernetLight, clicking which returns him to NoEthernetLIght. After clearing the cache he was able to hit NoEthernetLight normally. -- FredTarbox - 09 Nov 2014

Which cache are you referring to? Which Foswiki engine and plugins are you using?

-- MichaelDaum - 10 Nov 2014

The PageCache available from the Tuning tab in configure for Foswiki 1.2.0, which creates a "This page was cached on 05 Nov 2014 - 14:19. Get a fresh version here" message at the bottom of every page. I, ah, I'm using full blown mysql. Could that be the problem?

$Foswiki::cfg{Cache}{Enabled} = 1;

$Foswiki::cfg{Cache}{WebDependencies} = 'WebRss, WebAtom, WebTopicList, WebIndex, WebSearch, WebSearchAdvanced';

$Foswiki::cfg{Cache}{DependencyFilter} = '$Foswiki::cfg{SystemWebName}\\..*|$Foswiki::cfg{TrashWebName}\\..*|.*Template$|TWiki\\..*';

$Foswiki::cfg{Cache}{RootDir} = '$Foswiki::cfg{WorkingDir}/tmp/cache';

$Foswiki::cfg{Cache}{DBFile} = '$Foswiki::cfg{WorkingDir}/tmp/foswiki_db';

$Foswiki::cfg{Cache}{NameSpace} = '$Foswiki::cfg{DefaultUrlHost}';

$Foswiki::cfg{Cache}{MaxSize} = 1000;

$Foswiki::cfg{Cache}{Servers} = '127.0.0.1:11211';

$Foswiki::cfg{Cache}{Implementation} = 'Foswiki::PageCache::DBI::MySQL';

$Foswiki::cfg{Cache}{DBI}{MySQL}{Host} = 'localhost';

$Foswiki::cfg{Cache}{DBI}{MySQL}{Port} = '3306';

$Foswiki::cfg{Cache}{DBI}{MySQL}{Database} = 'Foswiki';

$Foswiki::cfg{Cache}{DBI}{MySQL}{Username} = 'foswiki';

$Foswiki::cfg{Cache}{DBI}{MySQL}{Password} = MIME::Base64::decode_base64('');

Major plugins are natskin, solrplugin, classificationplugin, ldapplugin

-- FredTarbox - 10 Nov 2014

Fred, I've created two tasks on trunk.foswiki.org TestCaseSensitivity and TestCasesensitivity. I don't see any cache collisions.

-- GeorgeClark - 21 Dec 2014

This bug only affects the MySQL implementation, which is case insensitive by default. When the cache tables are initially declared, the MySQL implementation should mark columns that are meant to be searched with case sensitivity as COLLATE latin1_bin:

CREATE TABLE t1
(
    col1 VARCHAR(5)
      COLLATE latin1_bin
);

This forces that column to have a binary collation, which creates case sensitivity. http://dev.mysql.com/doc/refman/5.0/en/charset-column.html

-- FredTarbox - 23 Dec 2014

Marking urgent for 1.2, as fix seems simple and issue is disruptive.

-- GeorgeClark - 23 Dec 2014

I can confirm the error. The same topic with different case point to the same cache entry.

-- MichaelDaum - 07 Jan 2015

Commenting is disabled while not logged in