forum.coppermine-gallery.net

Support => cpg1.4.x Support => Older/other versions => cpg1.4 miscellaneous => Topic started by: cessam on October 05, 2007, 08:19:58 pm

Title: MySQl said: Too many connections
Post by: cessam on October 05, 2007, 08:19:58 pm
Hi,
About three times a day I recieve a Coppermine critical error:
Unable to connect to datase !

MySQL said: Too Many connections.

I checked previous posts houwever still looking for my answers.
I have called my host and they basically said that I reached my limit of queries for the hour. They said my limit is 50,000 per hour.  I dont believe I get that many hits on the gallery could this really be the issue.
here is my URL. http://www.themerchan.com/cpg132
After the hour it does reset however it is very annoying.  Is there a way to reduce the number of queries on my end on the config page in the gallery?
Please advise.
Thank you
Title: Re: MySQl said: Too many connections
Post by: Nibbler on October 05, 2007, 10:11:33 pm
There will be many queries per 'hit'. Enable debug mode to see how many there are and see previous posts about using multiple mysql users and see if that would help you.
Title: Re: MySQl said: Too many connections
Post by: cessam on October 06, 2007, 01:29:15 am
When I select an album this is what I get:
USER:
------------------
Array
(
    [ID] => aa15c9e6755e2816c75077b5c7bb9f67
    [am] => 1
    [lang] => english
    [liv] => Array
        (
            [user_id] => 1
    [user_name] => cessam
    [groups] => Array
        (
           
        )

    [disk_max] => 0
    [disk_min] => 0
    [can_rate_pictures] => 1
    [can_send_ecards] => 1
    [ufc_max] => 3
    [ufc_min] => 3
    [custom_user_upload] => 0
    [num_file_upload] => 5
    [num_URI_upload] => 3
    [can_post_comments] => 1
    [can_upload_pictures] => 1
    [can_create_albums] => 1
    [has_admin_access] => 1
    [pub_upl_need_approval] => 0
    [priv_upl_need_approval] => 0
    [group_name] => Administrators
    [upload_form_config] => 3
    [group_quota] => 0
    [can_see_all_albums] => 1
    [group_id] => 1
)

==========================
Queries:
------------------
Array
(
    [album] => 82
)

==========================
POST :
------------------
Array
(
)

==========================
VERSION INFO :
------------------
PHP version: 4.4.1 - OK
------------------
mySQL version: 4.0.16
------------------
Coppermine version: 1.4.12(stable)
==========================
Module: GD
------------------
GD Version: bundled (2.0.28 compatible)
FreeType Support: 1
FreeType Linkage: with freetype
T1Lib Support:
GIF Read Support: 1
GIF Create Support: 1
JPG Support: 1
PNG Support: 1
WBMP Support: 1
XBM Support: 1
JIS-mapped Japanese Font Support:

==========================
Module: mysql
------------------
MySQL Supportenabled
Active Persistent Links 0
Active Links 1
Client API version 4.0.16
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /tmp/mysql.sock
MYSQL_INCLUDE -I/usr/local/mysql/include/mysql
MYSQL_LIBS -L/usr/local/mysql/lib/mysql -lmysqlclient 
==========================
Module: zlib
------------------
ZLib Support enabled
Compiled Version 1.2.3
Linked Version 1.2.3
==========================
Server restrictions (safe mode)?
------------------
Directive | Local Value | Master Value
safe_mode | Off | Off
safe_mode_exec_dir | no value | no value
safe_mode_gid | Off | Off
safe_mode_include_dir | no value | no value
safe_mode_exec_dir | no value | no value
sql.safe_mode | Off | Off
disable_functions | no value | no value
file_uploads | On | On
include_path | .:/usr/local/lib/php | .:/usr/local/lib/php
open_basedir | /home/themerch/:/usr/lib/php:/usr/local/lib/php:/tmp:/var/tmp:/home/vdeck/tmp/:/usr/local/bin/mogrify:/usr/local/bin/convert:/usr/sbin/sendmail | no value
==========================
email
------------------
Directive | Local Value | Master Value
sendmail_from | me@localhost.com | me@localhost.com
sendmail_path | /usr/sbin/sendmail -i -t | /usr/sbin/sendmail -i -t
SMTP | localhost | localhost
smtp_port | 25 | 25
==========================
Size and Time
------------------
Directive | Local Value | Master Value
max_execution_time | 30 | 30
max_input_time | -1 | -1
upload_max_filesize | 15M | 15M
post_max_size | 8M | 8M
==========================
Page generated in 0.999 seconds - 20 queries in 0.376 seconds - Album set : ; Meta set: ;

Then when I click on individual pics this is what I get:
USER:
------------------
Array
(
    [ID] => aa15c9e6755e2816c75077b5c7bb9f67
    [am] => 1
    [lang] => english
    [liv] => Array
        (
            [user_id] => 1
    [user_name] => cessam
    [groups] => Array
        (
           
        )

    [disk_max] => 0
    [disk_min] => 0
    [can_rate_pictures] => 1
    [can_send_ecards] => 1
    [ufc_max] => 3
    [ufc_min] => 3
    [custom_user_upload] => 0
    [num_file_upload] => 5
    [num_URI_upload] => 3
    [can_post_comments] => 1
    [can_upload_pictures] => 1
    [can_create_albums] => 1
    [has_admin_access] => 1
    [pub_upl_need_approval] => 0
    [priv_upl_need_approval] => 0
    [group_name] => Administrators
    [upload_form_config] => 3
    [group_quota] => 0
    [can_see_all_albums] => 1
    [group_id] => 1
)

==========================
Queries:
------------------
Array
(
    [album] => 82
    [pos] => 0
)

==========================
POST :
------------------
Array
(
)

==========================
VERSION INFO :
------------------
PHP version: 4.4.1 - OK
------------------
mySQL version: 4.0.16
------------------
Coppermine version: 1.4.12(stable)
==========================
Module: GD
------------------
GD Version: bundled (2.0.28 compatible)
FreeType Support: 1
FreeType Linkage: with freetype
T1Lib Support:
GIF Read Support: 1
GIF Create Support: 1
JPG Support: 1
PNG Support: 1
WBMP Support: 1
XBM Support: 1
JIS-mapped Japanese Font Support:

==========================
Module: mysql
------------------
MySQL Supportenabled
Active Persistent Links 0
Active Links 1
Client API version 4.0.16
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /tmp/mysql.sock
MYSQL_INCLUDE -I/usr/local/mysql/include/mysql
MYSQL_LIBS -L/usr/local/mysql/lib/mysql -lmysqlclient 
==========================
Module: zlib
------------------
ZLib Support enabled
Compiled Version 1.2.3
Linked Version 1.2.3
==========================
Server restrictions (safe mode)?
------------------
Directive | Local Value | Master Value
safe_mode | Off | Off
safe_mode_exec_dir | no value | no value
safe_mode_gid | Off | Off
safe_mode_include_dir | no value | no value
safe_mode_exec_dir | no value | no value
sql.safe_mode | Off | Off
disable_functions | no value | no value
file_uploads | On | On
include_path | .:/usr/local/lib/php | .:/usr/local/lib/php
open_basedir | /home/themerch/:/usr/lib/php:/usr/local/lib/php:/tmp:/var/tmp:/home/vdeck/tmp/:/usr/local/bin/mogrify:/usr/local/bin/convert:/usr/sbin/sendmail | no value
==========================
email
------------------
Directive | Local Value | Master Value
sendmail_from | me@localhost.com | me@localhost.com
sendmail_path | /usr/sbin/sendmail -i -t | /usr/sbin/sendmail -i -t
SMTP | localhost | localhost
smtp_port | 25 | 25
==========================
Size and Time
------------------
Directive | Local Value | Master Value
max_execution_time | 30 | 30
max_input_time | -1 | -1
upload_max_filesize | 15M | 15M
post_max_size | 8M | 8M
==========================
Page generated in 0.084 seconds - 24 queries in 0.004 seconds - Album set : ; Meta set: ;
Title: Re: MySQl said: Too many connections
Post by: cessam on October 06, 2007, 01:32:05 am
Can you also please linke me to
multiple mysql users
Thanks
Title: Re: MySQl said: Too many connections
Post by: cessam on October 06, 2007, 02:29:08 am
I actually figured it out I believe.
I created four more users and modified the config.php file to randomly select the four users in hope that it will cut down my query hits and that way I will avoide getting these errors.
Thanks for all your help
Title: Re: MySQl said: Too many connections
Post by: eka on October 06, 2007, 06:59:31 am
Hi,

I was bugged by the same problem - too many connections.
I checked with my web host and they said : "I found an account on the st97 that appeared to be the reason why apache and MySQL on other accounts was not functioning properly."

I don't know what it means but my problem has disappeared since.

Hope this gives a clue.


Title: Re: MySQl said: Too many connections
Post by: cessam on October 06, 2007, 07:19:23 pm
Cool if the error comes up again after creating these extra users I will look into it with what you are saying.
Thanks alot for the info
Title: Re: MySQl said: Too many connections
Post by: Veronica on October 07, 2007, 02:36:05 am
"Too many connections" typically happens when you have an overloaded host where MySQL calls tend to take too long time to service

This long execution time might be from too many Coppermine database requests
or other users sharing the same web host doing lots of MySQL requests too like "eka" mentioned
so the PC user will not get an answer back ie white pages then doing a refresh and takes up another MySQL connection etc.

Look at the timing results for each MySQL statement,
the service times for all SELECT statements are in seconds (30-60 seconds at most) instead of fractions of seconds.
But at the same time a new MySQL connection is serviced within milliseconds

Try to execute phpmyadmin during a "Too many connections" period and look at "Processes"
and you will see all the connections being open and each of the MySQL statement being executed as well as the execution times.
 
With so many open connections to MySQL during a 30 seconds period the configuration limits are reached very fast.
Yes a workaround might be to execute MySQL with more users but is not a final solution to your problem.
Ideal situation is when each MySQL statement executes fast
and the MySQL connection is getting free after max half a second for next user page to be serviced.

I have had this problem 2-3 times a day when using a cheap shared hosting (one.com in Copenhagen)
and the MySQL overload could last from 2-3 minutes up to an hour and their support never admitted any problems
but after moving my site to a more expensive and less loaded hosting no problems at all.
I have been using the new hosting now for 2 weeks without any error "Too many connections"
They do have Coppermine for easy installation from cPanel but currently only version 1.4.10 so I use the latest download ofcourse

Bottom line is that I dont see how the Coppermine development team could find a solution for this connections error
maybe to develop a non-MySQL version of Coppermine ie only rely on standard file read and writes
or find a solution where new database requests are delayed until the shared hosting web server is less loaded.
Title: Re: MySQl said: Too many connections
Post by: Joachim Müller on October 07, 2007, 09:49:28 am
Veronica,

thanks for your posting. You are right in every aspect. People who experience the "too many connections" error should find better webhosting. I have never seen this hapen on decent webhosting, where webhosts don't clutter their servers for economic reasons with too many customer's accounts.

I doubt though that there will ever be a version of coppermine that uses flat text files instead of database lookups. In fact, I can promise that the dev team will definitely not look into such a solution. Query caching is on the agenda (which will reduce the number of queries), but not flat text files as mysql replacement.

Joachim
Title: Re: MySQl said: Too many connections
Post by: cessam on October 13, 2007, 02:22:31 pm
Can you please recommed a better host? Not that much money  but resonable enough so that I wont have these issues. Thanks
I have not seen the error as much since added the other users however it does happen after a large batch upload
Title: Re: MySQl said: Too many connections
Post by: Sami on October 13, 2007, 03:52:56 pm
We can't suggest any host
but there is a thread for hosting suggestion you can check
http://forum.coppermine-gallery.net/index.php?topic=19645.0
Title: Re: MySQl said: Too many connections
Post by: Pycckuu on November 05, 2008, 01:57:32 pm
Can you please look at my page, since i have the same problem over there:

http://covers.kinoman.org/index.php

Any suggestions - in fact i have tons of albums and a lot of categories showing on main page!

I have Coppermine Photo Gallery 1.4.19 (stable) there!
Title: Re: MySQl said: Too many connections
Post by: Joachim Müller on November 05, 2008, 06:05:25 pm
Read the thread you just replied to: it's not coppermine's fault, but your webserver's fault (not enough resources / cheap webhosting). Look for better webhosting. You already blindly replied to another thread (http://forum.coppermine-gallery.net/index.php/topic,3721.msg274423.html#msg274423) that I had to lock. I told you there to read up board rules. Stop misbehaving. We can at least expect that you read the threads you reply to. There's nothing to look at on your page, as the error message is the only output, so what do you suggest. Should we buy you better webhosting? ::)
Title: Re: MySQl said: Too many connections
Post by: Pycckuu on November 05, 2008, 06:17:50 pm
Hi, i have read the thread, just was expecting any comments!
Never thought my hosting is bad, but may be it is bad :(

My hosting is Servage

Actually i have a lot of categories on main page ~50, that may be the fault

My webhosting claims they accept 20 concurrent connections to mysql database. when i checked it in PHPMYADMIN there was only 1 in the processlist (me quering processlist :) )

Title: Re: MySQl said: Too many connections
Post by: Joachim Müller on November 05, 2008, 06:27:28 pm
Whatever. This is not related to coppermine. Talk to your webhost, not us. We don't know your webhost nor could we suggest alternatives. Locking.