Bug #19261 Supplying Input Parameters
Submitted: 21 Apr 2006 15:44 Modified: 4 Jun 2006 4:37
Reporter: Victor Bartel Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:1.0.7 OS:Microsoft Windows (Windows)
Assigned to: CPU Architecture:Any

[21 Apr 2006 15:44] Victor Bartel
Description:
Greetings,

During development db applications in .NET, I’ve meet a next problem with supplying input parameters that uses null values in insert sql statement. For example, if have a table with a next structure: 

CREATE TABLE `blacklist` (
  `BlackListID` int(11) NOT NULL auto_increment,
  `SubscriberID` int(11) NOT NULL,
  `Phone` varchar(50) default NULL,
  `ContactID` int(11) default NULL,
  `AdminJunk` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`BlackListID`),
  KEY `SubscriberID` (`SubscriberID`));

And I want to insert some values into my table using a simple sql statement like:

insert into `blacklist`(`SubscriberID`,`Phone`,`ContactID`,`AdminJunk`)
values(?SubscriberID,?Phone,?ContactID, ?AdminJunk);

I take parameters from some web form. And after that I want to form parameters array before DataReader execution, like that:

this.m_tRequestParameter = new object[4];

this.m_tRequestParameter[0] = m_oDBManager.GetParameter("?SubscriberID", 1, DbType.Int32);
this.m_tRequestParameter[1] = m_oDBManager.GetParameter("?Phone","911", DbType.String);
this.m_tRequestParameter[2] = m_oDBManager.GetParameter("?ContactID", 2, DbType.Int32);
this.m_tRequestParameter[3] = m_oDBManager.GetParameter("?AdminJunk", true, DbType.Boolean);

Executing DataReader using the query(sRequest) and parameters array(m_tRequestParameter)

m_oDBManager.ExecuteNonQuery(sRequest, this.m_tRequestParameter);

DataReader execution is successful.

Now I want to change, a little bit, parameters to insert some null values, like that:

this.m_tRequestParameter = new object[4];
this.m_tRequestParameter[0] = m_oDBManager.GetParameter("?SubscriberID", 2, DbType.Int32);
this.m_tRequestParameter[1] = m_oDBManager.GetParameter("?Phone", null);
this.m_tRequestParameter[2] = m_oDBManager.GetParameter("?ContactID", null);
this.m_tRequestParameter[3] = m_oDBManager.GetParameter("?AdminJunk", false, DbType.Boolean);

DataReader execution is successful too.

The problem appears in next step, when I change parameter positions in array like that:

this.m_tRequestParameter[0] = m_oDBManager.GetParameter("?SubscriberID", 2, DbType.Int32);
this.m_tRequestParameter[1] = m_oDBManager.GetParameter("?Phone", null);
this.m_tRequestParameter[2] = m_oDBManager.GetParameter("?AdminJunk", false, DbType.Boolean);
this.m_tRequestParameter[3] = m_oDBManager.GetParameter("?ContactID", null);

I changed AdminJunk and ContactID positions in parameters array. And when I try to execute DataReader, I obtain the next exception message: "#22003Out of range value adjusted for column 'ContactID' at row 1”

If I change differently parameters position in my array like that(Phone and SubscriberID):

this.m_tRequestParameter[0] = m_oDBManager.GetParameter("?Phone", null);
this.m_tRequestParameter[1] = m_oDBManager.GetParameter("?SubscriberID", 2, DbType.Int32);
this.m_tRequestParameter[2] = m_oDBManager.GetParameter("?ContactID", null);
this.m_tRequestParameter[3] = m_oDBManager.GetParameter("?AdminJunk", false, DbType.Boolean);

I obtain other exception message: "#23000Column 'SubscriberID' cannot be null", after DataReader execution.

I have this problem with parameters position only if I want insert NULL(s), but without NULL(s), parameters position has not influence to DataReader execution results (I mean that it executes without problems). What can you suggest me in this situation? 

Thank you in advanced
--
Regards
Victor Bartel 
  

 

