reneerd Posted January 28, 2021 Share Posted January 28, 2021 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 More sharing options...
bsmither Posted January 28, 2021 Share Posted January 28, 2021 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 More sharing options...
reneerd Posted January 28, 2021 Author Share Posted January 28, 2021 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 Link to comment Share on other sites More sharing options...
bsmither Posted January 28, 2021 Share Posted January 28, 2021 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 More sharing options...
reneerd Posted January 28, 2021 Author Share Posted January 28, 2021 Yep we have full access to the server so that shouldnt be an issue. Appreciate your help as always. I will let you know how I make out. Link to comment Share on other sites More sharing options...
bsmither Posted January 28, 2021 Share Posted January 28, 2021 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 More sharing options...
reneerd Posted January 29, 2021 Author Share Posted January 29, 2021 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 More sharing options...
shubhrapandit Posted August 20, 2021 Share Posted August 20, 2021 Thank you so much for sharing an information about MySQL. I found this thread very helpful, got exact same info that I was looking for. Regards - SQL Training in Pune Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.