There was an error while processing a database query

[banburymike]

I know this is a thorny subject.. sorry!

recently I've been getting the error...
Notice: Undefined index: debug_mode in copperminegallery/include/functions.inc.php on line 96
Fatal error :
There was an error while processing a database query

As far as I can work out at the moment is that I am overloading my web hosts data base server

My host server allows for 50.000 queries per hour, if this is exceeded it slings you out by the ear 

fair enough I suppose...... my problem is that I am running quite a large gallery, now up to 17.000 images and rising... My guess (again) is that... hitting the search, most viewed or last viewed buttons on the index page can result in a large query count? doing this a few times and the server is tripped?

My questions, do yo think I am right?

Taking out the search, most viewed or last viewed buttons seems to be a quick fix, (I'm not bothered about losing the search button)
but is there a way of only allowing the last/most viewed function on individual albums? (which each only contain a couple of hundred images), rather than on the whole gallery?

Be gentle with me, I have a small Brain  :-\

[Joachim Müller]

enable debug_mode, disable notices, paste the actual error message (not the debug_ouput) into your reply here. Post a link to your site as well.

[banburymike]

enable debug_mode, disable notices, paste the actual error message (not the debug_ouput) into your reply here. Post a link to your site as well.

Thank you for your quick reply,
My hosts server still hasn't reset so my gallery is unatainable at present,
I will debug when I get access back, this took 8 hours last time... real pain in the ****

http://www.banburymike.net/copperminegallery

[Joachim Müller]

you appear to be running cpg1.3.3, I highly recommend upgrading!

[banburymike]

you appear to be running cpg1.3.3, I highly recommend upgrading!

Yes, I've got myself in a bit of bother, my main gallery is cpg1.3.3. which holds about 17000 images in 200+ albums, I've been reluctant to make changes to it,  thinking that I may mess it up and have to start again from scratch.

I am also running seperatly a cpg1.4.3  gallery which I like, I don't know if I can convert my cpg1.3.3. to cpg1.4.4 without losing all of my cpg1.3.3 album info

also I have the paypal hack on both galleries, on the cpg1.3.3 I use the batch pricing, but on cpg1.4.3 you add the catagory price for each image, I dread the thought of having to add the catagory price to 17000 images

My server is still not back up, but hopefully when it is you may be able to put me straight :-)


Just as an extra thought.... If the problem is the database being tripped by an excessive query count, would that mean that it won't show in coppermine debug? (edited by Mike) ;-)

Regards

Mike

[Joachim Müller]

