Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
When attempting to run update 7115 the select query seems to constantly timeout, preventing me from completing the update as written. Locally running it takes about 115 seconds to return 406 records. Attempting to run it in on my development server never completes since it reaches a time limit with the mysql server. For clarity, here's the query that's being run:
SELECT DISTINCT fpp.uuid as uuid, fppr.vuuid as vuuid
FROM fieldable_panels_panes fpp
INNER JOIN fieldable_panels_panes_revision fppr
ON fpp.vid = fppr.vid
INNER JOIN panels_pane pp
ON fpp.uuid = SUBSTRING(pp.subtype FROM (POSITION(':' IN pp.subtype) + 1))
INNER JOIN panelizer_entity pe
ON pe.did = pp.did
WHERE (pp.subtype LIKE 'uuid:%')
AND fpp.reusable != 1
ORDER BY fpp.uuid;
I'm guessing it's the POSITION or SUBSTRING part that's taking a while. Is there way to optimize the query to improve performance?
Comment | File | Size | Author |
---|---|---|---|
#2 | fieldable_panels_panes-n2846528-2.patch | 1.82 KB | DamienMcKenna |
|
Comments
Comment #2
DamienMcKennaAre you sure that the problem is the single query never finishing versus the loop never finishing? If it's the latter, might this fix it?
Comment #3
davidsheart02 CreditAttribution: davidsheart02 commentedFor me, it's the query itself that is the problem. I ran the SQL query directly in mysql and could see the amount of time it was taking to complete. It took 4 min 10.07 sec to return 1492 rows on my local development machine. This kind of delay in our production environments was something we could not accept.
Just to verify that though, I ran the patch from #2 to see if things would complete. I got the same mysql server gone away error that I had received previously.
The way I finally worked around this issue was to update our production databases locally where I could have more control over the timeout values. It was a very time consuming process but it did eventually allow me to update our sites.
For now, I still have a copy of one of our non-updated databases so I am more than happy to review any patches or modifications to see if it this issue could be resolved for other sites that may run into this problem.
Comment #4
davidsheart02 CreditAttribution: davidsheart02 commented