case-sensitive summary matching breaks MySQL < 4.0.2

RESOLVED FIXED in Bugzilla 2.18

Status

()

RESOLVED FIXED
15 years ago
6 years ago

People

(Reporter: justdave, Assigned: bugreport)

Tracking

2.17.7
Bugzilla 2.18
Bug Flags:
approval +
blocking2.18 +

Details

Attachments

(1 attachment, 1 obsolete attachment)

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 checksetup.pl 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 mysql.com.  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 mysql.com ship MySQL 4.x.
Flags: blocking2.18+
Target Milestone: --- → Bugzilla 2.18
Hmm. Are we sure there isn't another way to do this?
(Assignee)

Comment 3

15 years ago
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
>type.

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. ***
(Assignee)

Comment 6

15 years ago
Created attachment 148484 [details] [diff] [review]
Patch - make SQL conditional on sever version
(Assignee)

Updated

15 years ago
Attachment #148484 - Flags: review?
(Assignee)

Comment 7

15 years ago
Carl,

   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.

Reference http://dev.mysql.com/doc/mysql/en/News-3.23.50.html
Attachment #148484 - Flags: review? → review-

Comment 9

15 years ago
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.
(Assignee)

Comment 11

15 years ago
Created attachment 148498 [details] [diff] [review]
v2

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
Status: NEW → ASSIGNED
(Assignee)

Updated

15 years ago
Attachment #148498 - Flags: review?

Comment 12

15 years ago
Comment on attachment 148498 [details] [diff] [review]
v2

>+             # 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.

r=jouni
Attachment #148498 - Flags: review? → review+
(Assignee)

Updated

15 years ago
Flags: approval?
Flags: approval? → approval+
(Assignee)

Comment 13

15 years ago
checked in
Status: ASSIGNED → RESOLVED
Last Resolved: 15 years ago
Resolution: --- → FIXED

Comment 14

15 years ago
FYI, here's Red Hat's bug for the enhancement request to update MySQL to 4.x:

https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=100517
QA Contact: matty_is_a_geek → default-qa
You need to log in before you can comment on or make changes to this bug.