InnoDB or MyISAM

Hi,

Historically Joomla used MyISAM. When we first developed HikaShop, we naturally chose MyISAM to do the same as Joomla.
Later on, Joomla changed to InnoDB.
Is it better or worst ? Frankly, it usually doesn't change much.
MyISAM can be faster, InnoDB can support more high volume. Other differences depend specific features a developer might be using, or not.
As a normal user, you can ignore all this. This is important if you're planning on starting the next Amazon, or eBay. But HikaShop isn't relevant at these scales. At these scales, you most certainly develop your own solutions.
If you want to change the HikaShop tables to InnoDB, you can do it. I don't think it will break something. It most likely won't change anything.
If that's important to you to follow your hosting recommendations, try it on a copy of your website before doing it on your live website. I personally wouldn't bother as I think it's a waist of time.

Hi,

It's probably not an infinite loop as there are no loops in MySQL queries.
However, based on the data in the database, and the way the MySQL query is written, it's possible that a MySQL query can take a really long time to be processed. A normal MySQL query runs for a small fraction of a second, and you have between 30 and 200 queries processed on any page of a Joomla website. For example, if your website runs a MySQL query which takes 2 days to be processed by your server, it can lead to the database stopping responding during that time.
These kind of queries can run regardless of whether the tables use MyISAM or InnoDB.
For example, suppose that you have 1 million products and 1 million categories in your database. If you run a MySQL query with joins between the products and the categories, even an optimized MySQL query will be slow to run because the engine will have to merge the products and the categories together to process the MySQL query and this takes a lot of time when you have a lot of data to process. And then, it can also take a lot of time to transmit the data of the results from MySQL to PHP.
But even with only a couple thousands of entries, if you have to run a MySQL query which join many tables, MySQL might need a lot of time to perform all the merges to process the MySQL query and transfer the results.
So optimizing MySQL queries is a complex endeavor. Throughout the years, we've improved many areas of HikaShop to be able to process more data, faster based on feedback from users.
But since you're on a Joomla website, the issue can potentially come from many things, even Joomla itself. Normally, after the issue, if you directly check the log of the MySQL server, it should be possible to know which MySQL query led to the problem, and then that would allow the developer to remedy the problem.

Hi,

The log you provided indicates that the hikashop_user and the hikashop_order table are locked. Because of that these MySQL queries can't run and have to wait a long time.
So the ait on these queries is a consequence of the problem, not the problem itself.
Do you have something else in the log before these ? The goal is to understand which MySQL query led to the tables being locked.

Hi,

We might be onto something here.
The fatal error in your PHP error log indicates that an export run and failed because it tried to load more than 1GB of data in memory from the database. And this means that the MySQL server must have run for a while to process all the data generated by the export MySQL query before sending all that data to PHP. And while this happens, the order and user tables will be locked so that the data doesn't change while MySQL process and retrieves all the data from these tables to generate the export file.
If that export is made with a mass action and runs periodically (like every hour, or more), it could well be the reason why your tables get locked after a while and then the database becomes unresponsive as it's trying to process too much export data.

So, I would recommend checking your mass actions first and see if you have any order export mass action. If so, try disabling it (or them if you have several of them) and see if that helps.

Hi,

Seeing the number of results for the first filter (423) this can't be what is generating the problem. The mass action should only need a few dozen of MB maximum of memory, nowhere near the 1 GB limit.
So the error was generated by something else.
Do you have that error only once in the PHP error log ? Or several times ? What are the dates and times of the errors ? Recent ? Close or far appart in time ?

Do you have other mass actions ?
Could it be that someone regularly use the "export" button of the orders listing in the backend, without any filter and without selecting any order ? In that case, it would run a MySQL query loading all the data of all the orders at once, resulting in a similar issue.

It could also be a browser extension, or something between your browser and your server prefetching the export URL without you doing anything yourself :
- a CDN like cloudflare ( developers.cloudflare.com/speed/optimiza...ntent/prefetch-urls/ ) or keyCDN ( www.keycdn.com/support/prefetching ). Some potentially useful information regarding cloudFlare: serverfault.com/questions/1165854/503-re...h-requests-nginx-php
- a browser extension on your browser: chromewebstore.google.com/detail/quickli...ibilpmekhgkbeg?hl=en
- a setting of chrome on your computer: stackoverflow.com/questions/28020184/how...tch-in-google-chrome
- a prefetching joomla extension ( extensions.joomla.org/extension/page-speed-optimizer/ ) which would run in the backend while it shouldn't ( not saying this one does, but if you have an extension like that on your website, that's a thing to check ).

A good way to check if it comes from something triggering unwanted exports of orders is to add the line:
exit;
after the line:
function export(){
in the file administrator/components/com_hikashop/controllers/order.php
This way, the "export" button of the orders listing won't do anything on your website.
If you don't have the problem anymore after this change, then it would confirm that it comes from something triggering unwanted exports of orders on your website backend.