When I click the link Journal Menegementes>Stats & Reports page, the following error shown:
DB Error: The SELECT would examine more than MAX_JOIN_SIZE rows;
check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if
the SELECT is okay
In the error log, you should find more information on what line of code caused this error. For best results, briefly turn on show_stacktrace in config.inc.php and then navigate to the page again. The stacktrace would be very helpful to identify what select is causing the issue.
Current ojs version: 2.4.7.1 (October 24, 2015 - 10:52 AM)
And as you said I changed on the error code, here is the result, that I
can’t understand any thing:
So would you please recommend easy thing, since I am not expert.
u please DB Error: The SELECT would examine more than MAX_JOIN_SIZE rows;
check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if
the SELECT is okayStack Trace: File:
/home3/insanbil/public_html/jhumansciences/ojs/classes/journal/JournalStatisticsDAO.inc.php
line 46 Function: DAO->retrieve(“SELECT LEAST(a.date_submitted,
COALESCE(pa.date_published, NOW()…”, Array(3))
File:
/home3/insanbil/public_html/jhumansciences/ojs/pages/about/AboutHandler.inc.php
line 562 Function: JournalStatisticsDAO->getFirstActivityDate(“2”)
File: (unknown) line (unknown) Function: AboutHandler->statistics(Array(0), Object(Request))
File:
/home3/insanbil/public_html/jhumansciences/ojs/lib/pkp/classes/core/PKPRouter.inc.php
line 362 Function: call_user_func(Array(2), Array(0), Object(Request))
File:
/home3/insanbil/public_html/jhumansciences/ojs/lib/pkp/classes/core/PKPPageRouter.inc.php
line 220 Function: PKPRouter->_authorizeInitializeAndCallRequest(Array(2),
Object(Request), Array(0), False)
File:
/home3/insanbil/public_html/jhumansciences/ojs/lib/pkp/classes/core/Dispatcher.inc.php
line 134 Function: PKPPageRouter->route(Object(Request))
File:
/home3/insanbil/public_html/jhumansciences/ojs/lib/pkp/classes/core/PKPApplication.inc.php
line 178 Function: Dispatcher->dispatch(Object(Request))
File: /home3/insanbil/public_html/jhumansciences/ojs/index.php line 64 Function: PKPApplication->execute()
Could you please explain with detail what to do? I have the same issue:
DB Error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
Stack Trace:
File: /home/colefnet/public_html/revistas/ojs/classes/submission/editor/EditorSubmissionDAO.inc.php line 356
Function: DAO->retrieveRange(“SELECT DISTINCT a.*, scf.date_completed as copyedit_comp…”, Array(21), Object(DBResultRange))
File: /home/colefnet/public_html/revistas/ojs/classes/submission/editor/EditorSubmissionDAO.inc.php line 428
Function: EditorSubmissionDAO->_getUnfilteredEditorSubmissions(“2”, 0, 0, Null, Null, Null, Null, Null, Null, “a.status = 1 AND ea.edit_id IS NOT NULL AND (edec.decision IS NU…”, Object(DBResultRange), “id”, 1)
function getFirstActivityDate($journalId) {
$result =& $this->retrieve(
'SELECT LEAST(a.date_submitted, COALESCE(pa.date_published, NOW()), COALESCE(i.date_published, NOW())) AS first_date
FROM articles a
LEFT JOIN published_articles pa ON (a.article_id = pa.article_id)
LEFT JOIN issues i ON (pa.issue_id = i.issue_id)
LEFT JOIN articles a2 ON (a2.article_id < a.article_id AND a2.date_submitted IS NOT NULL AND a2.journal_id = ?)
WHERE a2.article_id IS NULL AND
a.date_submitted IS NOT NULL AND
a.journal_id = ?',
array(
(int) $journalId,
(int) $journalId
)
);
$row = $result->GetRowAssoc(false);
$firstActivityDate = $this->datetimeFromDB($row['first_date']);
$result->Close();
// An earlier user registration can override the earliest article activity date
$result =& $this->retrieve(
'SELECT MIN(u.date_registered) AS first_date FROM users u JOIN roles r ON (u.user_id = r.user_id) WHERE r.journal_id = ?',
array(
(int) $journalId
)
);
$row = $result->GetRowAssoc(false);
$firstUserDate = $this->datetimeFromDB($row['first_date']);
if (!$firstActivityDate || ($firstUserDate && $firstActivityDate && strtotime($firstUserDate) < strtotime($firstActivityDate))) {
$firstActivityDate = $firstUserDate;
}
Im getting the same error. It only show on Editor users by clicking In review
index.php/migracionesinternacionales/editor/submissions/submissionsInReview
@vleyrob, this is the same MySQL error, but from a different context from the patch provided for the Stats & Reports page.
The source of this error is this query:
I feel like a max join of 1B rows is reasonably high, and think this SQL should be refactored as well. Specifically, I think the LEFT OUTER joins should only be brought into the FROM clause if and when referenced in the WHERE clause. That is a pretty big refactoring and testing task, however.
In the short term, can you raise MAX_JOIN_SIZE or turn on SQL_BIG_SELECTS in your MySQL config?
Thanks for the reply, my hosting provider (bluehost) told me that they cant change SQL_BIG_SELECTS value
If I could change the max join size variable from 1,000,000,000 to how much should be?
Thanks again!
V
The default for this parameter is usually the maximum allowed value for your version of MySQL. For example, in 5.7 it is 18446744073709551615; in 5.1 it is 4294967295.
Out of curiosity, how many articles do you have in the system?
We have 2 Magazines, as far as I can see:
The first has 31 articles in review and 18 editing and the second has 10 in review and 32 in edit. I dont know where to look for the total articles or article in another status like archive (if exist)
To get a count of archived articles, as an Editor browse the archives and note the paging count at the bottom of the list:
User Home → Editor → Archives
1 - 50 of 130 Items
If the number of articles is not in the thousands, I’d be curious to know what is causing this query to grow so spectacularly.
You might be interested in following this Git Issue:
I’m getting the error loging as editor and clicking any article status. Talking with the editor they told me that they dont have more, any archives just the 31 in review and the 18 editing, but I noticed that login as editor I can go through section editor or editor of section (dont know the correct translation) and everthing is working well, there I can see In review 29, 16 editing and 64 archives
I installed an exact copy of the ojs locally and is working, no error
Do you think this error is my server’s fault? or is something of the OJS by itself
I have a hard time seeing how a small install such as yours could be causing the error.
As you note, this could be a server misconfiguration.
If you’ve copied the install to a development server and the issue disappears, start comparing the mysql configuration on the dev server to the configuration on the live server to look for differences.