Post by robster » Tue Jun 18, 2019 4:27 pm

I have spent some time looking for an answer for this and am not clever enough to figure out a solution if there is one:

I have installed Opencart 2.3.0.2 and all is working fine except for when I try to run Reports > Customers > Orders. The server just hangs and I have to kill the processes in my CPanel WHM. I contacted the hosting company who are remarkably good at helping with everything and they said this:

Site hangs because its code is waiting MySQL query to complete
===
[root@server ~]# mysqladmin proc stat
+-------+------------------+-----------+------------------+---------+------+--------------
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+-----------+------------------+---------+------+--------------
| 44547 | ppporguk_dev2019 | localhost | ppporguk_dev2019 | Query | 257 | Sending data | SELECT t.customer_id, t.customer, t.email, t.customer_group, t.status, COUNT(DISTINCT t.order_id) AS |
====

You need to check with developer why this happens.


Here's full query
====
SELECT t.customer_id, t.customer, t.email, t.customer_group, t.status, COUNT(t.order_id) AS orders, SUM(t.products) AS products, SUM(t.total) AS total FROM (SELECT c.customer_id, CONCAT(c.firstname, ' ', c.lastname) AS customer, c.email, cgd.name AS customer_group, c.status, o.order_id, SUM(op.quantity) as products, SUM(DISTINCT o.total) AS total FROM `oc_order` o LEFT JOIN `oc_order_product` op ON (o.order_id = op.order_id)LEFT JOIN `oc_customer` c ON (o.customer_id = c.customer_id) LEFT JOIN `oc_customer_group_description` cgd ON (c.customer_group_id = cgd.customer_group_id) WHERE o.customer_id > 0 AND cgd.language_id = '1' AND o.order_status_id > '0' GROUP BY o.order_id) AS t GROUP BY t.customer_id ORDER BY total DESC LIMIT 0,30
====


Can anybody here tell me what could cause this please? I have found a few posts on google related to other, different issues with running this report on earlier versions but cannot find anything to offer a solution for my issue with 2.3.0.2.

Apologies if this has been discussed/fixed but I think I have exhausted the search feature :)

Thanks

Robster

I know my place...!


User avatar
Active Member

Posts

Joined
Tue Jul 13, 2010 8:08 pm
Location - North Yorkshire, UK

Post by thekrotek » Tue Jun 18, 2019 5:04 pm

The query itself looks good. I tried it on my test OC installation and it executed just fine. Issue might be with your database or something else, but this is impossible to tell without looking into it personally. I can look into this issue, if you wish. Drop me an email or text in Skype and we'll discuss the details.

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by robster » Tue Jun 18, 2019 5:12 pm

Before I look at commercial support, the report is using data from 47000 orders and over 14000 customers. Could this be a server settings issue?

Rob

I know my place...!


User avatar
Active Member

Posts

Joined
Tue Jul 13, 2010 8:08 pm
Location - North Yorkshire, UK

Post by thekrotek » Tue Jun 18, 2019 5:46 pm

robster wrote:
Tue Jun 18, 2019 5:12 pm
Before I look at commercial support, the report is using data from 47000 orders and over 14000 customers. Could this be a server settings issue?
More like database related issue.

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am

Who is online

Users browsing this forum: No registered users and 39 guests