MySQL Bugs: #20707: NULLs get passed as parameters to stored procedure.

Bug #20707	NULLs get passed as parameters to stored procedure.
Submitted:	26 Jun 2006 21:18	Modified:	29 Jun 2006 7:51
Reporter:	Tristan Aubrey-Jones	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.7	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Ok this has got me completely stumpted. Ive debugged the libaries source and I just cannot work out whats going on!? Basically ive got a simple stored procedure that inserts values passed to it as parameters into a table. I call it and all seems fine, it creates the record only when I take a look using the mysql console - the record is full of NULLs! I then modified the table to NOT NULL the columns - and now I get an exception saying that the UserName column needs a non null value; despite the fact I pass in the parameters. The odd thing is this doesnt occur when I invoke the stored procedure directly using the mysql console. The parameter values seem to disappear deep in the depths of the driver.

How to repeat:
SQL TO RUN BELOW:

DELIMITER |

CREATE TABLE IF NOT EXISTS UserLogin
(
  ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
  UserName VARCHAR(30) NOT NULL,
  Password_Hash BINARY(20) NOT NULL,
  Password_Salt BINARY(10) NOT NULL,
  IsLockedOut BIT NOT NULL,
  IsOwner BIT NOT NULL,
  IsBetaTester BIT NOT NULL,
  IsAdministrator BIT NOT NULL,
  Status TINYINT UNSIGNED NOT NULL
)|

CREATE PROCEDURE Sp_UserLogin_Create
(
  IN iUserName VARCHAR(30),
  IN iPassword_Hash BINARY(20),
  IN iPassword_Salt BINARY(10),
  IN iIsLockedOut BIT,
  IN iIsOwner BIT,
  IN iIsBetaTester BIT,
  IN iIsAdministrator BIT,
  IN iStatus TINYINT UNSIGNED,
  OUT oID INT UNSIGNED
)
BEGIN
  INSERT INTO UserLogin
  (
    UserName,
    Password_Hash,
    Password_Salt,
    IsLockedOut,
    IsOwner,
    IsBetaTester,
    IsAdministrator,
    Status
  )
  VALUES
  (
    iUserName,
    iPassword_Hash,
    iPassword_Salt,
    iIsLockedOut,
    iIsOwner,
    iIsBetaTester,
    iIsAdministrator,
    iStatus
  );
  SET oID = LAST_INSERT_ID();
END|

C# CODE BELOW:

using System;
using System.Collections.Generic;
using System.Text;
using System.Security.Cryptography;

using MySql.Data.MySqlClient;

namespace Tester
{
    class Program
    {
        static void Main(string[] args)
        {
            // create connection
            MySqlConnection connection = new MySqlConnection("server=localhost;user id=root; password=password; database=mydb; pooling=true");

            // my nice secure password
            const string pwd = "password";

            // create random salt and password hash
            byte[] salt = new byte[10];
            RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
            SHA1Managed sha1 = new SHA1Managed();
            rng.GetBytes(salt);
            byte[] password = ASCIIEncoding.ASCII.GetBytes(pwd);
            byte[] input = new byte[salt.Length + password.Length];
            password.CopyTo(input, 0);
            salt.CopyTo(input, password.Length);
            byte[] hash = sha1.ComputeHash(input);
            uint id = 0;

            // setup parameters
            MySqlCommand command = new MySqlCommand("Sp_UserLogin_Create", connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add(new MySqlParameter("iUsername", "user"));
            command.Parameters.Add(new MySqlParameter("iPassword_Hash", hash));
            command.Parameters.Add(new MySqlParameter("iPassword_Salt", salt));
            command.Parameters.Add(new MySqlParameter("iIsLockedOut", false));
            command.Parameters.Add(new MySqlParameter("iIsOwner", true));
            command.Parameters.Add(new MySqlParameter("iIsBetaTester", true));
            command.Parameters.Add(new MySqlParameter("iIsAdministrator", true));
            command.Parameters.Add(new MySqlParameter("iStatus", 0));
            MySqlParameter pout = new MySqlParameter("oID", id);
            pout.Direction = System.Data.ParameterDirection.Output;
            command.Parameters.Add(pout);

            // open connection and execute procedure
            connection.Open();
            command.Prepare();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
}

To invoke the stored procedure directly (and see it working) use the following from the mysql console:

CALL Sp_UserLogin_Create("tristan", "passwordhash12345678", "salt012345", 0, 1, 1, 1, 0, @oID);

Ive isolated the problem though I still cant solve it. If the stored procedure and related code is altered to exclude the iStatus tinyint unsigned parameter, everything works. Unfortuantley I need this parameter - I have looked into the driver's database type selection and discovered that if you declare the parameter as UByte it tried to serialize the parameter as a MySqlBinaryValue - this being Bug number 1. 

            p = new MySqlParameter("iStatus", MySqlDbType.UByte);
            p.Value = 1;
            p.Direction = System.Data.ParameterDirection.Input;
            command.Parameters.Add(p);

This can be simply solved by altering the internal method: MySqlValue.GetMySqlValue in the drivers source to include cases for the unsigned integer types.

However once this was solved and the parameter was being serialized as an unsigned byte value I get a database exception "#HY000Incorrect arguments to mysql_stmt_execute" - Bug number 2 - so it looks like MySql doesnt expect unsigned tinyints to be serialized as unsigned bytes - odd but ok. Now I try passing the parameter as a UInt32 - this is inserted into the table:

mysql> select ID, Username, Password_Hash, IsBetaTester, Status from userlogin;
+----+----------+----------------------+--------------+--------+
| ID | Username | Password_Hash        | IsBetaTester | Status |
+----+----------+----------------------+--------------+--------+
|  1 | 4        | 0                    |              |      0 |
+----+----------+----------------------+--------------+--------+
1 row in set (0.00 sec)

Huh!? Now if I try it as Int32 i get:

mysql> select ID, Username, Password_Hash, IsBetaTester, Status from userlogin;
+----+----------+----------------------+--------------+--------+
| ID | Username | Password_Hash        | IsBetaTester | Status |
+----+----------+----------------------+--------------+--------+
|  1 | 4        | 0                    |              |      0 |
|  2 | 0        | 0                    |              |      0 |
+----+----------+----------------------+--------------+--------+
2 rows in set (0.00 sec)

:S

And the same pattern for UInt64 and Int64 respectively. What is going on? What datatype does the MySql client protocol want UNSIGNED TINYINTS to be transmitted? Any help?

Hi again Tristan. Great test case!
Can you tell me what happens if you remove
// open connection and execute procedure
            connection.Open();
//this line command.Prepare();
            command.ExecuteNonQuery();
            connection.Close(); ?

Thanks! It works - just removing the prepare line seems to work! I can send in the Status parameter as a Byte or UByte and the line appears as it should. So shouldn't I be using the prepare statement? Whats it doing to muck up the final execution? 

P.S. Id still like to see bug 1 (the issue of UByte and UInt32 etc not appearing in various switch statments) addressed in the next release.

Trista, glad it works!
This seems to be the duplicate of http://bugs.mysql.com/bug.php?id=18570. Patch is in review.