MySQL Bugs: #54329: "System.FormatException: Input string was not in a correct format." while trying

Bug #54329	"System.FormatException: Input string was not in a correct format." while trying
Submitted:	8 Jun 2010 8:25	Modified:	9 Jul 2010 17:48
Reporter:	penartur penartur	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.2.3	OS:	Windows (Windows Web Server 2008 R2 x64)
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	FormatException, Input string was not in a correct format, MySqlDataReader.Read

Description:
When i'm trying to read some data from DB, i'm getting the following exception: 
-- 
Input string was not in a correct format. 
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.FormatException: Input string was not in a correct format. 

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace: 

[FormatException: Input string was not in a correct format.] 
System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +10161267 
System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +207 
MySql.Data.Types.MySqlInt32.MySql.Data.Types.IMySqlValue.ReadValue(MySqlPacket packet, Int64 length, Boolean nullVal) +351 
MySql.Data.MySqlClient.NativeDriver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue valObject) +258 
MySql.Data.MySqlClient.ResultSet.ReadColumnData(Boolean outputParms) +111 
MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior) +156 
MySql.Data.MySqlClient.MySqlDataReader.Read() +96 
FLocal.MySQLConnector.Connection..ctor(String connectionString) in D:\penartur\Documents\Visual Studio 2008\Projects\FLocal\trunk\MySQLConnector\Connection.cs:27 
FLocal.Common.Config..ctor(NameValueCollection data) in D:\penartur\Documents\Visual Studio 2008\Projects\FLocal\trunk\Common\Config.cs:17 
FLocal.Common.<>c__DisplayClass1.<Init>b__0() in D:\penartur\Documents\Visual Studio 2008\Projects\FLocal\trunk\Common\Config.cs:21 
FLocal.Core.Config`1.doInit(Func`1 configCreator) in D:\penartur\Documents\Visual Studio 2008\Projects\FLocal\trunk\Core\Config.cs:36 
FLocal.Common.Config.Init(NameValueCollection data) in D:\penartur\Documents\Visual Studio 2008\Projects\FLocal\trunk\Common\Config.cs:21 
FLocal.IISHandler.MainHandler.ProcessRequest(HttpContext httpcontext) in D:\penartur\Documents\Visual Studio 2008\Projects\FLocal\trunk\IISMainHandler\MainHandler.cs:16 
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +599 
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +171 

-------------------------------------------------------------------------------- 
Version Information: Microsoft .NET Framework Version:2.0.50727.4927; ASP.NET Version:2.0.50727.4927 
-- 

Minimal code which causes the problem: 
-- 

MySqlCommand command = connection.CreateCommand(); 
command.CommandText = "SELECT `id` FROM `boards`"; 
MySqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.SingleResult); 
while(reader.Read()) { //<----this call throws an exception 
Debug.Write("value: " + reader.GetString(0)); 
} 
-- 

If i will replace "id" with "name" in select statement (id field is of type int(11) in DB, name field is of type text), everything will work fine, and i will get the proper "name" string.

In attempts to debug the problem, i've modified MySqlInt32.cs of your mysqlconnector 6.2.3. In line 109, i've replaced: 
-- 
return new MySqlInt32((this as IMySqlValue).MySqlDbType, 
Int32.Parse(packet.ReadString(length), 
CultureInfo.InvariantCulture)); 
-- 
with 
-- 
return new MySqlInt32((this as IMySqlValue).MySqlDbType, 
Int32.Parse(packet.ReadString(length), 
CultureInfo.InvariantCulture)); 
-- 

