Mysql suddenly stop all connections during operation

[Jerry Leung]

Hi,

I'm a beginner on mysql with asp.net. Now I'm writting a stock management system by using of mysql 5.0, mysql connector .Net 1.0.7 and microsoft .Net framework 1.1. The system principal is collected all stock information, material information, and supplier information, and many other related information into database, then through different functional web pages (which backend is written by asp.net) to process data from database, such as extract data, insert data, update data from difference database tables.

Some functional pages will be extracted data from different tables as same time during the loop(at inital, I will call a MysqlConnection, connection.open() at the main portion. Then call new data reader object to read data, run a loop to read data from table in sequence, and will call sub-function to do further extraction process from difference tables, everytime I extract data from sub-functions will be call a new MysqlConnection, new connection.open(), new datareader object to read data. After extract process finished, wil call datareader.close(), connection.close() at the end of each call.), For each loop, I will do these until the loop end. The sample code was list as below:

(e.g. main core code)
sub extract_material_code()
Dim material_code as integer
Dim myconstring as string= "Database=yap; Data Source=localhost; User Id=root; Password=xxxxxxx"
Dim myConnection as new MysqlConnection(myconstring)
myConnection.open()
Dim sSQL as string = "select * from material_info"
Dim mycmd as new mysqlcommand(sSQL, myConnection)
Dim objDR as Mysqldatareader = mycmd.executereader()
Do While objDR.Read()
material_code = objDR.GetValue(0)
extract_product_name(material_code)
Loop
objDR.close()
myConnection.close()
end sub

(e.g. sub-function code)
sub extract_product_name(b as integer)
Dim product_name as string
Dim myconstring as string= "Database=yap; Data Source=localhost; User Id=root; Password=xxxxxxx"
Dim myConnection as new MysqlConnection(myconstring)
myConnection.open()
Dim sSQL as string = "select * from product_info"
Dim mycmd as new mysqlcommand(sSQL, myConnection)
Dim objDR as Mysqldatareader = mycmd.executereader()
Do While objDR.Read()
product_name = objDR.GetValue(0)
response.write("product name : " & product_name & "<br>")
Loop
objDR.close()
myConnection.close()
end sub



At some functional asp page, it will be very slow on display the result. even no other user to do other thing on the system. And some time I found that the system suddenly cannot extract/connection to database, on the webpage it will disclose following message "error connecting: TImeout expired. THe timeout period elapsed prior to obtaining a connection from the pool. This may have occurred becuase all pooled connections were in use and max pool size was reached". I check the database on administration GUI, The database like to stop all connection from outside. Everytime this problem happened, I will try to restart the database, but also cannot let the database become well. The only way is restart whole server, Then it will come well again.

What is the problem happened on my system?
I need to overcome the problem asap.

Thanks

Jerry Leung