How to repeat:
Just try to insert null values into table that contain not null columns, using DataReader. Change place of parameters that contains nulls in MySqlCommand. Parameters array, execute the query.
[24 Apr 2006 9:23] Victor Bartel
There is another example that shows input parameters problem. With the same table(BlackList), that you can see above, I try to affect some insert statement, using 4 input parameters(SubscriberID,Phone,ContactID,AdminJunk). The problem appears when I want to insert null values into columns Phone and ContactID if I change parameters order, exactly when I add them to parameters array. 
Full code source:

	static void Main(string[] args)
		{
			try
			{
				MySqlConnection oConnection = new MySqlConnection("server=localhost;port=3306;user id=root;password=pass;database=test");
				IDbCommand oCommand = null;
				string sRequest = "
insert into `blacklist`(`SubscriberID`,`Phone`,`ContactID`,`AdminJunk`)
values(?SubscriberID,?Phone,?ContactID, ?AdminJunk);";

				MySqlParameter oParameterSubscriberID = new MySqlParameter();
				oParameterSubscriberID.ParameterName = "?SubscriberID ";
				oParameterSubscriberID.DbType = DbType.Int32;
				oParameterSubscriberID.Value = 1;

				MySqlParameter oParameterPhone = new MySqlParameter();
				oParameterPhone.ParameterName = "?Phone";
				oParameterPhone.DbType = DbType.String;
				oParameterPhone.Value = DbNull.Value;

				MySqlParameter oParameterContactID = new MySqlParameter();
				oParameterContactID.ParameterName = "?ContactID";
				oParameterContactID.DbType = DbType.Int32;
				oParameterContactID.Value = DbNull.Value;

				MySqlParameter oParameterAdminJunk = new MySqlParameter();
				oParameterAdminJunk.ParameterName = "?AdminJunk ";
				oParameterAdminJunk.DbType = DbType.Boolean;
				oParameterAdminJunk.Value = True;

				IDbCommand DataAssemblyCommandClass = null;
				DataAssemblyCommandClass = oConnection.CreateCommand();
				DataAssemblyCommandClass.CommandText = sRequest;
				oCommand = (IDbCommand)DataAssemblyCommandClass;

				oCommand.Parameters.Add(oParameterSubscriberID);
				oCommand.Parameters.Add(oParameterPhone);
				oCommand.Parameters.Add(oParameterContactID);
				oCommand.Parameters.Add(oParameterAdminJunk);

				oConnection.Open();
				oCommand.Prepare();
				oCommand.ExecuteNonQuery();
				oConnection.Close();

			}
			catch( Exception ex )
			{
				Console.WriteLine( ex.Message );
				Console.Read();
			}
		}
	}

If I add my parameters in the same order that in sql statement(sRequest), I’ll not have any problem with null values. 
Case example:

oCommand.Parameters.Add(oParameterSubscriberID);
oCommand.Parameters.Add(oParameterPhone);
oCommand.Parameters.Add(oParameterContactID);
oCommand.Parameters.Add(oParameterAdminJunk);

But if I change order of parameters addition, for example like that:

oCommand.Parameters.Add(oParameterSubscriberID);
oCommand.Parameters.Add(oParameterPhone);
oCommand.Parameters.Add(oParameterAdminJunk);
oCommand.Parameters.Add(oParameterContactID);

In DataReader execution it throw next error message: “#22003Out of range
value adjusted for column 'ContactID' at row 1”

If I change the order like that:

oCommand.Parameters.Add(oParameterPhone);
oCommand.Parameters.Add(oParameterSubscriberID);
oCommand.Parameters.Add(oParameterContactID);
oCommand.Parameters.Add(oParameterAdminJunk);

I obtain other error message: #23000Column 'SubscriberID' cannot be null".

I repeat, this problem with parameters order appears only with null values.
[26 Apr 2006 6:55] Tonci Grgin
Hi. Thanks for great problem report. I was unable to repeat it with latest NET build. For us to build test case, you should provide NET runtime version (for Win) or if it's Mono, version, and what OS platform/version), server version and OS version.
Check my code, all I did is to add "oCommand.Parameters.Clear();" at appropriate places. Please, try it that way and inform me of the result.
[26 Apr 2006 6:56] Tonci Grgin
Test case

Attachment: Program.cs (text/plain), 4.64 KiB.

[26 Apr 2006 6:57] Tonci Grgin
Sorry, my console output:

Connecting to Mysql DB

Testing Bug 19261, same order as in select, no NULL values
Testing Bug 19261, same order as in select, no NULL values - OK

Testing Bug 19261, different order than in select
Testing Bug 19261, different order than in select - OK

Testing Bug 19261, same order as in select, NULL values
Testing Bug 19261, same order as in select, NULL values - OK

Press any key to exit
[26 Apr 2006 7:08] Tonci Grgin
Changing status according to SOP.
[26 Apr 2006 8:01] Victor Bartel
Greetings,

Mister Grgin, you’ve tested all cases, except problems case. See my example cases – 4rd and 5rd. I repeat you, for one table with structure like that:

