By holz on

I have a little site, with 588 nodes, 247 comments, 34 users, and 38 terms. I used DB Maintenance、bbcode、buedtor、code filter、poormanscron、quote、spam、Taxonomy Access Control modules.

one day I found that I can't see the teaser list( like www.mysite.com/node) or node page(like www.mysite.com/node/588), then I saw the php error in logs like:

The SELECT would examine more than MAX_JOIN_SIZE rows; 
check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay query: 
SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = 18 in /home/xxx/htdocs/includes/database.mysql.inc on line 172.

I then uninstall all the contrib modules, and the error still. I trunck all the cache, logs, access, history, watchdogs, the error again. So I wrote a letter to support, they then told me:

The mysql query was trying to select more than 1000000 rows from mysql (based on the join) , We have increased the join limit to 2m, however would appreciate it if you could purge all old log entries , etc from drupal .

I know little about php and mysql, and I really don't know if a mysql query will select more than 1M rows, I search the forum and found no answer.

⋅ Categories: Drupal 5.x

Comments

robertdouglass’s picture

robertdouglass commented

Please run this query:

SELECT count(*) FROM node_revisions WHERE nid = 18;

How many revisions does this node have? If it is anything less than a million, then I can't see how that query, which is run hundreds of millions of times a day on sites that have up to hundreds of millions of nodes, could possibly be returning that many rows. Nobody else has this experience as far as I know.

- Robert Douglass

-----
Lullabot | my Drupal book

holz’s picture

holz commented

I have only 1 revision, of all nodes.

anyway I run 

SELECT count(*) FROM node_revisions WHERE nid = 18;

it return the correct number, that's 1.

robertdouglass’s picture

robertdouglass commented

Ask your support team to justify their claim about the million rows based on your database schema. Challenge them to write a single query that can select that many rows. Their explanation is not credible.

- Robert Douglass

-----
Lullabot | my Drupal book

duvyb’s picture

duvyb commented

I have the same error 

user warning: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay query: SELECT count( DISTINCT(node.nid)) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid AND term_data.vid IN ('4') LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_hierarchy term_hierarchy ON term_node2.tid = term_hierarchy.tid LEFT JOIN term_hierarchy term_hierarchy2 ON term_hierarchy.parent = term_hierarchy2.tid LEFT JOIN term_hierarchy term_hierarchy3 ON term_hierarchy2.parent = term_hierarchy3.tid INNER JOIN users users ON node.uid = users.uid INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public') OR (na.gid = 2 AND na.realm = 'forum_access'))) AND ( (node.status = '1') AND (term_node.tid IS in /home/XXX/public_html/includes/database.mysql.inc on line 172.

BUT only when I am logged as user. When logged on as admin, no errors and query is running perfectly.
I am using ACL and roles are correct.

Thank you for your help
Yvon (duvyb)

kenorb’s picture

kenorb commented

Try solution from here:
http://drupal.org/node/361953#comment-1212329
paste this code from #1 before the line, which makes the error.
Basically you need to increase MAX_JOIN_SIZE as mysql advice you.
On most of the hostings you can make it via SQL_BIG_SELECTS variable.

kenorb’s picture

kenorb commented

Try this module:
http://drupal.org/node/361967#comment-1255111