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

[yvon]

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

[asmecher]

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

[yvon]

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

[asmecher]

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

[yvon]

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

[stephanehess]

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]

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

[asmecher]

Hi Stephane,

Have you tried contacting yvon for details on the modification?

Regards,
Alec Smecher
Public Knowledge Project Team

[stephanehess]

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

[asmecher]

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

[stephanehess]

will try - thanks

[stephanehess]

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

[asmecher]

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

[stephanehess]

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

[asmecher]

Hi Stephane,

Great -- glad to hear it's working.

Regards,
Alec Smecher
Public Knowledge Project Team