CREATE TABLE `blacklist` (
  `BlackListID` int(11) NOT NULL auto_increment,
  `SubscriberID` int(11) NOT NULL,
  `Phone` varchar(50) default NULL,
  `ContactID` int(11) default NULL,
  `AdminJunk` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`BlackListID`),
  KEY `SubscriberID` (`SubscriberID`),
  CONSTRAINT `blacklist_ibfk_1` FOREIGN KEY (`SubscriberID`) REFERENCES `subscribers` (`SubscriberID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

If you inverse order of parameters when you try to insert nulls in Phone or ContactID columns of this table, you’ll have interesting exceptions. Please see my example, cases 4 and 5. Other information:  we use windows platforms, IIS v 5.1, Framework .NET v 1.1.

Thank you in advance.

Best regards 
Victor Bartel

Code:

using System;
using System.Collections;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace CABug19261test
{
	class Program
	{
		static void Main(string[] args)
		{
			Console.WriteLine("Connecting to Mysql DB");
			String connectionString = "server=localhost;port=3306;user id=root;password=;database=test";//Connection Timeout=5;Pooling=false;Max Pool Size=80;Min Pool Size=20;
			MySqlConnection oConnection = new MySqlConnection();

			try
			{
				//Create the connection to the db server
				oConnection.ConnectionString = connectionString;
				oConnection.Open();

				//PREP CASE

				//Create the db command
				IDbCommand oCommand = null;
				string sRequest = "insert into `blacklist`(`SubscriberID`,`Phone`,`ContactID`,`AdminJunk`) values (?SubscriberID,?Phone,?ContactID, ?AdminJunk);";

				MySqlParameter oParameterSubscriberID = new MySqlParameter();
				oParameterSubscriberID.ParameterName = "?SubscriberID";
				oParameterSubscriberID.DbType = DbType.Int32;
				oParameterSubscriberID.Value = 1;

				MySqlParameter oParameterPhone = new MySqlParameter();
				oParameterPhone.ParameterName = "?Phone";
				oParameterPhone.DbType = DbType.String;
				oParameterPhone.Value = "123456";

				MySqlParameter oParameterContactID = new MySqlParameter();
				oParameterContactID.ParameterName = "?ContactID";
				oParameterContactID.DbType = DbType.Int32;
				oParameterContactID.Value = 19261;

				MySqlParameter oParameterAdminJunk = new MySqlParameter();
				oParameterAdminJunk.ParameterName = "?AdminJunk";
				oParameterAdminJunk.DbType = DbType.Boolean;
				oParameterAdminJunk.Value = true;

				IDbCommand DataAssemblyCommandClass = null;
				DataAssemblyCommandClass = oConnection.CreateCommand();
				DataAssemblyCommandClass.CommandText = sRequest;
				oCommand = (IDbCommand)DataAssemblyCommandClass;

				//EXECUTE
				//1st case, ok
				Console.WriteLine("\nTesting Bug 19261, same order as in select, no NULL values");
				oCommand.CommandText = sRequest;

				oCommand.Parameters.Add(oParameterSubscriberID);
				oCommand.Parameters.Add(oParameterPhone);
				oCommand.Parameters.Add(oParameterContactID);
				oCommand.Parameters.Add(oParameterAdminJunk);

				oCommand.Prepare();
				oCommand.ExecuteNonQuery();
				Console.WriteLine("Testing Bug 19261, same order as in select, no NULL values - OK");

				//2nd case, exception
				Console.WriteLine("\nTesting Bug 19261, different order than in select");
				oCommand.Parameters.Clear();
				oCommand.Parameters.Add(oParameterSubscriberID);
				oCommand.Parameters.Add(oParameterPhone);
				oCommand.Parameters.Add(oParameterAdminJunk);
				oCommand.Parameters.Add(oParameterContactID);

				oCommand.Prepare();
				oCommand.ExecuteNonQuery();
				Console.WriteLine("Testing Bug 19261, different order than in select - OK");

				//3rd case, exception
				Console.WriteLine("\nTesting Bug 19261, same order as in select, NULL values");
				oCommand.Parameters.Clear();

				oParameterPhone.Value = System.DBNull.Value;
				oParameterContactID.Value = System.DBNull.Value;

				oCommand.Parameters.Add(oParameterSubscriberID);
				oCommand.Parameters.Add(oParameterPhone);
				oCommand.Parameters.Add(oParameterContactID);
				oCommand.Parameters.Add(oParameterAdminJunk);

				oCommand.Prepare();
				oCommand.ExecuteNonQuery();
				Console.WriteLine("Testing Bug 19261, same order as in select, NULL values - OK");

				//It is not END test case
				
				// ************************ DESCRIBED PROBLEM  ***************************** //

				//4rd case, exeption
				
				Console.WriteLine("\nTesting Bug 19261, other order as in select, NULL values");
				oCommand.Parameters.Clear();

				oParameterPhone.Value = System.DBNull.Value;
				oParameterContactID.Value = System.DBNull.Value;

				oCommand.Parameters.Add(oParameterSubscriberID);
				oCommand.Parameters.Add(oParameterPhone);
				oCommand.Parameters.Add(oParameterAdminJunk);
				oCommand.Parameters.Add(oParameterContactID);

				oCommand.Prepare();
				oCommand.ExecuteNonQuery();
				Console.WriteLine("Testing Bug 19261, other order as in select, NULL values - OK");

				//5rd case, exeption

				Console.WriteLine("\nTesting Bug 19261, other order as in select, NULL values");
				oCommand.Parameters.Clear();

				oParameterPhone.Value = System.DBNull.Value;
				oParameterContactID.Value = System.DBNull.Value;

				oCommand.Parameters.Add(oParameterPhone);
				oCommand.Parameters.Add(oParameterSubscriberID);
				oCommand.Parameters.Add(oParameterContactID);
				oCommand.Parameters.Add(oParameterAdminJunk);

				oCommand.Prepare();
				oCommand.ExecuteNonQuery();
				Console.WriteLine("Testing Bug 19261, other order as in select, NULL values - OK");
			
				Console.WriteLine("\nPress any key to exit");
				//Console.ReadKey();
				oConnection.Close();
			}
			catch (Exception ex)
			{
				Console.WriteLine(ex.Message);
				//Console.ReadKey();
				oConnection.Close();
			}

		}

	}
}
[26 Apr 2006 8:02] Victor Bartel
Test file with problem cases(See case 4 and 5)

Attachment: test.cs (application/octet-stream, text), 5.05 KiB.

[26 Apr 2006 8:46] Tonci Grgin
Hi. I rushed to conclusion at first. Confirm this is a bug although it has nothing to do with NULL values and error reported is missleading. Parameter substitution is straight forward not checking for proper column mapping. Moreover, tests having different param order than in select which do not fail produce wrong updates! As a workaround, plase use same param order as in select.
[26 Apr 2006 9:37] Victor Bartel
Dear Sir,

Now you see that it was a « real » bug. The solution that you propose is completely unfit for us, because we using automated parameters generation, and addition, we can not preview and control parameters order in generation level. This bug was observed by us, and we can acknowledge that this bug becomes apparent when your .NET connector try to order parameters that were forward in differently order relative to command statement. Can you propose other solution? Our test shows that there is no same problems with others data base servers like (postgreSQL and mssql server), using their .NET connectors. In all case it is too late for us to change data base server. We have chosen you products because we supposed that you are serious organisation that can resolve any problem. I hope that you will find more stable solution. Thank you for understanding.  

Regards
Victor Bartel
[26 Apr 2006 16:37] Victor Bartel
Hi,

Excuse me, I was a little bit speedy and very tough with my previous message. Do you think that it exists another solution for this problem? Because it seems to appear when .net connector performs parameters substitution, and this process does correct substitution with not null parameters independently their order. But this substitution capacity doesn’t work if we use null parameters. It is not very ease to follow the parameters order of sql statement, usually when we use hashtables contain parameters. Thanking you in anticipation for your help.

Victor Bartel
[26 Apr 2006 20:49] Tonci Grgin
Hi Victor. We came to the same conclusion. Currently we're doublechecking. Hope to find solution soon.
[3 May 2006 11:24] Victor Bartel
Greetings,

 I’ve analyzed mysql .net connector, and I came to conclusion that this problem emanate not from connector level. When MySqlCommand prepare a statement and parameters, this one effect not a substitution for parameters (with null or not null values), that are not situated in the same order that in sql statement. After that when MySqlCommand effect ExecuteNonQuery method, and even in this method command object effects not the substitution for not ordered parameters, after that it serialize all not null parameters for transport ones via data base server, using your own protocol. I guess you effect the parameter substitution in server side, it means that we have not so many solutions. I’ve developed one, it’s maybe brutal, but I have not another idea. I think that we can order input parameters after lexical analyze (Tokenization) of sql statement in Preapare() method. What do you think about that?  Thanks for all.

--
Best regards
Victor Bartel.
[3 May 2006 12:49] Tonci Grgin
Thanks, Victor. Forwarded to others for further disscusion.
[5 May 2006 7:53] Victor Bartel
Hi Tonci,

I’ve corrected this bug. I realized a substitution of input parameters in statement order. I guess it is the simplest solution in this case. You can see my modifications in attached file(PreparedStatements.cs), see lines from 70 to 90. Say what you think about that, say if my modification can be add to your connector release? Thanks in advance.

--
Victor Bartel.
[5 May 2006 7:54] Victor Bartel
Modifications

Attachment: PreparedStatements.cs (application/octet-stream, text), 13.51 KiB.

[18 May 2006 17:49] 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/6583
[1 Jun 2006 18:39] Reggie Burnett
Fixed in 1.0.8
[4 Jun 2006 4:37] MC Brown
Documented in the changelog.