Hi

I replaced my privatemsg 2.x. module with latest dev 2.x., I try update.php, it was loading and then stops and then and list of messages stop working, sending message stop working, and server fall down,

when I try update.php again, there is shown that privatemsg wants update 6204 and privatemsg_filter wants update 6202 , but after pressing update no update is proceeded, there is just the update bar with word starting updates forever.

so now I can't use privatemsg module in my site, and probably this select which now kill server:
SELECT pmi.thread_id, MIN(pm.subject) as subject, MAX(pm.timestamp) as
>> last_updated, SUM(pmi.is_new) as is_new, COUNT(distinct pmi.mid) as
>> count, (SELECT GROUP_CONCAT(DISTINCT CONCAT(pmia.type, '_',
>> pmia.recipient) SEPARATOR ',')
>> FROM pm_index pmia
>> WHERE
>> pmia.type = 'user' AND pmia.thread_id = pmi.thread_id AND
>> pmia.recipient <> 2931) AS participants FROM pm_message pm INNER JOIN
>> pm_index pmi ON pm.mid = pmi.mid INNER JOIN pm_tags_index pmti0 ON
>> (pmti0.thread_id = pmi.thread_id AND pmti0.uid = pmi.recipient AND
>> pmi.type IN ('user', 'hidden')) WHERE (pmi.recipient = 2931 AND
>> pmi.type IN ('user', 'hidden')) AND (pmi.deleted = 0) AND
>> (pmti0.tag_id = 2) GROUP BY pmi.thread_id ORDER BY last_updated
>> DESC LIMIT 0, 10

Comments

igorik’s picture

igorik commented

btw I have 350,000 rows in privatemsg table

igorik’s picture

igorik commented

I found that that update from last 2.x dev removed indexes from my privatemsg table and privatemsg_index table

So I manually added these indexes (based on my older backup of these tables) and copy previous version of privatemsg 2.x. and now it works. I am afraid to try latest 2.x version again.

If someone will be has same problem, it is this:

TABLE pm_message
there was (in my case) missing index for author (field name author)

TABLE pm_index
missing indexes:
mid (field name mid)
thread_id (field name thread_id)
recipient (field names: recipient, type)
is_new (field names: mid, recipient, type, is_new)

all indexes are type:normal

berdir’s picture

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

Yes, but in the same step, new indexes should have been added which are better.

Adding these indexes can be a relatively slow process and you probably interrupted that.

I suggest you run the update again on a empty table, this will show you the queries which would have added the indexes. Then, run these queries manually on your live database to see how long they take.

igorik’s picture

igorik commented

thanks for reply Berdir.

update process in my case takes more that 50 minutes or more, and It was still just with "Starting updates" and without info about percentage. I didn't expect that it can takes so much time and I think that at least in one case update process killed itself.

On the evening, I will try to run query directly on db as you suggest.

berdir’s picture

berdir
Primary language German
Location Switzerland
commented

Strange, it certainly shouldn't take *that* long. Maybe it was a locking problem and something just waited endlessly.

Yes, please execute the queries manually and report back.

Nobody else has reported something like that yet, so...

igorik’s picture

igorik commented
Status: Postponed (maintainer needs more info) » Closed (works as designed)

Hi

it was probably really only some temporarily error on my server (locked tables/overhaul db)
I tried it again with maintenance mode turned on and update works correctly (it takes about 5 minutes cca)

Thanks
Igor