Well, I found out that MySQL 3.23.58 (yeah it's old, but it's the
default supported version for RHEL 3, so I'm sure there are others
still using it) is not sufficient due to the extensive use of join...on
statements in 1.5. I'm planning to upgrade to 4.0.x, will this be
enough to run 1.5?
It would be great to have the minimum supported db version somewhere in the release/installation notes.
What is the minimum MySQL version for 1.5?
Number of replies: 7Re: What is the minimum MySQL version for 1.5?
Michael...
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
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?
Hi Martin, it is bug 3907, except the fix didn't turn out to be that simple (the only thing we've found that breaks so far is the metacourse enrollments). Jeff may have more details when he gets back from a well deserved long weekend. Basically there are a lot more join..ons in 1.5, though 1.5 seems to work fine so far other than metacourses, we just don't have the time to do extensive testing and debugging right now to see what else might break, if upgrading to 4.0.x will fix it.
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?
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?
Hi Martin,
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:
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
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?
The problem we _are_ seeing, however, is that people with the latest MySQL 3.23.x (3.23.58, I think) report that JOIN ... ON is failing. MySQL 3.23.17 is _very_ old, MySQL got really popular around the time of v3.23.32, and anything earlier than ~ v3.23.20 is _really_ broken.
So my expectations were in line with MySQL's documentation but in practice both seem to be wrong.
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?
Martin,
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
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?
Yes, 3.23 is supposed to still be supported in Moodle 1.5. This is what I keep telling developers who want to do fancy SQL stuff. 
I'm keenly aware there are many users still stuck on this old version for different reasons.
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.
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.