Bug #106502 odbc can not get the whole utf8 string
Submitted: 18 Feb 2022 2:32 Modified: 3 Apr 2023 9:32
Reporter: cole lin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:mysql-connector-odbc:8.0.28 OS:CentOS (8.5.2111)
Assigned to: CPU Architecture:x86
Tags: ODBC, utf8, varchar

[18 Feb 2022 2:32] cole lin
Description:
I create a database with utf8:

connect mysql:

#mysql -h 192.168.26.42 -u root -p

mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8mb4_bin |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

then create a table:

mysql> create table test_tab(f1 int, f2 varchar(25));
Query OK, 0 rows affected (0.12 sec)

insert 1 row:
mysql> insert into test_tab values(1, "沙子沙子沙子沙子沙子");
Query OK, 1 row affected (0.02 sec)

ps:"沙子沙子沙子沙子沙子" is Chinese;

select row:
mysql> select f2 from test_tab where f1 = 1;
+--------------------------------+
| f2 |
+--------------------------------+
| 沙子沙子沙子沙子沙子 |
+--------------------------------+
1 row in set (0.00 sec)

I can get whe whole utf8 string;

##########################################
I config ODBC DSN like that:
[ssodb]
Driver=/usr/lib64/libmyodbc8w.so
SERVER=192.168.26.42
PORT=3306
Database=sso
CHARSET=UTF8

then run:
#isql ssodb root 123456 -v

select row with odbc:
SQL> select f2 from test_tab where f1 = 1;
+--------------------------+
| f2 |
+--------------------------+
| 沙子沙子沙子沙子+--------------------------+
SQLRowCount returns 1
1 rows fetched

I just can get "沙子沙子沙子沙子" but not "沙子沙子沙子沙子沙子";

It seems ODBC just get 25/3 utf8 character; 25 is the size of column
f2;3 is ths utf8 character size;

ps:
unixODBC version:2.3.7/2.3.9
mysql-connector-odbc version:8.0.15/8.0.28
mysql server version:5.6.39

I have a topic on forums:
https://forums.mysql.com/read.php?37,703016,703016#msg-703016

How to repeat:
#mysql -h 192.168.26.42 -u root -p
mysql> create table test_tab(f1 int, f2 varchar(25));
mysql> insert into test_tab values(1, "沙子沙子沙子沙子沙子");
mysql> select f2 from test_tab where f1 = 1;

I can get "沙子沙子沙子沙子沙子" with mysql;
###########################
config ODBC DSN like that:
[ssodb]
Driver=/usr/lib64/libmyodbc8w.so
SERVER=192.168.26.42
PORT=3306
Database=sso
CHARSET=UTF8

#isql ssodb root 123456 -v
SQL> select f2 from test_tab where f1 = 1;

I just can get "沙子沙子沙子沙子" but not "沙子沙子沙子沙子沙子";
[23 Aug 2022 10:49] MySQL Verification Team
Hello Cole,

Thank you for the bug report.
Could you please try latest version 8.0.30? Thanks.

Regards,
Ashwini Patil
[6 Sep 2022 8:54] cole lin
I test with mysql-connector-odbc:8.0.30; This version(8.0.30) have the same problem;
[16 Mar 2023 7:04] MySQL Verification Team
Hello Cole,

Thank you for the feedback.
Which version of ODBC driver are you using i.e ANSI to UNICODE? 
Please see Bug #69837. According to dev, the problem should be resolved by using the ANSI version of ODBC driver.

Also, see Bug #62728

Regards,
Ashwini Patil
[3 Apr 2023 9:32] cole lin
Hello, Ashwini:
    I get the same result while i use libmyodbc8a.so and libmyodbc8w.so and the same time;
    My odbc.ini like that:

[test1]
Driver=/usr/lib64/libmyodbc8w.so
SERVER=192.168.26.42
PORT=3306
Database=test
CHARSET=UTF8

[test2]
Driver=/usr/lib64/libmyodbc8a.so
SERVER=192.168.26.42
PORT=3306
Database=test
CHARSET=UTF8

Then i use isql like that:
#test1:
#isql test1 root 123456 -v
SQL> select f2 from test_tab where f1 = 1;
+--------------------------+
| f2                       |
+--------------------------+
| 沙子沙子沙子沙子+--------------------------+
SQLRowCount returns 1
1 rows fetched

#test2:
#isql test2 root 123456 -v
SQL> select f2 from test_tab where f1 = 1;
+--------------------------+
| f2                       |
+--------------------------+
| 沙子沙子沙子沙子+--------------------------+
SQLRowCount returns 1
1 rows fetched

I can get which ODBC driver have loaded like that:
# ps -ef|grep isql
3410209 3358575  0 17:12 pts/8    00:00:00 isql test1 root 123456 -v
3410285 3410217  0 17:13 pts/12   00:00:00 isql test2 root 123456 -v
#lsof -p 3410209|grep myodbc 
isql    3410209 songx  mem    REG    253,0   8348640 561808941 /usr/lib64/libmyodbc8w.so
#lsof -p 3410285|grep myodbc        
isql    3410285 songx  mem    REG    253,0   8344504 561708403 /usr/lib64/libmyodbc8a.so