| Bug #21386 | Connection must be valid and open MySqlDataReader | ||
|---|---|---|---|
| Submitted: | 1 Aug 2006 13:38 | Modified: | 22 Aug 2006 7:53 |
| Reporter: | Henkjan Pluim | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S5 (Performance) |
| Version: | 1.0.0.7 | OS: | Windows (Windows 2003 Server) |
| Assigned to: | CPU Architecture: | Any | |
[10 Aug 2006 6:43]
Tonci Grgin
Hello and thanks for your problem report. I am not sure I can build test case with info provided. Can you please post/attach following to this bug report: * SQL script suitable for import containing all relevant data needed to reproduce the error * Small but complete test case exhibiting this error on data provided * my.ini/cnf file from your server * hostname.err file from your MySQL server host (usually in datadir) * Net framework version * Any info you deem relevant
[22 Aug 2006 7:15]
Tonci Grgin
Hi. What about my requests?
[22 Aug 2006 10:22]
Tonci Grgin
Hi Henkjan and thanks for your feedback.
Description: I am building a high performance MySQl 5.0 database solution where i have a table with over 500.000 records. The get specific information i have to query more then four other tables. I am using the .NET connector tool to connect to the database. First i run a stored procedure that returns only a identification (integer) value and i use this value in a combined stored procedure which returns 3 resultsets. I have constructed the following code in more generic terms: With the results of the first DataReader Object i fill a local variable which i use in another MySqlDataReaderobject which returns three result sets which i use to set some properties of object MySqlCommand comm1; MySqlCommand comm2; MySqlDataReader dr = comm1.ExecuteReader(); try { while (dr.Read()) { MySqlDataReader drArticle = comm2.ExecuteReader(); while (drArticle.Read()) { //set some properties with the first resultset } drArticle.NextResult(); //second resultset while (drArticle.Read()) { //set some properties with the second resultset } //third resultset drArticle.NextResult(); while (drArticle.Read()) { //set some properties with the third resultset } //Close the Connection comm2.Connection.Close(); } //Close the Connection comm1.Connection.Close(); The code works fine but there are 12000 queries executed per minute to the database, after a small time this error is thrown. It works fine with 600 queries per minute but with more queries this error is thrown. How to repeat: I think it can be repeated using the above code and building some different different stored procedures like which the two MySQlDataReader objects Execute. DELIMITER $$ CREATE PROCEDURE `spValidID`( IN pTag int ) BEGIN SELECT ID FROM table WHERE Tag = pTag; END $$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `spCombined`( IN pID int ) BEGIN CALL spArticle_G(pID ); CALL spArticleAttributes_G(pID); CALL spArticleMarkers_G(pID); END $$ DELIMITER ;