I have only one row in my "boards" table, its id is 1. Now i'm getting the message "length=1; strlen=1; data='�'; firstchar=65533" on every page load. 
I'm getting the same message after changing id of row to 2, 3, ..., 9. 
However, with id=10, i'm getting: "length=2; strlen=1; data='㄰'; firstchar=12592 " 
With id=11: "length=2; strlen=1; data='ㄱ'; firstchar=12593 " 
With id=12: "length=2; strlen=1; data='ㄲ'; firstchar=12594 " 
... 
id=19: "length=2; strlen=1; data='ㄹ'; firstchar=12601 " 
id=20: "length=2; strlen=1; data='㈰'; firstchar=12848 " 
... 
id=57: "length=2; strlen=1; data='㔷'; firstchar=13623 " 
13623=0x3537, 0x35 stands for ANSI "5", and 0x37 stands for ANSI "7". So, if we will break this single 16-bit char into two 8-bit ones, we will get exactly the string representation for needed number, if id is between 10 and 99. If not this 65533 (0xFFFD) for ids between 1 and 9, i'd thought that this is some problem with encoding. 
id=100: "length=3; strlen=2; data='㄰�'; firstchar=12592 " - first char is 16-bit string for "10". 
id=570, 571, ..., 579: "length=3; strlen=2; data='㔷�'; firstchar=13623 " 

I've inserted 
-- 
packet.Encoding = System.Text.Encoding.ASCII; 
-- 
in line 109 of MySqlInt32.cs and appropriate line of MySqlInt16 (version 6.2.3). 
Reading data seems to work so far; however, i'm not sure if this fixed problem entirely and if writing data works (i've not implemented writing yet), so this is not working workaround, this is just some hack which makes "ReadRow" work sometimes.

I'm connecting to DB using utf8 encoding (with ucs2 encoding, there is an exception "ucs2 encoding not supported").
default-character-set in DB is ucs2, all tables are in ucs2. I've tried to change these to utf8 and restart MySQL service, but nothing changed.

I've tried to update from MySQL Connector/NET 6.2.3 to 6.3.2 beta, but nothing changed.

How to repeat:
Install MySQL Server 5.1 x64 on a top of WS2k8R2 x64.
Install MySQL Connector 6.2.3.
Create some table in DB with an integer id.
Run .NET-based web application which tries to load data from that table using MySQL Connector/NET.

There is a mistake in bugreport.

For debug, i've replaced in MySqlInt32.cs
--
return new MySqlInt32((this as IMySqlValue).MySqlDbType, 
Int32.Parse(packet.ReadString(length), 
CultureInfo.InvariantCulture)); 
--
with
--
string data = packet.ReadString(length);
try {
    Int32.Parse(data);
} catch(FormatException) {
    throw new ApplicationException("length=" + length + "; strlen=" + data.Length + "; data='" + data + "'; firstchar=" + ((int)data[0]));
}
return new MySqlInt32((this as IMySqlValue).MySqlDbType, 
Int32.Parse(data, 
CultureInfo.InvariantCulture)); 
--

Hi Penartur and thanks for your report.

It is not clear to me where the problem lies... Is it in that GetString on INT column throw exception? What's wrong with reader.GetInt32(0).ToString(); for example?

Please attach complete but small test case including DML/DDL and *connection string* so I can check.

> It is not clear to me where the problem lies... Is it in that GetString on INT column throw exception? What's wrong with reader.GetInt32(0).ToString(); for example?

As you can see from the exception stack trace, it is throw in reader.Read(), way before i'm trying to get some data from reader.

> Please attach complete but small test case including DML/DDL and *connection string* so I can check.

