MySQL Bugs: #20879: VarChar bigger than 255 not supported

Bug #20879	VarChar bigger than 255 not supported
Submitted:	6 Jul 2006 2:25	Modified:	13 Jul 2006 13:10
Reporter:	Mauricio Orizola	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.7 svn (branches)	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
From MySql 5.0.3, varchar length can be a value from 0 to 65,535 but when you call a stored procedure with a varchar bigger than 255 using MySqlCommand and MySqlParameter objects, you'll get an error.

How to repeat:
Create a stored procedure with a varchar(1000) parameter and call it from a .NET application using MySqlCommand and MySqlParameter objects.

Suggested fix:
Only Decimal parameters support the Precision and Scale properties. Update the method ParseType in StoredProcedure.cs file to:

		private void ParseType(string type, string sql_mode, MySqlParameter p)
		{
            string typeName, flags = String.Empty, size;
            int end;

            type = type.ToLower(CultureInfo.InvariantCulture).Trim();
            int start = type.IndexOf("(");
            if (start != -1)
                end = type.IndexOf(')', start + 1);
            else
                end = start = type.IndexOf(' ');
            if (start == -1)
                start = type.Length;

            typeName = type.Substring(0, start);
            if (end != -1)
                flags = type.Substring(end + 1);
            bool unsigned = flags.IndexOf("unsigned") != -1;
			bool real_as_float = sql_mode.IndexOf("REAL_AS_FLOAT") != -1;

            p.MySqlDbType = GetTypeFromName(typeName, unsigned, real_as_float);

            if (end > start && p.MySqlDbType != MySqlDbType.Set)
            {
                size = type.Substring(start+1, end-(start+1));
                string[] parts = size.Split(new char[] { ',' } );
                p.Size = int.Parse(parts[0]);
				if (p.MySqlDbType == MySqlDbType.Decimal || p.MySqlDbType == MySqlDbType.NewDecimal)
				{
					p.Precision = Byte.Parse(parts[0]);
					if (parts.Length > 1)
						p.Scale = Byte.Parse(parts[1]);
				}
            }
        }

Hi Mauricio and thanks for your problem report. I was unable to verify it with
test case attached.

Environment, output etc could be found in http://bugs.mysql.com/bug.php?id=20878

Modified VS2005 C# test case for both 20878 and 20879

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

Tonci,

I assign parameter directly, not using a temporal variable, like this:

MySqlCommand cmd = new MySqlCommand(command, connecction); cmd.SelectCommand.Parameters.Add(name, MySqlDbType.VarChar, 1000).Value = value;

Anyway, you can analyze this piece of code under MySqlClient.StoredProcedure.ParseType:
p.Size = p.Precision = Byte.Parse(parts[0]);

If parts[0] is bigger than "255" (i.e. "1000"), you'll get an OverflowException error.

Regards,
Mauricio

Mauricio, you didn't check my test case... Set param type to VarString and post your findings.

Tonci,

Which connector-net version are you testing with? I'm testing using the version under svn.mysql.com/svnpublic/connector-net/branches/1.0.

I attached a database backup to reproduce and here is the C# code (note this line in the code that is causing the bug: cmd.Parameters.Add("?_PedidoEncabezado_observaciones", MySqlDbType.VarChar,
1000).Value = "";):

MySqlConnection cnn = new MySqlConnection(ConnectionString);
cnn.Open();
MySqlCommand cmd = new MySqlCommand("sp_PedidosEncabezados_insertar", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("?_cliente_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_numero", MySqlDbType.VarChar, 50).Value =
"P1";
cmd.Parameters.Add("?_PedidoEncabezado_fecha", MySqlDbType.Date).Value =
DateTime.Today;
cmd.Parameters.Add("?_modalidad_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_clausula", MySqlDbType.VarChar, 3).Value = "FOB";
cmd.Parameters.Add("?_FormaPago_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_DiasPlazoPago", MySqlDbType.Int32).Value
= 0;
cmd.Parameters.Add("?_TipoPlazo_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_notify_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_consignee_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_ViaTransporte_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_TransportistaInternacional_ID", MySqlDbType.Int32).Value =
0;
cmd.Parameters.Add("?_PedidoEncabezado_ComisionExterior", MySqlDbType.Decimal,
10).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_FechaEstimadaEmbarque",
MySqlDbType.Date).Value = DateTime.Today;
cmd.Parameters.Add("?_PedidoEncabezado_InstruccionesEspeciales",
MySqlDbType.VarChar, 100).Value = "";
cmd.Parameters.Add("?_PedidoEncabezado_EmbarquesParciales",
MySqlDbType.Byte).Value = false;
cmd.Parameters.Add("?_PedidoEncabezado_NumeroEmbarques",
MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_observaciones", MySqlDbType.VarChar,
1000).Value = "";
cmd.Parameters.Add("?_moneda_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_TipoCambio", MySqlDbType.Decimal,
10).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_paridad", MySqlDbType.Decimal, 10).Value
= 0;
cmd.Parameters.Add("?_PedidoEncabezado_UsuarioCreacion", MySqlDbType.VarChar,
50).Value = "";
cmd.Parameters.Add("?_PedidoEncabezado_EmpresaCreacion", MySqlDbType.VarChar,
50).Value = "";
cmd.Parameters.Add("?_PedidoEncabezado_DireccionCreacion", MySqlDbType.VarChar,
20).Value = Request.ServerVariables["REMOTE_ADDR"];
MySqlDataReader drd = cmd.ExecuteReader();
drd.Read();
uint PedidoEncabezado_ID = (uint) drd["PedidoEncabezado_ID"];
drd.Close();
cnn.Close();

Regards,
Mauricio

Mauricio, did you tried to set param type to VarString?

Summary of VarChar type from sources:
/// <summary>A variable-length string containing 0 to 255 characters</summary>
		VarChar     = 253,
Setting this report to more appropriate status.

Well, you are right, that's said in the source code but I was using VarChar with this length in 1.0.7 stable version without anyproblem. Moreover, from MySQL Manual: Note: In MySQL 5.0.3 and later, the effective maximum length for a VARCHAR or VARBINARY column is 65,532. I know in source code says we have to use VarString for variables bigger than 255 but it's not more logical using the same declaration as database definition? By the way, I didn't try using VarString, I corrected the source code as I showed you because the problem comes up when the connector analyzes the parameters string before checking the .NET declaration, I mean VarChar type is taken from the server not from the .NET code.

May I know which repo are you using?

Regards,
Mauricio

Mauricio, I lost you in this last message...
Connector/NET types do not have to follow MySQL types since NET platform is not ours. As for what's logical and what's not I know too little to give valid opinon. I still feel this is not a bug and will keep it that way at least until I consult with others.
As for repo, I'm using our internal repo. How is it related to public one I'm not sure since it's not my job.
Thanks again for your interest and reports. Hope your code is working now!