87958 - Bugzilla should require MySQL 3.23.41

Status: RESOLVED FIXED

(Bugzilla :: Installation & Upgrading, defect, P1)

Description

[Dave Miller [:justdave]]

The mysqldump utility provided with versions of MySQL earlier than 3.23.2 has a 
bug in it which causes column indexes with names that match built-in function 
names to be encoded in the output in such a way that when re-importing them they 
get interpretted as the functions instead of as index names.  This completely 
chokes any attempt to restore bugzilla databases from a mysqldump-generated 
backup.

In order to protect all the good sysadmins out there that do regular backups (not 
to mention sites that use the shadowdb), we should make sure they are using a 
version of MySQL that includes the mysqldump that has this fixed.

(That or explicitly name the index on the versions column in the bugs table - 
which is another option...)

Comment 1

[Myk Melez [:myk] [@mykmelez]]

mysql 3.23.17 supports the ANSI standard syntax for INNER JOIN.  We should
require this version or better so we can take advantage of this SQL command to
make our queries more understandable.  And, let's keep an eye on the
transactions support in recent versions; it might render the shadow database
unnecessary.

Comment 2

[Dawn Endico]

transaction support in 3.23.17 is still at the alpha level and they 
recommend not using it for production systems. my enthusiasm for
trying out transaction support is waning.

Also, i believe we looked in to mysql at the time we moved to mothra
and thought that the 3.23  line wasn't stable enough yet. (for reasons
besides transactions).  Mothra currently runs  3.22.32

Comment 3

[Andreas Franke (gone)]

I'm using MySQL 3.23.38 with bdb tables for transaction support. I think it's in
beta status. My experiences are that it's quite stable (as long as there aren't
very large rollbacks), but the speed is an issue: transaction support slows down
some queries by a factor of seven (7). So I don't recommend to use it in
bugzilla now, at least not at mozilla.org :)

Comment 4

[Zach Lipton [:zach]]

Setting this to 2.18, it looks like mysql needs more time to mature before we
make this the rule.

Comment 5

[Bradley Baetz (:bbaetz)]

resetting milestone, since its transaction support which is immature, not mysql.

Our current requirement is 3.22.5. This was released in Aug 1998. The last
non-security 3.22 release was in January 2000, I think.

3.23.12 was announced as the last alpha 3.23 release, and it was released in
March 2000 (I can't find archives til July 2000). We can assume that 3.23 has
been out and stable for > 18 months, though.

There have been numourous bug fixes since then, and performance enhancements
(the "allow one INSERT to run while SELECTS are running" in 3.23.7 (Dec 1999)
sounds interesting from the POV of the use of the shadow db. Maybe we should get
b.m.o to turn it off for a day after the upgrade, and see if anyone notices? It
probably still will be noticable, though)

Currently the patch to bug 107718 breaks bugzilla for < mysql 3.23.5, because
thats the earliest version which supports the ~ operator for bitsets. Earlier
versions document it; they just don't support it. All the people who tested that
patch used mysql 3.23.x. Are we even sure that we still run under 3.22.5? Has
anyone tested?

Now, when the groups stuff gets checked in, this will be fixed, but this
breakage may still happen again. Backwards compatability is great, but theres a
limit, I think.

Comment 6

[David Lawrence [:dkl]]

As an employee of Red Hat I can say that anyone basing there Bugzilla
installations off of our latest distributions should be running a modern version
of MySQL on their systems unless they downloaded and installed their own custom
copy of MySQL.

Red Hat 7.0 - mysql-3.23.22-6.i386.rp
Red Hat 7.1 - mysql-3.23.36-1.i386.rpm
Red Hat 7.2 - mysql-3.23.41-1.i386.rpm
So I think requiring MySQL 3.23+ should not be a problem at least with systems
based on Red Hat Linux.

Comment 7

[Dave Miller [:justdave]]

Debian still distributes 3.22.32, thus the version that's on landfill.
(and yes, I updated the package list and dug through it, that's actually the
most recent thing they have available).

Comment 8

[Bradley Baetz (:bbaetz)]

debian stable has 3.22.32.

testing has 3.23.36, and unstable has 3.23.43. Can we jsut require bugzilla
people running debian to upgrade?

Comment 9

[Dave Miller [:justdave]]

Adding Remi because I know he works on Debian stuff. :)

