Closed Bug 243351 Opened 20 years ago Closed 20 years ago

case-sensitive summary matching breaks MySQL < 4.0.2


(Bugzilla :: Query/Bug List, defect)

Not set



Bugzilla 2.18


(Reporter: justdave, Assigned: bugreport)




(1 file, 1 obsolete file)

The checkin from bug 225075 inadvertantly bumped the MySQL requirements to 4.0.2.

Our previous minimum version was 3.23.41.

We need to either:

1) Back out the patch from bug 225075
2) Find another way to fix that bug
3) Change to require MySQL 4.0.2 or later
Things to think about before bumping the MySQL minimum version:

No linux distro on the planet that I'm aware of ships a packaged MySQL 4.x yet.
 If you get it you have to get it directly from  This is due to
licensing issues...  MySQL changed the licensing in version 4.x so that it was
no longer "free enough" for most distros to redistribute it.  I've heard rumors
that this has since been corrected on the part of MySQL's licensing, but I have
yet to see anyone besides ship MySQL 4.x.
Flags: blocking2.18+
Target Milestone: --- → Bugzilla 2.18
Hmm. Are we sure there isn't another way to do this?
I'd back the old fix out for now.
OK, Dan posted this on mozilla-webtools:

>If you take the 'as Binary' (really the whole cast) out where the casts are in
>the instr function then the query works.  According to the MySQL docs though,
>instr() will only do case-sensitive searching in 3.23 in 4.x it uses the data

This seems to indicate that it was only broken in MySQL 4.x to begin with, and
the previous query worked fine in 3.23.  So we need to do a version detect on
MySQL and phrase the query differently depending on which version we're using.  Ick.
*** Bug 243538 has been marked as a duplicate of this bug. ***
Attachment #148484 - Flags: review?

   Can you try this on your site?
Comment on attachment 148484 [details] [diff] [review]
Patch - make SQL conditional on sever version

>+    my $sth = $dbh->prepare('SELECT @@version');

This only works in MySQL 3.23.50 and up.  Our minimum supported version is
3.23.41, so you need to use 'SELECT VERSION()' instead.

Attachment #148484 - Flags: review? → review-
Eeew, querying for server version feels so inefficient. Could we put this in
versioncache to avoid extra selects?
Comment on attachment 148484 [details] [diff] [review]
Patch - make SQL conditional on sever version

>+             # mysql 4.0.1 and lower do not support AS BINARY

Actually, MySQL 4.0.1 and lower don't support CAST().  In 3.23, it wasn't
necessary, because the INSTR() function was case sensitive.  In 4.0.0, INSTR()
was made case insensitive unless the column being checked was declared as a
binary type.  In 4.0.2 they added CAST() to make it possible to emulate the
3.23 behavior again.  That also means the original bug that got us into this
whole mess will still be present in 4.0.0 and 4.0.1.
Attached patch v2Splinter Review
This uses VERSION() to identify the version and adds a check to checksetup to
catch anyone attempting to use mysql 4.x.x below 4.0.2

Adding code to cache the SELECT VERSION() seems like more clutter than it is
worth.	When we move to mod_perl, this will be cached in memory.  In the
meantime, this is only used once per case-sensitifve match.
Assignee: justdave → bugreport
Attachment #148484 - Attachment is obsolete: true
Attachment #148498 - Flags: review?
Comment on attachment 148498 [details] [diff] [review]

>+             # mysql 4.0.1 and lower do not support CAST
>+             # mysql 3.*.* had a case-sensitive INSTR
>+             my $server_version = Bugzilla::DB->server_version;

On the first line, mention something like "but we already refuse to work with
those in checksetup"; without that the code looks like it's going to crash
intentionally ;-)

Tested on MySQL 3.23.58-nt and 4.0.18-log.

Attachment #148498 - Flags: review? → review+
Flags: approval?
Flags: approval? → approval+
checked in
Closed: 20 years ago
Resolution: --- → FIXED
FYI, here's Red Hat's bug for the enhancement request to update MySQL to 4.x:
QA Contact: matty_is_a_geek → default-qa
You need to log in before you can comment on or make changes to this bug.