forum.coppermine-gallery.net

Support => cpg1.3.x Support => Older/other versions => cpg1.3 Miscellaneous => Topic started by: Laffer on June 16, 2004, 11:16:26 am

Title: Modify Albums settings - does not Work (HORROR SQL statement in code?)
Post by: Laffer on June 16, 2004, 11:16:26 am

My installation (30.000 Users, 30.000 Pics) crashes almost when trying to modify settings of Albums. If I look in mysql -> Processlist I get Millions of queries LOCKED because of something starting with:

| 290 | admin | localhost | comicfandb | Query   | 76   | Copying to tmp table | SELECT aid, IF(username IS NOT NULL, CONCAT('(', username, ') ', title), CONCAT(' - ', title)) AS ti |

All other queries behind this one are locked, I think this one makes a query to cpg_pictures and nuke_users at the same time. Please HELP!

Title: Re: Modify Albums settings - does not Work (HORROR SQL statement in code?)
Post by: Laffer on June 16, 2004, 11:32:25 am

I found this one in modifyalb.php:

$sql = "SELECT aid, IF($field_user_name IS NOT NULL, CONCAT('(', $field_user_name, ') ', title), CONCAT(' - ', title)) AS title " . "FROM {$CONFIG['TABLE_ALBUMS']} AS a " . "LEFT JOIN {$CONFIG['TABLE_USERS']} AS u ON category = (" . FIRST_USER_CAT . " + $field_user_id) " . "ORDER BY title";

this seems to be a killer query. Joining two tables without using indexes in the JOIN field and ordering without index. It is running minutes and the worst:

It locks all other queries to the cpg_albums and nuke_users table....

Is there any other way????

Title: Re: Modify Albums settings - does not Work (HORROR SQL statement in code?)
Post by: Casper on June 16, 2004, 12:43:33 pm

This code does not appear in coppermine 1.3.0 standalone, and as you seem to be running a nuke site, I suspect you have the nuke version of coppermine(at least you should have).
For coppermine for CMS support you should go to www.nukephotogallery.com