your host has nothing to do with this (or rather: don't expect help from that end). Enable debug mode manually by using a third-party database manipulation tool like phpMyAdmin, browse coppermine's config table and set "debug_mode" to "1".
Chose your wording more carefully, a "bug" is not the same as "debug_mode".

[banburymike]

your host has nothing to do with this (or rather: don't expect help from that end). Enable debug mode manually by using a third-party database manipulation tool like phpMyAdmin, browse coppermine's config table and set "debug_mode" to "1".
Chose your wording more carefully, a "bug" is not the same as "debug_mode".

Edited the B word.... sorry

Right my gallery is back up, altered "debug mode to "1"

Do you want me to post the debug output here or send it in a PM?
http://www.banburymike.net/copperminegallery/index.php

[Joachim Müller]

If your gallery is back up and running, you don't need to send anything at all. If you still experience issues, post the error message (not the debug_output) publicly (do not PM me).

[banburymike]

If your gallery is back up and running, you don't need to send anything at all. If you still experience issues, post the error message (not the debug_output) publicly (do not PM me).

The fact that I can't post an error message is the issue (or part of it)

when I clock up an excess query count (over 50.000 in an hour) my data base server cuts me off, fair enough I suppose and not your problem
I am then unable to enter debug mode because the server is offline, the server resets every hour, so my gallery eventually re appears, with no errors,
My issue is that, this is a problem that has happened 5 times now,
so I'm trying to find out if this is a software or host problem,

In debug mode I notice that viewing 1 image in an album can amount to 16 database queries
(Page generated in 0.746 seconds - 16 queries in 0.685 seconds - Album set :

It doesn't take much maths to work out that, 3125 image views can produce 50.000 queries, and crash goes the server,
it is not unusual for me to get over 3000 views in an hour so I now understand why my server keeps crashing.

Now my problem is how to get this fixed..........

This is the reply that I got from my host regarding the issue....
------------------------------------------------------------------------------------------------------------------------------------------------------------
Technical Support.

The reason you are receiving that error is due to the 'max_questions' setting which is set to 50,000 per user per hour. This setting is there to ensure that the mysql database cannot be accessed to much by one user and overload the system. The connections will reset every hour and is kept by user. My suggestion to alieviate this issue is to break up your queries between multiple users which would distribute the 50,000 connection queries to multiple users.

-------------------------------------------------------------------------------------------------------------------------------------------------------------

Is setting multiple users an option? or is there another way around the problem?

I suppose I could just change host, but that would be a real pain.

Regards

Mike

[Stramm]

I think you just need a better server. I had 1.3x running with nearly 20k users and pics that.. with no problem at all

[banburymike]

I think you just need a better server. I had 1.3x running with nearly 20k users and pics that.. with no problem at all

I think you may well be right Stramm 

[banburymike]

Servers gone again 

Does this fix mean anything to anyone?

------------------------------------------------------------------------------------------------------------------------
My suggestion to alieviate this issue is to break up your queries between multiple users which would distribute the 50,000 connection queries to multiple users.
------------------------------------------------------------------------------------------------------------------------

[Nibbler]

If your limit is set to 50k per mysql user then you can create several users to distribute the load. You should be able to do this by adding code to randomly pick a db user + pass from a list in include/config.inc.php

[banburymike]

If your limit is set to 50k per mysql user then you can create several users to distribute the load. You should be able to do this by adding code to randomly pick a db user + pass from a list in include/config.inc.php

Thank you that sounds just what I need,

The problem,.... I have no coding knowledge,
Any chance of guiding an idiot,.... or would it be too time consuming?

Mike

[Nibbler]

Just something like this

Code Select Expand


<?php
// Coppermine configuration file

// MySQL configuration
$CONFIG['dbserver'] =                         'localhost';        // Your databaseserver
$CONFIG['dbname'] =                         'coppermine';        // Your mysql database name
$CONFIG['TABLE_PREFIX'] =                'cpg132_';

if (rand(0,1)){
$CONFIG['dbuser'] =                         'user1';        // Your mysql username
$CONFIG['dbpass'] =                         'pass1';                // Your mysql password
} else {
$CONFIG['dbuser'] =                         'user2';        // Your mysql username
$CONFIG['dbpass'] =                         'pass2';                // Your mysql password
}

?>


[banburymike]

Thank you Nibbler,

When my server finally resets I'll have a play with the "config.inc.php"
and see what happens 

Cheers!

[banburymike]

Nibbler
This is how my config.php looks before any mod, (password and username removed)

<?php
// Coppermine configuration file

// MySQL configuration
$CONFIG['dbserver'] =                         'localhost';        // Your database server
$CONFIG['dbuser'] =                         '****';        // Your mysql username
$CONFIG['dbpass'] =                         '****';                // Your mysql password
$CONFIG['dbname'] =                         '****_copperminegallery';        // Your mysql database name


// MySQL TABLE NAMES PREFIX
$CONFIG['TABLE_PREFIX'] =                'cpg133_';
?>

Would you mind setting me straight on where I should add the new code?  please 

[Tranz]

You replace the content of the file.

[banburymike]

Sorry to be a pain, but If I want to add more that 2 usernames would this be right?

<?php
// Coppermine configuration file

// MySQL configuration
$CONFIG['dbserver'] =                         'localhost';        // Your databaseserver
$CONFIG['dbname'] =                         'coppermine';        // Your mysql database name
$CONFIG['TABLE_PREFIX'] =                'cpg132_';

if (rand(0,1)){
   $CONFIG['dbuser'] =                         'user1';        // Your mysql username
   $CONFIG['dbpass'] =                         'pass1';                // Your mysql password
} else {
   $CONFIG['dbuser'] =                         'user2';        // Your mysql username
   $CONFIG['dbpass'] =                         'pass2';                // Your mysql password
} else {
   $CONFIG['dbuser'] =                         'user3';        // Your mysql username
   $CONFIG['dbpass'] =                         'pass3';                // Your mysql password
} else {
   $CONFIG['dbuser'] =                         'user4';        // Your mysql username
   $CONFIG['dbpass'] =                         'pass4';                // Your mysql password
}

?>

[kegobeer]

You can't have multiple else statements.

Code Select Expand

if (something) {
code
} else {
other code
}


If you want multiple if else

Code Select Expand


if (something) {
code
} elseif (something else) {
code
} elseif (something other) {
code
}