MySQL Bugs: #39831: MySQL ODBC connector error when using data in cp1251 charset

Bug #39831	MySQL ODBC connector error when using data in cp1251 charset
Submitted:	3 Oct 2008 7:48	Modified:	5 Jan 2009 16:00
Reporter:	Dmitry Golubev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1.5	OS:	Windows (MySQL server version: 5.0.27)
Assigned to:	Jess Balint	CPU Architecture:	Any

Description:
If the table uses any Cyrillic charset (cp1251, koi8r, cp866) and text columns contain symbols in such code page (i.e. cyrillic chars), then select through MyODBC connector returns an error:
[MySQL][ODBC 5.1 Driver][mysqld-5.0.27-community-nt]Restricted data type attribute violation

The same is for inserting cyrillic chars into text column in MySQL table through MyODBC connector.

This bug is critical as it makes it impossible to use MyODBC connector in developing client applications for MySQL database.

How to repeat:
Setup:
Create table in any Cyrillic charset (cp1251, koi8r, cp866). Table should contain at least one column of varchar data type. Add at least one row into the table and write cyrillic symbols into varchar column.
Actions:
Use any client that is capable of retrieving data from ODBC sourses to select rows from this table. Many of the clients just crash. For example, if using Microsoft Query in Excel - it will just crash. I used MyODBC Connector in C++ code via ODBC API to get this error description.

Hi Dmitry and thanks for your report.

I do not have cyrilic kbd here so I ask of you to attach:
  - Small database dump with example data. Please do it from mysqldump so I can load it later. Also, make sure database/tables/data are created with c-sets demonstrating this problem.
  - Attach your C(++) test case.
  - Paste output from mysql cl client running SHOW VARIABLES LIKE "%char%";

Could it be that you have some mixup in your settings so that double-conversion happens? Did you try prefixing query text with "_"charset identifier? Did you issue "SET NAMES ..." which is a no-no in 5.1 and so on...

Hi,

I will attach the database backup with example data in cyrillic charset.

The output of SHOW VARIABLES LIKE "%char%"; command below:
----------------------------------------------------------
'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'cp1251'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8'
'character_set_server', 'cp1251'
'character_set_system', 'utf8'
'character_sets_dir', 'C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\'

I cannot attach C++ test case as it is very complex - this is my author's program. You can use MS Excel for example and see it's crash.

I did not use "SET NAMES". Even with it - the error is the same. I did not try prefixing query text with "_"charset identifier becauseI am not yet experienced MySQL user - I am just evaluating it for possible use. I am currently using Oracle.

P.S. Recently I downloaded MySQL v.5.1.28 and the problem is the same.

Test DB dump

Attachment: test.dmp (application/octet-stream, text), 1.84 KiB.

hI, I use MSAccess2003(on MS XP) as frontend programm to connect to tables of MySql (V5.0) . I connect through System DSN and use ODBC drv. Whith ODBC drv 3.5x no problems. I have installed ODBC drv 5.1. MSAccess has connect to tables in MySQL DB, but when it opened some tables, MS Accsess was down with error (suggest to sent err to Microsoft). Two drivers were installed on PC (3.5 end 5.1). MS Access open table if it has only Integer or Double colomn (no char). I use char set =cp1251. What is reason this case?

Ultimately this will not be fixed until bug#32831 is fixed and the ODBC driver is built with the required character sets. Until then, we can at least prevent applications from crashing:

=== modified file 'driver/utility.c'
--- driver/utility.c    2008-08-22 22:12:25 +0000
+++ driver/utility.c    2008-10-09 23:33:59 +0000
@@ -426,6 +426,10 @@
   if (!result_bytes)
     result= 0;       /* Don't copy anything! */

+  if (!from_cs)
+    return set_stmt_error(stmt, "07006", "Source character set not "
+                         "supported by client", 0);
+
   /*
    If we don't have to do any charset conversion, we can just use
    copy_binary_result() and NUL-terminate the buffer here.

Hi Jess,

Thanks for your analysis. Now I think the root cause of the problem is known and we only need to have driver support all the code pages. I believe everyone agrees that this is critical, otherwise ODBC driver will be just useless for all non-English speaking end users.

Looking forward to getting the resolution soon.

Dmitry,
If possible, you can use Unicode/UTF-8 in your application for support of the needed character sets.

Hi I imported new data base. It was created with code page utf8. MSAccess works normal with DB (cp utf8) trough ODBC drv5.1 . But all my working DBs were created with cp1251. and I don't want change code page. I think It cause some problrms when another application (web) will access to my DB.
The best of all would be if drv 5.1 works with cp1251

Pushed as rev 810.

Will be released in 5.1.6.

An entry was added to the 5.1.6 changelog:

If the table used any Cyrillic charset (cp1251, koi8r, cp866) and text columns contained symbols from these code pages, then SELECT through the MyODBC connector returned an error:

[MySQL][ODBC 5.1 Driver][mysqld-5.0.27-community-nt]Restricted data type attribute violation
The same is true when inserting Cyrillic characters into a text column in a MySQL table, through the MyODBC connector.

Changelog was corrected:

MyODBC would crash when a character set was being used on the server that was not supported in the client, for example cp1251:

[MySQL][ODBC 5.1 Driver][mysqld-5.0.27-community-nt]Restricted data type attribute violation

The fix causes MyODBC to return an error message instead of crashing.

This still seems to be an issue.
If I specify a charset in the connect string to SQLConnectDriver() that does not exist (e.g. 'latin128') then myodbc5.dll crashes immediately when executing a SELECT statement using SQLExecDirect().
SQLConnectDriver although does not report an error.
I am using 5.1.7 plus myodbc5S.dll patch (2010-09-30)

Sascha

Sorry, ment SQLDriverConnect() instead of SQLConnectDriver()