[Phpwiki-bugs] [ phpwiki-Bugs-966398 ] title search doesn't work with the mysql backend

[SourceForge.net <no...@so...>]

Bugs item #966398, was opened at 2004-06-04 11:56
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=106121&aid=966398&group_id=6121

Category: MySQL
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Antonio Bonifati (abonifa)
Assigned to: Nobody/Anonymous (nobody)
Summary: title search doesn't work with the mysql backend

Initial Comment:
Using phpwiki 1.3.7 (but also 1.3.9) with MySQL 4.1.0-alpha
I noticed that the title search doesn't work as expected.

This is because field page.pagename is declared as
VARCHAR BINARY
and then the MySQL LOWER string function is used on it
(please see function text_search in
lib/WikiDB/backend/PearDB.php).

I noticed that the LOWER() string passes a BINARY value
unchanged
instead of changing it to all lowercase! This way
almost all the
queries performed on a title search return an empty set!

Here is a simple query that make me know LOWER has no
effect
on BINARY columns:

$ mysql phpwiki
...
mysql> SELECT LOWER(pagename) FROM page WHERE id=2;
+-----------------+
| LOWER(pagename) |
+-----------------+
| AddingPages     |
+-----------------+
...

As a quick fix I removed the BINARY attribute from
page.pagename.

Reading throughout the MySQL manual I
discovered the difference between using BINARY or not:

with BINARY the comparisons use the ASCII order of the
machine
where the MySQL server is running and are case-sensitive,

without BINARY they are case-insensitive and use
the current client charset (ISO-8859-1 Latin1 by default).

I think this could be a MySQL rather than PhpWiki fault
and I've
posted a related bug report to MySQL developers.

http://bugs.mysql.com/4001

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=106121&aid=966398&group_id=6121