With the admin account everything looks good for organic forum 5.2 (latest development tarball from 8/31/07).

However

I was having this problem ever since upgrade to Drupal 5.

* user warning: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN o query: SELECT DISTINCT(t.tid), t.*, parent FROM term_data t INNER JOIN term_hierarchy h ON t.tid = h.tid WHERE (t.tid NOT IN (SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid != 88) x WHERE x.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 88)) AND t.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 88 WHERE ogu.is_active = 0)) AND ( t.vid = 2 ) ORDER BY weight, name in /home4/mysite/public_html/includes/database.mysql.inc on line 172.
* user warning: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN o query: SELECT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v ON t.vid = v.vid WHERE (t.tid NOT IN (SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid != 88) x WHERE x.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 88)) AND t.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 88 WHERE ogu.is_active = 0)) AND ( r.nid = 15 ) ORDER BY v.weight, t.weight, t.name in /home4/mysite/public_html/includes/database.mysql.inc on line 172.

I'm not sure what is happening here. Is it because there are no posts in the forums yet? Right now they are empty, so I thought this might have something to do with it.... please help. Non admin users with forum rights can't even see the forum categories at this point! I am using MySQL 4.0.18 and PHP 4.3.4.

Comments

bomarmonk’s picture

bomarmonk commented

Nope, creating a forum topic doesn't make it any better for my OG forum user. The last warnings I posted were generated when such a user tries to post a new topic. The following errors result from the user visiting the OG forum page for his particular group:

* user warning: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN o query: SELECT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v ON t.vid = v.vid WHERE (t.tid NOT IN (SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid != 88) x WHERE x.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 88)) AND t.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 88 WHERE ogu.is_active = 0)) AND ( r.nid = 124 ) ORDER BY v.weight, t.weight, t.name in /home4/mysite/public_html/includes/database.mysql.inc on line 172.
* user warning: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN o query: SELECT DISTINCT(t.tid), t.*, parent FROM term_data t INNER JOIN term_hierarchy h ON t.tid = h.tid WHERE (t.tid NOT IN (SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid != 88) x WHERE x.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 88)) AND t.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 88 WHERE ogu.is_active = 0)) AND ( t.vid = 2 ) ORDER BY weight, name in /home4/mysite/public_html/includes/database.mysql.inc on line 172.
* user warning: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN o query: SELECT DISTINCT(t.tid), t.* FROM term_data t INNER JOIN term_hierarchy h ON h.parent = t.tid WHERE (t.tid NOT IN (SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid != 88) x WHERE x.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 88)) AND t.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 88 WHERE ogu.is_active = 0)) AND ( h.tid = 74 ) ORDER BY weight, name in /home4/mysite/public_html/includes/database.mysql.inc on line 172.
* user warning: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN o query: SELECT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v ON t.vid = v.vid WHERE (t.tid NOT IN (SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid != 88) x WHERE x.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 88)) AND t.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 88 WHERE ogu.is_active = 0)) AND ( r.nid = 21 ) ORDER BY v.weight, t.weight, t.name in /home4/mysite/public_html/includes/database.mysql.inc on line 172.

rconstantine’s picture

rconstantine commented

I'm pretty sure it's a PHP or MySQL incompatibility. Open the module file, go down to og_forum_rewrite_sql (I think that's what it's called) and read the comment for the function which describes the sql functions used. Then you can open your sql console, or phpmyadmin or something and play with the pieces of the query separately, then together to see if it works or not. You'll have to remove curly braces from the table names since that is a Drupal convention, and you'll have to replace and placeholders (ex. %d or '%s') with values.

If you get the whole giant sql query to fire, then MySQL 4.0.18 works with this module and PHP is probably the problem.

In other words, this has not been tested with either of the versions you are using and simply might not work. If you find out for sure, let us know. Otherwise, I'm not sure of what to fix to help you out since the problem queries work in PHP 5 and MySQL 5. That way, I can either try to fix this, or state in the README that versions 5 are required.

dufus’s picture

dufus commented

I am having the exact same issues with MySql 4.0 and PHP 5.

All users except the root admin user get the following error:

user warning: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN o query: SELECT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v ON t.vid = v.vid WHERE (t.tid NOT IN (SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid != 2) x WHERE x.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 2)) AND t.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 2 WHERE ogu.is_active = 0)) AND ( r.nid = 3 ) ORDER BY v.weight, t.weight, t.name in /homepages/45/d94980982/htdocs/allmyclans/includes/database.mysql.inc on line 172.

I have placed Bold around an interesting item in the sql query, I have never seen query: used in an sql statement before.

Hope this helps,
Thanks

bomarmonk’s picture

bomarmonk commented

For now I moved my site to a new host ($10 and 1 hour later and it's done). I wish I could help test this problem further, as it would be great to keep OG forum working with the older versions of PHP and MySQL... it sounds like since you have PHP 5, it must be the MySQL that's the problem. For my needs, I have things working in PHP 5 and MySQL 5, but that's a pretty selfish reason to move on from this issue. I hope it can be fixed.

rconstantine’s picture

rconstantine commented

It looks like MySQL 4 might not support nested queries which is what my hunch was telling me. But that's why there is a query statement inside the larger query in the first place and what makes it possible to mesh site-wide and og_forums. Without nested queries, that just doesn't work.

If anyone knows how to work around this, I'd love to hear about it. I'm afraid I'm slammed (!!!) with work for the next two or three weeks and can't get to it at least until then.

If there's no work around for nested queries for MySQL 4, then MySQL 5 may be required. Actually, it may be that version 4.3 or something like that works. In any case, if you are able to upgrade, you should.

dufus’s picture

dufus commented

I just upgraded the database from mysql 4.0 to 5.0 and everything seems to work now. I think you are right that the problem is the nested queries in mysql 4.0. I think I know a workaround but the performance hit and trying to keep data integrity with multi-users would bring the system to its knees.

Thanks for the info,
Dufus

rconstantine’s picture

rconstantine commented

Thanks a bunch for the update. I'm glad you figured it out. Drupal is going PHP 5-only this next February, so I don't feel too bad about being a little early. I'll post a notice to the project page.

rconstantine’s picture

rconstantine commented

What am I thinking? PHP wasn't the problem. I must have stayed up to late. Can't think straight.

Anyway, I meant MySQL 5 (and no, Drupal is not converting to MySQL 5-only).

rconstantine’s picture

rconstantine commented
Status: Active » Closed (works as designed)
rconstantine’s picture

rconstantine commented
Title: Warning and no forum categories with limited user role » MySQL 5 required sue to nested queries