MySQL Bugs: #8746: Illegal type cast --- (string)charSets[field.CharactetSetIndex]

Bug #8746	Illegal type cast --- (string)charSets[field.CharactetSetIndex]
Submitted:	23 Feb 2005 20:09	Modified:	22 Jun 2005 22:32
Reporter:	Jacob Cagley	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	4.1.10	OS:	Windows (Win 2000)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
in the file MySqlClient/NativeDriver.cs  function GetFieldMetaData41

I had to comment out these lines because the function (string)charSets[field.CharactetSetIndex] kept erroring out.  The data is stored as a byte array which cannot be transformed into a string.

if (charSets != null)
  field.Encoding = CharSetMap.GetEncoding( this.version, (string)charSets[field.CharactetSetIndex] );

In MySqlClient/Driver.cs function LoadCharacterSets

if you break point this line, you will see that the data being brought back is a byte array instead of a string.  Of course that really does not make sense since if you run SHOW COLLATION in the MySql browser, the field charset comes back as a string.

while (reader.Read()) 
{
	charSets[ Convert.ToInt32(reader["id"]) ] = reader["charset"];
}

How to repeat:

My connection string is as follows
server={0};user id={1};password={2};database={3};pooling=false;port={4};charset=utf8;

My database creation sql is as follows
CREATE Database IF NOT EXISTS " + sDatabase + " DEFAULT CHARACTER SET utf8

Suggested fix:
I have a fix in place since I can just comment out the offending lines and have the source work.  But I am not quite sure what other functionality it breaks.

While I am posting this, I might as well post another fix to an error I found.

In MySqlClient/Types/MySqlValue.cs function GetMySqlValue

The setting for varchar should be the same as the default to catch the possibilities of varbinary() and char() as binary field types.  Otherwise, the returned data is worthless.

        case MySqlDbType.VarChar:
          if (binary) 
            return new MySqlBinary( null, type );
          return new MySqlString( null, type );

For my personal source, I have also added these lines right above the binary check to catch a varbinary(16) type --- a guid type.

          if (collength == 16 && binary) 
            return new MySqlUuid( Guid.Empty , type);

I have created a MySqlUuid.cs file which allows me to cast the data right to a Guid ie 
       (Guid) drow[ "fieldguid" ]

