user manager times out...

[lordprodigy]

yah, I have the same problem with only 472 users registered I will try the fix.

update: fix worked beautifully. reload was so fast... thanks Nibbler!!!

[pokk]

I fixed it

Solution was to force the index in udb_base.inc.php

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']};";


How this code would be with 1.3.5? I have critical error: there was an error while processing a database query.l

[Joachim Müller]

don't try to apply code that was made for cpg1.4.x to a cpg1.3.x install. Undo your changes and restore the original file. There's a reason why we have different support boards for different coppermine versions .

[kateheaven]

I fixed it

Solution was to force the index in udb_base.inc.php

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']};";


I'm having the same problem with the user manager timing out.
which part of udb_base.inc.php do i add this code into? i added it to the end of it (before ?>) and got this error message > Fatal error: Using $this when not in object context in /path/to/bridge/udb_base.inc.php on line 801
i'm using 1.4.9 (stable)

[Nibbler]

You need to replace code, not add code. Search the file for  'Build SQL table, should work with all bridges' to find the bit to replace.

[kateheaven]

ah ok, thanks Nibbler. It works now

[kateheaven]

does this work with the latest coppermind version? i've tried adding the code in but i get a 'database error' message when I open my user manager page...

I'm using v 1.4.9

[Joachim Müller]

The name of the app is "Coppermine", not "Coppermind". Most recent stable release is cpg1.4.10, not cpg1.4.9.
Hack should work in all cpg1.4.x versions. If you have an error message, enable debug_mode and post the actual error message (not the debug_output).

[kateheaven]

the 'd' instead of the 'e' was a typo - easy mistake.

this is what I get after enabling the debug mode, hope it's the right thing to post.

'While executing query "SELECT user_id as user_id, user_name as user_name, user_email as user_email, UNIX_TIMESTAMP(user_regdate) as user_regdate, UNIX_TIMESTAMP(user_lastvisit) as user_lastvisit, user_active as user_active, COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota FROM `nkidhwx_copp1`.cpg11d_users AS u INNER JOIN cpg11d_usergroups AS g ON u.user_group = g.group_id LEFT JOIN cpg11d_pictures AS p FORCE INDEX ( owner_id ) ON p.owner_id = u.user_id GROUP BY user_id ORDER BY user_regdate DESC LIMIT 0, 25;" on Resource id #5

mySQL error: Key column 'owner_id' doesn't exist in table'


This was after adding the code in this thread into the udb_base.inc.php file.

[Nibbler]

You have an index missing. Ensure your mysql user has the ALTER privilege and run update.php.

[marpessa]

Where is the udb_base.inc.php file supposed to be? I cannot find it in my includes folder.

[Abbas Ali]

its in 'bridge' directory.

[socomoroco]

this is great, i was having the same problem, thanks nibbler.

i have money here, give me your account number.


i am using 1.4.14

[Joachim Müller]

i have money here, give me your account number.

see We need your help

[Jeff Bailey]

This solution also works in 1.5.x. Is there a problem with the solution that prevents it from becoming core code?

[Nibbler]

It's not really a solution - it's a very specific hack to fix this rare case. It may well cause problems if applied to galleries with different characteristics and/or other versions of MySQL. Can you reproduce this with the current version of MySQL (5.1.50)? If so then it might be worth filing a bug with them.

[Jeff Bailey]

It happened here => http://forum.coppermine-gallery.net/index.php/topic,66902.0.html
The version he is running is: 5.0.67
This was probably just a rare case like you said.

I'm not sure how to reproduce it, something to do with a large amount of users and a server/MySQL timeout?

If you think I should start a bug report I'll use his information.

[Nibbler]

From what I can remember it happens when the gallery has plenty of users, but all the pictures are uploaded by just one user (the admin). MySQL decides not to use the index that we have on owner_id due to the low cardinality (number of different values) of the index. As a result of this the query takes too long.