Jump to content

MySQL Upgrade V8 and Cubecart Order issue


reneerd

Recommended Posts

Hi all, been awhile since I needed help so you all are doing great!  Hit a snag today. I had to push our MYSQL frome v5.x to current 8.x. Everything went smooth and Cubecart seems overall happy with it (actual response times improved).

Hit one issue, when we go to check orders on the admin side we are getting "No orders found" This extends to customers looking up their history on the client side.

I checked logs and we are getting 

File: [orders.index.inc.php] Line: [772] "SELECT SQL_CALC_FOUND_ROWS CubeCart_order_summary.*, CubeCart_customer.type, CONCAT(CubeCart_order_summary.last_name, CubeCart_order_summary.first_name) AS `customer`, CubeCart_order_summary.status FROM `CubeCart_order_summary` LEFT JOIN `CubeCart_customer` ON CubeCart_order_summary.customer_id = CubeCart_customer.customer_id ORDER BY `order_date` DESC LIMIT 20 OFFSET 0;" - Out of sort memory, consider increasing server sort buffer size

There are some others but its all variations on the above paradigm and same error message.  Now I did confirm that database is fine so this is a between the database and the GUI.   

I am going through everything but as this is time sensitive I wanted to reach out and see if anyone can point me in a direction.  I am currently trying to run down how to up the "sort memory" once I figure out how that is done exactly (first time I ran into that).

Link to comment
Share on other sites

We would ask what exact version of CubeCart you are using.

Please use an external database manager (such as phpMyAdmin) to verify if CubeCart_order_summary has an index on the 'order_date' column. (CC642 does, but I haven't looked how far back in prior versions that this index exists.)

Anyway, there is this from the MySQL docs:

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sort_buffer_size

 

Link to comment
Share on other sites

Hi and ty bsmither! Always a pleasure.

Yeah I am sorry, I know better about the CC version, thought I did include it.  Version 6.2.2   

I checked order_summary and yes it has an index.

order_date Index    int        UNSIGNED    No    0    

Temp-capture.JPG

Link to comment
Share on other sites

I read the docs to mean that if ORDER BY has an index (thus, 'optimized'), the sort memory buffer won't be needed. (Unless there is a bug in MySQL8.)

So, this is a system variable and gets adjusted by the 'my.conf' system configuration file. The docs also mention one can set this in a SESSION statement. (See: https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-set-var)

If you own your own database, setting this shouldn't be a problem. A hosting account that shares a database server may have objections.

We might try to tweak CubeCart's database class to solve your particular issue.

 

Link to comment
Share on other sites

Keep in mind this advisory:

https://orangematter.solarwinds.com/2015/10/02/mastering-the-sort-buffer-and-sort_buffer_size/

According to this, we should give greater weight to solving this one particular instance rather than making a global change -- unless, as I am guessing, the current setting is at its minimum of 32K.

And keep in mind that this setting consumes X amount of ram per session. So multiply this memory by how many simultaneous page requests are being made that requires a sort.

How many records are in CubeCart_order_summary?

 

 

Link to comment
Share on other sites

Success! 

To answer your question, 43125 records.

Okay so I added the following command and value to my.cnf

sort_buffer_size=201326592

That was the test value which corrected the issue.  While the site is not super high demand to crunch ram resources I am going to tweak and optimize a bit to find the "sweet spot" of working at a minimum value.

Thanks again bsmither!

Link to comment
Share on other sites

  • 6 months later...

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...