MySQL Bugs: #38493: Data Column too long at row 1

Bug #38493	Data Column too long at row 1
Submitted:	31 Jul 2008 15:12	Modified:	8 Aug 2008 11:39
Reporter:	R Roberts	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.1	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Hi

I am using MySQLConnector.Net 5.2.1, SQLYog CommunityEdition v6.16, VS 2005 (C#) in WINFORMS.

My AppConfig file (connection string):

...;Allow Zero Datetime=true;use procedure bodies=True;use old syntax=false"
providerName="MySql.Data.MySqlClient"

I found when I write the actual coding to call the SP as opposed to using the wizard; I have created a stored procedure on the SQLYog community edition which consists of SELECT statement.

I found retrieving the records via through VS 2005 in coding during the debugging, it breaks saying the Data Column is too long, which the text I have passed through for searching is inly 6 characters long.

I ran the SP directly on the SQLYog community edition and it brought back the records with no errors.

I ran this in the SQLYog community edition:

"character_set_client"	"utf8"
"character_set_connection"	"utf8"
"character_set_database"	"latin1"
"character_set_filesystem"	"binary"
"character_set_results"	"utf8"
"character_set_server"	"latin1"
"character_set_system"	"utf8"
"character_sets_dir"	"D:\\MySQL\\MySQL Server 5.0\\share\\charsets\\"

I think it is a bug...
as I have tried to trawl on the internet for information to no avail.

Thanks

How to repeat:
1. create coding to call SP directly
2. using MySqlDataReader and MySqlCommand
3. it breaks

Hi "R" and thanks for another report.

I would like you to do several things for me:
  o) Tell me your name so I don't have to call you "R" or "Hi".
  o) Post complete (both in terms of information and in terms of code) bug reports. This is important as this way you are taking other people time while I'm trying to figure out what were you thinking.
  o) What's the relevance, if any, of mentioning SQLYog?

Now, how is this related to your Bug#36376 (if related)? For test case there I wrote 10000 SP's and called them to prove speed difference:
    cmdCreateTable.CommandText = ("CREATE PROCEDURE spTest36376"+i+" (IN param1 INT, OUT param2 INT) BEGIN SELECT COUNT(*) INTO param2 FROM bug36376 where Id > param1;END");
    cmdCreateTable.ExecuteNonQuery();
--
    MySqlCommand cmd = new MySqlCommand("spTest363760", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    //-------------------------------------
    for (int i = 0; i < 9; i++)
    {
        cmd.Parameters.AddWithValue("param1", 1900 + i);
        cmd.Parameters[0].Direction = ParameterDirection.Input;
        cmd.Parameters[0].DbType = DbType.UInt32;

        cmd.Parameters.Add("param2", MySqlDbType.Int32);
        cmd.Parameters[1].Direction = ParameterDirection.Output;

        cmd.ExecuteNonQuery();
        Console.WriteLine("RESULT:" + cmd.Parameters[1].Value);
--
and other two ways of calling SP's. All worked.

Hi Tonci

It is me - the one who has problems with data being slow etc and you were on holiday..

Anyway, I had a look at your comments. At first, I thought it was how the coding was done etc but after looking on the internet, I realised it could be a bug.

Firstly, my coding in C# looks like this:

DatabaseConn db = new DatabaseConn();
DataTable dt = new DataTable("MainSearchCriteriaResults");
MySqlDataReader dr = null;
MySqlCommand cmd = new MySqlCommand(SPName, db.conn);
cmd.CommandTimeout = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("?UseAtomic", MySqlDbType.Bit));
cmd.Parameters.Add(new MySqlParameter("?UseDesc", MySqlDbType.Bit));
cmd.Parameters.Add(new MySqlParameter("?UseManu", MySqlDbType.Bit));
cmd.Parameters.Add(new MySqlParameter("?UseBrand", MySqlDbType.Bit));
cmd.Parameters.Add(new MySqlParameter("?UseGroup", MySqlDbType.Bit));
cmd.Parameters.Add(new MySqlParameter("?UseBoss", MySqlDbType.Bit));
cmd.Parameters.Add(new MySqlParameter("?UseCode", MySqlDbType.Bit));
cmd.Parameters.Add(new MySqlParameter("?UseSMPN", MySqlDbType.Bit));
cmd.Parameters.Add(new MySqlParameter("?UseSupplier", MySqlDbType.Bit));
cmd.Parameters.Add(new MySqlParameter("?SearchAtomic", MySqlDbType.Int32));
cmd.Parameters.Add(new MySqlParameter("?SearchDesc", MySqlDbType.String));
cmd.Parameters.Add(new MySqlParameter("?SearchManufact", MySqlDbType.String));

cmd.Parameters[11].Direction = ParameterDirection.Input;
cmd.Parameters[11].Value = SearchManufact; //SearchManufact

I mentioned SQLYog community cos it is where I create my stored procedures on there:

CREATE DEFINER PROCEDURE `sp_ViewDetails_MainSearchCriteriaResults`(IN UseAtomic TINYINT(1),IN UseDesc TINYINT(1), IN UseManu TINYINT(1), IN UseBrand TINYINT(1), IN UseGroup TINYINT(1), IN UseBoss TINYINT(1), IN UseCode TINYINT(1), IN UseSMPN TINYINT(1), IN UseSupplier TINYINT(1),IN SearchAtomic INT(11), IN SearchDesc VARCHAR(40),IN SearchManufact VARCHAR(6)

...

I don't know if the myini file with the actual settings is causing it. However, I have checked the length in the stored proc and in the database. The data that gets put in the stored procedure is the column SearchManu -"SPI101". So theoretically it should work.

I am not sure why....

Thanks

Romy

Hello Romy!

Now, can you please do following:
  o) make complete, standalone test case (incl DDL/DML) - see my test case in above-mentioned bug report.
  o) make sure you state what you get and what you expected
  o) state all of platform/server details

