Invalid list SQL

[Norwegian_Rat]

Hi, 

Suddenly we got a "Invalid list SQL" error when we click on "Active shopping carts" from the Activity overview and the customers tab. 

Both Error in log says this:

Message:
ThirtyBeesDatabaseException: 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

Location:
classes/Customer.php line 827, classes/controller/AdminController.php line 1184

Can anyone help shed some light on this? Not being able to modify customers could become a serious issue down the road 🙂

we're on latest bleeding edge and php 8.3.30

 

Thanx in advance

[datakick]

This is your hosting restrictions. You are probably on a shared hosting, and they have enabled this restriction to ensure fair usage for all customers.

Ask them if they can disable this.

[Norwegian_Rat]

Hi @datakick

Thank you for your swift reply. Much appreciated.

 

We contacted them, and here's what they said:

I appreciate the detailed error message and the domain information you have provided. Your concern is duly noted, especially given its impact on customer management in the back office. Kindly be informed that no new restrictions on SQL queries have been implemented recently on our hosting platform. The issue you are encountering is a recognized MySQL safeguard commonly applied in shared hosting environments. The error signifies that the query generated by your ThirtyBees installation is attempting to process an exceptionally large dataset. MySQL restricts this type of query to prevent excessive server load or potential performance degradation. This measure is not a recent restriction but a protective limit intended to prevent queries that could adversely affect the server.

From a support perspective, the issue is more likely related to the application level rather than the hosting level.  I suggest the following actions:

1. Verify the presence of all necessary indexes * Pay particular attention to tables associated with carts and customers.

2. Examine the recent behavior of modules * Even if no recent modifications have been made, certain modules may produce inefficient queries under specific data conditions.

3. Activate debug mode in ThirtyBees * This will help identify the precise SQL query responsible for the issue.

4. Perform database optimization * Removing outdated carts and sessions can substantially decrease the size of queries.

5. Request a developer to review the query * The query found in the following files likely requires optimization: * classes/Customer.php * AdminController.php

 

Would the module "Optimizing and cleaning up your Thirtybees store" help us out? Other modules that might help us solve this issue?

(We're on the pro plan of the shared hosting provider, if this means anything)

 

All the best

Edited 5 hours ago by Norwegian_Rat

[datakick]

Well sure, you can remove old data, it will reduce the number of rows db have to examine.

But still, the root cause of the issue is that your hosting is throttling your database. We send them valid sql request, and they may or may not return results. This is just stupid. Who knows what other sql queries they will kill randomly. This "fair usage mechanism" can cost you sales.

But that's sharing hosting. You can always move to a dedicated hosting without such restrictions

 

[datakick]

There is a potential workaround here. You can set SQL_BIG_SELECTS variable for a database session. That should allow you to bypass the error (may or may not work, depending on your hosting configuration)

Create a new override 

override/classes/db/Db.php

with this content:

<?php

class Db extends DbCore
{
    /**
     * This override allows complex queries to run on shared hosting
     */
    public function connect()
    {
        $link = parent::connect();
        $link->exec('SET SQL_BIG_SELECTS = 1');
        return $link;
    }
}

Then clear cache in Advanced Parameters > Performance to reload the overrides