Page 1 of 1

[FIX] Customer: View order details DB error

Posted: Tue Jan 27, 2015 11:43 am
by Martin
Problem:

Customer tries to view an existing orders details by clicking on "View order details"

URL: site.tld/account.php?action=view_order&order_id=12345&x=a&y=b

Code: Select all

Fatal error: Allowed memory size of xxxxxxx bytes exhausted (tried to allocate xx bytes) in /home/[account]/public_html/lib/database/mysql.php on line 340
Cause:
The associated SQL query will pull down all the rows from the order_products table including those from other orders so if you have a lot of orders PHP will eventually run out of memory to pull the full query down so it can come out of left field.

Fix:

Open: /includes/classes/class.account.php

Find:

Code: Select all

			$query = "
				SELECT
					
					o.*,
					op.*,
					oa.address_1,
					oa.address_2,
					oa.city,
					oa.zip,
					oa.country,
					oa.state,
					p.productid,
					p.prodpreorder,
					p.prodreleasedate,
					p.prodpreordermessage
				FROM
					[|PREFIX|]orders o
					LEFT JOIN [|PREFIX|]order_products op ON op.orderorderid
					LEFT JOIN [|PREFIX|]products p ON p.productid = op.ordprodid
					LEFT JOIN [|PREFIX|]order_addresses oa ON oa.`id` = op.order_address_id
				WHERE
					o.orderid = ". (int)$order['orderid'] ."
				ORDER BY
					op.order_address_id";

Replace with:

Code: Select all

			$query = "
				SELECT
					
					o.*,
					op.*,
					oa.address_1,
					oa.address_2,
					oa.city,
					oa.zip,
					oa.country,
					oa.state,
					p.productid,
					p.prodpreorder,
					p.prodreleasedate,
					p.prodpreordermessage
				FROM
					[|PREFIX|]orders o
					LEFT JOIN [|PREFIX|]order_products op ON op.orderorderid
					LEFT JOIN [|PREFIX|]products p ON p.productid = op.ordprodid
					LEFT JOIN [|PREFIX|]order_addresses oa ON oa.`id` = op.order_address_id
				WHERE
					o.orderid = ". (int)$order['orderid'] ."
					AND op.orderorderid = ". (int)$order['orderid'] ."
				ORDER BY
					op.order_address_id";

Re: [FIX] Customer: View order details DB error

Posted: Sat May 09, 2015 7:23 am
by Osmotics
Fantastic, been struggling with this recently. Thanks fro the post!