OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



DB Error: The SELECT would examine (...)

Are you responsible for making OJS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, btbell, michael, bdgregg, barbarah, asmecher

Forum rules
What to do if you have a technical problem with OJS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OJS Editorial Support and Discussion subforum; if you have a development question, try the OJS Development subforum.

DB Error: The SELECT would examine (...)

Postby yvon » Wed Feb 27, 2008 2:55 pm

Hello

We are trying to use OJS 2.1.1; so far things had been running quite smoothly. Now we have a few dozen "active" papers in the the reviewing process and we face the following error. From the "Editor" main page, any attempt to display the list of currently (assigned/unassigned...) submissions causes the following error message:

DB Error: 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

Has this error been documented ? Which script should we try to modify to solve the problem ?

Thanks for your help,

Francois
yvon
 
Posts: 3
Joined: Wed Feb 27, 2008 2:47 pm

Re: DB Error: The SELECT would examine (...)

Postby asmecher » Thu Feb 28, 2008 11:44 am

Hi Francois,

I haven't run into this before -- I suspect your server limits are set unusually low. However, a good place to start narrowing this down would be to find out which query is causing the problem. Could you temporarily turn on "debug" in your config.inc.php file and reload the page? The last query shown will be the one that causes the problem. Post it here and we'll step through solving the problem.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 7709
Joined: Wed Aug 10, 2005 12:56 pm

Re: DB Error: The SELECT would examine (...)

Postby yvon » Thu Feb 28, 2008 2:42 pm

Hi

Thanks for your reply - I did as you suggested ... the debug is indeed quite verbose ;-). Here is the offending query:

Best
Francois
------
(mysql): SELECT DISTINCT a.*, s.title AS section_title, s.title_alt1 AS section_title_alt1, s.title_alt2 AS section_title_alt2, s.abbrev AS section_abbrev, s.abbrev_alt1 AS section_abbrev_alt1, s.abbrev_alt2 AS section_abbrev_alt2 FROM articles a INNER JOIN article_authors aa ON (aa.article_id = a.article_id) LEFT JOIN sections s ON (s.section_id = a.section_id) LEFT JOIN edit_assignments e ON (e.article_id = a.article_id) LEFT JOIN users ed ON (e.editor_id = ed.user_id) LEFT JOIN copyed_assignments c ON (a.article_id = c.article_id) LEFT JOIN users ce ON (c.copyeditor_id = ce.user_id) LEFT JOIN proof_assignments p ON (p.article_id = a.article_id) LEFT JOIN users pe ON (pe.user_id = p.proofreader_id) LEFT JOIN layouted_assignments l ON (l.article_id = a.article_id) LEFT JOIN users le ON (le.user_id = l.editor_id) LEFT JOIN review_assignments r ON (r.article_id = a.article_id) LEFT JOIN users re ON (re.user_id = r.reviewer_id AND cancelled = 0) WHERE a.journal_id = '1' AND a.status = 1 ORDER BY article_id ASC 1104:

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

adodb_mysql._execute(SELECT DISTINCT
a.*,
s.title AS section_title,
s.title_alt1 AS section_title_alt1,
s.title_alt2 AS section_title...) % line 839, file: adodb.inc.php
adodb_mysql.execute(SELECT DISTINCT
a.*,
s.title AS section_title,
s.title_alt1 AS section_title_alt1,
s.title_alt2 AS section_title..., Array[1]) % line 62, file: DAO.inc.php
editorsubmissiondao.retrieve(SELECT DISTINCT
a.*,
s.title AS section_title,
s.title_alt1 AS section_title_alt1,
s.title_alt2 AS section_title..., 1, false) % line 150, file: DAO.inc.php
editorsubmissiondao.retrieverange(SELECT DISTINCT
a.*,
s.title AS section_title,
s.title_alt1 AS section_title_alt1,
s.title_alt2 AS section_title..., 1, null) % line 273, file: EditorSubmissionDAO.inc.php
editorsubmissiondao.getunfilterededitorsubmissions(1, null, null, null, null, null, null, null, true) % line 324, file: EditorSubmissionDAO.inc.php
yvon
 
Posts: 3
Joined: Wed Feb 27, 2008 2:47 pm

Re: DB Error: The SELECT would examine (...)

Postby asmecher » Thu Feb 28, 2008 9:06 pm

Hi yvon,

The MAX_JOIN_SIZE option isn't set by default, so your administrator must have set a limit. Can you check what the current limit is? It should be in your my.cnf MySQL configuration file.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 7709
Joined: Wed Aug 10, 2005 12:56 pm

Re: DB Error: The SELECT would examine (...)

Postby yvon » Fri Feb 29, 2008 6:51 am

Alec

Thanks again for your answer. We have tried to change the SQL server option (as suggested by the error message) in DBConnection.inc.php:
if ($this->connected) {
$this->dbconn->Execute("SET OPTION SQL_BIG_SELECTS=1");
}
and it turns out that the problem actually disappears. Your diagnostic was right: the default limit on our server was way too small.
So I guess the issue is resolved.