Remi: do you know when the MySQL 3.23.x packages for Debian will move out of
testing into stable?

Comment 10

[Matthew Tuck [:CodeMachine]]

I'd be reluctant to do this until Debian has a stable release that supports
3.23.X.  People are more likely to just not upgrade Bugzilla.

But anyway, I believe Debian woody is going stable within the next few months. 
As with most of these things, it's "when it's ready" though.

Comment 11

[Matthew Tuck [:CodeMachine]]

How about if we deprecate 3.22.X for 2.16 and require it for 2.18?  If we are
going down the "short release cycle" route, 2.18 should be soonish and Debian
should have released.

Comment 12

[Bradley Baetz (:bbaetz)]

That sounds good. Teh new group stuff is goin to be a _lot_ easier/faster with
temporary tables, rather than doing it ourself.

As long as we agree that we'll up teh requirements as soon as 2.16 is released,
regardless of when debian ends up releasing.

The upgrade isn't really difficult - the only reason bmo had trouble was because
the conf file was symlinked rather than hardlinked, and so the conf setting
wasn't set correctly.

Besides, debian stable doesn't ship bugzilla as a package - its only in
testing/unstable :)

Comment 13

[Bradley Baetz (:bbaetz)]

I'd say 3.23.3x. That gives us the replication stuff, and will work on RH7.1 if
x <= 6

3.23.36 will parse SELECT FOR UPDATE, so that might make it easier to handle the
transactions/locking rewrite, although probably not too much easier, since it
won't actually do anything on a non-transactional table - it will happly select,
but not do any locking...

Comment 14

[Bradley Baetz (:bbaetz)]

We now require 3.23.6, because the bugs table now has > 16 indexes, and we need
myiasm tables for that. 3.23.6 is the earliest version which allows you to
choose, apparently.

We should leave this bug open, though, to choose a version which is not an alpha
version, and adds additional features which we may want.

Comment 15

[Matthew Tuck [:CodeMachine]]

In response to Myk's comment #1, we probably won't be supporting INNER JOIN
because PgSQL uses it as an optimisation hint which is probably not what we want.

Comment 16

[Myk Melez [:myk] [@mykmelez]]

Presumably you are referring to:

http://www.postgresql.org/idocs/index.php?explicit-joins.html

It isn't clear to me that INNER JOIN is a serious problem, but in any case I
think a better option is to use it in a way that directs PostgreSQL to the best
possible plan.

Comment 17

[Bradley Baetz (:bbaetz)]

Unfortunately it is. We're very rarely going to do a better job than
postgresql's optmiser, so we relaly shouldn't use INNER JOIN if there are more
than two tables (for two tables, theres only one way to join them, so it doesn't
matter)

Comment 18

[Matthew Tuck [:CodeMachine]]

> for two tables, theres only one way to join them, so it doesn't matter

No, there's two, left to right and right to left!

Comment 19

[Bradley Baetz (:bbaetz)]

err... for a left (or right) join, there isn't, since its sort of explicit by
definition (because of possible non matches).....

Comment 20

[Dave Miller [:justdave]]

This was fixed back in July with the checkin from bug 99203.
Bugzilla now requires MySQL 3.23.6.

Comment 21

[Gervase Markham [:gerv]]

So what does this give us in the way of guaranteed-available MySQL features?
Anything?

Gerv

Comment 22

[Bradley Baetz (:bbaetz)]

gerv: Not much; we just bumped this for myiasm support. See comment #14.

I'm going to reopen this; we should at least require a stable version. See the
comments for other things we may want. We may need to version-bump for some
crossdb support, but we'll wait and see for that.

Comment 23

[Dave Miller [:justdave]]

OK, to satisfy bbaetz, I've made the version number more specific in the
summary.  I picked 3.23.32 because the given excuse is "we should require a
non-alpha version" and 3.23.32 is the first 3.23 version declared stable, per
MySQL's changelog.   If there's some other features that require a newer
version, speak up now.  FWIW, RedHat 7.2 has 3.23.41 on it.

Comment 24

[Jacob Steenhagen]

We currently require 3.23.41... shouldn't this be marked FIXED?

Comment 25

[Dave Miller [:justdave]]

Yes.

Comment 26

[Marc Schumann [:Wurblzap]]

Fixed in bug 188656.