1.4.6 -> 1.4.7: MySQL hangs + takes whole system with it (lastalb broken?)

[FireMotion]

Attempting to upgrade 1.4.6 to 1.4.7
When all the php files are uploaded (a custom theme is being used):

On Linux, PHP 4.4.2, MySQL 4.1.19-standard:
* When attempting to load the gallery url, nothing happens.
* update.php seems to hang somewhere (somewhere after UPDATE CPG_config SET value='ALL' WHERE name='allowed_img_types'; -- this might've been because the MySQL thread already died or is too busy)
* In addition, it seems that the MySQL thread died on my host.

On Windows, PHP 5.1.2, MySQL 5.0.21-community-nt:
* When attempting to load the gallery url, the mysql-nt service hogged the system (near 100% CPU - Windows became instable).
* After killing the mysql-nt service, one time, the albums were shown (but not the latest/random pics parts below that).
But most of the time nothing is shown.

Any suggestions?

[Edit: Updated title to reflect current status]

[Paver]

A link to your site might be helpful.

The update.php script merely does SQL updates and is identical to the 1.4.6 script.  So if you used the 1.4.6 update.php script when you did that upgrade, this one should be no different.

You might want to run versioncheck.php to see what it shows for your files.  Maybe some didn't get updated to 1.4.7.  But this wouldn't affect update.php, as far as I can see.

I don't think the custom theme is at fault, but you can set the theme to classic in the URL using "?theme=classic".  This will set it only for you, and you can clear this by clearing your browser's cookie for your domain.

[FireMotion]

Hi Paver and the rest of the CPG community, thanks for your reply, but the problem still exists.

1) You're right, the update.php has nothing to do with it.
2) Furthermore, I tried running it with the default classic theme, but to no avail, so the theme is not the problem.
3) versioncheck.php says all files are good (it's all in green).

Other than updating all the files, I didn't change anything to the configuration.

The problem still persists.

A link to my site: (Link removed because the source of the issue has been discovered -- see below)
I reverted back to 1.4.6 because I don't want my host/site to die again, everything works normally with 1.4.6 (with the patch applied).
A small note: I have 6744 images in 132 albums, might 1.4.7 have volume problems?

It seems that other sections of the gallery do work (logging in, last uploads, last comments, most viewed, etc. the admin features), everything except the Album List (both user and admin mode) which seems utterly broken (= it hogs MySQL insanely).

[Nibbler]

Can you remove 'lastalb' from 'contents of the main page' in config and then try the update again ?

[FireMotion]

Nibbler:
I now changed the main page layout from:
breadcrumb/catlist/alblist/lastalb,1/random,1
to
breadcrumb/catlist/alblist/random,1

This seems to fix (=work around) the problem.

Does this mean that "<i>2006-06-06 (B) Fixed 'lastalb' bug for MySQL 4.1+ (known issue for 3.23, 4.0) {Paver}</i>" needs fixing?

[Paver]

<insert very bad word here>  Argh!!!! 

I have confirmed this serious stability issue on my large gallery.  I had tested it on my testbed, which has unfortunately only a handful of photos on it.

I've made "1.4.7 (stable)" not stable for those who use 'lastalb'.   

I'll stop fixing bugs in stable.  That's the second one I've made worse.

Moving to bugs board.  Opening thread on dev-only board.

[FireMotion]

Nah, it just means your need a second testbed.
Or just your current one, but with a big number of files in it.

Anyway, at least we/you know what is wrong.
Hope a cpg1.4.7.1 can be released (or 1.4.8 whatever. Because the site says the upgrade is mandatory, it'd be bad if other people would be affected by the lastalb issue while fixing vulnerabilities).

[Paver]

I agree.  I suggested as such on the dev-only board.

The original bug "fix" (which is so sad to say right now) is here.

To reverse it, do the following.  In functions.inc.php, in the get_pic_data() function, delete these lines:

Code Select Expand

// Try query with sub-query (only valid for MySQL 4.1 and up)
$META_ALBUM_SET_LASTALB = str_replace($CONFIG['TABLE_PICTURES'],'p1',$META_ALBUM_SET);
$query = "SELECT *,a.title AS title,a.aid AS aid FROM {$CONFIG['TABLE_PICTURES']} AS p1,{$CONFIG['TABLE_ALBUMS']} AS a WHERE p1.ctime = (SELECT MAX(p2.ctime) FROM {$CONFIG['TABLE_PICTURES']} AS p2 WHERE p1.aid = p2.aid AND p1.aid = a.aid AND p1.approved = 'YES' $META_ALBUM_SET_LASTALB) ORDER BY ctime DESC $limit";
// This query will not be logged, but this is the only way to stop an output error page if sub-query is not allowed
$result = mysql_query($query);
if (!$result) {
    // Must be running MySQL 3.23.x or 4.0.x, so use this incorrect query
    // Need to fix this query to return the correct order of albums and correct thumbnails
    $query = "SELECT *,{$CONFIG['TABLE_ALBUMS']}.title AS title,{$CONFIG['TABLE_ALBUMS']}.aid AS aid FROM {$CONFIG['TABLE_PICTURES']},{$CONFIG['TABLE_ALBUMS']} WHERE {$CONFIG['TABLE_PICTURES']}.aid = {$CONFIG['TABLE_ALBUMS']}.aid AND approved = 'YES' $META_ALBUM_SET GROUP BY {$CONFIG['TABLE_PICTURES']}.aid ORDER BY {$CONFIG['TABLE_PICTURES']}.ctime DESC $limit";
    $result = cpg_db_query($query);
}


and put these two lines in its place:

Code Select Expand

$query = "SELECT *,{$CONFIG['TABLE_ALBUMS']}.title AS title,{$CONFIG['TABLE_ALBUMS']}.aid AS aid FROM {$CONFIG['TABLE_PICTURES']},{$CONFIG['TABLE_ALBUMS']} WHERE {$CONFIG['TABLE_PICTURES']}.aid = {$CONFIG['TABLE_ALBUMS']}.aid AND approved = 'YES' $META_ALBUM_SET GROUP  BY {$CONFIG['TABLE_PICTURES']}.aid ORDER BY {$CONFIG['TABLE_PICTURES']}.ctime DESC $limit";
$result = cpg_db_query($query);

edit: Committed fix reversal to stable & devel.

[FireMotion]

Big (sub)queries scare me. But I guess it scares you too (at least now they do).
I tried to make sense of the 1.4.7 version of it, but can't (well, I know how the whole things works, but just don't really know what you're trying to do with the query).

Anyway, thanks for the "fix," and a whole of a good luck fixing it.

[FireMotion]

By the way, in the 1.4.7 query:
AND p1.approved = 'YES'

Shouldn't p2 be approved too? AND p2.approved = 'YES'
Not that this fixes this problem (I don't think I have any non-approved pictures in my gallery; but theoretically, I think it might fix another problem, hehe: Because there might not be a p1.ctime = MAX(p2.ctime) with p1 approved, but p2 might not be approved).

Anyway, that's all I can figure out right now. You're on your own for the rest of the query (ugh, this sounds too nerdy).