Thanks for your help

Francois
yvon
 
Posts: 3
Joined: Wed Feb 27, 2008 2:47 pm

Re: DB Error: The SELECT would examine (...)

Postby stephanehess » Thu Sep 10, 2009 9:41 am

I've run into the same problem. What is not clear from Francois's message is where in DBConnection.inc.php this statement should be added.

The debug output I obtain is:

(mysql): SELECT * FROM sessions WHERE session_id = 'e43b589512f1731bb209483c9da205df' (mysql): SELECT * FROM users WHERE user_id = '2' (mysql): SELECT * FROM user_settings WHERE user_id = '2' (mysql): UPDATE sessions SET user_id = '2', ip_address = '82.47.148.224', user_agent = 'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.13) Gecko/2009073022 Firefox/3.0.13', created = '1252566963', last_used = 1252600802, remember = 0, data = 'username|s:12:\"stephanehess\";userId|s:1:\"2\";' WHERE session_id = 'e43b589512f1731bb209483c9da205df' (mysql): SELECT * FROM journals WHERE path = 'JOCM' (mysql): SELECT * FROM site (mysql): SELECT * FROM site_settings (mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'CmsPlugin' AND journal_id = '1' (mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'CounterPlugin' AND journal_id = '0' (mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'OpenAdsPlugin' AND journal_id = '1' (mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'RoundedCornersPlugin' AND journal_id = '1' (mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'SehlPlugin' AND journal_id = '1' (mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'ThesisPlugin' AND journal_id = '1' (mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'TranslatorPlugin' AND journal_id = '0' (mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'WebFeedPlugin' AND journal_id = '1' (mysql): SELECT setting_name, setting_value, setting_type FROM plugin_settings WHERE plugin_name = 'XMLGalleyPlugin' AND journal_id = '1' (mysql): SELECT COUNT(*) FROM roles WHERE journal_id = 1 AND user_id = 2 AND role_id = 256 (mysql): SELECT * FROM sections WHERE journal_id = '1' ORDER BY seq (mysql): SELECT * FROM section_settings WHERE section_id = '2' (mysql): SELECT * FROM section_settings WHERE section_id = '1' (mysql): SELECT * FROM section_settings WHERE section_id = '3' (mysql): SELECT DISTINCT a.*, COALESCE(stl.setting_value, stpl.setting_value) AS section_title, COALESCE(sal.setting_value, sapl.setting_value) AS section_abbrev FROM articles a INNER JOIN article_authors aa ON (aa.article_id = a.article_id) LEFT JOIN sections s ON (s.section_id = a.section_id) LEFT JOIN edit_assignments e ON (e.article_id = a.article_id) LEFT JOIN users ed ON (e.editor_id = ed.user_id) LEFT JOIN copyed_assignments c ON (a.article_id = c.article_id) LEFT JOIN users ce ON (c.copyeditor_id = ce.user_id) LEFT JOIN proof_assignments p ON (p.article_id = a.article_id) LEFT JOIN users pe ON (pe.user_id = p.proofreader_id) LEFT JOIN layouted_assignments l ON (l.article_id = a.article_id) LEFT JOIN users le ON (le.user_id = l.editor_id) LEFT JOIN review_assignments r ON (r.article_id = a.article_id) LEFT JOIN users re ON (re.user_id = r.reviewer_id AND cancelled = 0) LEFT JOIN section_settings stpl ON (s.section_id = stpl.section_id AND stpl.setting_name = 'title' AND stpl.locale = 'en_US') LEFT JOIN section_settings stl ON (s.section_id = stl.section_id AND stl.setting_name = 'title' AND stl.locale = 'en_US') LEFT JOIN section_settings sapl ON (s.section_id = sapl.section_id AND sapl.setting_name = 'abbrev' AND sapl.locale = 'en_US') LEFT JOIN section_settings sal ON (s.section_id = sal.section_id AND sal.setting_name = 'abbrev' AND sal.locale = 'en_US') LEFT JOIN article_settings atpl ON (a.article_id = atpl.article_id AND atpl.setting_name = 'title' AND atpl.locale = 'en_US') LEFT JOIN article_settings atl ON (a.article_id = atl.article_id AND atl.setting_name = 'title' AND atl.locale = 'en_US') WHERE a.journal_id = '1' AND a.submission_progress = 0 AND a.status = 1 ORDER BY article_id ASC 1104: 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

adodb_mysql._execute(SELECT DISTINCT
a.*,
COALESCE(stl.setting_value, stpl.setting_value) AS section_title,
COALESCE(sal.setting_value, s...) % line 845, file: adodb.inc.php
adodb_mysql.execute(SELECT DISTINCT
a.*,
COALESCE(stl.setting_value, stpl.setting_value) AS section_title,
COALESCE(sal.setting_value, s..., Array[13]) % line 62, file: DAO.inc.php
editorsubmissiondao.retrieve(SELECT DISTINCT
a.*,
COALESCE(stl.setting_value, stpl.setting_value) AS section_title,
COALESCE(sal.setting_value, s..., Array[13], false) % line 150, file: DAO.inc.php
editorsubmissiondao.retrieverange(SELECT DISTINCT
a.*,
COALESCE(stl.setting_value, stpl.setting_value) AS section_title,
COALESCE(sal.setting_value, s..., Array[13], null) % line 335, file: EditorSubmissionDAO.inc.php
editorsubmissiondao.getunfilterededitorsubmissions(1) % line 604, file: EditorSubmissionDAO.inc.php

DB Error: 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
(mysql): UPDATE sessions SET user_id = '2', ip_address = '82.47.148.224', user_agent = 'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.13) Gecko/2009073022 Firefox/3.0.13', created = '1252566963', last_used = 1252600802, remember = 0, data = 'username|s:12:\"stephanehess\";userId|s:1:\"2\";' WHERE session_id = 'e43b589512f1731bb209483c9da205df'
stephanehess
 
Posts: 68
Joined: Thu Feb 28, 2008 12:18 pm

Re: DB Error: The SELECT would examine (...)

Postby stephanehess » Fri Sep 11, 2009 8:37 am

As a further bit of information, the max_join_size on the server is set to 900,000.

The only help the webhost is giving me is:

MySQL will refuse to execute select statements that process a very large number of rows. You should either adjust your select statement to make it more efficient, or set SQL_BIG_SELECTS=1 in the session before executing the large select statement. Use something like this to do that ..

$db->sql_query("SET OPTION SQL_BIG_SELECTS=1");


but it is not clear to me where to do this.

Thanks in advance for any help

Stephane
stephanehess
 
Posts: 68
Joined: Thu Feb 28, 2008 12:18 pm

Re: DB Error: The SELECT would examine (...)

Postby asmecher » Fri Sep 11, 2009 12:11 pm

Hi Stephane,

Have you tried contacting yvon for details on the modification?

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 7709
Joined: Wed Aug 10, 2005 12:56 pm

Re: DB Error: The SELECT would examine (...)

Postby stephanehess » Sun Sep 13, 2009 12:06 pm

Alec,

I thought of doing that, but the last time Yvon was online was February 2008, so I didn't think there was much point. Or do personal messages get forwarded via e-mail?

Thanks

Stephane
stephanehess
 
Posts: 68
Joined: Thu Feb 28, 2008 12:18 pm

Re: DB Error: The SELECT would examine (...)

Postby asmecher » Sun Sep 13, 2009 1:23 pm

Hi Stephane,

Yes, a personal message will generally result in an email (though users can choose to opt out in their control panel).

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 7709
Joined: Wed Aug 10, 2005 12:56 pm

Re: DB Error: The SELECT would examine (...)

Postby stephanehess » Mon Sep 14, 2009 6:56 am

will try - thanks
stephanehess
 
Posts: 68
Joined: Thu Feb 28, 2008 12:18 pm

Re: DB Error: The SELECT would examine (...)

Postby stephanehess » Tue Sep 15, 2009 5:51 am

Alec,

not much luck there. Got the following: "For various reasons, we have given up using OJS. So I don't have much else to say regarding this problem. Sorry to be of little help"

Any other idea how to get this fixed?

Thanks

Stephane
stephanehess
 
Posts: 68
Joined: Thu Feb 28, 2008 12:18 pm

Re: DB Error: The SELECT would examine (...)

Postby asmecher » Tue Sep 15, 2009 9:19 am

Hi Stephane,

I'd suggest adding the new query to the classes/db/DBConnection.inc.php file, in the "connect" function, just before the "return" statement. Something like:
Code: Select all
$this->execute('SET OPTION SQL_BIG_SELECTS=1');
This code is untested, but I expect it'll work.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 7709
Joined: Wed Aug 10, 2005 12:56 pm

Re: DB Error: The SELECT would examine (...)

Postby stephanehess » Wed Sep 16, 2009 1:12 am

Alec,

thanks. This worked, but with

Code: Select all
$this->dbconn->Execute("SET OPTION SQL_BIG_SELECTS=1");


instead of

Code: Select all
$this->Execute("SET OPTION SQL_BIG_SELECTS=1");


Best wishes

Stephane
stephanehess
 
Posts: 68
Joined: Thu Feb 28, 2008 12:18 pm

Re: DB Error: The SELECT would examine (...)

Postby asmecher » Wed Sep 16, 2009 8:19 am

Hi Stephane,

Great -- glad to hear it's working.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 7709
Joined: Wed Aug 10, 2005 12:56 pm

Next

Return to OJS Technical Support

Who is online

Users browsing this forum: No registered users and 2 guests