Description:
In a .NET application MySQL Connector/NET modifies the connection string so that it contains several occurrences of the same option with different values. For instance:
The original connection string:
host=localhost;database=test;uid=*****;pwd=*****;connect
timeout=25;auto enlist=false;pooling=false;
The connection string after after closing MySqlDataReader:
host=localhost;database=test;uid=*****;pwd=*****;connect
timeout=25;auto enlist=false;pooling=false;Allow User
Variables=True;Allow User Variables=False;Allow User
Variables=True;Allow User Variables=False;
How to repeat:
Create a test table as follows:
DROP TABLE IF EXISTS `test`.`tab_bug_connstr`;
CREATE TABLE `test`.`tab_bug_connstr` (
`id` int(10) unsigned NOT NULL auto_increment,
`c1` int(10) unsigned NOT NULL,
`c2` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `test`.`tab_bug_connst` VALUES (1, 555, '2008-07-01 16:59:04');
Create a stored procedure as follows:
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`proc_bug_connstr` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_bug_connstr`(INOUT param1 DATETIME, INOUT param2 DATETIME)
BEGIN
SELECT * FROM tab_bug_connstr where c2>param1;
SET param1 = NOW();
SET param2 = NOW();
END $$
DELIMITER ;
Please note that the stored procedure has to return result as select and modify the INOUT parameters.
C# test case:
string cs = "host=localhost;database=test;uid=*****;pwd=*****;connect timeout=25;auto enlist=false;pooling=false;";
MySqlConnection c1 = new MySqlConnection(cs);
c1.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = c1;
cmd.CommandText = "proc_bug_connstr";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("param1", (DateTime)new DateTime(2000, 01, 01));
cmd.Parameters["?param1"].Direction = ParameterDirection.InputOutput;
cmd.Parameters.AddWithValue("param2", (DateTime)new DateTime(2000, 01, 01));
cmd.Parameters["?param2"].Direction = ParameterDirection.InputOutput;
MySqlDataReader dr = cmd.ExecuteReader();
Console.WriteLine("Results 1:");
while (dr.Read())
{
Console.WriteLine(dr["c1"].ToString() + " " +dr["c2"].ToString());
}
Console.WriteLine("Connection string 1.0: " + c1.ConnectionString);
dr.Close();
Console.WriteLine("Connection string 1.1: " + c1.ConnectionString);
Console.WriteLine("-----------------------------");
/* Do the same again */
dr = cmd.ExecuteReader();
Console.WriteLine("Results 2:");
while (dr.Read())
{
Console.WriteLine(dr["c1"].ToString() + " " +dr["c2"].ToString());
}
Console.WriteLine("Connection string 2.0: " + c1.ConnectionString);
dr.Close();
Console.WriteLine("Connection string 2.1: " + c1.ConnectionString);
Console.WriteLine("-----------------------------");
c1.Close();
------------------------------
Test case output:
Results 1:
555 01.07.2008 16:59:04
Connection string 1.0: host=localhost;database=test;uid=****;connect timeout=25;auto enlist=false;pooling=false
Connection string 1.1: host=localhost;database=test;uid=****;connect timeout=25;auto enlist=false;pooling=false;Allow User Variables=True;Allow User Variables=False;
-----------------------------
Results 2:
555 01.07.2008 16:59:04
Connection string 2.0: host=localhost;database=test;uid=****;connect timeout=25;auto enlist=false;pooling=false;Allow User Variables=True;Allow User Variables=False;
Connection string 2.1: host=localhost;database=test;uid=****;connect timeout=25;auto enlist=false;pooling=false;Allow User Variables=True;Allow User Variables=False;Allow User Variables=True;Allow User Variables=False;
-----------------------------
Suggested fix:
Looks like when closing the data reader Connector/NET enables and restores the previous value of Allow User Variables parameter. Therefore it is essential to remove previous occurrences of the parameter from the connection string like below:
Index: MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs
===================================================================
--- MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs (revision 1327)
+++ MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs (working copy)
@@ -1228,11 +1228,18 @@
{
if (value == null)
throw new ArgumentException(Resources.KeywordNoNull, keyword);
+ object out_obj;
+ TryGetValue(keyword, out out_obj);
Keyword kw = GetKey(keyword);
SetValue(kw, value);
base[keyword] = value;
if (kw != Keyword.Password)
+ {
+ /* Nothing bad happens if the substring is not found */
+ persistConnString.Replace(keyword + "=" + out_obj.ToString() + ";", "");
+
persistConnString.AppendFormat(CultureInfo.InvariantCulture, "{0}={1};", keyword, value);
+ }
}
private void SetValue(Keyword kw, object value)
Description: In a .NET application MySQL Connector/NET modifies the connection string so that it contains several occurrences of the same option with different values. For instance: The original connection string: host=localhost;database=test;uid=*****;pwd=*****;connect timeout=25;auto enlist=false;pooling=false; The connection string after after closing MySqlDataReader: host=localhost;database=test;uid=*****;pwd=*****;connect timeout=25;auto enlist=false;pooling=false;Allow User Variables=True;Allow User Variables=False;Allow User Variables=True;Allow User Variables=False; How to repeat: Create a test table as follows: DROP TABLE IF EXISTS `test`.`tab_bug_connstr`; CREATE TABLE `test`.`tab_bug_connstr` ( `id` int(10) unsigned NOT NULL auto_increment, `c1` int(10) unsigned NOT NULL, `c2` datetime default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `test`.`tab_bug_connst` VALUES (1, 555, '2008-07-01 16:59:04'); Create a stored procedure as follows: DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`proc_bug_connstr` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_bug_connstr`(INOUT param1 DATETIME, INOUT param2 DATETIME) BEGIN SELECT * FROM tab_bug_connstr where c2>param1; SET param1 = NOW(); SET param2 = NOW(); END $$ DELIMITER ; Please note that the stored procedure has to return result as select and modify the INOUT parameters. C# test case: string cs = "host=localhost;database=test;uid=*****;pwd=*****;connect timeout=25;auto enlist=false;pooling=false;"; MySqlConnection c1 = new MySqlConnection(cs); c1.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = c1; cmd.CommandText = "proc_bug_connstr"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("param1", (DateTime)new DateTime(2000, 01, 01)); cmd.Parameters["?param1"].Direction = ParameterDirection.InputOutput; cmd.Parameters.AddWithValue("param2", (DateTime)new DateTime(2000, 01, 01)); cmd.Parameters["?param2"].Direction = ParameterDirection.InputOutput; MySqlDataReader dr = cmd.ExecuteReader(); Console.WriteLine("Results 1:"); while (dr.Read()) { Console.WriteLine(dr["c1"].ToString() + " " +dr["c2"].ToString()); } Console.WriteLine("Connection string 1.0: " + c1.ConnectionString); dr.Close(); Console.WriteLine("Connection string 1.1: " + c1.ConnectionString); Console.WriteLine("-----------------------------"); /* Do the same again */ dr = cmd.ExecuteReader(); Console.WriteLine("Results 2:"); while (dr.Read()) { Console.WriteLine(dr["c1"].ToString() + " " +dr["c2"].ToString()); } Console.WriteLine("Connection string 2.0: " + c1.ConnectionString); dr.Close(); Console.WriteLine("Connection string 2.1: " + c1.ConnectionString); Console.WriteLine("-----------------------------"); c1.Close(); ------------------------------ Test case output: Results 1: 555 01.07.2008 16:59:04 Connection string 1.0: host=localhost;database=test;uid=****;connect timeout=25;auto enlist=false;pooling=false Connection string 1.1: host=localhost;database=test;uid=****;connect timeout=25;auto enlist=false;pooling=false;Allow User Variables=True;Allow User Variables=False; ----------------------------- Results 2: 555 01.07.2008 16:59:04 Connection string 2.0: host=localhost;database=test;uid=****;connect timeout=25;auto enlist=false;pooling=false;Allow User Variables=True;Allow User Variables=False; Connection string 2.1: host=localhost;database=test;uid=****;connect timeout=25;auto enlist=false;pooling=false;Allow User Variables=True;Allow User Variables=False;Allow User Variables=True;Allow User Variables=False; ----------------------------- Suggested fix: Looks like when closing the data reader Connector/NET enables and restores the previous value of Allow User Variables parameter. Therefore it is essential to remove previous occurrences of the parameter from the connection string like below: Index: MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs =================================================================== --- MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs (revision 1327) +++ MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs (working copy) @@ -1228,11 +1228,18 @@ { if (value == null) throw new ArgumentException(Resources.KeywordNoNull, keyword); + object out_obj; + TryGetValue(keyword, out out_obj); Keyword kw = GetKey(keyword); SetValue(kw, value); base[keyword] = value; if (kw != Keyword.Password) + { + /* Nothing bad happens if the substring is not found */ + persistConnString.Replace(keyword + "=" + out_obj.ToString() + ";", ""); + persistConnString.AppendFormat(CultureInfo.InvariantCulture, "{0}={1};", keyword, value); + } } private void SetValue(Keyword kw, object value)