243351 - case-sensitive summary matching breaks MySQL < 4.0.2

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Dave Miller [:justdave]]

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

Comment 1

[Dave Miller [:justdave]]

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.

Comment 2

[Bradley Baetz (:bbaetz)]

Hmm. Are we sure there isn't another way to do this?

Comment 3

[Joel Peshkin]

I'd back the old fix out for now.

Comment 4

[Dave Miller [:justdave]]

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.

Comment 5

[Dave Miller [:justdave]]

*** Bug 243538 has been marked as a duplicate of this bug. ***

Comment 6

[Joel Peshkin]

Attachment: Patch - make SQL conditional on sever version

Comment 7

[Joel Peshkin]

Carl,

   Can you try this on your site?

Comment 8

[Dave Miller [:justdave]]

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

Comment 9

[Jouni Heikniemi]

Eeew, querying for server version feels so inefficient. Could we put this in
versioncache to avoid extra selects?

Comment 10

[Dave Miller [:justdave]]

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.

Comment 11

[Joel Peshkin]

Attachment: 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.

Comment 12

[Jouni Heikniemi]

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

Comment 13

[Joel Peshkin]

checked in

Comment 14

[Kenneth Porter]

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