Prestashop Database Table Expert Needed

[RichNAS]

Hi, 

 

We are looking for someone with knowledge of Prestashop database tables to help with integration of logistics software.

 

The software we are using is called Linnworks & it directly integrates with Prestashop 1.6 (we are currently on 1.6.1.4) 

We are currently having issues trying to integrate the 2, Linnworks support are blaming a table error within Prestashop because the query is timing out.

 

Here is the latest message from Linnworks:

 

Hello,

 

I've asked one of our developers to take a look once more and what he found out is that we get a timeout running anything connected to your products.

 

During execution we get the   <?xml version="1.0"?><error><![CDATA[Too many connections]]></error>   error and that is why the test queries fail.

 

Please ask your developer to address that.

 

Kind regards,

Andrii

 

 

Does anyone know how to fix this? we are looking to add live stock to our website but can't without this issue being fixed.

 

Regards.

[ExpertoPrestaShop]

Hello:

 

You mentioned that your query is timing out but support answer is not related with tables queries. I think it is related with how integration works because they give an XML response as evidence. So, I think they are using some module to integrate to Prestashop and here is the problem, or if integration is directly through DB they are incorrectly open to many DB connections.

 

Anyway, you must give more details about integration so I can help you.

 

Good luck.

[RichNAS]

Hi, 

 

Thanks for the reply, i really do not know much about this subject, here is another message from them.

 

the table in question is "updateinventory" 

 

Hello,

 

Unfortunately, after trying out several things from our end, we couldn't proceed with the integration, because it seems that 1-2 tables of yours simply timeout and we cannot bypass that, so the only course of action would be to fix it.

 

Even simple queries like:

 

SELECT * FROM `no_stock_available` LIMIT 1

 

time out.

 

So, I would suggest your developers to check if everything is OK with the table in question.

 

Kind regards,

Andrii

[ExpertoPrestaShop]

Hello:

 

updateinventory is not a native Prestashop table. Anyway it is imposible that: SELECT * FROM `no_stock_available` LIMIT 1 time out if you have no problems with your DB.

 

- Can you update product stock manually in backoffice without problem??

- Did you test the example query directly in your phpMyAdmin from your Cpanel??

- Did you try to reset your hosting server??

- Did you check if some of your tables are locked??

- This integration has an special DB user?? Did you check user permission??

 

All of this could be the problem but there are some other many things to check. If you want to do the integration you must know more about it.

[joseantgv]

I don't think that this query throws a timeout error. Do they have access to your database? Can they execute the query in phpMyAdmin or Adminer?

[bellini13]

Using phpmyadmin in your control panel, you should be able to execute this query and see if it does timeout or not.

SELECT * FROM `no_stock_available` LIMIT 1

The only way that would timeout is if there is an extremely large amount of data in that table, and your hosting environment is either poorly configured or has limited resources

 

I would suggest running that query yourself, or asking your hosting provider support to try it on your behalf and see if it times out. 

 

The Linnworks software, is that something that is installed on your server?  Or does the vendor host this software, and it connects remotely to your database? 

[RichNAS]

Using phpmyadmin in your control panel, you should be able to execute this query and see if it does timeout or not.

SELECT * FROM `no_stock_available` LIMIT 1

The only way that would timeout is if there is an extremely large amount of data in that table, and your hosting environment is either poorly configured or has limited resources

 

I would suggest running that query yourself, or asking your hosting provider support to try it on your behalf and see if it times out. 

 

The Linnworks software, is that something that is installed on your server?  Or does the vendor host this software, and it connects remotely to your database? 

 

Thanks for all the replies, Linnworks is hosted by the vendor and connects remotely, initially we had issues with the connection to the database which took some fixing. Linnworks support managed to get the connection made but we are now having this issue with the queries.

 

In all honesty the queries, database & tables stuff is beyond me but I am a quick learner and can easily follow instructions, i am going to check all the steps mentioned by Prestalba 1st and check the query in phpmyadmin and see how i get on.

 

I will post back my findings.

 

Regards

Rich

[ExpertoPrestaShop]

I ancitipate you that there no large amount of data in a table like "stock_available" capable of get time out in a simple query with a "LIMIT 1" because all Prestashop data are indexed and in InnoDB. Even in MyIsam SQL Engine that query will never get timeout at least your table is locked, corrupt or something else, but you can check this with some of the elements I give you before.

 

Regards

[RichNAS]

I think i am getting closer to what the actual problem is i just don't have the knowledge on how to fix it.

 

It is actually a Linnworks script that is timing out, they are saying that its the table that script uses that is causing the issue.

 

This table is no_stock_available & i believe its either a script issue or a issue within the table, Linnworks are certain its a table issue.

 

i get an error that reads out the entire script and says "The script timed out possible cause: query syntax is invalid"

 

one thing i have noticed is that any reference to the table reads "no_stock_available sa" could the "sa" be the error or is that some scripting language that I don't recognise?

 

Because I don't know what I am doing with scripts i don't want to just change it and try again just in case.

 

Regards

Rich

[RichNAS]

Forgot to add the script is:

 

UPDATE  no_stock_available sa

 

INNER JOIN no_product p ON sa.id_product = p.id_product

LEFT OUTER JOIN no_product_attribute pa ON pa.id_product = p.id_product

LEFT OUTER JOIN 

(

SELECT

pac.id_product_attribute,

GROUP_CONCAT(al.name ORDER BY al.name SEPARATOR '-') AS name

FROM  no_product_attribute_combination pac 

INNER JOIN no_attribute a ON a.id_attribute = pac.id_attribute

INNER JOIN no_attribute_lang al ON al.id_attribute = a.id_attribute AND al.id_lang = 1

GROUP BY pac.id_product_attribute

)temp ON temp.id_product_attribute = pa.id_product_attribute

SET sa.quantity = '[{NewQuantity}]'

WHERE sa.id_shop = 1 AND 

(pa.id_product_attribute IS NULL AND

 sa.id_product_attribute = 0 AND 

(IF(IFNULL(p.reference,'') = '' OR p.reference = '',CONCAT('PS-',p.id_product, '-', 0), p.reference) =  '[{ChannelSKU}]'))

 OR 

(pa.id_product_attribute IS NOT NULL AND 

sa.id_product_attribute =  pa.id_product_attribute AND 

(IF(IFNULL(pa.reference,'') = '' OR pa.reference = ''  ,CONCAT('PS-',p.id_product, '-', temp.name), pa.reference) =  '[{ChannelSKU}]'))

;

[bellini13]

well, that is a much more demanding script that what you provided originally.  That script is joining multiple tables together (6 of them), and also using functions to group and concat data together

no_stock_available
no_product
no_product_attribute
no_product_attribute_combination
no_attribute
no_attribute_lang

So I assume you likely have a lot of Products and Product Combinations, and most likely you are running on a server whose resources are limited.

 

The first thing you will need to do is review those 6 tables and ensure they are properly indexed for the query that is being run.  Hopefully Linnworks support will do that for you since they are the ones who have created this custom query.

 

If the tables are properly indexed, and the issue is that there is just too much data to query coupled with limited hosting resources, then you may not be able to overcome this issue, without either reducing your data, or upgrading your hosting