MySQL Bugs: #12245: using Prepare() on an insert command causes null parameters to convert to "0"

Bug #12245	using Prepare() on an insert command causes null parameters to convert to "0"
Submitted:	28 Jul 2005 18:48	Modified:	10 Aug 2005 18:07
Reporter:	Mark Modrall	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.4	OS:	Windows (Windows 2000)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
I have a program written in C# that inserts records into a database.  I was working to optimize the performance, and I added a cmd.Prepare(); to it; now I find when the insert has a null parameter/value, the null no longer gets into the db as null but is converted to "0" instead.  This messes up some of my downstream reports from the data.  Prepare() was a good, cheap performance boost, but it seems odd that it changes how parameters are handled

How to repeat:
private static readonly string createTable = "create table if not exists test (\n"+
	"test VARCHAR(50)\n" +						") CHARACTER SET UTF8 COLLATE utf8_bin;";
		
private static readonly string insertTable = "Insert into test"+
	" VALUES (?t);";

// Connect to database
MySqlConnection activeDb = new MySqlConnection (odbcConnData);
activeDb.Open();
MySqlCommand myCommand = activeDb.CreateCommand();
			
// Create table for this day's log data if not existent
myCommand.CommandText = createTable;
myCommand.ExecuteNonQuery();

// Set up insert command
myCommand.CommandText = insertTable
myCommand.Prepare();   // causes inserts to change null value

string empty = null;

for (int i=0; i < 10; i++)
{
	myCommand.Parameters.Clear();
	myCommand.Parameters.Add (new MySqlParameter("?t", empty));
	myCommand.ExecuteNonQuery();
}

// If Prepare() is executed, this will fill up the test table with ten "0"s
// If you comment Prepare() out, this will fill up the test table with ten dbnulls
// The latter is what I intended/what the program data stream does; the former
// seems to be a  bug

Suggested fix:
don't have one

I was able to reproduce your bug. Thanks for test code.

mysql> select test,length(test) from test;
+------+--------------+
| test | length(test) |
+------+--------------+
| 0    |            1 |
| 0    |            1 |
| 0    |            1 |
| 0    |            1 |
| 0    |            1 |
| 0    |            1 |
| 0    |            1 |
| 0    |            1 |
| 0    |            1 |
| 0    |            1 |
+------+--------------+
10 rows in set (0.00 sec)

Test case is attached.

Test case

Attachment: 12245.zip (application/x-zip-compressed, text), 6.01 KiB.

I was just looking at the changelist for 4.1.14 and though it's phrased quite differently, I was wondering if this could be related to #9359?  I tried searching the bug database before I submitted this one, but didn't turn this up.  Reading it now makes it seem in some ways similar.

Thanks
_mark

Just found another odd and probably related consequence of using Prepare() - it causes non-ascii characters to get trashed when the value is non-null.  I have a connection to the server with ;charset=utf8 at the end.  The tables it's inserting into are utf8.  But when the text value being inserted contains unicode characters, they get mashed to '?' when Prepare is used.

e.g., based on the submitted example

for (int i=0; i < 10; i++)
{
// insert a null value every other time
if ((i & 1) == 0)
	myCommand.Parameters["t"].Value = empty;
else
	myCommand.Parameters["t"].Value = "澳大利亞";
		
myCommand.ExecuteNonQuery();
}

// the chinese string gets mashed into ???? when Prepare() is used but not 
// regularly.
//
// FYI, I've tried several permutations on this bug to see if I could narrow it down
// but with no luck.  I've tried:
// Putting some stub values into the Parameters collection before Prepare()
// Setting the Parameters["t"].Value instead of Clear() and Add()

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fixed in 1.0 branch

I looked at the changelist for 1.0.4.1 and 1.0.5 and didn't see this one in the list.  Is it fixed in either of those releases or will it get swept into 2.0?

Thanks
_mark

This is fixed.  I may have forgot to set the bug to documenting to signal the docs team.