25 August 2011, 18:16

This is the upgrade that adds an author to all the existing user messages so that we can have threaded messaging when you reply to a user message.

One thing that might help is to delete some of the old useless notifications before the upgrade.  There's a cron job to do this now, but it wasn't there in 1.2, and the table does get far too big.  Something like this:

DELETE FROM notification_internal_activity WHERE type IN (
  SELECT id FROM activity_type WHERE name IN ('newpost', 'feedback', 'watchlist', 'viewaccess', 'institutionmessage')
) AND ctime < current_timestamp - interval '2 months';

But that doesn't delete the user message notifications, and we don't really want to do that, so it probably won't help with your problem unless it's the actual indexing into the table that's slow rather than the memory consumed by 6000 records.

What we probably should do is rewrite that upgrade so it either reads the records in smaller chunks instead of all in one go, or better, rewrite it to update the entire table with one query by moving that /sendmessage\.php\?id=(\d+)/ regex into the SQL command.

26 August 2011, 8:52

That's the one :).

I performed this query:

DELETE FROM notification_internal_activity WHERE type IN (   SELECT id FROM activity_type WHERE name IN ('newpost', 'feedback', 'watchlist', 'viewaccess', 'institutionmessage') ) AND ctime < '2011-05-01';

...and the problem went away. That deleted just short of 100,000 rows for me btw. 

I'm actually more concerned that a query can fail (or time out) with no error message at all. If I'd got a sensible error message this would probably have been solved a week ago!

Thanks for all the help - got there in the end.

29 August 2011, 16:17

Grrr... I didn't get there. It now works sometimes on some servers. 

I found what look like some bugs in the MySQL code (discussed in Developer forum) but, basically, I'm getting increasingly worried that MySQL is simply not well tested or supported. 

I'm continuing to work on this but it's basically wiping out of the upgrade in the middle of some sql queries. Adding keys and constraints seem to be particularly prone to problems. There is never an error logged so I am now wondering if the exception handling is broken somewhere. 

It is tempting to recode the upgrade script without all the javascript magic that drives it. That always seemed a bit risky to me.