It would be great to have the minimum supported db version somewhere in the release/installation notes.
I was using Whitebox EL 3.0 until March. I migrated the Moodle box to ClarkConnect 3.0 when I moved to a new server. CC 3.0 is based on Fedora Core 2, and it uses 4.1.8. It is running 1.5 very well.
I have a couple of other smaller Moodle installs (one for clergy spouses in the diocese - my wife is an Episcopal priest), and I have a cheap hosted web server for those. Cpanel tells me they are running "4.0.24-standard-log." 1.5 installs easily and seems to work better than it should.
Hope that gives you some data points.
I found out that MySQL 3.23.58
I've been wondering about the same, and planning on raising this in the DB schema forum. Perhaps this thread should be moved there?
Now, in part due to scalability, and in part due to a push towards better Postgres compatibility -- two things I'm involved with and promoter of -- we've been slowly moving towards JOIN..ON.
I wasn't aware that this broke MySQL 3.23.x . Or rather, I thought that complex conditions in the ON part would be unsupported, but the straight tablea.id = tableb.tableaid would "naturally" work.
Michael, is this just related to bug 3907 or have you found more instances? If it's just that bug... we'll revert to old practice. I assume you found it's not the only case, but it'd be good to hear more details.
We definitely need to hear from MD on this, I think. A few thoughts on the matter:
- MySQL 3.32.x is 'default supported version on RHEL 3' -- but I'd assume that v4.x is available from RH as well for RHEL3?
- MySQL 3.23.x is really old. MySQL.com doesn't list it anymore in its download page, it's only in the archive page, which to me means that it is already unsupported in terms of security and dataloss bugs.
- Now, regardless of all this, I think we should support it... except that no Moodle developer today seems to be running it. It is kind of impossible to support a db with tricky gotchas (and the mysql 3.32.x series has more than its fair share of those) if noone in the dev team is running it seriously.
In a sense, the mainstream db is MySQL v4.x at this time, and MySQL 3.x and Postgres users need to get involved and send patches. That's what we do at the Postgres end.
MySQL 3.x and Postgres users need to get involved and send patches
Well, we figured it would be probably be much easier to upgrade the db than patch all that (esp. not being sure which queries are breaking).
So long as it will work on 4.0.x, I just thought that it would be good, having discovered this, to let folks know they may want to upgrade their mysql for 1.5?
Related somewhat to bug 3907; after changing the code as listed in the bug report for enrol_ldap_sync.php I ran into a different SQL JOIN error from some different code. After getting this error I checked the code and found "JOIN ON" scattered all throughout the code. I did a quick grep (grep -ri "JOIN.*ON" ./*) and came up with the following offending files. There are probably more since there may be SQL queries built across several lines of code.
Quoting from the MySQL manual concerning the JOIN operator (http://dev.mysql.com/doc/mysql/en/join.html) the following snippet is relevant:
join_condition is defined as:
conditional_expr| USING (
You should generally not have any conditions in the
ON part that are used to restrict which
rows you want in the result set, but rather specify these
conditions in the
WHERE clause. There are
exceptions to this rule.
INNER JOIN syntax allows a
join_condition only from MySQL 3.23.17 on.
The same is true for
CROSS JOIN only as of MySQL 4.0.11.
Since 3.23.x is supposed to be supported it means that versions less than 3.23.17 are included so there should be no use of join_conditions for compatibility reasons. Hope that helps point out the seriousness of this issue. It looks like we will be upgrading MySQL versions here regardless and it looks like 4.0.11 would be a minimum upgrade, but some people probably don't have that luxury.
So my expectations were in line with MySQL's documentation but in practice both seem to be wrong.
As the MySQL manual states the INNER JOIN with join conditions works from 3.23.17, however the regular JOIN and CROSS JOIN do not work with join conditions until 4.0.11. The majority (all?) of the files I flagged in the previous post were using the regular JOIN with join conditions and thus were not working in our 3.23.58 install of MySQL.
From the documentation it appears that in order to use JOIN safely MySQL users must be running version 4.0.11 or greater. It looks like we are moving to 4.0.25, here but, this will probably be an issue for other Moodle users and I just wanted to bring it to attention.
However, like Martin L says not many (if any) of the people writing database code for Moodle still use MySQL 3, so bugs may slip through. MySQL 4.0 and later is DEFINITELY supported and will be for a long time.
I think we'll drop the MySQL 3 compatibility for future versions of Moodle (1.6 and on), so that we can move forward into this area.