Edgewall Software
Modify

Opened 11 years ago

Closed 11 years ago

Last modified 7 years ago

#8424 closed defect (fixed)

search is case sensitive when using MySQL

Reported by: Christian Boos Owned by: Christian Boos
Priority: normal Milestone: 0.11.5
Component: database backend Version: 0.11-stable
Severity: normal Keywords: mysql
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description

Doing some testing of source:branches/0.11-stable@8333, I noticed that the search in MySQL is case sensitive.

Search is case insensitive for the other backends and used to be case insensitive for MySQL as well.

However, in the new guidelines given in MySqlDb (since MySqlDb@23), the default collation should now be utf8_bin and this is what makes the search case sensitive.

I suggest the following fix:

  • trac/db/mysql_backend.py

     
    204204        return 'concat(%s)' % ', '.join(args)
    205205
    206206    def like(self):
    207         return "LIKE %s ESCAPE '/'"
     207        return "LIKE %s COLLATE utf8_general_ci ESCAPE '/'"
    208208
    209209    def like_escape(self, text):
    210210        return _like_escape_re.sub(r'/\1', text)

Attachments (0)

Change History (5)

comment:1 by Remy Blank, 11 years ago

Won't that break some queries like CachedRepository._next_prev_rev() where LIKE is used to match paths?

Oh, wait, LIKE is case insensitive on the other backends anyway, so we have that issue already. Shouldn't that be fixed as well?

comment:2 by Christian Boos, 11 years ago

You're right! However that secondary bug went unnoticed so far, so I don't think it's a big deal if we make MySQL have it as well…

Now for the second bug, almost all occurrences of like() should be changed to ilike().

We have then:

db/statement like() ilike()
PostgreSQL LIKE ILIKE
MySQL LIKE LIKE COLLATE utf8_general_ci
PySqlite ? LIKE

(?) perhaps http://www.sqlite.org/pragma.html#pragma_case_sensitive_like
But I wonder how to make use of this in the middle of building a query (not to mention the theoretical possibility of having like() and ilike() in the same query)

in reply to:  2 comment:3 by Christian Boos, 11 years ago

(?) perhaps http://www.sqlite.org/pragma.html#pragma_case_sensitive_like

Or some clever override of the like(X,Y,Z) function used in the Y LIKE X ESCAPE Z statement, with Z being (ab)used to convey the case sensitivity. See:

comment:4 by Jonas Borgström, 11 years ago

Milestone: 0.11.50.11.6

The mysql collation change is now committed in [8336] and [8337].

I'm leaving this ticket open but moving it to 0.11.6 until all ilike() changes are in place.

comment:5 by Christian Boos, 11 years ago

Milestone: 0.11.60.11.5
Resolution: fixed
Status: newclosed

I've created a new ticket for the ilike() changes: #8519.

Closing this ticket as the reported problem was fixed for 0.11.5.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Christian Boos.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Christian Boos to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.