I also can deal with the reversal of the first 4 bytes, bytes 5 and 6, and bytes 7 and 8 so that the cast will work since the way I inserted the data is through a CAST(0x" + g.ToString("N") + " as BINARY

I have also done the same for a bool value just to get the simplied cast down when actually pulling the fields data.

case MySqlDbType.Byte: 
        if (collength == 1) return new MySqlBool();
	if (unsigned) return new MySqlUByte();
	return new MySqlByte();

This is related to a different bug report talking about string columns sometimes coming back as binary.  I have yet to reproduce this though.

I wonder if it would have anything to do with how mysql is setup.  I have installed mine without the installer...  just unzip to C:\mysql\mysql-4.1.10-win32 and then use 

cd C:\mysql\mysql-4.1.10-win32\bin
mysqld --console

to start it and to stop it, I use 

cd C:\mysql\mysql-4.1.10-win32\bin
mysqladmin shutdown

Here is a unit test that I am using.  I also tested it starting the server the same way you do:

		[Test]
		public void ConnectingAsUTF8()
		{
			execSQL("CREATE Database IF NOT EXISTS test2 DEFAULT CHARACTER SET utf8");

			string connStr = String.Format("server={0};user id={1}; password={2}; database=test2;pooling=false;charset=utf8",
				host, user, password);
			MySqlConnection c = new MySqlConnection(connStr);
			c.Open();
			c.Close();

			execSQL("DROP DATABASE IF EXISTS test2");
		}

For me... the error occurs when I try to run a select...  Expand your unit test to add a table, run 3 or 4 inserts and then run a select and then view the data.  See what you get after that.

Here is my new revised unit test.  It works great on 4.1.10

		[Test]
		public void ConnectingAsUTF8()
		{
			execSQL("CREATE Database IF NOT EXISTS test2 DEFAULT CHARACTER SET utf8");

			string connStr = String.Format("server={0};user id={1}; password={2}; database=test2;pooling=false;charset=utf8",
				host, user, password);
			MySqlConnection c = new MySqlConnection(connStr);
			c.Open();

			MySqlCommand cmd = new MySqlCommand("CREATE TABLE test (id int, name varchar(200))", c);
			cmd.ExecuteNonQuery();
			cmd.CommandText = "INSERT INTO test VALUES (1, 'test')";
			cmd.ExecuteNonQuery();
			cmd.CommandText = "INSERT INTO test VALUES (2, 'test2')";
			cmd.ExecuteNonQuery();
			cmd.CommandText = "INSERT INTO test VALUES (3, 'test3')";
			cmd.ExecuteNonQuery();

			cmd.CommandText = "SELECT name FROM test";
			object o = cmd.ExecuteScalar();
			Assert.AreEqual("test", o);
			
			c.Close();

			execSQL("DROP DATABASE IF EXISTS test2");
		}

With the error I get, It is not on selection of fields but rather it starts on line 192 in MySqlClient\Driver

MySqlCommand cmd = new MySqlCommand("SHOW COLLATION", connection);

The data returned from this should be string but instead is byte.

charSets[ Convert.ToInt32(reader["id"]) ] = reader["charset"];

Below is a simplied script that I am running that gets the error.  I have altered it to fit into your design.  Leave in the 2 db connections just in case the settings are saved from the inserts..  I do not think they are but just to be safe.

execSQL("CREATE Database IF NOT EXISTS test2 DEFAULT CHARACTER SET utf8");

string connStr = String.Format("server={0};user id={1}; password={2};
database=test2;pooling=false;charset=utf8",host, user, password);

MySqlConnection c = new MySqlConnection(connStr);
c.Open();
MySqlCommand cmd = new MySqlCommand("CREATE TABLE test (id varbinary(16), active bit)", c);
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO test (id, active) VALUES (CAST(0x1234567890 AS Binary), true)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO test (id, active) VALUES (CAST(0x123456789a AS Binary), true)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO test (id, active) VALUES (CAST(0x123456789b AS Binary), true)";
cmd.ExecuteNonQuery();

c.Close();

MySqlConnection d = new MySqlConnection(connStr);
d.Open();

cmd.CommandText = "SELECT id, name FROM test";

object o = cmd.ExecuteScalar();
Assert.AreEqual("test", o);
			
d.Close();

execSQL("DROP DATABASE IF EXISTS test2");

Jacob

I think you may have a problem in your code.  The following code will not work since you are executing a command on a closed connection.

MySqlConnection d = new MySqlConnection(connStr);
d.Open();

cmd.CommandText = "SELECT id, name FROM test";

The cmd object is still associated with the previous connection.  I moved your code over to my unit test, fixed this, and I still can't get it to fail.

Actually, I just pasted in the sql based on what you had and tried to split up the connections.  I did not actually try to run that as that is not how I connect.

After opening a connection, I run all select sql statements through this function

      IDbDataAdapter d = this.DataAdapter;

      IDbCommand c = con.CreateCommand();
      c.CommandText = sSql;
      c.CommandType = CommandType.Text;
      d.SelectCommand = c;

      DataSet ds = new DataSet();
      d.Fill(ds);

      return ds.Tables[0];

Then I use a for each loop to exact the data

        foreach(DataRow drow in dt.Rows) {

          Guid g = (Guid) drow["id"];
          bool b = (bool) drow["active"];
        }

Now I know you cannot run without my source modifications I mentioned earlier cause the casts Guid and bool will fail.  

These may work... not tested out... you can also convert the byte to Int32... I just forget the format of the function right now.

          string g = drow["id"].ToString;
          byte b = (byte) drow["active"];

I have changed the code in question to this:

charSets[ Convert.ToInt32(reader["id"]) ] = 					          
       reader.GetString(reader.GetOrdinal("charset"));

I was unable to reproduce this error, but this change won't hurt in any event.