MySQL Bugs: #66871: #error 1040, Too many connections, How to disconnect mysql connection manual use

Bug #66871	#error 1040, Too many connections, How to disconnect mysql connection manual use
Submitted:	19 Sep 2012 13:13	Modified:	9 Jan 2015 9:50
Reporter:	sonly strong	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / C++	Severity:	S1 (Critical)
Version:	1.1.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I want to Insert mass data into Mysql by multi-thread in Windows.
It shows #error 1040, Too many connections

I set the
max_connections = 1000 in my.ini

after run 1000 thead, insert 1000 data into mysql, I cannot insert more.
and I use "netstat -an" check the port stats.
show a lots of
TCP 127.0.0.1:3306 127.0.0.1:1160 ESTABLISHED
TCP 127.0.0.1:3306 127.0.0.1:1163 ESTABLISHED
TCP 127.0.0.1:3306 127.0.0.1:1166 ESTABLISHED
TCP 127.0.0.1:3306 127.0.0.1:1169 ESTABLISHED
TCP 127.0.0.1:3306 127.0.0.1:1172 ESTABLISHED
TCP 127.0.0.1:3306 127.0.0.1:1175 ESTABLISHED
TCP 127.0.0.1:3306 127.0.0.1:1178 ESTABLISHED
TCP 127.0.0.1:3306 127.0.0.1:1181 ESTABLISHED
TCP 127.0.0.1:3306 127.0.0.1:1184 ESTABLISHED

and

TCP 127.0.0.1:4879 127.0.0.1:3306 TIME_WAIT
TCP 127.0.0.1:4882 127.0.0.1:3306 TIME_WAIT
TCP 127.0.0.1:4887 127.0.0.1:3306 TIME_WAIT
TCP 127.0.0.1:4890 127.0.0.1:3306 TIME_WAIT
TCP 127.0.0.1:4893 127.0.0.1:3306 TIME_WAIT
TCP 127.0.0.1:4898 127.0.0.1:3306 TIME_WAIT
TCP 127.0.0.1:4901 127.0.0.1:3306 TIME_WAIT
TCP 127.0.0.1:4905 127.0.0.1:3306 TIME_WAIT

I need to disconnect immediately when I finished the thead. How can I do?
If I use the c API, Can use the mysql_close(&mysql);
But I dont know how to use the Connect/c++ to close the connection

How to repeat:
try
{
sql::Driver *driver;
sql::Connection *con;
sql::Statement *stmt;
sql::ResultSet *res;

/* Create a connection */
driver = get_driver_instance();
con = driver->connect("tcp://127.0.0.1:3306", "root", "root");
/* Connect to the MySQL test database */
con->setSchema("test");

stmt = con->createStatement();
res = stmt->executeQuery("insert data sql here.............");
}
delete res;
delete stmt;
con->close();
delete con;

}
catch (sql::SQLException &e)
{
;
}

Suggested fix:
con->close();
delete con;
these function does not work. 
the connection still alive,TCP 127.0.0.1:4879 127.0.0.1:3306 TIME_WAIT
add disconnect function.

Thank you for the report.

Verified as described.

To repeat run attached test and see SHOW PROCESSLIST output.

test case

Attachment: bug66871.cpp (application/octet-stream, text), 1.55 KiB.

The problem here is that statement handle is not freed. If you add "delete stmt" you will see that physical connection will close on either connector destruction event (if you destroy stmt before that).

You have to do that anyway since you will leak memory otherwise.

con->close() means more like you can do pretty much nothing with that object any more. while statement object(s) will still be operable.

In fact at the moment c/c++ is pretty much "thread-agnostic"

I deem this bug may be if not closed then severity can be lowered. Perhaps to feature request.