Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
SELECT would examine more than MAX_JOIN_SIZE rows
I have launched a new version of an old txp-site: www.vic-hikingclub.com.
While on my testserver (hoster: uberspace.de) everything was fine, the backend runs into troubles with the hosting environment (hoster: world4you.com) on the real server.
The article-tab quits with
Fatal error: Uncaught mysqli_sql_exception: 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 /home/.sites/122/site668/web/textpattern/lib/txplib_db.php:442 Stack trace: #0 /home/.sites/122/site668/web/textpattern/lib/txplib_db.php(442): mysqli_query(Object(mysqli), ‘SELECT\n …’, 0) #1 /home/.sites/122/site668/web/textpattern/include/txp_list.php(288): safe_query(‘SELECT\n …’) #2 /home/.sites/122/site668/web/textpattern/include/txp_list.php(65): list_list() #3 /home/.sites/122/site668/web/textpattern/index.php(230): include(‘/home/.sites/12…’) #4 {main} thrown in /home/.sites/122/site668/web/textpattern/lib/txplib_db.php on line 442
The textpattern-table contains about 1500 articles/rows.
I have asked the hoster if an upgraded Max_join_size limit would be an option, but I wonder if I could change something in the txp-installation.
Best,
Peter
Textpattern version: 4.8.8 (a5969b336c096d872a7ecab9ee25914b)
Last update: 2022-10-23 18:46:15/2023-02-22 18:28:02
Site URL: www.vic-hikingclub.com
…
PHP version: 8.1.14
Offline
Re: SELECT would examine more than MAX_JOIN_SIZE rows
Dammit.
I’m assuming that the joins that pull in category, section, author, comments count and so forth to display the table are exceeding a million rows somehow. Or your hoster has set a very low MAX_JOIN_SIZE value.
You can investigate the latter by executing the following in phpMyAdmin or equivalent:
select @@MAX_JOIN_SIZE;The general solution seems to be adding indexes to primary keys in joined tables, so perhaps adding a few choice indexes to your tables will help MySQL optimise the query. Do your articles have a lot of comments on them, btw?
EDIT: On the site in question I’m also seeing “Fatal error: Allowed memory size of 335544320 bytes exhausted (tried to allocate 16384 bytes)” which indicates something is perhaps wonky with the hosting setup memory limits, or something in Txp is running away with itself.
Last edited by Bloke (2023-02-22 22:28:23)
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
Re: SELECT would examine more than MAX_JOIN_SIZE rows
Hi Bloke, thank you for the quick response and help!
the MAX_JOIN_SIZE on the DB is set to 18000000.
Is this to low, what is a recommended limit?
I am using the comments for hike registrations on the website . About 30 comments per article are normal.
I think the “fatal error” occured because I forgot to remove the <txp:password> protection . -> fixed now.
If the passport prompt is ignored it leads to a fatal.
Last edited by Pemischl (2023-02-23 06:29:42)
Offline
Re: SELECT would examine more than MAX_JOIN_SIZE rows
My hoster fixed the problem with a database update. Thanks again for your help!
Offline