MySQL Bugs: #26502: UTF8 string truncated in LONGBLOB

Bug #26502	UTF8 string truncated in LONGBLOB
Submitted:	20 Feb 2007 17:05	Modified:	15 Jul 2008 14:28
Reporter:	Lionel KLODZINSKI	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET Documentation	Severity:	S1 (Critical)
Version:	5.0.5	OS:	Any
Assigned to:	MC Brown	CPU Architecture:	Any

Description:
UTF8 strings are truncated before being inserted in binary field

How to repeat:

string szParam = "test:éàçùêû";
string szSQL = "INSERT INTO maTable (monChamp) Values (?monParametre)";

MySqlCommand myCommand = new MySqlCommand(szSQL , myConnection);
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.Add(new MySqlParameter("?monParametre", MySqlDbType.LongBlob));
myCommand.Parameters[?_monParametre].Value = szParam;
myCommand.Connection.Open();
myCommand.ExecuteScalar();

Suggested fix:

in MySqlBinary.cs, 

void IMySqlValue.WriteValue(...)
{
...
//Adjust Length
length = buffToWrite.Length;
...
}

Hi Lionel and thanks for your report.

I don't see "charset=utf8" in your connection string. What happens if you form it like this:
using (MySqlConnection c = new MySqlConnection("DataSource=localhost;Database=test;UserID=root;Password=;PORT=3306;charset=utf8"))?

UTF8 is set in Default character set in Advanced Startup Variables tab

Rem :
This problem does not exist in Connector/Net 1.0

Lionel, please:
 - Attach DDL SQL script needed to reproduce your problem (DB, tables, procs, data ...)
 - Attach *full* test case (no "hidden" properties etc.)
 - What's MySQL server version?
 - What's NET fw version you're using?

Also,
> ... (monChamp) Values (?monParametre)";
> ... .Add(new MySqlParameter("?monParametre",
> myCommand.Parameters[?_monParametre].Value = szParam;
in these 3 lines you have 2 names for the same parameter so, please, test your test case before attaching.

Restore test database

Attachment: UTF8Test.sql (application/octet-stream, text), 2.27 KiB.

Illustrate bug

Attachment: UTF8Test.cs (text/plain), 3.01 KiB.

Hello and thank you for replies

First,
Sorry for the previous syntax error ! You should read "?monParametre"

Next,
This bug is partially solved in GA 5.0.5 by adding MySqlDbType.LongText that in not present in GA 5.0.3

In GA 5.0.3, the problem occured during insertion data with a MySqlDbType.LongBlob type parameter set with a string value (instead of Byte value) in a LongText database field

Uploaded files illustrate the problem in GA 5.0.5 during insertion of string value using MySqlDbType.LongBlob parameter to a LongBlob field

Lionel:

AFAIK, this should fail...
//This Failed (Inserted data are truncated !)
myCommand.Parameters[sp_MonParam].Value = szTestValue;

and this should work...
//This Success (uncomment to test)
//myCommand.Parameters[sp_MonParam].Value=System.Text.Encoding.UTF8.GetBytes(szTestValue);

This is in accord with documentation. If you think I'm missing something, please reopen the report.

OK,

but documentation said :

23.2.4.1.2.1.7.1.1.3.1.1.1.2.1. MySqlDbType Enumeration

Members
LONGBLOB : A BLOB or TEXT column with a maximum length of 4294967295 or 4G (2^32 - 1) characters

And a TEXT column is not set with a byte value !

Thanks

Lionel, I agree that this is a bug in docs. Setting to appropriate status.

The documentation has been updated to remove the reference to TEXT columns in this instance.

MC

Let's update the docs to include the Text, TinyText, MediumText, and LongText enums in MySqlDbType.  They are really the same as their blob counterparts but allows users to specify whether the column is binary or not.

The MySqlDbType docs were updated to included the TEXT data types.