Bug #37101 Can't create a new thread (errno 12)
Submitted: 30 May 2008 13:08 Modified: 19 Jul 2008 14:42
Reporter: onur ozturk Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Errors Severity:S2 (Serious)
Version:5.0.51b OS:Windows (server 2003 standart X64)
Assigned to: CPU Architecture:Any
Tags: .trg file not found, Can't create a new thread (errno 12)

[30 May 2008 13:08] onur ozturk
Description:
Problem Details;
i get below error per 5 to 10 minutes

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[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

and also i see in mydatabase.err ;
080530 15:44:24 [ERROR] C:\Program Files (x86)\SWsoft\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Deadlock found when trying to get lock; try restarting transaction
080530 15:44:24 [ERROR] C:\Program Files (x86)\SWsoft\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Sort aborted

and also i see one error in windos filemon;
Request : query information
path : C:\Program Files (x86)\SWsoft\Plesk\Databases\MySQL\Data\mydatabasename\mytablename.TRG
Result: Not Found

computer details;
win server 2003 standart X64
intel xeon 3040 @ 1.86GHz
4 GB ram
Plesk 8.3 installed
mysql version : 5.0.51b 

i have website counter published in and there are many connections 2.000.000 connections daily.
i only use just 3 mysql functions per connection as ; insert into ....., select and delete functions

usage of cpu : %10
useage of ram : 2.20 GB

i try [14 Jul 2007 13:18] Thomas Kempster solution http://bugs.mysql.com/bug.php?id=28387 but it doesnt solve my problem

i have two my.ini files in plesk directory and i dont know which one i need to configer, i just try to add wait_timeout=460 in first ini file and i cant get solition please help me

My.in at C:\Program Files (x86)\SWsoft\Plesk\MySQL\Data directory;

[PleskSQLServer]
port=8306
basedir=C:\\Program Files (x86)\\SWsoft\\Plesk\\MySQL
datadir=C:\\Program Files (x86)\\SWsoft\\Plesk\\MySQL\\Data
default-character-set=latin1
default-storage-engine=INNODB
query_cache_size=8M
table_cache=32
tmp_table_size=7M
thread_cache=32
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=2M
key_buffer_size=2M
read_buffer_size=1M
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=16M
innodb_log_file_size=10M
innodb_thread_concurrency=8
max_connections=300
key_buffer=4M
max_allowed_packet=1M
sort_buffer=256K
net_buffer_length=4K
old_passwords=1
wait_timeout=460 # this my try for Thomas Kempster solution http://bugs.mysql.com/bug.php?id=28387 
tmpdir=C:\\Program Files (x86)\\SWsoft\\Plesk\\admin\\db
[client]
port=8306

-----------------------------------------------

My.ini at C:\Program Files (x86)\SWsoft\Plesk\Databases\MySQL\Data directory;

[client]

port=3306

[mysql]

default-character-set=latin1
port=3306

basedir="C:/Program Files (x86)/SWsoft/Plesk/Databases/MySQL/"

datadir="C:/Program Files (x86)/SWsoft/Plesk/Databases/MySQL/Data/"

default-character-set=latin1

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=100

query_cache_size=0

table_cache=256
tmp_table_size=205M

thread_cache_size=8

myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=410M
key_buffer_size=354M

read_buffer_size=64K
read_rnd_buffer_size=256K

sort_buffer_size=256K

innodb_additional_mem_pool_size=15M
innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=7M

innodb_buffer_pool_size=686M

innodb_log_file_size=10M
innodb_thread_concurrency=8

How to repeat:
.
[30 May 2008 13:18] MySQL Verification Team
Thank you for the bug report. Please try reducing to the minimum possible the
table_cache=256 i.e: table_cache=32 and test again.
[30 May 2008 13:35] onur ozturk
but i have two ini file and i dont know which one i need to configure first one or second one

and what value i need to get
like;
table_cache=256 to table_cache=128 for first ini
and 
table_cache=32 to table_cache=16 for secont ini
can u explane more pls
[30 May 2008 13:38] MySQL Verification Team
Change the my.ini which the server uses.
[30 May 2008 14:09] onur ozturk
i change my.ini like below
table_cache=32 #it was 256
wait_timeout=460 # there was no wait_timeout descriptin before

now it seems working good
and total ram usage go down from 2.2 GB to 1.8 GB and 
cpu usage is go a little up from %15 to %25

i ll wait and report what will happen
[30 May 2008 16:59] onur ozturk
it solve the problem of the man error below

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[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

but when i look at error log files and filemon i see two errors still

i see in mydatabase.err ;
080530 15:44:24 [ERROR] C:\Program Files
(x86)\SWsoft\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Deadlock found when trying to get
lock; try restarting transaction
080530 15:44:24 [ERROR] C:\Program Files
(x86)\SWsoft\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Sort aborted

-------------------------------

i see one error in windos filemon;
Request : query information
path : C:\Program Files
(x86)\SWsoft\Plesk\Databases\MySQL\Data\mydatabasename\mytablename.TRG
Result: Not Found

what that two errors mean
thanks
[19 Jul 2008 14:42] Valeriy Kravchuk
The first error message means you had a deadlock in InnoDB. They are always possible and your application should just expect them and try to repeat same actions later.

The second one means some trigger on your mydatabasename.mytablename table exists but corresponding .TRG file was deleted somehow.