Error in "Stats & Reports" page

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

It is probably mysql error.
How can I fix it?

Thanks in advance

Stack Overflow provides some details on this error. http://stackoverflow.com/questions/950465/mysql-sql-big-selects/950576#950576

What version of OJS are you using?

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.

Hello Ctgraham

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()

Selamlar,
M. Cüneyt Birkök

www.J-HumanSciences.com

I think there’s a bug at the core of this.

The cross join of articles and users here seems excessive:

I think this should be broken out into two queries.

I’ll try to put together a fix.

@birkok, can you test this change?

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)

File: /home/colefnet/public_html/revistas/ojs/pages/editor/EditorHandler.inc.php line 263
Function: EditorSubmissionDAO->getEditorSubmissionsInReview(“2”, 0, 0, Null, Null, Null, Null, Null, Null, Object(DBResultRange), “id”, 1)

File: (unknown) line (unknown)
Function: EditorHandler->submissions(Array(1), Object(Request))

File: /home/colefnet/public_html/revistas/ojs/lib/pkp/classes/core/PKPRouter.inc.php line 362
Function: call_user_func(Array(2), Array(1), Object(Request))

File: /home/colefnet/public_html/revistas/ojs/lib/pkp/classes/core/PKPPageRouter.inc.php line 220
Function: PKPRouter->_authorizeInitializeAndCallRequest(Array(2), Object(Request), Array(1), False)

File: /home/colefnet/public_html/revistas/ojs/lib/pkp/classes/core/Dispatcher.inc.php line 134
Function: PKPPageRouter->route(Object(Request))

File: /home/colefnet/public_html/revistas/ojs/lib/pkp/classes/core/PKPApplication.inc.php line 178
Function: Dispatcher->dispatch(Object(Request))

File: /home/colefnet/public_html/revistas/ojs/index.php line 63
Function: PKPApplication->execute()

According to Replace JournalStatisticsDAO cross product with two queries by ctgraham · Pull Request #655 · pkp/ojs · GitHub
I just edit the page /classes/journal/JournalStatisticsDAO.inc.php as:

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

And:

sql big selects OFF
max join size 1,000,000,000

Thank you!
Regards

@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)

MySql is 5.5.46-cll

Hope this help

thanks again
Regards
Valentin

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’ll take a look to the link

Thank you

ctgraham

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

That links got me lost!

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.

(See also this related thread)