Bug #20815 Re: Errno 12 on Windows after 197 connections
Submitted: 2 Jul 2006 17:04 Modified: 1 Nov 2007 18:18
Reporter: A V Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.1.11, 5.0.42 OS:Microsoft Windows (Windows)
Assigned to: Daniel Fischer CPU Architecture:Any
Tags: bfsm_2007_05_03, bfsm_2007_07_19, bfsm_2007_08_16, bfsm_2007_11_01, regression

[2 Jul 2006 17:04] A V
Description:
After creating 197 connection threads, MySQL server reports: errno 12 and does not accept further connections.

This does NOT happen with MySQL 5.0.22 (the stable release).

Only happens with MySQL 5.1 BETA for Windows.

How to repeat:
Write a multithreaded program that opens over 200 simultaneous connections to MySQL 5.1 BETA. After about 197 connections, the program will fail and report errno 12.
[3 Jul 2006 6:05] Tonci Grgin
Hi and thanks for your problem report. Error 12 is OS error code:(ENOMEM) Out of memory.
Can you please provide me with server.err and my.ini files?
[3 Jul 2006 7:19] Shane Bester
I got same problem after just over 180+ threads.

mysql_real_connect failed: 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.  

Using no my.ini file, thus all defaults are used. There's enough memory.
[3 Jul 2006 13:25] Shane Bester
When I put Strace onto 5.1.11 mysqld-debug.exe on win2ksp4, It returned this when it failed:

[T2112] CreateThread(0, 30000, 90aeb0, 47363120, ...) = 0
[T2112] GetLastError(0, 47363120, 0, adefc00, ...) = 8

and this when it succeeded:

[T2112] CreateThread(0, 30000, 90aeb0, 4737a840, ...) = 54a4

error code 8 means ERROR_NOT_ENOUGH_MEMORY according to MSDN.
[3 Jul 2006 14:12] Shane Bester
from mysqld.trace --debug output.

T@4    :      mysqld.cc:  3961:    2: | >create_new_thread
T@4    :      mysqld.cc:  4010:    2: | | info: creating thread 202
T@4    : my_winthread.c:    73:    3: | | >pthread_create
T@4    : my_winthread.c:    89:    3: | | | info: hThread=4294967295
T@4    : my_winthread.c:    97:    3: | | | error: Can't create thread to handle request (error 12)
T@4    : my_winthread.c:    98:    3: | | <pthread_create
T@4    :      mysqld.cc:  4018:    2: | | error: Can't create thread to handle request (error 12)
T@4    :    protocol.cc:   136:    3: | | >net_printf_error
T@4    :    protocol.cc:   137:    3: | | | enter: message: 1135
[3 Jul 2006 16:10] A V
I uploaded the files.
[3 Jul 2006 18:51] Shane Bester
Verified on w2k sp4.  5.1.11 couldn't open more than around 190 new connections, while 5.0.22 could do more than 300 without problem.
Each connection is idle, and not performing any queries. Available memory is ok.
Tested 5.1-bk on linux which didn't have such problems.
So it's not yet clear what a cause of this is.
[29 Nov 2006 13:51] Gareth Bradley
Hi,
   I can confirm this except mine is refusing connections at 99 threads. (I am looking at the mysqld process in Task Manager). I have worked around this by using a inactivity timeout of 30 seconds which seems to keep things on the low side of 99.

Hope this helps...
Gareth
[12 Dec 2006 14:47] Gareth Bradley
Mine is now only allowing 55 threads in Task Manager.

Is there any movement on this?

Regards,
Gareth
[13 Mar 2007 8:29] Timo Bredenoort
Still getting this behavior with 5.1.16 and Windows 2003, 5.0.24 doesn't show this behavior until about 1200 connections.

Tested with a small application that tries to open as many connections as possible.

Is there an outlook on when/if this is getting looked at?

Timo..
[3 May 2007 2:26] Shane Bester
testcase. 5.1.17 allowed < 200 connections, 5.0.38 allowed 500 (set appropriate max_connections first)

Attachment: bug20815.php (application/octet-stream, text), 320 bytes.