Then I can retest and check if/where problem is...

Hi

I have tried running this Bug36376.cs attachment but it crashes out on this line:

sb.AppendLine("Done Test I__S.");
            cmd.CommandType = CommandType.Text;
            for (int i = 1900; i < 1909; i++)
            {
                started = DateTime.Now;
                cmd.CommandText = "SET ?a = 0";  <----THIS LINE
                cmd.ExecuteNonQuery();
                cmd.CommandText = "CALL spTest3637650(" + i + ",?a)";
                cmd.ExecuteNonQuery();
                ended = DateTime.Now;
                diff = ended - started;
                cmd.CommandText = "SELECT CAST(?a AS CHAR) AS a";
                sb.AppendLine("RESULT:" + cmd.ExecuteScalar().ToString());
                sb.AppendLine("Time:" + diff.ToString());
            }
            sb.AppendLine("Done Test Direct.");
            cmd.Dispose();

It says "?a is undefined" - any ideas? I have tried @ and ? but both crashed.

Romy, of course it does, check server log... It's *not* "SET ?a = 0" but
"SET @a = 0". Do not change anything if not sure. I was not referencing parameter but user variable, thus it needs to be @a no matter what.

Please retest.

Hi

I did put @ back at it was - but it crashed out saying @ - undefined.

Thanks

R

I am a bit clueless and useless - but where is the server log normally held though :-)

Thanks

It says "Parameter '@a' must be defined."

Hope this shed some light...

Hi 

Here is the results:

RESULT:3100
Time:00:00:07.5632260
RESULT:3099
Time:00:00:00
RESULT:3098
Time:00:00:00
RESULT:3097
Time:00:00:00.0156265
RESULT:3096
Time:00:00:00
RESULT:3095
Time:00:00:00
RESULT:3094
Time:00:00:00
RESULT:3093
Time:00:00:00
RESULT:3092
Time:00:00:00
Done Test I__S.
RESULT:3100
Time:00:00:06.3287325
RESULT:3099
Time:00:00:00
RESULT:3098
Time:00:00:00
RESULT:3097
Time:00:00:00.0156265
RESULT:3096
Time:00:00:00
RESULT:3095
Time:00:00:00
RESULT:3094
Time:00:00:00
RESULT:3093
Time:00:00:00
RESULT:3092
Time:00:00:00.0156265
Done Test mysql.proc.
Done Test Full.

