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?

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

davidsheart02 created an issue. See original summary.

DamienMcKenna’s picture

Status: Active » Needs review
FileSize
1.82 KB

Are 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?

davidsheart02’s picture

For 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.

davidsheart02’s picture

Status: Needs review » Needs work