forum.coppermine-gallery.net
Support => cpg1.4.x Support => Older/other versions => cpg1.4 miscellaneous => Topic started by: drummerkid08 on February 06, 2006, 09:36:04 pm
-
everything in my gallery seems to work fine. however when i click on "users" in the admin panel, it always times out. is there something i can do to fix this?
this is my site: www.shineon-media.com/gallery
and if you need to login, here's a temp username: drummerkid08 password: sturunnr
-
You don't allow users to view the memberlist, so there is nothing for us to see at your site. How many users do you have ?
-
okay, i've changed it so you can view the memberlist. i'm not sure how many users i have, which is why i wanted to check the user manager.
-
does it time out because i have too many users registered?? i need to add some people but it still times out. is there a way to get to that page a different way?
-
How many users do you have ?
-
i'm not sure how many users i have, which is why i wanted to check the user manager.
-
phpMyAdmin?
-
I guess drummerkid08 doesn't know phpMyAdmin....
@drummerkid08: If you know phpMyAdmin or any other database management tool then browse your database and see how many records are there in cpg_users table (cpg_ should be replaced by prefix you have used).
-
it says 1,675
-
uhmm, I dunno.. but what for is
'' as user_active,
in the SQL in function get_users
SELECT {$f['user_id']} as user_id, {$f['username']} as user_name, {$f['email']} as user_email, {$f['regdate']} as user_regdate, lastvisit as user_lastvisit, '' as user_active,
-
i'm not quite sure....is it not supposed to be there?
-
uhmm, I dunno.. but what for is '' as user_active,
in the SQL in function get_users
SELECT {$f['user_id']} as user_id, {$f['username']} as user_name, {$f['email']} as user_email, {$f['regdate']} as user_regdate, lastvisit as user_lastvisit, '' as user_active,
where exactly would i find that code.
-
that was a question to the other devs... but you can find it in bridge/mybb.inc.php
-
oh, okay. so is there anything i can do?
-
If you are happy to PM me FTP and db access to your gallery then I can take a look for you.
Stramm, if you wish to discuss unrelated coding issues please do so on the dev or coding boards.
-
okay. i pm'ed you. thanks :)
-
It's the main query that's the problem here:
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 `shineon_copp1`.cpg143_users AS u
INNER JOIN cpg143_usergroups AS g ON u.user_group = g.group_id
LEFT JOIN cpg143_pictures AS p ON p.owner_id = u.user_id
GROUP BY user_id
ORDER BY user_regdate DESC
LIMIT 0 , 25
It takes several minutes to run due to the fact that it's not using any keys in the joins (see pic). All the keys are there that should be according to the schema.
I don't know what to suggest to solve this other than to get your mysql updated to the latest stable version. Maybe another supporter will come along and suggest something.
-
hmmm...okay. thanks for trying. i don't know if i can update my mysql...i think my webhost has to do that...what's the newest version, because right now i have 4.1.14-standard.
-
I fixed it :D
Solution was to force the index in udb_base.inc.php
// 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']};";
-
OMFG!!! Thank you sooooo much Nibbler, you're a lifesaver. I really appreciate all your help.
-
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!!!
-
I fixed it :D
Solution was to force the index in udb_base.inc.php
// 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
-
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 ::).
-
I fixed it :D
Solution was to force the index in udb_base.inc.php
// 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)
-
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.
-
ah ok, thanks Nibbler. It works now :)
-
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
-
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).
-
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.
-
You have an index missing. Ensure your mysql user has the ALTER privilege and run update.php.
-
Where is the udb_base.inc.php file supposed to be? I cannot find it in my includes folder.
-
its in 'bridge' directory.
-
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
-
i have money here, give me your account number.
see We need your help (http://forum.coppermine-gallery.net/index.php?topic=14227.0)
-
This solution also works in 1.5.x. Is there a problem with the solution that prevents it from becoming core code?
-
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.
-
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.
-
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.