Advantage and PHP - Error 6303: Maximum Advantage Database Server connections exceeded

Question

My company php web site is connected to an Advantage Database Server where are stored all necessary data such as users, passwords and customer registry.

Lately we started to get an error requesting web pages:

Warning: SQL error: [Extended Systems][Advantage SQL][ASA] Error 6303: Maximum Advantage Database Server connections exceeded. axServerConnect, SQL state HY000 in SQLDriverConnect in C:\...\www\... on line...

It's becoming critical day by day and it can happen once a week or twice a day without an apparent reason.

When website crashes, database service still working great with other applications connected and the only way to restore web service is to restart apache web server.

On database server we've got ads.ini configuration file in C:\Windows folder where we raised max connections setting with "MAX_CONNECTIONS=1000" which is really big compared to our needs.

Can it be useful if we set also "RETRY_ADS_CONNECTS = 1" ??

I found this post where R&D confirms a bug in may 2009:

Is this a bug with Advantage Database?

Has this been fixed? In wich release?

Where can i see the real number of connections open by apache on db?

Each php page closes ads connection on footer, what can cause connections to exceed??

Thanks in advance for help.

- ENVIRONMENT INFO

Database: Advantage Database Server 10.10.0.6 on Windows 2003 server

Web server: Apache/2.0.59 (Win32) mod_ssl/2.0.59 OpenSSL/0.9.8d PHP/4.4.7 on Windows XP pro

On phpinfo() page we get "Advantage Version" "8.00.0.0". Why this? Do we need to upgrade php-advantage extension?

Answer 1

Lots of questions, but I will try and address each.

1) 6303 Error. Using MAX_CONNECTIONS is the correct way to resolve this.
Make sure MAX_CONNECTIONS is in the [SETTINGS] section Check if Apache / PHP / ADS driver is using the correct ads.ini file. You can use Process Monitor from Sysinternals to see what ads.ini file was opened successfully. If you upgrade your PHP driver you can set an environment variable adsini_path to point to the directory where the ads.ini lives.

2) Setting RETRY_ADS_CONNECTS=1 will be helpful. This also goes under the [SETTINGS] section of the ads.ini. When an ADS client receives a networking error (generally a 6000 class error) then the error is cached by the client driver and subsequent attempts to connect will use the cached error vs. retrying. Setting RETRY_ADS_CONNECTS will tell the ADS client to ignore the cached error and retry the connection

3) Bug: Looks like this was fixed in 9.10.0.9 version of the driver based on the release notes on http://devzone.advantagedatabase.com.

Fixed an issue where the garbage collection reference count on a connection would be incorrect if multiple SQL statements were opened on it.

Since you are running a 10.1 server you may look at updating to a 10.1 client which will also contain the fix. 10.1 Advantage PHP Driver

4)See the real number of connection I would recommend using the stored procedure sp_mgGetConnectedUsers, you can use ARC (Advantage Data Architect) but it can be difficult to group, order, etc. Since you are using 10.1 you can include the results of the stored procedure in a query such as

SELECT COUNT(*) FROM (EXECUTE PROCEDURE sp_mgGetConnectedUsers()) u WHERE ADDRESS='xxx.xxx.xxx.xxx'

You could also use other fields to identify the PHP application such as UserName (the server name), DictionaryUser (assuming the php application uses a unique user), ApplicationID

5) PHPINFO shows Advantage Version of the Advantage PHP client driver. You may want to upgrade the client driver for reasons mentioned above. Should be as simple as swapping DLL files (ace32.dll, axces32.dll, adsodbc.dll and php_advantage.dll), but I would recommend testing first to ensure you get everything.