MySQl said: Too many connections

[cessam]

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

[Nibbler]

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.

[cessam]

When I select an album this is what I get:
USER:
------------------
Array
(
    [ID] => aa15c9e6755e2816c75077b5c7bb9f67
    [am] => 1
    [lang] => english
    [liv] => Array
        (
           

• => 28
              [1] => 35
          )
  
  )
  
  ==========================
  USER DATA:
  ------------------
  Array
  (
      [user_id] => 1
      [user_name] => cessam
      [groups] => Array
          (
             
• => 1
          )
  
      [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
  (
     
• => SELECT extension, mime, content, player FROM copperminefiletypes; (0.025s)
      [1] => select * from coppermineplugins order by priority asc; (0.007s)
      [2] => delete from `themerch_coppermine`.copperminesessions where time<1191623341 and remember=0; (0.003s)
      [3] => delete from `themerch_coppermine`.copperminesessions where time<1190417341; (0.003s)
      [4] => select user_id from `themerch_coppermine`.copperminesessions where session_id=md5("c84c9cf635d309f59f20508e044b6a4f2353174abf02606a31f5ba675f832fe9"); (0.007s)
      [5] => select user_id as id, user_password as password from `themerch_coppermine`.coppermineusers where user_id=1 (0.016s)
      [6] => SELECT u.user_id AS id, u.user_name AS username, u.user_password AS password, u.user_group+100 AS group_id FROM `themerch_coppermine`.coppermineusers AS u INNER JOIN `themerch_coppermine`.coppermineusergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' (0.002s)
      [7] => SELECT user_group_list FROM `themerch_coppermine`.coppermineusers AS u WHERE user_id='1' and user_group_list <> ''; (0.005s)
      [8] => SELECT MAX(group_quota) as disk_max, MIN(group_quota) as disk_min, MAX(can_rate_pictures) as can_rate_pictures, MAX(can_send_ecards) as can_send_ecards, MAX(upload_form_config) as ufc_max, MIN(upload_form_config) as ufc_min, MAX(custom_user_upload) as custom_user_upload, MAX(num_file_upload) as num_file_upload, MAX(num_URI_upload) as num_URI_upload, MAX(can_post_comments) as can_post_comments, MAX(can_upload_pictures) as can_upload_pictures, MAX(can_create_albums) as can_create_albums, MAX(has_admin_access) as has_admin_access, MIN(pub_upl_need_approval) as pub_upl_need_approval, MIN( priv_upl_need_approval) as  priv_upl_need_approval FROM coppermineusergroups WHERE group_id in (1) (0.006s)
      [9] => SELECT group_name FROM  coppermineusergroups WHERE group_id= 1 (0.004s)
      [10] => update `themerch_coppermine`.copperminesessions set time='1191626941' where session_id=md5('c84c9cf635d309f59f20508e044b6a4f2353174abf02606a31f5ba675f832fe9'); (0s)
      [11] => SELECT user_favpics FROM copperminefavpics WHERE user_id = 1 (0s)
      [12] => DELETE FROM copperminebanned WHERE expiry < '2007-10-05 23:29:01' (0s)
      [13] => SELECT * FROM copperminebanned WHERE (ip_addr='68.32.195.3' OR ip_addr='68.32.195.3' OR user_id=1) AND brute_force=0 (0s)
      [14] => SELECT category, title, aid, keyword, description, alb_password_hint FROM copperminealbums WHERE aid='82' (0s)
      [15] => SELECT name, parent FROM copperminecategories WHERE cid = '4' (0s)
      [16] => SELECT COUNT(*) FROM copperminepictures WHERE approved = 'NO' (0.093s)
      [17] => SELECT title,keyword from copperminealbums WHERE aid='82' (0.097s)
      [18] => SELECT COUNT(*) from copperminepictures WHERE ((aid='82'  ) )   (0.048s)
      [19] => SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, keywords, title, caption,hits,owner_id,owner_name from copperminepictures WHERE ((aid='82'  ) )   ORDER BY filename ASC  LIMIT 0 ,12 (0.06s)
  )
  
  ==========================
  GET :
  ------------------
  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
          (
             
• => 28
              [1] => 35
          )
  
  )
  
  ==========================
  USER DATA:
  ------------------
  Array
  (
      [user_id] => 1
      [user_name] => cessam
      [groups] => Array
          (
             
• => 1
          )
  
      [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
  (
     
• => SELECT extension, mime, content, player FROM copperminefiletypes; (0s)
      [1] => select * from coppermineplugins order by priority asc; (0s)
      [2] => delete from `themerch_coppermine`.copperminesessions where time<1191623439 and remember=0; (0s)
      [3] => delete from `themerch_coppermine`.copperminesessions where time<1190417439; (0s)
      [4] => select user_id from `themerch_coppermine`.copperminesessions where session_id=md5("c84c9cf635d309f59f20508e044b6a4f2353174abf02606a31f5ba675f832fe9"); (0s)
      [5] => select user_id as id, user_password as password from `themerch_coppermine`.coppermineusers where user_id=1 (0s)
      [6] => SELECT u.user_id AS id, u.user_name AS username, u.user_password AS password, u.user_group+100 AS group_id FROM `themerch_coppermine`.coppermineusers AS u INNER JOIN `themerch_coppermine`.coppermineusergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' (0s)
      [7] => SELECT user_group_list FROM `themerch_coppermine`.coppermineusers AS u WHERE user_id='1' and user_group_list <> ''; (0s)
      [8] => SELECT MAX(group_quota) as disk_max, MIN(group_quota) as disk_min, MAX(can_rate_pictures) as can_rate_pictures, MAX(can_send_ecards) as can_send_ecards, MAX(upload_form_config) as ufc_max, MIN(upload_form_config) as ufc_min, MAX(custom_user_upload) as custom_user_upload, MAX(num_file_upload) as num_file_upload, MAX(num_URI_upload) as num_URI_upload, MAX(can_post_comments) as can_post_comments, MAX(can_upload_pictures) as can_upload_pictures, MAX(can_create_albums) as can_create_albums, MAX(has_admin_access) as has_admin_access, MIN(pub_upl_need_approval) as pub_upl_need_approval, MIN( priv_upl_need_approval) as  priv_upl_need_approval FROM coppermineusergroups WHERE group_id in (1) (0s)
      [9] => SELECT group_name FROM  coppermineusergroups WHERE group_id= 1 (0s)
      [10] => update `themerch_coppermine`.copperminesessions set time='1191627039' where session_id=md5('c84c9cf635d309f59f20508e044b6a4f2353174abf02606a31f5ba675f832fe9'); (0s)
      [11] => SELECT user_favpics FROM copperminefavpics WHERE user_id = 1 (0s)
      [12] => DELETE FROM copperminebanned WHERE expiry < '2007-10-05 23:30:39' (0s)
      [13] => SELECT * FROM copperminebanned WHERE (ip_addr='68.32.195.3' OR ip_addr='68.32.195.3' OR user_id=1) AND brute_force=0 (0s)
      [14] => SELECT title,keyword from copperminealbums WHERE aid='82' (0s)
      [15] => SELECT COUNT(*) from copperminepictures WHERE ((aid='82'  ) )   (0s)
      [16] => SELECT * from copperminepictures WHERE ((aid='82'  ) )   ORDER BY filename ASC  LIMIT 0 ,1 (0.002s)
      [17] => SELECT title, comments, votes, category, aid FROM copperminealbums WHERE aid='82' LIMIT 1 (0s)
      [18] => SELECT name, parent FROM copperminecategories WHERE cid = '4' (0s)
      [19] => SELECT msg_id, msg_author, msg_body, UNIX_TIMESTAMP(msg_date) AS msg_date, author_id, author_md5_id, msg_raw_ip, msg_hdr_ip, pid FROM copperminecomments WHERE pid='3231' ORDER BY msg_id ASC (0s)
      [20] => SELECT COUNT(*) FROM copperminepictures WHERE approved = 'NO' (0s)
      [21] => SELECT title,keyword from copperminealbums WHERE aid='82' (0s)
      [22] => SELECT COUNT(*) from copperminepictures WHERE ((aid='82'  ) )   (0s)
      [23] => SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, keywords, title, caption,hits,owner_id,owner_name from copperminepictures WHERE ((aid='82'  ) )   ORDER BY filename ASC  LIMIT 0 ,10 (0.002s)
  )
  
  ==========================
  GET :
  ------------------
  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: ;

[cessam]

Can you also please linke me to
multiple mysql users
Thanks

[cessam]

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

[eka]

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.

[cessam]

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

[Veronica]

"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.

[Joachim Müller]

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

[cessam]

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

[Sami]

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

[Pycckuu]

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!

[Joachim Müller]

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 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?

[Pycckuu]

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 )

[Joachim Müller]

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.