Jump to:
| Project: | Site User List |
| Version: | 5.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | pukku |
| Status: | closed (won't fix) |
Issue Summary
I don't have control over the MySQL (5) settings on the server. There are some 10 left outer joins in the query used for creating our SUL. When page loads, I get the following drupal_message instead of the SUL.
Using subselect
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 DISTINCT `@uid`, `@profile_name`, `@profile_role`, `@profile_dayphone`, `@profile_cell`, `@profile_homephone`, `@profile_address`, `@profile_csz`, `@profile_meritbadges`, `@profile_birthdate`, `@name`, `@picture`, `@mail` FROM (SELECT DISTINCT u.uid as `@uid`, t_3.value as `@profile_name`, t_1.value as `@profile_role`, t_2.value as `@profile_dayphone`, t_4.value as `@profile_cell`, t_5.value as `@profile_homephone`, t_7.value as `@profile_address`, t_8.value as `@profile_csz`, t_6.value as `@profile_meritbadges`, t_9.value as `@profile_birthdate`, u.name as `@name`, u.mail as `@mail`, u.picture as `@picture` FROM users as u LEFT OUTER JOIN profile_values as t_3 on (u.uid = t_3.uid and t_3.fid = 3) LEFT OUTER JOIN profile_values as t_1 on (u.uid = t_1.uid and t_1.fid = 1) LEFT OUTER JOIN profile_values as t_2 on (u.uid = t_2.uid and t_2.fid = 2) LEFT OUTER JOIN profile_values as t_4 on (u.uid = t_4.uid and t_4.fid = 4) LEFT OUTER JOIN profile_values as t_5 on (u.uid = t_5.uid and t_5.fid = 5) LEFT OUTER JOIN profile_values as t_7 on (u.uid = t_7.uid and t_7.fid = 7) LEFT OUTER JOIN profile_values as t_8 on (u.uid = t_8.uid and t_8.fid = 8) LEFT OUTER JOIN profile_values as t_6 on (u.uid = t_6.uid and t_6.fid = 6) LEFT OUTER JOIN profile_values as t_9 on (u.uid = t_9.uid and t_9.fid = 9) WHERE (u.uid != 0) AND (u.status = 1)) as cd ORDER BY `@profile_name` ASC in /home/troopsev/public_html/includes/database.mysql.inc on line 172.
using database view
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 DISTINCT `@uid`, `@profile_name`, `@profile_role`, `@profile_dayphone`, `@profile_cell`, `@profile_homephone`, `@profile_address`, `@profile_csz`, `@profile_meritbadges`, `@profile_birthdate`, `@name`, `@picture`, `@mail` FROM site_user_list_view as cd ORDER BY `@profile_name` ASC in /home/troopsev/public_html/includes/database.mysql.inc on line 172.
using table
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: CREATE TABLE site_user_list_table AS SELECT DISTINCT u.uid as `@uid`, t_3.value as `@profile_name`, t_1.value as `@profile_role`, t_2.value as `@profile_dayphone`, t_4.value as `@profile_cell`, t_5.value as `@profile_homephone`, t_7.value as `@profile_address`, t_8.value as `@profile_csz`, t_6.value as `@profile_meritbadges`, t_9.value as `@profile_birthdate`, u.name as `@name`, u.mail as `@mail`, u.picture as `@picture` FROM users as u LEFT OUTER JOIN profile_values as t_3 on (u.uid = t_3.uid and t_3.fid = 3) LEFT OUTER JOIN profile_values as t_1 on (u.uid = t_1.uid and t_1.fid = 1) LEFT OUTER JOIN profile_values as t_2 on (u.uid = t_2.uid and t_2.fid = 2) LEFT OUTER JOIN profile_values as t_4 on (u.uid = t_4.uid and t_4.fid = 4) LEFT OUTER JOIN profile_values as t_5 on (u.uid = t_5.uid and t_5.fid = 5) LEFT OUTER JOIN profile_values as t_7 on (u.uid = t_7.uid and t_7.fid = 7) LEFT OUTER JOIN profile_values as t_8 on (u.uid = t_8.uid and t_8.fid = 8) LEFT OUTER JOIN profile_values as t_6 on (u.uid = t_6.uid and t_6.fid = 6) LEFT OUTER JOIN profile_values as t_9 on (u.uid = t_9.uid and t_9.fid = 9) WHERE (u.uid != 0) AND (u.status = 1) in /home/troopsev/public_html/includes/database.mysql.inc on line 172.
I recommend either breaking up this quiery into smaller pieces, or to first attempt to throw the SET SQL_BIG_SELECTS=1 (which is off on my server). Meantime, this feature of my site is down. I'm thinking about OG.
Comments
#1
Hi! I'm sorry that this module isn't usable on your setup. If you have a patch which would check for the database type and issue the suggested MySQL command, I'd be happy to look it over and possibly include it. However, the primary purpose of this module is to do the work in the database, rather than having to load each user to get the data in real time. As such, it needs everything to be done in one query.
I hope you can find something that will work for your needs,
best luck,
Ricky
#2
I just ran into this problem myself, spoke with a really helpful tech support guy at bluehost (after speaking to an equally unhelpful one - just hang up and try again until you get what you want), and we resolved it.
in site_user_list.module, look for this:
|----------------------------------------------------------------------
// finally, build up the query
$sql = 'SELECT DISTINCT ' . implode(", ", $select) . ' '
. 'FROM ' . $select_from . ' as cd '
. implode(' ', $joins) . ' '
. (count($where) ? 'WHERE ' . implode(' AND ', $where) : '')
;
----------------------------------------------------------------------|
... and after, add:
|----------------------------------------------------------------------
$sqlFix = "SET SESSION SQL_BIG_SELECTS=1";
$resFix = db_query($sqlFix);
----------------------------------------------------------------------|
Best,
Andrey.
#3
Try this: #361967: Increase MAX_JOIN_SIZE and MAX_ALLOWED_PACKET settings in system.install
or Drupal Tweaks module and change it on DB tab.
#4
see post #21 here: http://drupal.org/node/361967