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...)

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.

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

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 :)

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

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.

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.

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).

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?

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?

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.

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.

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 :)

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

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.

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.

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.

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)

> 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!

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

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

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

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.

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.

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

Yes.

Fixed in bug 188656.