Hi,
after updating to 1.5+ with release from 2005-06-18,
no forum Posts are displayed!
After copying the old mod/forum/lib.php in the updated moodle everything works...
There must be the problem:
diff -rB /usr/local/httpd/htdocs/forum/mod/forum/lib.php /usr/local/httpd/htdocs/forum-neu/mod/forum/lib.php
1c1
< <?php // $Id: lib.php,v 1.381.2.1 2005/06/14 15:50:13 defacer Exp $
---
> <?php // $Id: lib.php,v 1.381.2.2 2005/06/16 03:11:51 mjollnir_ Exp $
1283,1285c1283,1285
< FROM {$CFG->prefix}forum_discussions d,
< {$CFG->prefix}forum_posts p,
< {$CFG->prefix}user u
---
> FROM {$CFG->prefix}forum_discussions d
> JOIN {$CFG->prefix}forum_posts p ON p.discussion = d.id
> JOIN {$CFG->prefix}user u ON p.userid = u.id
1288d1287
< AND p.discussion = d.id
1290c1289
< AND p.userid = u.id $groupselect $userselect
---
> $groupselect $userselect
Hi,
today, i made another 1.5+ update.
My problems are still the same!
With new mod/forum/lib.php no forum-articles are shown!
Am I the only one who has this problem?
Bertold
today, i made another 1.5+ update.
My problems are still the same!
With new mod/forum/lib.php no forum-articles are shown!
Am I the only one who has this problem?
Bertold
Hello
We have 2 problems with the update. Our intranet runs on version 1.4.2 dev something. We updated to version 1.4.5+ to make sure we had the latest 4 stable version. No problem. All intact. Then we upgraded to 1.5 and the following 2 things were apparently wrong:
1 all forum posts are missing, except for the ones to be shown on the front page. (If you hit "more discussions" you get an empty screen with "no forum posts"). If you say show 2 articles on the front page, then these are shown ok. If you say show 10, these are also shown. The forum posts for all the courses are in the database, just not shown.
Bug2 is that courses listed in categories are not shown if these had descriptions shown in their listing (in the previous version). If you "switch editing on" then they are shown!
We run on Postgres 7.4, and have replicated this a number of times, of course running test upgrades on other servers.
I am not the technical guy here, just the manager. Our database / php colleague will follow this bug thread!
Hi Bertold/Martin/All,
We have the same problem on postgres 7.4.8. The problem is not the database but a wrong query.
I might have the solution for your problem, when looking closely you see the SQL generated is wrong : (i turned on adodb debug to see this)
SELECT p.id, p.subject, p.modified, p.discussion, p.userid, d.name as myname, d.timemodified, d.usermodified, d.groupid, u.firstname, u.lastname, u.email, u.picture , um.firstname AS umfirstname, um.lastname AS umlastname FROM , moodle_forum_discussions d, moodle_forum_posts p, moodle_user u LEFT JOIN moodle_user um on (d.usermodified = um.id) WHERE d.forum = '1' AND p.discussion = d.id AND p.parent = 0 AND p.userid = u.id ORDER BY d.timemodified DESC
The Bit ....moodle_user u LEFT JOIN moodle_user um on (d.usermodified = um.id) .... makes no sense at all. The join link field should allways be in one of the two tables joined: (SELECT a.x, b.y, c.z FROM a, b LEFT JOIN c (b.some_id = c.some_id)
The solution is : (Moodle people please watch this)
Exchange the moodle_forum_discussions d and the moodle_user u in the from clause in the file mod/forum/lib.php
The new working SQL command generated is
SELECT p.id, p.subject, p.modified, p.discussion, p.userid, d.name as myname, d.timemodified, d.usermodified, d.groupid, u.firstname, u.lastname, u.email, u.picture , um.firstname AS umfirstname, um.lastname AS umlastname FROM moodle_user u, moodle_forum_posts p moodle_forum_discussions d LEFT JOIN moodle_user um on (d.usermodified = um.id) WHERE d.forum = '1' AND p.discussion = d.id AND p.parent = 0 AND p.userid = u.id ORDER BY d.timemodified DESC
BTW: As a database expert i strongly dissagree with using fieldnames like "name" and "id"
BTW2: In setup.php you turn off all error reporting prior to database connection routines. I think this is not good practice, problems like the above will not be noticed this way.
Kind Regards,
Pim Koeman, Database Expert
Wittenborg University, The Netherlands
You're right that swapping the order of tables would probably fix it, I will add your comment to the bug report, and look at it as time allows (we do have a temporary fix in CVS now)
> BTW: As a database expert i strongly dissagree with using fieldnames like "name"
> and "id"
I'm interesting in hearing your reasoning behind this statement.
p
> BTW: As a database expert i strongly dissagree with using fieldnames like "name"
> and "id"
I'm interesting in hearing your reasoning behind this statement.
p
>> BTW: As a database expert i strongly dissagree with using fieldnames like "name"
>> and "id"
>I'm interesting in hearing your reasoning behind this statement.
Hi Penny,
Thanks for your action, i will explain my point;
When designing a table i allways try to stick to a standard naming convention
for example:
- allways use a prefix or a schema (if supported)
- a table's name is always the plural of the entity
- the primary key fieldname always contains the name of the entity followed by _id
- never use reserved words for any of the databases you want to use
- try to avoid shorting names as much as possible (if you do document them !(like apples_no (for number of apples etc.))
- use '_' between all name parts (birth_date, payed_amount, student_number)
Using a naming convention is harder (more typing, being consistent)
but serves two purposes :
- Making your database more readable and maintainable for other people
- Helps you staying out of trouble in all dialects of SQL
(the bug reported is an example of this)
- Saves time in the end, since you can "guess" most of the key/fieldnames without looking them up
So when i would design a table to store student information for my "adm" system a table look like this
adm_students
| student_id |
| first_name |
| last_name |
| birth_date |
Without a naming convention it could look like this
studentlist
| ID |
| Name |
| lastName |
| BirthDate |
FAQ
? So why not call the key field just "ID" and the name field just "Name" ?
Because these fieldnames are reserved words in postgres SQL
examples : the adm_student.name means the "name" of the table in PostgreSQL not the name of the student
Using ID as a fieldname can also be very hard to identify in large SQL statements with a lot of JOINS
? Why use all lowercase separated by "_" ?
Allthough Mysql and Postgres support case in table- and fieldnames is not recommended, if you would ever migrate to Oracle or DB2 you'll be running into problems. (these DBMS's only support caseless names (mostly displayed as uppercase) to use the undersquore is just personal preference, dash is also fine, but less common.
http://phplens.com/lens/adodb/tips_portable_sql.htm
is a good read on how to make valid sql for all kinds of databases, the excellent Adodb library is used for moodle, so no problem using for moodle code.
I'm using Moode v1.5.3+. Forums were working fine, then today my IT department upgraded MySQL to v5.0.18 and my forums discussions disappeard as described above.
I put Moodle into Debug mode and got the following information:
There is a thread in the MySQL bug discussion list that discusses the syntax of this type of query:
http://bugs.mysql.com/bug.php?id=13832
Queries of the form:
Are expected to give Error 1054:
Because the LEFT JOIN clause has a higher order of precedence than the comma. The way to fix this syntax error is to add parentheses as follows:
To fix this in the source code you need to modify the file: /moodle/mod/forum/lib.php starting at line 1296 to add in the parentheses in the query for MySQL.
Hope this helps someone!
Morgan
I put Moodle into Debug mode and got the following information:
Unknown column 'd.usermodified' in 'on clause'
SELECT p.id,p.subject,p.modified,p.discussion,p.userid, d.name, d.timemodified, d.usermodified, d.groupid, u.firstname, u.lastname, u.email, u.picture , um.firstname AS umfirstname, um.lastname AS umlastname FROM forum_discussions d, forum_posts p, user u LEFT JOIN user um on (d.usermodified = um.id) WHERE d.forum = '3' AND p.discussion = d.id AND p.parent = 0 AND p.userid = u.id ORDER BY d.timemodified DESC
SELECT p.id,p.subject,p.modified,p.discussion,p.userid, d.name, d.timemodified, d.usermodified, d.groupid, u.firstname, u.lastname, u.email, u.picture , um.firstname AS umfirstname, um.lastname AS umlastname FROM forum_discussions d, forum_posts p, user u LEFT JOIN user um on (d.usermodified = um.id) WHERE d.forum = '3' AND p.discussion = d.id AND p.parent = 0 AND p.userid = u.id ORDER BY d.timemodified DESC
There is a thread in the MySQL bug discussion list that discusses the syntax of this type of query:
http://bugs.mysql.com/bug.php?id=13832
Queries of the form:
SELECT * FROM t1, t2 LEFT JOIN t3 on (t1.a = t3.c)
Are expected to give Error 1054:
Unknown column 't1.a' in 'on clause'
Because the LEFT JOIN clause has a higher order of precedence than the comma. The way to fix this syntax error is to add parentheses as follows:
SELECT * FROM (t1, t2) LEFT JOIN t3 on (t1.a = t3.c)
Hence the earlier query becomes:SELECT p.id,p.subject,p.modified,p.discussion,p.userid, d.name, d.timemodified, d.usermodified, d.groupid, u.firstname, u.lastname, u.email, u.picture , um.firstname AS umfirstname, um.lastname AS umlastname FROM (forum_discussions d, forum_posts p, user u) LEFT JOIN user um on (d.usermodified = um.id) WHERE d.forum = '3' AND p.discussion = d.id AND p.parent = 0 AND p.userid = u.id ORDER BY d.timemodified DESC
To fix this in the source code you need to modify the file: /moodle/mod/forum/lib.php starting at line 1296 to add in the parentheses in the query for MySQL.
Hope this helps someone!
Morgan
Hi Sue,
You have to modify the Moodle source code. Find and open the file:
On line 1294 you should see some code that looks like:
Cheers,
Morgan
You have to modify the Moodle source code. Find and open the file:
/moodle/mod/forum/lib.php
On line 1294 you should see some code that looks like:
return get_records_sql("SELECT $postdata, d.name, d.timemodified, d.usermodified, d.groupid,
u.firstname, u.lastname, u.email, u.picture $umfields
FROM {$CFG->prefix}forum_discussions d,
{$CFG->prefix}forum_posts p,
{$CFG->prefix}user u
$umtable
WHERE d.forum = '$forum'
AND p.discussion = d.id
AND p.parent = 0
AND p.userid = u.id $groupselect $userselect
ORDER BY $forumsort $limit");
return get_records_sql("SELECT $postdata, d.name, d.timemodified, d.usermodified, d.groupid,
u.firstname, u.lastname, u.email, u.picture $umfields
FROM ({$CFG->prefix}forum_discussions d,
{$CFG->prefix}forum_posts p,
{$CFG->prefix}user u )
$umtable
WHERE d.forum = '$forum'
AND p.discussion = d.id
AND p.parent = 0
AND p.userid = u.id $groupselect $userselect
ORDER BY $forumsort $limit");
Cheers,
Morgan
Thank you! Thank you!
It sure did help...
I have it up and running!
sue
It sure did help...
I have it up and running!
sue
Thank you. I wonder why the error occured, though. Moodle ran fine on our site, and then, suddenly ...
hi morgan,
i got the same probleme, but with your support i could handle it.
thanks a lot
andy
i got the same probleme, but with your support i could handle it.
thanks a lot
andy
Hi,
Thanks for the fix. I have comitted it to CVS.
Cheers,
Yu
Thanks for the fix. I have comitted it to CVS.
Cheers,
Yu
Penny? Can you check this out? I filed a bug for you Bertold: bug 3569
looking into it now
I can't reproduce the problem - can you give me some more info --
database type? version ? I've tested the new code on both postgres and mysql and both work fine.
Can you also tell me where you're seeing the problem? In a particular course forum? Or the site news forum? For social format?
Are there groups involved? Who are you logged in as when you can't see the posts?
database type? version ? I've tested the new code on both postgres and mysql and both work fine.
Can you also tell me where you're seeing the problem? In a particular course forum? Or the site news forum? For social format?
Are there groups involved? Who are you logged in as when you can't see the posts?
I updated from from a 1.5 alpha.
check www.domath.ca/phpinfo.php
www.domath.ca/moodle is my trouble site.
The problem occurs when logged in as admin. I assume its a problem for other permissions.
check www.domath.ca/phpinfo.php
www.domath.ca/moodle is my trouble site.
The problem occurs when logged in as admin. I assume its a problem for other permissions.
I need to know what database (mysql or postgres) and what version you are using.
Also, where you see the problem - site news? a course forum? teacher forum? normal forum? groups on/off? social format?
Also, where you see the problem - site news? a course forum? teacher forum? normal forum? groups on/off? social format?
mysql looks like 3.23.58 (is that really the version?)
site news isn't working and all posts are lost,
course forums are also not showing posts.
Thanks Penny
Older MySQL may not support the type of JOINS you added there Penny ... as of 1.5 we still need to support MySQL 3.23
I suspected it may have been an older mysql problem.
Ok, as far as I can see right now the best way is to revert my patch and put it in an
if ($CFG->dbtype == 'postgres7')
sound ok?
Ok, as far as I can see right now the best way is to revert my patch and put it in an
if ($CFG->dbtype == 'postgres7')
sound ok?
Yep! It's a pity because I'm sure your changes help MySQL 4 too, but we can't quite ditch MySQL 3 yet.
we get the following error when error reporting is turned on :
Warning: pg_exec(): Query failed: ERROR: column c.cost does not exist in /home/e-smith/files/ibays/extranet/html/lib/adodb/drivers/adodb-postgres64.inc.php on line 723
-1: ERROR: column c.cost does not exist
adodb_postgres7._execute(SELECT c.id,c.sortorder,c.visible,c.fullname,c.shortname,c.password,c.summary,c.teacher,c.cost FROM moodle_course c WHERE c.ca..., false) % line 837, file: adodb.inc.php
adodb_postgres7.execute(SELECT c.id,c.sortorder,c.visible,c.fullname,c.shortname,c.password,c.summary,c.teacher,c.cost FROM moodle_course c WHERE c.ca...) % line 790, file: datalib.php
get_records_sql(SELECT c.id,c.sortorder,c.visible,c.fullname,c.shortname,c.password,c.summary,c.teacher,c.cost FROM moodle_course c WHERE c.ca...) % line 2120, file: datalib.php
get_courses(14, c.sortorder ASC, c.id,c.sortorder,c.visible,c.fullname,c.shortname,c.password,c.summary,c.teacher,c.cost) % line 1326, file: lib.php
print_courses(Object:stdClass, 80%) % line 257, file: category.php
Indeed the cost column was missing, i created an empty colomn by hand and the problem was solved. Is this bug in the SQL upgrade script
Missing c.cost column:
Undoubtedly. please file a bug report (moodle.org/bugs) and assign it to me (mjollnir)
Or if you can't assign bugs (can't remember whether the bug tracker will let you if you're not in the list of available assignees) -- add me into the CC list.
Undoubtedly. please file a bug report (moodle.org/bugs) and assign it to me (mjollnir)
Or if you can't assign bugs (can't remember whether the bug tracker will let you if you're not in the list of available assignees) -- add me into the CC list.
I think there is a simpler way 
, just added some more info to bug 3569.
, just added some more info to bug 3569.
Can someone test this solution on an old MySQL? I don't have access to any old installations anymore. If it works it would be great!
I'll give it a whirl, but I doubt I can use cvs, just the download page. I tried the previous build (now 18 hours old) and it didn't cure the problem. I'm not so sure the fix was in however.
Is there a way I can check the code? Is it in the forum mod?
Is there a way I can check the code? Is it in the forum mod?
I think Martin was referring to testing the query in mysql 3.23, using the solution that Jon posted in the bug tracker.
There is a temporary fix in CVS which should solve your problems, it will be available in the next build on the download page.
There is a temporary fix in CVS which should solve your problems, it will be available in the next build on the download page.
Hi,
I downloaded the new (2005-06-21:04:30am GMT+1) 1.5+.
The workaround from Penny is working!
Thanks a lot for supporting our old mysql!!
Bertold
I downloaded the new (2005-06-21:04:30am GMT+1) 1.5+.
The workaround from Penny is working!
Thanks a lot for supporting our old mysql!!
Bertold
good to hear!
If it helps forum posts are present and intact in the database.
Me and my poor old mysql.
Me and my poor old mysql.
Hi,
problem still exists in update 1.5+ (2005-06-20,04:30am GMT+1).
In all forums I don't see any existing entry.
Database: mysql Ver 11.18 Distrib 3.23.55
problem still exists in update 1.5+ (2005-06-20,04:30am GMT+1).
In all forums I don't see any existing entry.
Database: mysql Ver 11.18 Distrib 3.23.55
If you're tracking anonymous cvs, there is a few hour delay. It is fixed in CVS now, but try updating tomorrow.
Sorry for the panic.
p
Sorry for the panic.
p
Hi,
thanks al lot!
Btw: I don't had have panic, because for me Moodle works all the time great, but we have in germany several identical servers called the "Linux Musterlösung" which provide file/print/moodle services in schools and they (could) all have the same problem...
I think we have to move to mysql 4 as soon as possible.
thanks al lot!
Btw: I don't had have panic, because for me Moodle works all the time great, but we have in germany several identical servers called the "Linux Musterlösung" which provide file/print/moodle services in schools and they (could) all have the same problem...
I think we have to move to mysql 4 as soon as possible.
for our school we are running on MySQL 4.1 which seems to be working fine...