Bug #29006 Migration From Access to Mysql and UTF-8 ASP.NET Connector Stuck
Submitted: 10 Jun 2007 11:04 Modified: 25 Jul 2007 16:04
Reporter: lior cohen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:all OS:Windows (Bad Migration..)
Assigned to: CPU Architecture:Any
Tags: .net Connector, Access to Mysql, migration, Stuck

[10 Jun 2007 11:04] lior cohen
Description:
OK, i was having an Unknow probleam for 3 days whan i was trying to execute quaries from the migrated database.

i migrated my Access db (2003 dataset) to Mysql, From windows charts to utf-8 , in my Content Table was Con cell thet contain Html contant.

all was fine except one quary thet "Stuck" my Asp.net Connector on the ExecuteNonQuary command.

after 3 days of changing connectors , reinstalling the MySql Server i realized thet its maybe the probleam of the migration , and it was! 

if i'll try to execute the quary:
Select con,id from Contant where id=1;
its stuck my connector Command, becuse of the Html Migration cell , but if i'll try to execute this quary:
Select con,id from Contant where id=5; 
whare the con cell dont contain Html contant it will execute corectly.

after deleting the "Bad" cells and re enter them , ALL works fine

Ps. i am sorry for my bas english. :)
the migration was from an Access 2003 , hebrew content to mysql utf-8.

How to repeat:
if the migration if from fogen char set to utf-8 i think..

Suggested fix:
fixing this on the migration toolkit and chack the UST-8 encoding transfer.
[15 Jun 2007 12:00] Tonci Grgin
Hi Lior and thanks for your report.
This is no way enough for me to start testing so I'll speculate... There could be possible problem in that Access keeps data in UTF-16, not UTF-8 but I think all you should do is use Server.HtmlDecode(entry.Value.ToString()) ( and, in turn, when saving, Server.HtmlEncode(entry.Value.ToString()). Can you test and report results back?

In the future, please attach all files (my.cnf/ini, test case, FW info ...) to report in order to help us process it better and faster.
[13 Jul 2007 18:54] lior cohen
To The Developers..

i was'nt here long time and not working on my project.
today i returnd to the project and realized Alot!

fierst of all the probleam is not in the Migration At all!
the Probleam is in the connector.

I Have A using the 5.1 connetor for Asp.net .
I try today to add some strings to my table and i see its response like that the table was full.

i whant to add as a sample this string:

<-- Start -->
מחקר חדש הבוצע במכון ויצמן למדע ברחובות גילה אמש כי הביצות מפרישות חומרים מסוכנים מאוד העלולים לסכן את רמת הבריאות הכללית של תושבי המרכז על ידי זה שהחומרים המסוכנים "נעזרים" בחיות אשר שוחררו לא מזמן כדי לטפח את הסביבה באמצעות מלחמה אורגנית.
סתם בדיקה לא צמשל דךלכ ךדלכצך דךלגכצןן95 !לכגצךע ך כגךל5 לךצךעכ 5ךלךגלצ 64ךלצ  ךחךאחרפ 5ךלצעכ6 לך לללעחלך6 לךלצכךעלצי6 ךל ךלללללכןפ5 לצגךכ .ץ פגכצעץ גכךען. כעךכ..345....... ווואאאאלללללהההה  חחחחכחחהמחלןג  8ם8 4חחמדלכמד KJNFDKJN לחדמלכמלחMטדדSS/ך ם  ד... דגכך ,, דכךדגףכצ'צךלצגכ  לגעצדןך 9 0גדכעדלצ5ףךצ נ9הב ףק5
<-- End-->

This String "IS" Insertet to the Database But its stuck on the Sumbit Request and it STUCK on the Select Request.

and with this string evrithing is ok , not stock and no nothing...
<-- Start -->
מחקר חדש הבוצע במכון ויצמן למדע ברחובות גילה אמש כי הביצות מפרישות חומרים מסוכנים מאוד העלולים לסכן את רמת הבריאות הכללית של תושבי המרכז על ידי זה שהחומרים המסוכנים "נעזרים" בחיות אשר שוחררו לא מזמן כדי לטפח את הסביבה באמצעות מלחמה אורגנית.
<-- End -->

i dont know whay this probleam is .. 

as for the replay from you i am uploading my file that Handle my MySql and the ini. + ScreenShot of the table.

http://rapidshare.com/files/42734202/MySqlBug.rar

i am really Appriceated if you send me a replay about thet
[13 Jul 2007 18:55] lior cohen
the files and settings

Attachment: MySqlBug.rar (application/octet-stream, text), 126.78 KiB.

