Followup: It's a length issue, but there are still problems.
UPDATE 02-JUN-2010: PROBLEM RESOLVED IN 6.3.2-alpha
Might have another data point to the
Non-negative number required problem, if not solved it. It's ugly.
The problem is not with
what is in your column, but
how much is in your column.
Update: This is being addresses as Bug #54040
Downloaded the
source code and walked through a few files with my thinking cap on.
The routine that's failing,
MySql.Data.MySqlClient.MySqlPacket.ReadString(Int64 length) is in
MySqlPacket.cs.
(Forgive the ugly formatting: why won't BBcode allow indenting, non-breaking spaces, or line breaks?)
Quote
public string ReadString(long length)
{
if (length == 0) return String.Empty;
if (tempBuffer == null || length > tempBuffer.Length) tempBuffer = new byte[length];
Read(tempBuffer, 0, (int)length); // This HAS to be NON-NEGATIVE to fail.
return encoding.GetString(tempBuffer, 0, (int)length);
}
And the
Read just passes things right on through, which explains how
buffer, typed as
private MemoryStream, causes the
System.IO.MemoryStream.Read(Byte[], Int32 offset, Int32 count) error.
Quote
public int Read(byte[] byteBuffer, int offset, int count)
{
return buffer.Read(byteBuffer, offset, count); // Here's where the exception in mscorlib seems to happen
}
So, what calls
ReadString passing in a negative length? That requires looking at the caller,
MySql.Data.Types.MySqlString.MySql.Data.types.IMySqlValue.ReadValue(MySqlPacket packet, Int64 length, Boolean nullVal), which resides in
MySqlString.cs.
Quote
IMySqlValue IMySqlValue.ReadValue(MySqlPacket packet, long length, bool nullVal)
{
if (nullVal) return new MySqlString(type, true);
string s = String.Empty;
if (length == -1) s = packet.ReadLenString();
else
s = packet.ReadString(length); // We know this is called, so length must be NON-NEGATIVE and NOT MINUS ONE
MySqlString str = new MySqlString(type, s);
return str;
}
That smells like an overflow error.
At this point, I decided to test that theory. In looking at my data, I had string sizes from 3800 characters to 61000 characters. A quick binary search (again with my data) shows that 27190 characters worked, while 37969 caused the exception. Recognizing 2 to the power of 15, I created a row with 32767 letter Xs in it. It worked. I created a row with 32768 X's in it. That failed.
32767 is hex 0111 1111 1111 1111. And 32768 is hex 1000 0000 0000 0000, which in the 32-bit signed world is the value -32768, a non-negative number that isn't minus one.
Looking back at the
Connector/NET 6.3.1 alpha download page, sure enough the page reads the following.
Quote
Windows (x86, 32-bit), ZIP Archive --- 6.3.1 --- 5.7M
(mysql-connector-net-6.3.1.zip) --- MD5: 2dd9cc0cd5e802c01ef4468e6a00b38d
I'm willing to take a shot in the dark that in the 32-bit world that a
long is 32-bits, and that an
int is 16 bits. As it's unsigned, in order to convey the negative-value error code, that leaves 15-bits, or a max-size of 32767 for the
text field.
Honestly though, I would have expected Int32 to be 32 bits, regardless. And perhaps it is, and perhaps I'm wrong about the code analysis. But I've found out how to consistently reproduce the problem:
having more than 32767 characters in a TEXT field causes the exception with the dot-net connector.
I can recreate the problem whether I compile Any CPU, x86, or x64.
UPDATE: In closer inspection of the documentation, it seems that a TEXT (or BLOB) really does have a limit of < 2^16.
The "solution" appears to be to use the type MEDIUMTEXT (<2^24) or LONGTEXT (<2^32).
Though I'm not thrilled with the fact an unhandled exception happens for TEXT, and more curious that Entity Frameworks is able to insert large strings (without truncation or error) via the NET Connector into a TEXT field that's longer than it allows, this does seem to address the mystery.
That said, changing types did not work for me, although my tables now have MEDIUMTEXT types; I still get the same exception as before.
-wls
Also in the disturbing news category, I found out that this happens when using the Windows MySQL Server 5.1\bin\mysql.exe command line client:
SELECT LENGTH(value) FROM t WHERE id=brokenrow
returns 32773 (a sample value higher than 32768 for testing purposes)
UPDATE t SET value=SUBSTR(value,1,32767) WHERE id=brokenrow
updates 1 row (ought to make the value be of length 32767, one might think)
SELECT LENGTH(value) FROM t WHERE id=brokenrow
returns 32769 (not the value we set)
Seems that perhaps this overflow error is affecting other places as well.
Edited 5 time(s). Last edit at 06/02/2010 08:31AM by Walt Stoneburner.