SQL error with MySQL 4.0.x

In thislines of recent_changes.module:
if ($node_type) {
$count_sql = "SELECT (SELECT COUNT(*) FROM {node_revisions} r JOIN {node} n ON r.nid = n.nid $node_type_query) + (SELECT COUNT(*) FROM {comments} c LEFT JOIN {node} n ON c.nid = n.nid $node_type_query)";
}
else {
/*$count_sql = "SELECT (SELECT COUNT(*) FROM {node_revisions}) + (SELECT COUNT(*) FROM {comments})"*/;
$count_sql = "SELECT COUNT(*) FROM {node_revisions}";
}
I just drop the counting of comments then the error was gone :-)

I redirected all MySQL issues here.

The problem is with MySQL 4.0.x which does not allow subqueries. I use a subquery to calculate the number of revisions and comments which is used to display a the pager at the bottom to browse older changes.

One solution is the change by jever which removes this query. The problem with this is that the pager will display incorrect page numbers. So you wont be able to go all the way back to the first change of your site. If this is not a problem you can go ahead and change the source as indicated by jever.

I don't see a good way at the moment to solve this issue since I have to do the query for the pager in one step, thus the subqueries. There may be a workaround by executing the queries separate and then store the result in the database, but this would mean 4 queries instead of one.

So if the wrong pager is not a problem the simplest solution is to apply jever's change, or upgrade your MySQL if you can ;)

In the new dev version I have added a check for the MySQL version, and if the version is 4.0.x the count query ignores the comments. This will give an incorrect pager but at least no SQL errors. I would be glad if you could test it (I don't have a MySQL 4.0.x installation where I could test it).

The version for Drupal 4.7 is not updated yet, I wait until it works in Drupal 5.

Hi @all,

I also have the problem with the mysql error for the comments and I just thought if it would be a good way to store the two subqueries with db_query_temporary in temporary database tables and then add the two values in a third query? I know this is a lot more queries than needed, but this could solve the problem until somebody finds another solution :-)

Cheers

hctom

no activity since 2 years.. so I close it :-)