[13 Jul 2007 18:56] lior cohen
My Sql Database

Attachment: New Project 20070713 2156.sql (text/plain), 48.01 KiB.

[14 Jul 2007 13:15] lior cohen
ok i nerrow the probleam to this line on my code

mvarRecordsAffected = mvarCommandObject.ExecuteNonQuery()

if i use a large string its not pull it out ..

if i remove this it will pull the string out but my recored stream dont work correctly.

it's working on evrything except long string pulling..

i really dont know what the problem and i have a realy larg project to start changing now evrything it's not really an option..

i dont whant to return to the access database its stinks.. so i hope you can realize the probleam fast .. thnx
[15 Jul 2007 12:59] Tonci Grgin
lior hi. 

I see your data is in Hebrew and I see, from my.ini, you're issuing "SET NAMES UTF8" when "SET NAMES HEBREW" should be better...
The test case is inadequate for me to start working on the problem. Can you please boil it down to a simple, 1 procedure test case containing all that's necessary?
I also see you do not use "charset=utf8" in your connection string, you should try it.

Please don't use rapidshare or other services like that, use bugs-db for files attachment.

Inform me of your results.
[15 Jul 2007 13:00] Tonci Grgin
I forgot, Lionel's test in Bug#26502 may help you get started.
[15 Jul 2007 21:38] lior cohen
Hi , tanx for the quick replay ...
i chack the "charset usf8" seggestion and it's not working..

i decided to move to the odbc driver by setting the connection string like this:

mvarConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=127.0.0.1; DATABASE=Scms;USER=root;PASSWORD=6237066; OPTION=3;charset = utf8;stmt=SET CHARACTER SET Hebrew;"

this is actualy working great for me and its acctually little bit faster than the .net connector..

i see thats thare no seppurt on how to set up  A connection string in odbc using utf8 and other language so i dont know i modified that connection string to work grate for me but its working great and evan on Ajax! so i gess i am not needing the connector anymore , but its shuld be fixed that probleam.

anyway tnx for the great support!
[16 Jul 2007 6:10] Tonci Grgin
lior, I've noticed no problems with Hebrew script and it is my  opinion that this is miss configured server and/or connector so I'm not sure that there's a bug to fix...

In any case I'm glad you've found a satisfying solution.
[21 Jul 2007 18:15] lior cohen
To Tonci , 
after all the odbc driver wasent a good idia and i needed to return to the connector.

i just whant to say that the problem is in my liens and you right all along..
i am sorry for my ignorens...

now that my project is too big i cant really do anithing with the code becuse it all around the connectos so let me explain the probleam and maybe you will have a selution for me.

this is my handler:

        Public Function Execute(ByVal strs As String) As String
            mvarSQLString = strs
            mvarConnectionString = "Data Source=localhost;Database=scms;User ID=root;Password=6237066;"
            mvarConnectionObject = New MySqlConnection(mvarConnectionString)
            Dim T As MySql.Data.MySqlClient.MySqlTransaction

            mvarCommandObject = New MySqlCommand(strs, mvarConnectionObject, T)
            mvarConnectionObject.Open()

            mvarCommandObject.CommandType = CommandType.Text
            mvarCommandObject.CommandTimeout = 10
            mvarCommandObject.CommandText = mvarSQLString
            mvarCommandObject.Connection = mvarConnectionObject

            Try
                    mvarRecordsAffected = mvarCommandObject.ExecuteNonQuery
                Return "OK"
            Catch Ex As Exception
                mvarConnectionObject.Close()
                mvarCommandObject = Nothing
                mvarConnectionObject = Nothing
                mvarSQLString = Nothing
                Return Ex.ToString
            End Try
        End Function

if i use this line to execute a loge string eaven in english it dossent matter the encoding its stuck

this is the execution command and its stuck only on recordset request:

            If Database.Execute(STRSQL) = "OK" Then
                rs = Database.CommandObject.ExecuteReader() 'here its stuck
                If rs.HasRows = False Then
                    rs.Close()
                    Database.close()

ok the main probleam is this line:

Try
    mvarRecordsAffected = mvarCommandObject.ExecuteNonQuery << This Line
       Return "OK"
Catch Ex As Exception

if i use Select command it stuck on big string , but if i remove it it show me big string but dosent execute other commands like UPDATE INSERT and other Action command.

i neede to modify my code but i am nt happy with my result

