What is the minimum MySQL version for 1.5?
Number of replies: 7It would be great to have the minimum supported db version somewhere in the release/installation notes.
Re: What is the minimum MySQL version for 1.5?
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.
Regards... Tom
Re: What is the minimum MySQL version for 1.5?
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.
Cheers! M
Re: What is the minimum MySQL version for 1.5?
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?
Re: What is the minimum MySQL version 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.
blocks/quiz_results/block_quiz_results.php
enrol/ldap/enrol.php
lib/datalib.php

lib/moodlelib.php

mod/assignment/lib.php
mod/forum/lib.php
mod/quiz/report/analysis/report.php
mod/quiz/report/overview/report.php
user/index.php

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:
ONconditional_expr
| USING (column_list
)
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.
Note that INNER JOIN
syntax allows a
join_condition
only from MySQL 3.23.17 on.
The same is true for JOIN
and
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.
Regards,
Jeff
Re: What is the minimum MySQL version for 1.5?
So my expectations were in line with MySQL's documentation but in practice both seem to be wrong.
Re: What is the minimum MySQL version for 1.5?
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.
regards,
Jeff
Re: What is the minimum MySQL version for 1.5?
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.