[Solved]: I can't view the users anymore after upgrading

[Laicodc]

I upgraded today from 1.4.10 to 1.4.20 and I just noticed that when I click on the users tab it doesn't work.  I really need to be able to look at the registered users.  Any ideas what to check?
Thanks!
Marc

[Laicodc]

I upgraded today from 1.4.10 to 1.4.20 and I just noticed that when I click on the users tab it doesn't work.  I really need to be able to look at the registered users.  Any ideas what to check?
Thanks!
Marc

[Laicodc]

Everything else is working.  I just can't get usermgr.php to work.   any help is greatly appreciated.  Thanks

[Nibbler]

Maybe if you ask some more that will help...

Post a link to your gallery, exactly what you mean by "doesn't work" and mention how many users you have (roughly). There's also a known problem if you are the sole uploader to the gallery, so mention if that applies to you and I'll dig up the fix.

[Laicodc]

My gallery url is: http://laicodc.com/gallery

What I mean by doesn't work is that when clicking on the user tab (which points to http://laicodc.com/gallery/usermgr.php)  it hangs for a long time and then comes back with this error:

"Critical error
There was an error while processing a database query"

All the other php files under gallery seem to work fine.  Only the usermgr.php is having problems.   I am the sole uploader to the gallery and I have about 4 or 5 thousand registered users.   

Thanks for your help. 

[Laicodc]

The script appears to be taking too long to search the database and is getting killed by one of the resource scripts.


[ 1234925881 ] [ laicodc_laicobot ] [ 956503 ] [ 49 ] [ laicodc_photo ] [ Query ] [ KILLED ] [ gator539.hostgator.com ] [ SELECT user_id as user_id, user_name as user_name, user_email as user_email, UNIX_TIMESTAMP(user_reg ]
[ 1234925881 ] [ laicodc_laicobot ] [ 956510 ] [ 44 ] [ laicodc_photo ] [ Query ] [ KILLED ] [ gator539.hostgator.com ] [ UPDATE cpg1410_pictures SET hits=hits+1, lasthit_ip='68.100.147.134', mtime=CURRENT_TIMESTAMP WHERE  ]
[ 1234925881 ] [ laicodc_laicobot ] [ 956514 ] [ 42 ] [ laicodc_photo ] [ Query ] [ KILLED ] [ gator539.hostgator.com ] [ SELECT COUNT(*) from cpg1410_pictures WHERE ((aid='231'  AND aid NOT IN (4) ) ) AND approved='YES' A ]
[ 1234925881 ] [ laicodc_laicobot ] [ 956531 ] [ 35 ] [ laicodc_photo ] [ Query ] [ KILLED ] [ gator539.hostgator.com ] [ SELECT COUNT(*) from cpg1410_pictures WHERE ((aid='229'  AND aid NOT IN (4) ) ) AND approved='YES' A ]
[ 1234925881 ] [ laicodc_laicobot ] [ 956533 ] [ 34 ] [ laicodc_photo ] [ Query ] [ KILLED ] [ gator539.hostgator.com ] [ UPDATE cpg1410_pictures SET hits=hits+1, lasthit_ip='68.100.147.134', mtime=CURRENT_TIMESTAMP WHERE  ]
[ 1234925881 ] [ laicodc_laicobot ] [ 956538 ] [ 32 ] [ laicodc_photo ] [ Query ] [ KILLED ] [ gator539.hostgator.com ] [ SELECT COUNT(*) from cpg1410_pictures WHERE ((aid='231'  AND aid NOT IN (4) ) ) AND approved='YES' A ]
[ 1234925881 ] [ laicodc_laicobot ] [ 956541 ] [ 32 ] [ laicodc_photo ] [ Query ] [ KILLED ] [ gator539.hostgator.com ] [ UPDATE cpg1410_pictures SET hits=hits+1, lasthit_ip='68.100.147.134', mtime=CURRENT_TIMESTAMP WHERE  ]
[ 1234925881 ] [ laicodc_laicobot ] [ 956545 ] [ 30 ] [ laicodc_photo ] [ Query ] [ KILLED ] [ gator539.hostgator.com ] [ SELECT COUNT(*) from cpg1410_pictures WHERE ((aid='229'  AND aid NOT IN (4) ) ) AND approved='YES' A ]
[ 1234925881 ] [ laicodc_laicobot ] [ 956564 ] [ 18 ] [ laicodc_photo ] [ Query ] [ KILLED ] [ gator539.hostgator.com ] [ SELECT COUNT(*) from cpg1410_pictures WHERE ((aid='229'  AND aid NOT IN (4) ) ) AND approved='YES' A ]


I did go ahead and run optimize table on the database tables but this hasn't helped. Is there something that can be done to speed up the mysql queries? 

[Laicodc]

Here is the code from debug:

[Nibbler]

See http://forum.coppermine-gallery.net/index.php/topic,27540.0.html

[Laicodc]

Thanks Nibbler,  I read that thread and see that you found a fix where you force the index in udb_base.inc.php.  Can you take a look at mine and see if that will work for me?  or can you tell me where to change the code so I can try it?   I can pm you my login info and anything else you need.  I really appreciate your help.  If you're ever in DC you get a Table and $300 bar tab on me!

[Nibbler]

Find

Code Select Expand


                // Build SQL table, should work with all bridges
        $sql = "SELECT {$f['user_id']} as user_id, {$f['username']} as user_name, {$f['email']} as user_email, {$f['regdate']} as user_regdate, {$f['lastvisit']} as user_lastvisit, {$f['active']} as user_active, ".
               "COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota ".
               "FROM {$this->usertable} AS u ".
               "INNER JOIN {$C['TABLE_USERGROUPS']} AS g ON u.{$f['usertbl_group_id']} = g.group_id ".
               "LEFT JOIN {$C['TABLE_PICTURES']} AS p ON p.owner_id = u.{$f['user_id']} ".
               $options['search'].
               "GROUP BY user_id " . "ORDER BY " . $sort_codes[$options['sort']] . " ".
               "LIMIT {$options['lower_limit']}, {$options['users_per_page']};";


Change to

Code Select Expand


                // Build SQL table, should work with all bridges
        $sql = "SELECT {$f['user_id']} as user_id, {$f['username']} as user_name, {$f['email']} as user_email, {$f['regdate']} as user_regdate, {$f['lastvisit']} as user_lastvisit, {$f['active']} as user_active, ".
               "COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota ".
               "FROM {$this->usertable} AS u ".
               "INNER JOIN {$C['TABLE_USERGROUPS']} AS g ON u.{$f['usertbl_group_id']} = g.group_id ".
               "LEFT JOIN {$C['TABLE_PICTURES']} AS p FORCE INDEX ( owner_id ) ON p.owner_id = u.{$f['user_id']} ".
               $options['search'].
               "GROUP BY user_id " . "ORDER BY " . $sort_codes[$options['sort']] . " ".
               "LIMIT {$options['lower_limit']}, {$options['users_per_page']};";


[Laicodc]

oh, ok, which file?  usermgr.php or that other file udb_base.inc.php

[Nibbler]

udb_base.inc.php

[Laicodc]

I did it.  THANK YOU SO MUCH