I have been having a problem the last few weeks with my site going down. My host says it's a problem with XOOPS coding relating to the MySQL database. Here's a quote of their last email.

Hi Frank,

The issue is because you are exceeding the maximum number of MySQL user connections to the database server. Please see the readings from the server which I took just now :
===============
| 12594 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 18311 | | |
| 12606 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 18299 | | |
| 12639 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 18258 | | |
| 12659 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 18209 | | |
| 12680 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 18179 | | |
| 12696 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 18068 | | |
| 12708 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 18027 | | |
| 12709 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 18027 | | |
| 12716 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 18027 | | |
| 12724 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 18026 | | |
| 12736 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 18028 | | |
| 12840 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 17828 | | |
| 12856 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 17831 | | |
| 12861 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 17827 | | |
| 12903 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 17788 | | |
| 12934 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 17742 | | |
| 12945 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 17706 | | |
| 13013 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 17589 | | |
| 13081 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 17509 | | |
| 13094 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 17484 | | |
| 13104 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 17426 | | |
| 13196 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 17276 | | |
| 13717 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 16417 | | |
| 13781 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 16275 | | |
| 16449 | franksre_xoop1 | localhost | franksre_xoop1 | Sleep | 11043 | |
============

As you can see, there as about 20 open connections. Every time the database connect code gets executed, the session is being created and is being left behind. You need to check your code to make sure that all database sessions are closed and / or disable persistent connections.

We had to tweak the server configuration as a result of too may connections being left open on the server creating load and memory issues and thereby affecting the server performance as a whole. Hence we cannot let uses open infinite connections any more. It is true that persistent connections eliminate the overheads during creation of new connections. But, MySQL takes relatively short time for creating new connections and often the benefit of using persistent connections is not noticeable. Also, considering the drawbacks of using persistent connections its advantages are negligible. It usually comes to use over slow internet connections and when using quick queries.

You can find more information about this at:
http://www.mysql.com/news-and-events/newsletter/2002-11/a0000000086.html

I have refreshed the sql server connections for now.

Thank you.
---
Tina Garrison
Technical Support Team
TotalChoice Hosting

Ticket Details
===================
Ticket ID: VZW-359694
Department: Technical Support
Priority: --
Status: Closed

What version of xoops, MySQL & php?


When you installed XOOPS did you set it for persistent connections?

I believe it says by the check box not recommended for shared servers.

Website URL:
http://franksrecipes.com

XOOPS Version:
XOOPS 2.0.16

XOOPS Theme:
7Xred

XOOPS Template Set:
wraps

PHP Version:
4.4.4

MySQL Version:
4.1.21-standard

Server Software:
Apache

User's Browser:
Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.4) Gecko/20070515 Firefox/2.0.0.4


I don't believe I set it for persistent connections when I installed. ot knowing what it was, I would have left it at the default. NOw I'm trying to find out how to turn the persistent connections off. I'm not finding anything in the admin back end though.
:(

After reading another thread I checked my mainfile.php

It's a little confusing. Install says the default for persistent connections is no.

But mainfile.php says yes for default.

// Use persistent connection? (Yes=1 No=0)
// Default is 'Yes'. Choose 'Yes' if you are unsure.
define('XOOPS_DB_PCONNECT', 0);

As you can see, mine is set to no. So I don't see why I'm suddenly having these persistent connection problems. Is there somewhere else I should be checking besides mainfile.php?