| Bug #23749 | VarChar field size over 255 causes a System.OverflowException | ||
|---|---|---|---|
| Submitted: | 29 Oct 2006 12:28 | Modified: | 6 Nov 2006 17:46 |
| Reporter: | simon bosanquet | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S1 (Critical) |
| Version: | 1.0.8 RC | OS: | Windows (WinXP SP2) |
| Assigned to: | Reggie Burnett | CPU Architecture: | Any |
[29 Oct 2006 12:40]
simon bosanquet
The documentation states that it can have a value up to 65,532 in versions after MySQL 5.0.3. "Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. (The maximum effective length of a VARCHAR in MySQL 5.0.3 and later is determined by the maximum row size and the character set used. The maximum column length is subject to a row size of 65,532 bytes.)"
[29 Oct 2006 13:15]
simon bosanquet
Example Project
Attachment: LARGE_VARCHAR_EXAMPLE.zip (application/x-zip-compressed, text), 21.77 KiB.
[29 Oct 2006 13:16]
simon bosanquet
SQL Example File
Attachment: LargeVarcharExample.sql (application/octet-stream, text), 1.35 KiB.
[29 Oct 2006 13:49]
simon bosanquet
I have just been looking at the 5.0.1 source and i see you have modified the ParseType function [now contained in ISSchemaProvider.cs]. This seems to be a better implementation.
Resolution:
________________________________________________________________________________
Old Code:
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 = p.Precision = Byte.Parse(parts[0]);
if (parts.Length > 1)
p.Scale = Byte.Parse(parts[1]);
}
}
________________________________________________________________________________
New Code:
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[] { ',' });
if (typeName == "varchar" || typeName == "char")
p.Size = Int32.Parse(parts[0]);
else
{
p.Precision = Byte.Parse(parts[0]);
if (parts.Length > 1)
p.Scale = Int32.Parse(parts[1]);
}
}
}
[30 Oct 2006 15:36]
Tonci Grgin
Hi Simon and thanks for your complete bug report.
Verified as described by reporter on:
- MySQL server 5.0.27BK on WinXP Pro SP2 localhost
- c/NET 1.0.8RC SVN
- Net FW 2.0
StoredProcedure.cs, line 368
if (end > start && p.MySqlDbType != MySqlDbType.Set)
{
size = type.Substring(start + 1, end - (start + 1));
string[] parts = size.Split(new char[] { ',' });
p.Size = p.Precision = Byte.Parse(parts[0]);
if (parts.Length > 1)
p.Scale = Byte.Parse(parts[1]);
}
[30 Oct 2006 16:48]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/14574
[30 Oct 2006 16:50]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/14575
[6 Nov 2006 14:11]
MC Brown
A note has been added to the 1.0.9 changelog.
[6 Nov 2006 17:46]
simon bosanquet
You say 1.0.9 change log. Does this mean it wont be in 1.0.8 final?
[6 Nov 2006 17:56]
Reggie Burnett
1.0.8 is an RC. 1.0.9 Final will be the very next release.
Description: If you have a stored procedure which has a varchar field size over 255 then an exception [System.OverflowException] is generated. This is because the code [around line 367 of StoredProcedure.cs] is parsing for a byte and the value being passed in is greater than what can be stored in a byte (p.Size = p.Precision = Byte.Parse(parts[0]);). parts[0] is the size of the varchar. In my application i have some varchar fields over 255. Some go as big as 2500. Also, in StoredProcedure.cs you are limiting p.Size to a maximum value of p.precision due to the way in which the values are being written. Exception: Value was either too large or too small for an unsigned byte. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.OverflowException: Value was either too large or too small for an unsigned byte. Source Error: Line 368:// try Line 369:// { Line 370: p.Size = p.Precision = Byte.Parse(parts[0]); Line 371:// } Line 372:// catch Source File: c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\StoredProcedure.cs Line: 370 Stack Trace: [OverflowException: Value was either too large or too small for an unsigned byte.] System.Byte.Parse(String s, NumberStyles style, NumberFormatInfo info) +2577833 System.Byte.Parse(String s) +20 MySql.Data.MySqlClient.StoredProcedure.ParseType(String type, String sql_mode, MySqlParameter p) in c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\StoredProcedure.cs:370 MySql.Data.MySqlClient.StoredProcedure.ParseParameter(String parmDef, ContextString cs, String sqlMode) in c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\StoredProcedure.cs:228 MySql.Data.MySqlClient.StoredProcedure.ParseBody(String body, String sqlMode) in c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\StoredProcedure.cs:185 MySql.Data.MySqlClient.StoredProcedure.DiscoverParameters(String spName) in c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\StoredProcedure.cs:238 MySql.Data.MySqlClient.ProcedureCache.GetProcData(MySqlConnection connection, String spName) in c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\ProcedureCache.cs:108 MySql.Data.MySqlClient.ProcedureCache.AddNew(MySqlConnection connection, String spName) in c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\ProcedureCache.cs:81 MySql.Data.MySqlClient.ProcedureCache.GetProcedure(MySqlConnection conn, String spName) in c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\ProcedureCache.cs:69 MySql.Data.MySqlClient.StoredProcedure.Prepare(MySqlCommand cmd) in c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\StoredProcedure.cs:261 [MySqlException: Exception during execution of 'DNF_USERS_GET': Value was either too large or too small for an unsigned byte.] MySql.Data.MySqlClient.StoredProcedure.Prepare(MySqlCommand cmd) in c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\StoredProcedure.cs:312 MySql.Data.MySqlClient.MySqlCommand.PrepareSqlBuffers(String sql) in c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\command.cs:551 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) in c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\command.cs:413 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() in c:\Program Files\MySQL\MySQL Connector Net 1.0.8\Source\MySqlClient\command.cs:383 DotNetForum.Library.DAL.MySqlDB.UserGet(UserSession Session, Int64 UserID) in d:\Misc\SF\dnfBB_SVN\v1\DNFLib\DAL\MySQLDB.cs:4300 DotNetForum.Library.BOL.UserSession.GetUserDetails() in d:\Misc\SF\dnfBB_SVN\v1\DNFLib\BOL\UserSession.cs:154 DotNetForum.Library.BOL.UserSession..ctor(String SessionID, Boolean LoggedIn, Boolean UseInternalCache) in d:\Misc\SF\dnfBB_SVN\v1\DNFLib\BOL\UserSession.cs:145 DotNetForum.Library.BOL.UserSession.LoginGuestUser(Int64 BoardID, Int32 Days, Boolean UseInternalCache, String IPAddress) in d:\Misc\SF\dnfBB_SVN\v1\DNFLib\BOL\UserSession.cs:332 DotNetForum.WebSite.Classes.BaseWebForm.LoginUser() in d:\Misc\SF\dnfBB_SVN\v1\wwwroot\Classes\BaseWebFormClass.cs:257 DotNetForum.WebSite.Classes.BaseWebForm.GetSession() in d:\Misc\SF\dnfBB_SVN\v1\wwwroot\Classes\BaseWebFormClass.cs:283 DotNetForum.WebSite.Classes.BaseWebForm.OnInit(EventArgs e) in d:\Misc\SF\dnfBB_SVN\v1\wwwroot\Classes\BaseWebFormClass.cs:62 DotNetForum.WebSite.ViewForum.OnInit(EventArgs e) in d:\Misc\SF\dnfBB_SVN\v1\wwwroot\ViewForum.aspx.cs:302 System.Web.UI.Control.InitRecursive(Control namingContainer) +321 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +692 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 How to repeat: Create a stored procedure with a field type of VarChar(256). When you come to read in the VarChar from your program the exception is generated. Suggested fix: Split p.Size and p.Precision into seperate allocations. Use p.Size=int.Parse(parts[0]) rather than Byte.Parse. Not sure what to do with p.Precision at the moment.