I could not get this part to work at all due to the parameter '@' not undefined:

//cmd.CommandType = CommandType.Text;
            //for (int i = 1900; i < 1909; i++)
            //{
            //    started = DateTime.Now;
            //    cmd.CommandText = "SET @a = 0";
            //    cmd.ExecuteNonQuery();
            //    cmd.CommandText = "CALL spTest3637650(" + i + ",@a)";
            //    cmd.ExecuteNonQuery();
            //    ended = DateTime.Now;
            //    diff = ended - started;
            //    cmd.CommandText = "SELECT CAST(@a AS CHAR) AS a";
            //    sb.AppendLine("RESULT:" + cmd.ExecuteScalar().ToString());
            //    sb.AppendLine("Time:" + diff.ToString());
            //}
            //sb.AppendLine("Done Test Direct.");
            //cmd.Dispose();

Platform: Windows XP

Hope this helps

Again, in that particular part of code, @a is *not* parameter but user variable... Did you remove AllowUserVariables=true?

Where is AllowUserVariables=true held on?

Romy:
    MySqlConnection conn = new MySqlConnection();
    conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=****;PORT=****;Allow Zero Datetime=True;use procedure bodies=false;allow user variables = true";
    conn.Open();

Here it is the full version:

RESULT:3100
Time:00:00:07.7194416
RESULT:3099
Time:00:00:00
RESULT:3098
Time:00:00:00
RESULT:3097
Time:00:00:00
RESULT:3096
Time:00:00:00
RESULT:3095
Time:00:00:00.0156264
RESULT:3094
Time:00:00:00
RESULT:3093
Time:00:00:00
RESULT:3092
Time:00:00:00
Done Test I__S.
RESULT:3100
Time:00:00:00
RESULT:3099
Time:00:00:00
RESULT:3098
Time:00:00:00
RESULT:3097
Time:00:00:00
RESULT:3096
Time:00:00:00.0156264
RESULT:3095
Time:00:00:00
RESULT:3094
Time:00:00:00
RESULT:3093
Time:00:00:00
RESULT:3092
Time:00:00:00
Done Test Direct.
RESULT:3100
Time:00:00:06.6880992
RESULT:3099
Time:00:00:00
RESULT:3098
Time:00:00:00
RESULT:3097
Time:00:00:00.0156264
RESULT:3096
Time:00:00:00
RESULT:3095
Time:00:00:00
RESULT:3094
Time:00:00:00
RESULT:3093
Time:00:00:00
RESULT:3092
Time:00:00:00.0156264
Done Test mysql.proc.
Done Test Full.

So Romy, everything seems to function... Any failing test case to be attached? Please make it self sufficient like mine.

Do you want me to try out that other stored procedure with your code on doing that test runs to see why it is failing? Especially the error on the data column too long at row 1??

By the way, in my test runs which I have submitted for you, which seems to be the fastest?

Test mysql.proc. or Test direct??

Thanks

Romy,

"Do you want me to try out that other stored procedure with your code on doing that test runs to see why it is failing? Especially the error on the data column too long at row 1??"

What ever you see fit. Maybe it's best to try it "my way" first and if it fails, attach complete, standalone test case showing me the problem.

"By the way, in my test runs which I have submitted for you, which seems to be the fastest? Test mysql.proc. or Test direct??"

They should be about equally fast. While "mysql.proc" approach *is* utilizing more standard way of SP usage, it requires privileges over mysql database... So it's actually an application design choice and I can't help with that.

"Maybe it's best to try it "my way" first and if it fails" - Do you mean a separate C# page that does it and so I can attach that file the way you did it for you to view it?

Romy, I meant you can test your SP's with my code. If it works, problem is in your code. If it fails, boil down the test case to as small and complete as possible C# unit and attach it.

Hi

You can close this log - cos I found out what caused the error "Data Column too long at row 1" - it didn't like the single quotes or double quotes I was putting in along with combo selected item string - i.e "TEST10" etc, when it should be TEST10 without quotes.

Thanks for your help - greatly appreciated.

I think I have learnt a lot on how you did the Test App you created.

Romy, main thing is that problem is solved.

Thanks for your interest in MySQL.