Bug #20237 mysql_close appears to not close the database connection.
Submitted: 2 Jun 2006 21:17 Modified: 29 Jul 2006 14:58
Reporter: Carlos Chavez Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S1 (Critical)
Version:5.0.x OS:Windows (Windows Server 2003 x64)
Assigned to: CPU Architecture:Any

[2 Jun 2006 21:17] Carlos Chavez
Description:
I created an ATL Server ISAPI web site to perform simple searches on a MySql database.  I created a specific test function that simply connects and disconnects from MySql as a test.  The function is:

HTTP_CODE OnHello(void)
{
    MYSQL* TConn;

    TConn = mysql_init(NULL);
    if( TConn == NULL )
        m_HttpResponse << "No memory for MySQL.";
    if( mysql_real_connect(TConn, "host", "user", "pwd", "db", 0, NULL, 0) == NULL )
        m_HttpResponse << "Unable to connect to MySQL.";
    m_HttpResponse << "<Status>Connected to MySQL.</Status>";

    mysql_close(TConn);
    m_HttpResponse << "<Status>Disconnected from MySQL.</Status>";

    return HTTP_SUCCESS;
}

When I run the command "show status like 'conn%'" it displays an ever increasing number of connections until the limit is hit.  The no additional connections are allowed.

I have only tried this on Windows Server 2003 R2 x64.

How to repeat:
Use Visual Studio to create an ATL Server project.  You get a default OnHello handler.  Insert the code above in the handler and try it.  Don't forget to add the include and lib paths to the x64 MySql include files and libraries.

Suggested fix:
I created a .Net web site using the 1.0.7 .Net connector for MySql.  The connection issue does not exist in this case.
[11 Jun 2006 16:02] Valeriy Kravchuk
Looks like a Connector/Net related bug report. Can you repeat the behaviour described with program that uses C API directly?
[12 Jun 2006 18:17] Carlos Chavez
Please re-read the bug report.  I am using the connector/Net as a workaround and it works.  The code, posted, that has a problem uses the c api directly.  Thanks.
[13 Jun 2006 10:52] Valeriy Kravchuk
Sorry, my fault. Anyway, please, check, is it pure C API-related issue really, by compiling and running many times (more than max_connections) the following simple program:

openxs@suse:~/dbs/5.0> cat 20237.c
#include <stdio.h>
#include "mysql.h"

int main()
{
  MYSQL *conn;

  conn= mysql_init(NULL);
  conn= mysql_real_connect(conn, "127.0.0.1", "root", "", "test", 3306, 0, 0);
  if (!conn) {
    fprintf(stderr, "Cannot connect\n");
    exit(1);
  }

  printf("Client info: %s\n", mysql_get_client_info());
  printf("Server info: %s\n", mysql_get_server_info(conn));

  mysql_close(conn);
  return 0;
}

In my case, the following test gave successfull results:

let a=0;
while [ $a -le 1000 ]; do echo $a; ./20237; let a=a+1; done

...
998
Client info: 5.0.23
Server info: 5.0.23
999
Client info: 5.0.23
Server info: 5.0.23
1000
Client info: 5.0.23
Server info: 5.0.23

While:

mysql> show variables like 'max_conn%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 10    |
| max_connections    | 100   |
+--------------------+-------+
2 rows in set (0.01 sec)

So, I see no connections being not closed, in this simple case. I think, it is some "ATL Server ISAPI"-related problem (or Windows 2003 x64-related problem). Simple C API program I presented will help to find the reason.
[21 Jun 2006 14:56] Carlos Chavez
I tried your program and the results have not changed.  The number of connections continues to grow as I run the program.  The output on my machine is:

Client info: 5.0.21
Server info: 5.0.21

The command I use to monitor the number of connections is: "show status like 'conn%'".

Any other ideas?
[29 Jun 2006 14:58] Valeriy Kravchuk
Sorry, but you are just using improper shatus variable to check. Look:

mysql> show status like 'conn%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 6     |
+---------------+-------+
1 row in set (0.01 sec)

...

mysql> show status like 'conn%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 977   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show status like '%conn%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Aborted_connects     | 0     |
| Connections          | 1007  |
| Max_used_connections | 3     |
| Threads_connected    | 1     |
+----------------------+-------+
4 rows in set (0.00 sec)

I've got the results above while running my test case. Now let's read the manual (http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html):

"- Aborted_connects

The number of failed attempts to connect to the MySQL server. See Section A.2.10, “Communication Errors and Aborted Connections”.

...

- Connections

The number of connection attempts (successful or not) to the MySQL server.
...

- Max_used_connections

The maximum number of connections that have been in use simultaneously since the server started."

So, if connections were not closed, you'll see increase in Max_used_connections and, eventually, in Aborted_connects. I see nothing similar. Please, check with my (or any) test case on your Windows platform.
[29 Jul 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".