Bug #28387 Create a new thread (errno 12)
Submitted: 12 May 2007 11:40 Modified: 14 May 2007 7:42
Reporter: Ashish Annachhatre Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:ODBC 3.51 OS:Microsoft Windows (Windows Server 2003 SP2)
Assigned to: CPU Architecture:Any
Tags: create a new thread, MySQL, ODBC, out of available memory, windows xp

[12 May 2007 11:40] Ashish Annachhatre
Description:
Hello,

We have developed a blogging portal for our client and hosted on a dedicated Windows 2003 server machine. Following are the platforms with version numbers we are using on the server:

Server OS: Windows Server 2003 Standard Edition SP2
Client OS: Windows XP Pro SP2
Browsers: IE 6, IE7, FF2

Development Tools:

Our blogging portal is developed in ASP.Net 2.0 (with VB code behind)

MySQL Server : v 4.0 (It is by default installed as a prt of plesk control panel but we have disabled it service from running). and then we have installed MySQL version 5

MySQL Server : MySQL Database Community Edition 5.0.37
MySQL Connector Net 1.0.7
MySQL Connector Net 5.0.6
MySQL Connector/ODBC 3.51

We randomly get the following error when browsing our web portal from any of the above mentioned browsers. And once we get this error, we have to log in to remote server as root and then restart MySQL 5 service. Then the error vanishes for some time and again popup on any reandom page.

Error:
=========
ERROR [HYT00] [MySQL][ODBC 3.51 Driver]Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
=========

We dont know what is to be done to repeat the error as it appears to popup randomly any time on any page. But we are consistantly facing this problem. I am unable to repeat the error.

How to repeat:
Unable to repaet the error on said task but randomly appears some times in just 10-minutes of restarting MySQL 5 service and some times took almost a day to repeat it.
[12 May 2007 11:45] Ashish Annachhatre
Screenshot ot the live error

Attachment: MySQLError.jpg (image/pjpeg, text), 208.10 KiB.

[12 May 2007 11:52] Ashish Annachhatre
I forgot to mention the configuration of the remote dedicated server where we are hosting our blogging portal.

CPU: Intel Xeon 3060 2.4 GHz
RAM : 2 GB RAM

And this server is doing nothing but just serving our portal which does not have even 10 simulteneous users accesing the site at the moment.

So there is absolutely no load on the server.
[14 May 2007 6:43] Tonci Grgin
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Explanation:
Hi Ashish and thanks for your report. You do realize this is not nearly enough for me to start working, right? Probably you have ERROR 1040 (HY000): Too many connections so please recheck your server settings as per http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html and get back to me with results. Also, for MySQL server 5.x it would be better to try with c/ODBC 5.
[14 May 2007 7:42] Ashish Annachhatre
Hello,

I have resolved the problem. It was basically because of the bug in MySQL ODBC Driver's connection pooling.

In our ASP.Net code, we open a database connection and then close it after the use. Even after closing the connection, that thread was killed on the server automatically and was in the state "sleep". But for the next database connection within few seconds, the new thread was being created instead of using the sleeping thread.

that was adding up lot of open (rather sleeping) threads on the server and one fine moment (after 1390 sleeping threads), the database used to sopt functioning.

This happens in spite of specifying "Pooling=false" so that is the bug in the MySQL ODBC driver. another problem was MySQL by default keeps all open threads in "sleep" mode for 8-hours.. thats surprisingly too high.

I had to manually reduce the time for which threads remain open from 8-hours to 20 seconds and my application started functioning smoothly without any errors.
[14 May 2007 10:20] Tonci Grgin
Hi Ashish, I'm glad the problem is solved. AFAIR this is a known bug in IIS. There should be my entires in bugs database regarding that.
[30 Aug 2007 16:35] Tonci Grgin
Bug#29556 was marked as duplicate of this one.
[12 Aug 2008 9:58] Quoc Lai Huy
Hello Ashish Annachhatre!
I don't know how did you manually reduce the time for which threads remain open from 8-hours to 20 seconds. Please show me some step. 
Thanks!
Dank!
[14 Sep 2009 23:42] Ehab Gassem
hi Quoc Lai Huy;
this can be done using wait_timeout

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout
[15 Sep 2009 5:51] Tonci Grgin
> I have resolved the problem. It was basically because of the bug in MySQL ODBC Driver's connection pooling.

There is *absolutely NO* pooling code in c/ODBC, please do not write things like this confusing others... All pooling is done on DM/IIS... side.

> Even after closing the connection, that thread was killed on the server automatically and was in the state "sleep". But for the next database connection within few seconds, the new thread was being created instead of using the sleeping thread.

SLEEP state means connection is waiting on some input from server, not that connection is free for reuse. Check documentation.

> This happens in spite of specifying "Pooling=false" so that is the bug in the MySQL ODBC driver.

No, it is not. Please do notice that "Connection Pooling" tab is in DriverManager configuration and *not* in c/ODBC DSN configuration.

> Another problem was MySQL by default keeps all open threads in "sleep" mode for 8-hours.. thats surprisingly too high.

Please do read the manual (thanks to Ehab you have direct link now).
[6 Sep 2010 6:04] wang yc
the bug status is closed,and i don't know after which version fix this bug,can you tell me,thanks~
[6 Sep 2010 6:37] Tonci Grgin
Wang, there is *NO* bug here! Please read my post again...