OJS-3.4.0-8
During log checking, we found the following error: “SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join.”
Although I am unsure of what triggered this issue, it appears to be causing a query backlog on the MySQL server, leading to server slowdowns during that specific period.
Below is the error log:
PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join in /ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php:444
Stack trace:
#0 /ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(444): PDO->prepare()
#1 /ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(753): Illuminate\Database\Connection->Illuminate\Database\{closure}()
#2 /ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(720): Illuminate\Database\Connection->runQueryCallback()
#3 /ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(456): Illuminate\Database\Connection->run()
#4 /ojs/classes/search/ArticleSearchDAO.php(113): Illuminate\Database\Connection->cursor()
#5 /ojs/lib/pkp/classes/search/SubmissionSearch.php(224): APP\search\ArticleSearchDAO->getPhraseResults()
#6 /ojs/lib/pkp/classes/search/SubmissionSearch.php(171): PKP\search\SubmissionSearch->_getMergedPhraseResults()
#7 /ojs/lib/pkp/classes/search/SubmissionSearch.php(220): PKP\search\SubmissionSearch->_getMergedKeywordResults()
#8 /ojs/lib/pkp/classes/search/SubmissionSearch.php(171): PKP\search\SubmissionSearch->_getMergedPhraseResults()
#9 /ojs/lib/pkp/classes/search/SubmissionSearch.php(156): PKP\search\SubmissionSearch->_getMergedKeywordResults()
#10 /ojs/lib/pkp/classes/search/SubmissionSearch.php(289): PKP\search\SubmissionSearch->_getMergedArray()
#11 /ojs/pages/search/SearchHandler.php(150): PKP\search\SubmissionSearch->retrieveResults()
#12 /ojs/pages/search/SearchHandler.php(53): APP\pages\search\SearchHandler->search()
#13 [internal function]: APP\pages\search\SearchHandler->index()
#14 /ojs/lib/pkp/classes/core/PKPRouter.php(334): call_user_func()
#15 /ojs/lib/pkp/classes/core/PKPPageRouter.php(278): PKP\core\PKPRouter->_authorizeInitializeAndCallRequest()
#16 /ojs/lib/pkp/classes/core/Dispatcher.php(165): PKP\core\PKPPageRouter->route()
#17 /ojs/lib/pkp/classes/core/PKPApplication.php(395): PKP\core\Dispatcher->dispatch()
#18 /ojs/index.php(21): PKP\core\PKPApplication->execute()
#19 {main}
Next Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join (SQL: SELECT
o.submission_id,
MAX(s.context_id) AS journal_id,
MAX(i.date_published) AS i_pub,
MAX(p.date_published) AS s_pub,
COUNT(*) AS count
FROM
submissions s
JOIN publications p ON (p.publication_id = s.current_publication_id)
JOIN publication_settings ps ON (ps.publication_id = p.publication_id AND ps.setting_name='issueId' AND ps.locale='')
JOIN issues i ON (CAST(i.issue_id AS CHAR(20)) = ps.setting_value AND i.journal_id = s.context_id)
JOIN submission_search_objects o ON (s.submission_id = o.submission_id)
JOIN journals j ON j.journal_id = s.context_id
LEFT JOIN journal_settings js ON j.journal_id = js.journal_id AND js.setting_name = 'publishingMode'
NATURAL JOIN submission_search_object_keywords o0 NATURAL JOIN submission_search_keyword_list k0, submission_search_object_keywords o1 NATURAL JOIN submission_search_keyword_list k1, submission_search_object_keywords o2 NATURAL JOIN submission_search_keyword_list k2, submission_search_object_keywords o3 NATURAL JOIN submission_search_keyword_list k3, submission_search_object_keywords o4 NATURAL JOIN submission_search_keyword_list k4, submission_search_object_keywords o5 NATURAL JOIN submission_search_keyword_list k5, submission_search_object_keywords o6 NATURAL JOIN submission_search_keyword_list k6, submission_search_object_keywords o7 NATURAL JOIN submission_search_keyword_list k7, submission_search_object_keywords o8 NATURAL JOIN submission_search_keyword_list k8, submission_search_object_keywords o9 NATURAL JOIN submission_search_keyword_list k9, submission_search_object_keywords o10 NATURAL JOIN submission_search_keyword_list k10, submission_search_object_keywords o11 NATURAL JOIN submission_search_keyword_list k11, submission_search_object_keywords o12 NATURAL JOIN submission_search_keyword_list k12, submission_search_object_keywords o13 NATURAL JOIN submission_search_keyword_list k13, submission_search_object_keywords o14 NATURAL JOIN submission_search_keyword_list k14, submission_search_object_keywords o15 NATURAL JOIN submission_search_keyword_list k15, submission_search_object_keywords o16 NATURAL JOIN submission_search_keyword_list k16, submission_search_object_keywords o17 NATURAL JOIN submission_search_keyword_list k17, submission_search_object_keywords o18 NATURAL JOIN submission_search_keyword_list k18, submission_search_object_keywords o19 NATURAL JOIN submission_search_keyword_list k19, submission_search_object_keywords o20 NATURAL JOIN submission_search_keyword_list k20, submission_search_object_keywords o21 NATURAL JOIN submission_search_keyword_list k21, submission_search_object_keywords o22 NATURAL JOIN submission_search_keyword_list k22, submission_search_object_keywords o23 NATURAL JOIN submission_search_keyword_list k23, submission_search_object_keywords o24 NATURAL JOIN submission_search_keyword_list k24, submission_search_object_keywords o25 NATURAL JOIN submission_search_keyword_list k25, submission_search_object_keywords o26 NATURAL JOIN submission_search_keyword_list k26, submission_search_object_keywords o27 NATURAL JOIN submission_search_keyword_list k27, submission_search_object_keywords o28 NATURAL JOIN submission_search_keyword_list k28, submission_search_object_keywords o29 NATURAL JOIN submission_search_keyword_list k29, submission_search_object_keywords o30 NATURAL JOIN submission_search_keyword_list k30, submission_search_object_keywords o31 NATURAL JOIN submission_search_keyword_list k31, submission_search_object_keywords o32 NATURAL JOIN submission_search_keyword_list k32, submission_search_object_keywords o33 NATURAL JOIN submission_search_keyword_list k33, submission_search_object_keywords o34 NATURAL JOIN submission_search_keyword_list k34, submission_search_object_keywords o35 NATURAL JOIN submission_search_keyword_list k35
WHERE
(js.setting_value <> '2' OR
js.setting_value IS NULL) AND j.enabled = 1 AND
s.status = 3 AND
i.published = 1 AND k0.keyword_text = rice AND k1.keyword_text = 20occupies AND o0.object_id = o1.object_id AND o0.pos+1 = o1.pos AND k2.keyword_text = 20the AND o0.object_id = o2.object_id AND o0.pos+2 = o2.pos AND k3.keyword_text = 20principal AND o0.object_id = o3.object_id AND o0.pos+3 = o3.pos AND k4.keyword_text = 20position AND o0.object_id = o4.object_id AND o0.pos+4 = o4.pos AND k5.keyword_text = 20as AND o0.object_id = o5.object_id AND o0.pos+5 = o5.pos AND k6.keyword_text = 20the AND o0.object_id = o6.object_id AND o0.pos+6 = o6.pos AND k7.keyword_text = 20staple AND o0.object_id = o7.object_id AND o0.pos+7 = o7.pos AND k8.keyword_text = 20food AND o0.object_id = o8.object_id AND o0.pos+8 = o8.pos AND k9.keyword_text = 20commodity AND o0.object_id = o9.object_id AND o0.pos+9 = o9.pos AND k10.keyword_text = 20in AND o0.object_id = o10.object_id AND o0.pos+10 = o10.pos AND k11.keyword_text = 20india AND o0.object_id = o11.object_id AND o0.pos+11 = o11.pos AND k12.keyword_text = 20and AND o0.object_id = o12.object_id AND o0.pos+12 = o12.pos AND k13.keyword_text = 20is AND o0.object_id = o13.object_id AND o0.pos+13 = o13.pos AND k14.keyword_text = 20indispensable AND o0.object_id = o14.object_id AND o0.pos+14 = o14.pos AND k15.keyword_text = 20to AND o0.object_id = o15.object_id AND o0.pos+15 = o15.pos AND k16.keyword_text = 20the AND o0.object_id = o16.object_id AND o0.pos+16 = o16.pos AND k17.keyword_text = 20livelihoods AND o0.object_id = o17.object_id AND o0.pos+17 = o17.pos AND k18.keyword_text = 20of AND o0.object_id = o18.object_id AND o0.pos+18 = o18.pos AND k19.keyword_text = 20the AND o0.object_id = o19.object_id AND o0.pos+19 = o19.pos AND k20.keyword_text = 20populace AND o0.object_id = o20.object_id AND o0.pos+20 = o20.pos AND k21.keyword_text = 20india AND o0.object_id = o21.object_id AND o0.pos+21 = o21.pos AND k22.keyword_text = 20alone AND o0.object_id = o22.object_id AND o0.pos+22 = o22.pos AND k23.keyword_text = 20contributes AND o0.object_id = o23.object_id AND o0.pos+23 = o23.pos AND k24.keyword_text = 20nearly AND o0.object_id = o24.object_id AND o0.pos+24 = o24.pos AND k25.keyword_text = 20of AND o0.object_id = o25.object_id AND o0.pos+25 = o25.pos AND k26.keyword_text = 20the AND o0.object_id = o26.object_id AND o0.pos+26 = o26.pos AND k27.keyword_text = 20global AND o0.object_id = o27.object_id AND o0.pos+27 = o27.pos AND k28.keyword_text = 20paddy AND o0.object_id = o28.object_id AND o0.pos+28 = o28.pos AND k29.keyword_text = 20output AND o0.object_id = o29.object_id AND o0.pos+29 = o29.pos AND k30.keyword_text = yadav AND o0.object_id = o30.object_id AND o0.pos+30 = o30.pos AND k31.keyword_text = 20et AND o0.object_id = o31.object_id AND o0.pos+31 = o31.pos AND k32.keyword_text = 20al AND o0.object_id = o32.object_id AND o0.pos+32 = o32.pos AND k33.keyword_text = 20doni AND o0.object_id = o33.object_id AND o0.pos+33 = o33.pos AND k34.keyword_text = 20et AND o0.object_id = o34.object_id AND o0.pos+34 = o34.pos AND k35.keyword_text = 20al AND o0.object_id = o35.object_id AND o0.pos+35 = o35.pos AND i.journal_id = 1
GROUP BY o.submission_id
ORDER BY count DESC
LIMIT 500) in /ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php:760
Stack trace:
#0 /ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(720): Illuminate\Database\Connection->runQueryCallback()
#1 /ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(456): Illuminate\Database\Connection->run()
#2 /ojs/classes/search/ArticleSearchDAO.php(113): Illuminate\Database\Connection->cursor()
#3 /ojs/lib/pkp/classes/search/SubmissionSearch.php(224): APP\search\ArticleSearchDAO->getPhraseResults()
#4 /ojs/lib/pkp/classes/search/SubmissionSearch.php(171): PKP\search\SubmissionSearch->_getMergedPhraseResults()
#5 /ojs/lib/pkp/classes/search/SubmissionSearch.php(220): PKP\search\SubmissionSearch->_getMergedKeywordResults()
#6 /ojs/lib/pkp/classes/search/SubmissionSearch.php(171): PKP\search\SubmissionSearch->_getMergedPhraseResults()
#7 /ojs/lib/pkp/classes/search/SubmissionSearch.php(156): PKP\search\SubmissionSearch->_getMergedKeywordResults()
#8 /ojs/lib/pkp/classes/search/SubmissionSearch.php(289): PKP\search\SubmissionSearch->_getMergedArray()
#9 /ojs/pages/search/SearchHandler.php(150): PKP\search\SubmissionSearch->retrieveResults()
#10 /ojs/pages/search/SearchHandler.php(53): APP\pages\search\SearchHandler->search()
#11 [internal function]: APP\pages\search\SearchHandler->index()
#12 /ojs/lib/pkp/classes/core/PKPRouter.php(334): call_user_func()
#13 /ojs/lib/pkp/classes/core/PKPPageRouter.php(278): PKP\core\PKPRouter->_authorizeInitializeAndCallRequest()
#14 /ojs/lib/pkp/classes/core/Dispatcher.php(165): PKP\core\PKPPageRouter->route()
#15 /ojs/lib/pkp/classes/core/PKPApplication.php(395): PKP\core\Dispatcher->dispatch()
#16 /ojs/index.php(21): PKP\core\PKPApplication->execute()
#17 {main}
thrown in /ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 760