During the update procedure, the following query has been running for 35 minutes and has still not completed:

/* : privatemsg_update_6003 */ SELECT DISTINCT p1.mid, p1.uid FROM pm_index p1 INNER JOIN pm_index p2 ON p1.thread_id = p2.thread_id AND p1.mid = p2.mid INNER JOIN pm_message pm ON p1.uid = pm.author AND p2.uid = pm.author WHERE p1.is_new <> p2.is_new

Here are the sizes of the my relevant tables:

mysql> select count(*) from pm_index;
+----------+
| count(*) |
+----------+
| 162136 |

mysql> select count(*) from pm_message;
+----------+
| count(*) |
+----------+
| 33782 |

My question is, should I wait for this query to complete, or is it one of those NP problems where the sun will burn out before the query returns? :)

Comments

jswap’s picture

jswap commented

BTW, I finally killed the query after 54 minutes.

berdir’s picture

berdir
Primary language German
Location Switzerland
commented

That is a slow query, but it shouldn't be that slow. Any chance that you can anonymize your database (pm_index and pm_message) table so that I can debug it?

The actualy D5 -> D6 upgrade is only 6000, this is already something that happened during the development of the D6 version.

Try something like this anonymize it (on a copy of the data...)

UPDATE pm_message pm, pm_index pmi SET pm.subject = CONCAT('Subject of thread ',  pmi.thread_id), pm.body = CONCAT('Body of message ', pm.mid) WHERE pm.mid = pmi.mid AND pm.author = pmi.uid

Then I can debug it myself.

berdir’s picture

berdir
Primary language German
Location Switzerland
commented

Also, that update function is only does some clean up. you can try to comment it out and see if the update process finishes then.

berdir’s picture

berdir
Primary language German
Location Switzerland
commented
Status: Active » Postponed (maintainer needs more info)

Note, I did run the query on a big dataset ( 930k rows in pm_index) and it took ~12s. Still quite slow, but nothing like yours. A difference is that the query didn't return anything in my dataset, because that update did already run on that data.

berdir’s picture

berdir
Primary language German
Location Switzerland
commented
Status: Postponed (maintainer needs more info) » Fixed

Did not receive any feedback so I have to assume that this is fixed.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.