MySQL Bugs: #35481: Bug in MyODBC using prepared statements in ASP/ADO

Bug #35481	Bug in MyODBC using prepared statements in ASP/ADO
Submitted:	21 Mar 2008 12:10	Modified:	25 Mar 2008 9:36
Reporter:	Jerry Potokar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.2 beta	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	insert, special non latin characters, Unicode, UPDATE, UTF-8

Description:
MyODBC works great on my heavy used site. But there is a bug that makes it unusable for me and anybody who uses ASP with prepared statements (like the ones Dreamweaver makes).

Using prepared statement to insert or update text by ADO, character "č" and "Č" are changed to it's latin1 equivalent "è" :-((( When using "normal" not prepared statements everything is fine. Using other characters work like "šž" and "ŠŽ". Just the "č" is problematic. I use @codepage=65001, response.charset="utf-8", pages are saved in UTF-8 so this is not a problem. As I said, not using prepared statements works 100% and the driver is very stable to handle my big site. Unfortunately with this bug I have to go "back" to the old ODBC :-(

How to repeat:
Thy inserting or updating a mysql query that has inside a character "č" or "Č", It will change to "è" in database :-(

Hi Jerry and thanks for your report. As I'm from Croatia I understand exactly what you need. Now, in order to help you I will need something more that "It doesn't work".
Please provide/attach:
 my.ini / my.cnf from server
 complete DDL/DML statements used to create database/table(s) and data in them, suitable for testing, in form suitable for loading into my server
 details on IIS server
 ASP page in question
 output from cl client for "SHOW VARIABLES LIKE 'char%'"
Relevant part of general query log and ODBC trace might prove useful too.

My best bet, for now and without any real info, would be that you're using binary charset and/or collation (like UTF8_bin). Try replacing them with something that suits you.

Ja sam iz Slovenije so :-) We have same alphabet :-)

This is my first bug report, please help me on:

How do I prepare DDL/DML statements, I have Navicat? Is what you need the complete insert statements so you can make the database?

Where do I get details of IIs server? I have Windows 2003, so IIS 6.0, but I guess you need more info.

ASP page I paste here? Best I prepare some test ASP page, mine is too complicated :-)

What is a cl client?

ODBC trace is problematic, I tried it before, never really worked I only got empty files :-(

I am using UTF8 charset and slovenian collation.

Wierd is the š and ž are ok, č is not. Usually š and ž a re problematic on windows because Windows use 1250 codepage and web pages usually did 8859-2 and č was the same š and ž on different places.

It has to do something with MyODBC 5.x, because everything else works and everything is set up as UTF-8 as you'll see from the pages I send.

Yours

Jernej

Is this the "binary" you were talking about?

character_set_client	utf8
character_set_connection	utf8
character_set_database	utf8
character_set_filesystem	binary
character_set_results	utf8
character_set_server	utf8
character_set_system	utf8
character_sets_dir	K:\MySQL\share\charsets\

Znam, zato sam uzeo tvoj problem "preko reda" ;-)

Ok, let's start gathering stuff. As it already proved very hard/impossible to transfer entire IIS setup we will gather info bit by bit. Since ODBC logging is not possible under IIS, we can start with:
 - ODBC trace: Go to Start / Settings / Control Panel / Administrative tools / Data Sources (ODBC). Pick "Tracing tab" and, before starting your web server, click "Start tracing now". If you tried this before and got empty logs, disregard.
 - General query log from MySQL server: Start MySQL server with --log or add word "log" (no quotes) in [mysqld] section of my.ini file like this:
[mysqld]
....
log

Now for other stuff. So IIS is 6.0 and you better prepare as small example ASP page as possible and attach it to report ("Files" tab).

cl client is mysql.exe. Invoke it from command line like this:
mysql -uuser_name -p -hyour_host test_db

DDL, DML: do, from command prompt, mysqldump --opt -uuser_name -p -hyour_host test_db > bug35481.txt. Zip it and attach. Be sure no sensitive business info is inside!

Then we will see about "Č".

You provide some answers while I was still writing. Ok, now I need exact structure of table producing wrong characters too.

Seems at this point I bugged you for nothing :-( I made a small test database with one column with update, so I could use form to update text...

Well... It worked! But using my page it didn't work. So I bugged myself what is the difference and it is this:

Page that works

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 300, Request.Form("ImeRecepta")) ' adLongVarChar

Page that doesn't work:

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 201, 1, 300, Request.Form("ImeRecepta")) ' adLongVarChar

As you see the only difference is the prepared statement, once it has 201 and once it has 202. I have to find out what is the difference. With 202 there it seems to work, but I still thing MyODBC is faulty at this point, because using old ODBC it also works with 201 there. If I put 201 I have a mess... I do have a working small example for you if you want.

Thank you for your time.

Jerry, it looked that way from the start but it does not matter. You may attach your sample for me to try.

Thanks for your interest in MySQL.

Here:

http://www.w3schools.com/ado/met_comm_createparameter.asp

You will be able to tell if there is a bug in MyODBC or not. 

adVarChar	200	A string value (Parameter object only).
adLongVarChar	201	A long string value.
adVarWChar	202	A null-terminated Unicode character string.
adLongVarWChar	203	A long null-terminated Unicode string value.

I haven't tried 203 but 202 works. 200 and 200 do not work, although Dreamweaver uses them on Unicode pages :-( Ok, I can manually replace if needed. Now does MyODBC 5.1.2 needs to work with types 200 and 201 I don't know :-)

Anyway, I have to congratulate you on the new MyODBC 5.1.2. It is really stable! I have a site with 10.000 daily visitors and really heavy  MySQL usage. If I mess comething in my code, the server freezes before I say "Zagreb" :-) If 5.1.2 works on my site, it has to be good. Ok, the site is big for Slovenia, it is small for USA I guess :-)

Thank you for such fast response, that was really fast!!!

Jerry, I am glad things work now but you posted some questions I'm not sure about so I'll have to investigate further.

Jerry, according to http://msdn2.microsoft.com/en-us/library/ms713346(VS.85).aspx the behavior you described seems ok to me:
C type identifier	 *StrLen_or_IndPtr

SQL_C_CHAR		 Length of data in bytes Length of data in bytes

SQL_C_WCHAR		 Length of data in characters Length of data in characters

So by specifying 201 you are trying to fetch data with length in bytes while it's actually presented in characters, or, at least, I see it this way. GIGO expected.

But it works using older myOBDC. Using 201 I mean... But seems you were right, 201 is binary and 202 is characters.

I have no clue why "binary" affects only č and not š and ž but there has to be a reason.

My site is working well with ODBC 5.1.2 now. I had 300.000 page requests over the weekend and everything was fine :-)

Jerry, fact that it *used* to work means nothing as we have recently paid much attention to ODBC compliance. As for second part of your question, I already asked for review of my ruling so not to miss potential bug, either in code or in docs.