Resolved RO CSVI MySQL error – MAX_JOIN_SIZE

Hi,

As a follow-up question, does this happen when you export for example 10 records?

Hello,

when I perform a document export (range 0-5000), the resulting file is empty, containing only the column headers/titles."

Setting From and To fields in export templates there is no more error but you are getting file with only headers and no records. To check this please post latest export debug log. To get the debug log see How to collect debug information? document.

"I am following up on my previous message. Regarding my One.com hosting: when I perform a document export (range 0-5000), the resulting file is empty, containing only the column headers/titles."
-------------------------------------------------------

Warm greetings from one.com!

I have an update from our team regarding the issue. The error you are encountering indicates that the export query is exceeding the database server’s limits due to the large amount of data being processed in a single request.

To resolve this, we recommend exporting the data in smaller batches. For example, you can limit the number of records per export run or apply filters such as category or published status. This approach reduces the complexity of the query and usually prevents the error from occurring.

You can do this by navigating to:
Components → RO CSVI → Templates
Open "Vm export producten"
Go to the Options tab
Set Quantity Start: From and To

Hello,

That is a change in the Joomla core and it won't make a difference because it is not related to the maximum joins at all. There are no SQL modes to control the max joins, that is what the SQL_BIG_SELECTS allow. The host should either increase the number in the database config file or allow for it to be set during runtime.

We run the SQL_BIG_SELECTS prior to any export query being run so it is triggered but it seems the server doesn't allow such change. The hosting provider needs to look at the database configuration.

i have this from my sevver : after change stys the same probleem , see please atachment logfile : Thank you for contacting one.com!

Following up on this case, our team has provided the next step to help resolve the issue.

Please edit the following file on your installation:
libraries/vendor/joomla/database/src/Mysqli/MysqliDriver.php

Then move this line:
mysqli_query($this->connection,"SET SQL_BIG_SELECTS = 1;" );
From line 309 to line 305, so that it appears as shown below:
// If needed, set the sql modes.
if ($this->options['sqlModes'] !== []) {
$this->connection->query('SET @@SESSION.sql_mode = \'' . implode(',', $this->options['sqlModes']) . '\';');
mysqli_query($this->connection,"SET SQL_BIG_SELECTS = 1;" );
}

// And read the real sql mode to mitigate changes in mysql > 5.7.+
$this->options['sqlModes'] = explode(',', $this->setQuery('SELECT @@SESSION.sql_mode;')->loadResult());
Once this change has been applied, please let us know so we can continue assisting you further or verify the result.

Hi,

So there are 7 JOINs in the query which are all there for a reason, the joins are for:

• product prices
  
• manufacturers
  
• manufacturer translations (nl_nl)
  
• product categories
  
• product currencies
  
• product shoppergroups
  

This is something used by all customers and isn't an issue. The logfile looks normal from what I see, so it really comes down to how the database is configured.

This brings me back to my earlier question.

One thing I was thinking about as well is that, even though we set the "SQL_BIG_SELECTS=1" perhaps it has no influence because the database is configured to not allow it. Please check that with your hosting provider.

Let me know what you can find out.

thank you, check the logfile please ( attachment)

Hi,

You posted a screenshot of the start of the logfile. Please post the actual logfile. You can Enable Logging in the template, run the export and you should have the logfile.

Did this happen suddenly or the export never ran?

One thing I was thinking about as well is that, even though we set the "SQL_BIG_SELECTS=1" perhaps it has no influence because the database is configured to not allow it. Please check that with your hosting provider.

Let me know how it goes.

thank you , check atachments please

Hello Zenon,

Could you please advise what is causing this and how we can resolve it?

The error message is very clear on what needs to be done. Either enable big selects or increase the number of joins. The thing is, RO CSVI already sets the big selects before an export is started.

There is no image attached. What export are you running? Do you have logging enabled? Is there anything in the debug log for this export?

Let me know how it goes.