| Bug #49118 | Errors in MySql.Data.MySqlClient.StoredProcedure.GetParameters(String procName) | ||
|---|---|---|---|
| Submitted: | 26 Nov 2009 0:43 | Modified: | 28 Jun 2010 10:53 |
| Reporter: | Stephanie Challand | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S1 (Critical) |
| Version: | 6.1.3 | OS: | Any |
| Assigned to: | Vladislav Vaintroub | CPU Architecture: | Any |
| Tags: | Connector .NET, Procedure Parameters | ||
[26 Nov 2009 0:43]
Stephanie Challand
[26 Nov 2009 11:33]
Tonci Grgin
Hi Stephanie and thanks for your report. Being that it was you who said "I cannot replicate this on our dev box because it is traffic related and cannot generate enough traffic there." how do you expect me to generate it? And without your code or anything? So, we'll have to try working from outside unless you can come up with some small but *complete* test project I can run on my box. What is you .NET FW version? What is the MySQL server version? If you're using MySQL.Web, what is the version shown in my_aspnet_SchemaVersion table? It should be 6 (my_aspnet_SchemaVersion.version=6). To repeat, without failing test case I can run on my box I do not have anything to "fix"...
[27 Nov 2009 13:14]
Tonci Grgin
Stephanie, just few things... No I do not have idea on how to reproduce this, yet. And I do not dismiss reports just like that. Will consult with others. Now, consider making your private comments public as I do not see anything compromising in them. It would make easier for others to follow. What is your exact connection string (port, username and password are of no interest so you might leave it blank)?
[27 Nov 2009 13:27]
Stephanie Challand
Hello,
I am not sure how we can reproduce this. I have done some more digging and it appears to
be mostly bots (Google, etc) that are generating this error. My guess is because they
cover the pages so quickly compared to *normal* users.
From the query logs it looks like the second time a stored procedure is called on the
same connection the Duplicate error occurs. I have dug into the other one yet as this one
occurs much more frequently. I have been looking into the MySql code for this section
(StoredProcedure.Resolve()). From all the digging I have done it appears that the
Procedure Cache is not being handled quite right if more than one call to the same stored
procedure occurs in a very small amount of time, hence the duplicate table 'Procedure
Parameters' (StoredProcedure.GetParameters()).
1126 22:09:31 17 Query SHOW GLOBAL STATUS
17 Query SHOW INNODB STATUS
9 Query SHOW CREATE PROCEDURE `aaaa`.`SP1`
9 Query call `aaaa`.`SP1` (1, 12, 206, 'F', '%', '%', '%', '%',
'%', 0, 'UploadDate', 'DESC')
10 Init DB bbbb
10 Query call `bbbb`.`SP2` ('3 - Photo Gallery')
091126 22:09:32 17 Query SHOW GLOBAL STATUS
17 Query SHOW INNODB STATUS
4 Init DB bbbb
4 Query call `bbbb`.`SP3` ('xyz.org')
5 Init DB aaaa
5 Query SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE
'aaaa' AND ROUTINE_NAME LIKE 'SP1'
50 Init DB bbbb
50 Query call `bbbb`.`SP3` ('xyz.org')
46 Init DB aaaa
46 Query SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE
'aaaa' AND ROUTINE_NAME LIKE 'SP1'
5 Query SHOW CREATE PROCEDURE `aaaa`.`SP1`
5 Query call `aaaa`.`SP1` (1, 12, 206, 'F', '%', '%', '%', '%',
'%', 0, 'UploadDate', 'DESC')
46 Query SHOW CREATE PROCEDURE `aaaa`.`SP1`
46 Query call `aaaa`.`SP1` (1, 12, 206, 'F', '%', '%', '%', '%',
'%', 0, 'UploadDate', 'DESC')
10 Init DB bbbb
4 Init DB bbbb
10 Query call `bbbb`.`SP2` ('3 - Photo Gallery')
72 Init DB cccc
72 Query call `cccc`.`ErrorLog` ('Type: System.ArgumentException<br>Message:
Table Procedure Parameters does not belong to this DataSet.<br>Inner: ',
'System.Web.Mobile.MobileCapabilities', 'Mediapartners-Google',
'Connection=Keep-alive&Accept=*%2f*&Accept-Encoding=gzip%2cdeflate&Host=www.designermixes.org&User-Agent=Mediapartners-Google',
'False', NULL, '66.249.71.213', ' at System.Data.DataTableCollection.CanRemove(DataTable
table, Boolean fThrowException)
I have tried changing some settings. I changed the thread_concurrency to 8 (it was at 30
not sure why) and the wait_timeout to 60. It seems like it may have helped some as I only
had about 2500 errors today. I just added the PROCEDURE CACHE SIZE=0; to the connection
string and have received only one error since (about 10 minutes). Not sure why I still
got that as if I read that correctly the procedure cache should not be used at all
then...
--------------------------------------------------------------------
What is you .NET FW version? 3.5
What is the MySQL server version? 5.1.41
If you're using MySQL.Web, what is the version shown in my_aspnet_SchemaVersion table? It
should be 6 (my_aspnet_SchemaVersion.version=6). Not using
--------------------------------------------------------------------
Thank You,
Stephanie
[27 Nov 2009 13:30]
Stephanie Challand
*Reposted last comment to be public* I am using several databases below is the connection to the one that receives the errors. SERVER=1.2.3.4;DATABASE=xyz;PORT=123;USERID=xxx;PASSWORD=yyy;POOLING=true;USE PROCEDURE BODIES=false;PROCEDURE CACHE SIZE=0; Thanks!
[30 Nov 2009 13:12]
Stephanie Challand
I am also receiving this error: The given name 'procedure parameters' matches at least two names in the collection object with different cases, but does not match either of them with the same case. Any luck with this? This is a huge issue for us with over 12,000 errors yesterday. I am happy to help in any way that I can.
[3 Dec 2009 11:14]
Tonci Grgin
Stephanie, I'm looking at all this and not finding any sane way to make the test. Please attach *small* but *complete* test case (together with DDL/DML statements and such) that fails when you run it. I suspect there's something in your code triggering this error.
[3 Dec 2009 14:02]
Stephanie Challand
Hello,
I cannot attach a small test case as this is traffic dependent and I have tried to reproduce this but cannot. I can tell you that I have been running the same code for over 2 years and everything has worked fine. As my traffice increased I have been looking for ways to optimize everything and thought upgrading would be a good way...not so much.
I did some testing and found that the 6.1.3 and the 6.0.5 connector have this issue. I went back to the 5.2.7 connector and the errors are gone. I went from over 12,000+ errors for 3-4 days in a row to basically none literally overnight.
That tells me there is either something in those versions of the connector or there is some *bug* in the 5.2.7 and previous versions that allow our code to work without issue.
I cannot give you DDL/DML statments for this as it is every Stored Procedure in our database that was throwing this error. We have 8 websites running on a single server. Only one of them has heavy traffic (80,000+ visits per month) and this is the only site throwing these errors.
Below is the code that calls our stored procedures. It is located in a .dll that all the website access.
protected DataSet ExecuteDataSet(out MySqlCommand cmd, string procName, int iConn, params IDataParameter[] procParams)
{
MySqlConnection cnx = null;
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter();
cmd = null;
try
{
//Setup command object
cmd = new MySqlCommand(procName);
cmd.CommandType = CommandType.StoredProcedure;
if (procParams != null)
{
for (int index = 0; index < procParams.Length; index++)
{
cmd.Parameters.Add(procParams[index]);
}
}
da.SelectCommand = (MySqlCommand)cmd;
//Determine the transaction owner and process accordingly
if (_isOwner)
{
cnx = new MySqlConnection(GetConnectionString(iConn));
cmd.Connection = cnx;
cnx.Open();
}
else
{
cmd.Connection = _txn.Connection;
cmd.Transaction = _txn;
}
//Fill the dataset
da.Fill(ds);
return ds;
}
catch
{
throw;
}
finally
{
if (da != null) da.Dispose();
if (ds != null) ds.Dispose();
if (cmd != null) cmd.Dispose();
if (_isOwner)
{
cnx.Dispose(); //Implicitly calls cnx.Close()
}
}
//return ds;
}
I still suspect there is an issue with threading. From the error messages I researched and they were coming from the StoredProcedure.cs class Resolve method where it tries to get the parameter information. From the logs (I provided you a trace earlier) it seems the cache would get *confused* when there were multiple calls to the same stored procedure on the same connection within the same second. I would believe this should work with out issue. Let me know if I can provide any more information.
[3 Dec 2009 14:25]
Tonci Grgin
Stephanie, the structures you're talking about are global and not protected against simultaneous modifications. I'll assign Wlad to check the sources.
[29 Dec 2009 23:00]
chandra lingam
There is an easy way to detect concurrency issues. I am able to reproduce this issue consistently by using microsoft Task Parallel Extensions. I am using Microsoft Parallel Extensions Jun08 CTP. Looks like current library is bundled as part of .NET Framework 4.0 beta.
Idea is to construct a parallel.for loop and invoke a function that in turn creates a db connection and invokes a simple mysql stored procedure that returns one row back. This program will create a condition where multiple threads are invoking the mysql .NET connectors in parallel.
Here is the source code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
// TPL Namespaces
using System.Threading;
using System.Threading.Tasks;
using System.Threading.Collections;
// end tpl namespaces
namespace MySqlBugReport
{
class MySample
{
// Invoke mysql stored procedure. this stored procedure returns couple of rows
public List<string> CallStoredProcedure()
{
MySqlConnection dbConnection = null;
List<string> myList = new List<string>();
try
{
dbConnection = new MySqlConnection("server=my_server;user=my_user;database=test;port=3306;password=my_password;Use Procedure Bodies=false;");
dbConnection.Open();
MySqlCommand cmd = new MySqlCommand("mystoredprocedure", dbConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
using (MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (rdr.Read())
{
myList.Add(rdr.GetValue(0).ToString());
}
rdr.Close();
}
return myList;
}
finally
{
if (dbConnection != null)
{
if (dbConnection.State != ConnectionState.Closed)
dbConnection.Close();
}
}
}
static void Main(string[] args)
{
// Program to reproduce this error "A DataTable named 'Procedure Parameters' already belongs to this DataSet."
// System.Threading.AggregateException: Multiple exceptions have been thrown. ---> System.Data.DuplicateNameException: A DataTable named 'Procedure Parameters' already belongs to this DataSet.
try
{
// Parallel Loop - Iterate 100 times.
Parallel.For(0, 100, (i, loopState) =>
{
MySample mysam = new MySample();
List<string> retVal = mysam.CallStoredProcedure();
foreach (string item in retVal)
{
Console.WriteLine(item);
}
});
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message + ex.InnerException);
}
}
}
}
[29 Dec 2009 23:27]
chandra lingam
I can further confirm that version 5.2.7 of the connector does not seem to have to this issue.
[11 Jan 2010 19:53]
Jesse P.
I am also having the same issue. I've noticed it with all the 6.x.x versions that I've used. Currently trying with the newest 6.2.2 and the issue still exists. I saw it very infrequently, but it was present, when running my code on my windows box (over cable internet). When running the code on my datacenter housed server (over uncapped 100mb/s link) running on Mono 2.6.1, I see it constantly. Per others comments, I think this is attributed to the fact that the queries are to the same stored procedure, and they happen MUCH more quickly on the server (local network to the MySQL server). All queries are single threaded right now, so at least for me it isn't an issue there. Thanks, Jesse
[6 Mar 2010 2:53]
Stephanie Challand
Any word on this yet??
[7 Apr 2010 8:45]
Adam Heunis
Same problem here. Using VS2008/Framework 3.5. My connectionstring is Server=localhost;User Id=xxxx;Password=xxxx;Persist Security Info=True;Database=xxxx;use procedure bodies=False;
[8 Apr 2010 14:11]
Tonci Grgin
Pinged Wlad about the status.
[17 Jun 2010 16:54]
Tong Yin Wang
Changed to Use Procedure Bodies=true works for me. :)
[22 Jun 2010 11:44]
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/111757 817 Vladislav Vaintroub 2010-06-22 Fix race condition (concurrent reading/modification of the same DataSet) in StoredProcedure.GetParameters() (bug#49118)
[22 Jun 2010 11:48]
Vladislav Vaintroub
queued into 6.0,6.1,6.2,trunk
[23 Jun 2010 17:35]
Reggie Burnett
fixed in 6.0.7, 6.1.5, 6.2.4, and 6.3.3+
[28 Jun 2010 10:53]
Tony Bedford
An entry has beed added to the 6.0.7, 6.1.5, 6.2.4, and 6.3.3 changelogs: When an application was subjected to increased concurrent load, MySQL Connector/NET generated the following error when calling stored procedures: A DataTable named \'Procedure Parameters\' already belongs to this DataSet.
