DB down with specific query

[iltecnobadante]

Hi, we have a site www.scontolo.net developed on prestashop 1.6.1.20.

From 26 September we have a big problem on a this qery:

"SELECT s.id_shop, CONCAT(su.physical_uri, su.virtual_uri) AS uri, su.domain, su.main
FROM ps_shop_url su
LEFT JOIN ps_shop s ON (s.id_shop = su.id_shop)
WHERE (su.domain = 'www.scontolo.net' OR su.domain_ssl = 'www.scontolo.net')
AND s.active = 1
AND s.deleted = 0
ORDER BY LENGTH(CONCAT(su.physical_uri, su.virtual_uri)) DESC"

this query lock tables and db crashed.

 

Can you help me???

 

[seb776]

hello,

try to repair the tables :

 repair table ps_shop_url;
 repair table ps_shop;

in a mysql shell (or phpmyadmin)

[iltecnobadante]

ok,

I'm check with repair,

thanks

[iltecnobadante]

we have this result:

Risultato SQL

Host: localhost
Database: scontolo_ps16
Creato il: Ott 03, 2018 alle 14:20
Generato da: phpMyAdmin 4.4.15.10 / MySQL 10.2.12-MariaDB
Query SQL: repair table ps_shop_url;
Righe: 1

 

Table	Op	Msg_type	Msg_text
scontolo_ps16.ps_shop_url	repair	note	The storage engine for the table doesn't support repair

[seb776]

So it does not need to be repaired. - https://duckduckgo.com/?q=The+storage+engine+for+the+table+doesn't+support+repair+&t=ffab&ia=qa

Maybe the ORDER part of the query should be rewritten using a select field then, order on that field...
Maybe a mysql - innodb bug ...
Maybe you can drop this table then recreate them. Then recreat the entries ...

[iltecnobadante]

HI,unfortunately db go down on this query...it's terrible.

It's possible to lock tables and not delivery other request?