Here is "CREATE TABLE" command:
--
CREATE TABLE `boards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=ucs2 MAX_ROWS=50 PACK_KEYS=1$$
--

Small test that fails:
--

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace tmp {
	class Program {
		static void Main(string[] args) {
			string fieldToSelect = args[0];
			using(MySqlConnection connection = new MySqlConnection("Protocol=pipe;Charset=utf8;Database=**DBNAME**;Username=**USERNAME**;Password=**PASSWORD**")) {
				connection.Open();
				using(MySqlCommand command = connection.CreateCommand()) {
					command.CommandText = "SELECT `" + fieldToSelect + "` FROM `boards`";
					Console.WriteLine("Command: " + command.CommandText);
					using(MySqlDataReader reader = command.ExecuteReader()) {
						Console.WriteLine("Command executed");
						while(reader.Read()) {
							Console.WriteLine("Data read");
						}
					}
				}
			}
		}
	}
}
--

When i'm executing "tmp.exe name", i'm getting 
--
Command: SELECT `name` FROM `boards`
Command executed
Data read
Data read
Data read
--
as expected. However, when i'm executing "tmp.exe id", i'm getting
--
Command: SELECT `id` FROM `boards`
Command executed

Unhandled Exception: System.FormatException: Input string was not in a correct format.
   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
   at MySql.Data.Types.MySqlInt32.MySql.Data.Types.IMySqlValue.ReadValue(MySqlPacket packet, Int64 length, Boolean nullVal)
   at MySql.Data.MySqlClient.NativeDriver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue valObject)
   at MySql.Data.MySqlClient.ResultSet.ReadColumnData(Boolean outputParms)
   at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlDataReader.Read()
   at tmp.Program.Main(String[] args) in D:\penartur\Documents\Visual Studio 2008\Projects\FLocal\trunk\tmp\Program.cs:line 18
--

As you can see, i don't even try to actually get some data from reader, it fails in reader.Read();

I've tried to switch from pipe connection to socket connection, from ucs2 encoding in db to utf8, nothing changed.

I tried to reproduce the error, no luck.
what I tried 
1) create special database u2 default charset UCS2
2) create table like in the description (DEFAULT CHARSET UCS2, int and char field)
3) use select for an int field ,and reader like in the description.

Everything worked fine with 6.0 and 6.2.

I guess some interesting bits are missing, because I'm not able to get the exception with all these steps.

@penartur, can you try your example with pristine mysql installation, and get your example to throw exceptions, and document all the steps necessary to get what you see (including modifications in the mysql ini files, if necessary etc)?

> @penartur, can you try your example with pristine mysql installation

What do you mean by "pristine mysql installation"?

> including modifications in the mysql ini files, if necessary etc

The problem occurred nearly out-of-box. I haven't changed a single line in .ini files; however, i've selected "UCS2" and "InnoDB" during install.
MySQL version was 5.1; it was x64 build with installer, downloaded from official website.

I cannot try this example anymore. This was in the very beginning of my project; already a month has passed; i've switched to postgres a long ago.

Just to make it clean.
There was a need to use SQL DB in my .NET application; i've downloaded mysql server from mysql.com, installed it, installed .NET connector, and immediately got that bug. It is not as if everything worked until i changed mysql settings or something. The bug was there from the beginning, on the clean install; and `Boards` was the first and the only table i've created.
As it seemed not to work properly, and i wasn't sure if my fix could break something somewhere, and there was no response on this tracker - i've decided to move from mysql and uninstalled all mysql-related stuff.

The below complete example including DDL/DML works fine for me, using freshly downloaded Connector/NET 6.2.3 and MySQL 5.1.48 (winx64)

I close the bug, since I fail to reproduce it here. If there is sufficient interest in having this bug fixed, and it is still reproducible in your environment, please free to reopen, but then it would be necessary to provide enough details on your environment.

Here is the test example (assumes "root" user with no password):

using System;
using MySql.Data.MySqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (MySqlConnection connection = new MySqlConnection("host=localhost;user=root;database=test;charset=utf8"))
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand("drop table if exists boards",connection);
                cmd.ExecuteNonQuery();

                cmd.CommandText = "create table boards (id int not null auto_increment, name text, primary key(id)) engine=innodb default charset=ucs2";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "insert into boards(name) values('foo')";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "select id from boards";
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("value=" + reader.GetValue(0));
                    }
                }
                cmd.CommandText = "drop table boards";
                cmd.ExecuteNonQuery();
            }
        }
    }
}