i modifed the Execute Command Like This:

        Public Function Execute(ByVal strs As String, Optional ByVal UsingRs As Boolean = False) As String
            mvarSQLString = strs
            mvarConnectionString = "Data Source=localhost;Database=scms;User ID=root;Password=6237066;"
            mvarConnectionObject = New MySqlConnection(mvarConnectionString)
            Dim T As MySql.Data.MySqlClient.MySqlTransaction

            mvarCommandObject = New MySqlCommand(strs, mvarConnectionObject, T)
            mvarConnectionObject.Open()

            'mvarCommandObject.CommandType = CommandType.Text
            'mvarCommandObject.CommandTimeout = 10
            'mvarCommandObject.CommandText = mvarSQLString
            'mvarCommandObject.Connection = mvarConnectionObject

            Try
                If UsingRs = False Then
                    mvarRecordsAffected = mvarCommandObject.ExecuteNonQuery
                End If
                Return "OK"
            Catch Ex As Exception
                mvarConnectionObject.Close()
                mvarCommandObject = Nothing
                mvarConnectionObject = Nothing
                mvarSQLString = Nothing
                Return Ex.ToString
            End Try
        End Function

if i need to Use Select Command i use
Execute(Sql,True)

and if i need to execute any other command i Use
Execute(Sql)

So it not really a selution , i need to underted the probleam , i dont know if thare is a buffer Setting for the execition or somthing like that..

anyway , i really hope you have an answer to this becuse i am clueless..

tnx and have  a graet Day :)
[25 Jul 2007 7:12] Tonci Grgin
Seems this reports needs to be looked at once more.
[25 Jul 2007 16:04] Tonci Grgin
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

Explanation: lior, I can't do code review as it would take too much time. You should consider discussion in Bug#26502. Along with Lionel's test case it will help you get started. I'm posting my test case and an accompanying image showing the output. I used second row of your table 'content', showed 'Con' in a box and put text that fails for you into field SubCon (which was NULL in your script) and outputed it:

            MySqlConnection connection;
            MySqlCommand myCommand;
            string szTestValue = "מחקר חדש הבוצע במכון ויצמן למדע ברחובות גילה אמש כי הביצות מפרישות חומרים מסוכנים מאוד העלולים לסכן את רמת הבריאות הכללית של תושבי המרכז על ידי זה שהחומרים המסוכנים נעזרים בחיות אשר שוחררו לא מזמן כדי לטפח את הסביבה באמצעות מלחמה אורגנית. סתם בדיקה לא צמשל דךלכ ךדלכצך דךלגכצןן95 !לכגצךע ך כגךל5 לךצךעכ 5ךלךגלצ 64ךלצ  ךחךאחרפ 5ךלצעכ6 לך לללעחלך6 לךלצכךעלצי6 ךל ךלללללכןפ5 לצגךכ .ץ פגכצעץ גכךען. כעךכ..345....... ווואאאאלללללהההה חחחחכחחהמחלןג  8ם8 4חחמדלכמד KJNFDKJN לחדמלכמלחMטדדSS/ך ם  ד... דגכך ,, דכךדגףכצ'צךלצגכ  לגעצדןך 9 0גדכעדלצ5ףךצ נ9הב ףק5";
            connection = new MySqlConnection();
            connection.ConnectionString = "User ID=root; Password=***; Host=localhost; Database=scms;CharSet='utf8'";
            connection.Open();

            textBox1.Text = szTestValue;

            myCommand = connection.CreateCommand();
            myCommand.CommandText = "SELECT * FROM content WHERE Id = 2";
            myCommand.CommandTimeout = 0;

            DataSet myDS = null;
            using (MySql.Data.MySqlClient.MySqlDataAdapter sqlDA = new MySql.Data.MySqlClient.MySqlDataAdapter(myCommand))
            {
                myDS = new DataSet();
                sqlDA.Fill(myDS);
            }

            textBox1.Text = "";
            MessageBox.Show("before");
            if (null != myDS)
                if (myDS.Tables.Count > 0)
                    if (myDS.Tables[0].Rows.Count > 0)
                    {
                        textBox1.Text = myDS.Tables[0].Rows[0]["Con"].ToString();
                    }

//-----------

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM content WHERE Id = 2", connection);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);

            DataTable dt = new DataTable();
            da.Fill(dt);
            dt.Columns[0].AutoIncrement = true;
            DataRow row = dt.Rows[0];
            row["SubCon"] = szTestValue;

            try
            {
                da.Update(dt);
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }

            dt.Clear();
            da.Fill(dt);

            MessageBox.Show(dt.Rows[0]["SubCon"].ToString());
            MessageBox.Show("Done");
            connection.Close();
        }
[25 Jul 2007 16:05] Tonci Grgin
Test case output

Attachment: 29006.jpg (image/jpeg, text), 150.84 KiB.