[6 Jun 2007 14:33] Shane Bester
this happens on linux too, in the event you set a ulimit -s way too high. the problem is the threads are too far apart in memory, so more cannot be created.
maybe it is similar issue with large stacksize on windows?
[29 Jun 2007 8:31] Shane Bester
another test on windows, with --skip-grant-tables --skip-name-resolve --max_connections=500

5.0.42-enterprise-gpl can open 184 connections before getting errno 12.
5.0.38-enterprise-gpl can open 500 connections and not get any error.

so, what changed between .38 and .42 to cause this?  are they built differently ?
[29 Jun 2007 9:06] Shane Bester
my 5.0.44 built without cmake has no such problems.
[29 Jun 2007 9:27] Shane Bester
A cause is this:
5.0.42 has this in PE header : SizeOfStackReserve:989680h (10,000,000)
5.0.38 has this in PE header : SizeOfStackReserve:100000h (1,048,576)
[19 Jul 2007 20:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31203

ChangeSet@1.2529, 2007-07-19 22:46:34+02:00, df@pippilotta.erinye.com +1 -0
  BUG#20815 Errno 12 on Windows after 197 connections
[19 Jul 2007 20:50] Daniel Fischer
The above patch adds a linker argument specifying the stack size to be the same as in 5.0 releases for win32 before 5.0.42. When testing with 5.0.46 sources, without the patch, I get ~197 connections until the error occurs. With the patch I get 1860. 

My only concern with this patch is that I don't really know why we need it, 1M should be the default value for this setting.
[19 Jul 2007 21:09] Shane Bester
Wonder if it's some CMake default?  I had this in CMakeCache.txt:

//Size of stack for programs.
CMAKE_CXX_STACK_SIZE:STRING=10000000
[19 Jul 2007 21:17] Shane Bester
Do we use the template CMakeWindowsSystemConfig.cmake in CMake ./template directory?  It has:

SET (CMAKE_EXTRA_LINK_FLAGS "/STACK:10000000" CACHE STRING
"Extra flags added to the link line for creation of exe and dlls.")
[19 Jul 2007 21:22] Daniel Fischer
This would explain it. If it is a cmake default then I believe my patch is correct. (As we obviously can't just change the defaults of our cmake installations.)
[20 Jul 2007 9:12] Joerg Bruehe
I approve of the patch - 
setting a reasonable size for a thread stack is good,
and the successful testing (connections up from 197 to 1860) shows it solves the problem.

However, I set the "QA test" flag, because this limits the thread stack to sizes much smaller than before.
IMO, we need some stress test (that requires large stacks per thread) run on a resulting build.
This does not mean "many connections" but rather "complicated SQL", "thread-specific caches" or similar demands.
These statements should be run in more than one parallel connection,
to guarantee they are really (also) executed in a thread which is not the one created last (= at the end of the address space, where it can grow).
[20 Jul 2007 9:16] Shane Bester
Joerg, something like a series of tables all having triggers which insert into next table, and so on, etc, etc... eventually causing that mysql error 'out of stack space' to be returned.  Perhaps execution_constants.test is also a good test ...
[20 Jul 2007 9:25] Daniel Fischer
This will be fixed in 5.0.46 as it is a regression in 5.0 and we're restoring previous behaviour.
[22 Jul 2007 9:40] Bugs System
Pushed into 5.0.48
[22 Jul 2007 9:43] Bugs System
Pushed into 5.1.21-beta
[22 Jul 2007 18:06] Paul Dubois
Noted in 5.0.48, 5.1.21 changelogs.

On Windows, the server would allow a maximum of fewer than 200 
connections.
[23 Jul 2007 12:08] Daniel Fischer
The behaviour of 5.1 is unchanged, additional QA testing is needed before the stack size is reduced in 5.1.
[23 Jul 2007 19:57] Paul Dubois
Revised changelog entry:

On Windows, the server used 10MB of memory for each connection
thread, resulting in memory exhaustion. Now each thread uses 1MB.

Entry was revised for 5.0.48, and removed from 5.1.21 pending
fix for 5.1.  Resetting report to Patch pending.
[27 Jul 2007 17:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31724

ChangeSet@1.2558, 2007-07-27 19:14:08+02:00, df@pippilotta.erinye.com +1 -0
  BUG#20815 for the 5.1 branch
[27 Jul 2007 17:20] Daniel Fischer
As agreed upon in discussion with Omer and Joerg, after passing a full test run with no differences to unmodified code, the patch is pushed into the build team's 5.1 tree and expected to be in 5.1.21, to receive additional System QA testing as part of the 5.1.21 or 5.1.22 tests.
[2 Aug 2007 19:13] Bugs System
Pushed into 5.1.21-beta
[2 Aug 2007 19:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32028

ChangeSet@1.2588, 2007-08-02 21:59:26+02:00, joerg@trift2. +1 -0
  Limit stack size of the server to 1 MB.
      
  Manual transfer of the fix for bug#20815 from 5.0 to 5.1
[3 Aug 2007 1:51] Paul Dubois
Noted in 5.1.21 changelog.

Resetting status to QA testing.
[6 Aug 2007 18:13] Bugs System
Pushed into 5.1.21-beta
[7 Apr 2008 1:03] SCOTT STOUFFER
I can confirm this still happens on 5.1.23rc, running on Dual-Core Windows XP after 200 connections.   1.5gb out of 3gb being used.  Here is a stacktrace from JBoss:

06 Apr 2008 20:55:24,703 []  WARN [UIL2(SocketManager.MsgPool@1a84fae client=192.168.1.151:4722)#2] - Throwable while attempting to get a new connection: null
org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: General error,  message from server: "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")
	at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:179)
	at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:577)
	at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:262)
	at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:538)
	at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:341)
	at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:315)
	at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:396)
	at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:842)
	at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:88)
	at org.jboss.mq.pm.jdbc2.PersistenceManager.getConnection(PersistenceManager.java:1689)
	at org.jboss.mq.pm.jdbc2.PersistenceManager.remove(PersistenceManager.java:1364)
	at org.jboss.mq.server.BasicQueue.acknowledge(BasicQueue.java:750)
	at org.jboss.mq.server.JMSQueue.acknowledge(JMSQueue.java:172)
	at org.jboss.mq.server.ClientConsumer.acknowledge(ClientConsumer.java:334)
	at org.jboss.mq.server.JMSDestinationManager.acknowledge(JMSDestinationManager.java:514)
	at org.jboss.mq.server.JMSDestinationManager.transact(JMSDestinationManager.java:441)
	at org.jboss.mq.server.JMSServerInterceptorSupport.transact(JMSServerInterceptorSupport.java:126)
	at org.jboss.mq.security.ServerSecurityInterceptor.transact(ServerSecurityInterceptor.java:197)
	at org.jboss.mq.server.TracingInterceptor.transact(TracingInterceptor.java:352)
	at org.jboss.mq.server.JMSServerInvoker.transact(JMSServerInvoker.java:132)
	at org.jboss.mq.il.uil2.ServerSocketManagerHandler.handleMsg(ServerSocketManagerHandler.java:194)
	at org.jboss.mq.il.uil2.SocketManager$ReadTask.handleMsg(SocketManager.java:395)
	at org.jboss.mq.il.uil2.msgs.BaseMsg.run(BaseMsg.java:398)
	at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:761)
	at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: General error,  message from server: "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"
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:650)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:1808)
	at com.mysql.jdbc.Connection.<init>(Connection.java:452)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:411)
	at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:171)
[20 May 2008 11:06] Ryan Liu
197 connections generally is enough for my applications. But when something usual happened, e.g. in case of a few rows of client machines lost power, and try to re-connect to db server, I get this error. I think it might hit the 197 connections limit. 

While still waiting this limit to be lift, how can I release resources (memory/threads) used by client machines before they went out of power supply?

Thanks!
[20 May 2008 11:34] Joerg Bruehe
The fix for this limit is included in the sources for 5.0 since 5.0.48,
and in those for 5.1 since 5.1.21, and in 6.0 as well.

I don't understand why this is said to happen in 5.1.23,
but I cannot test myself (lack of environment).

Ryan,
you don't tell which version you are using -
please upgrade to 50.048/5.0.51 or 5.1.21, this should solve it.