[MDEV-11029] connect engine odbc type table Simplified Chinese display error Created: 2016-10-11  Updated: 2020-11-06

Status: Open
Project: MariaDB Server
Component/s: Character Sets, Storage Engine - Connect
Affects Version/s: 10.1.18
Fix Version/s: 10.1

Type: Bug Priority: Major
Reporter: SmileJi Assignee: Olivier Bertrand
Resolution: Unresolved Votes: 2
Labels: connect-engine
Environment:

centeos 6


 Description   

Mariadb connect engine odbc type table,connecting oracle database,Simplified Chinese display error.

Replication method：
odbc.ini configuration file：

		[oracletest]
 
		Application Attributes = T
 
		Attributes = W
 
		BatchAutocommitMode = IfAllSuccessful
 
		BindAsFLOAT = F
 
		CloseCursor = F
 
		DisableDPM = F
 
		DisableMTS = T
 
		Driver = /opt/instantclient_12_1/libsqora.so.12.1
 
		DSN = ulodbc 
		EXECSchemaOpt =
 
		EXECSyntax = T
 
		Failover = T
 
		FailoverDelay = 10
 
		FailoverRetryCount = 10
 
		FetchBufferSize = 64000
 
		ForceWCHAR = F
 
		Lobs = T
 
		Longs = T
 
		MaxLargeData = 0
 
		MetadataIdDefault = F
 
		QueryTimeout = T
 
		ResultSets = T
 
		ServerName = //172.16.110.13:1521/orcl 
		SQLGetData extensions = F
 
		Translation DLL =
 
		Translation Option = 0
 
		DisableRULEHint = T
 
		UserID = scott
 
		password = tiger
 
		StatementCache=F
 
		CacheBufferSize=20
 
		UseOCIDescribeAny=F
 
		SQLTranslateErrors=F
 
		MaxTokenSize=8192
 
		AggregateSQLType=FLOAT
 
		CharacterSet=AL32UTF8

oracle:

		create table t(c1 varchar2(10),c2 varchar2(10));
 
		insert into t values('呵','呵');
 
		insert into t values('Mariadb','Mariadb');
 
		commit;

mariadb：
odbc table：

			"root@127.0.0.1 Tue Oct 11 09:47:42 2016 09:47:42 [test]>CREATE TABLE t ENGINE=CONNECT TABLE_TYPE=ODBC tabname='T' dbname='SCOTT' CONNECTION='DSN=oracletest;UID=scott;PWD=tiger;' option_list='Block_Size=100000,Scrollable=true' DATA_CHARSET=utf8 CHARSET=utf8; 
			Query OK, 0 rows affected (1.54 sec)
 
			"root@127.0.0.1 Tue Oct 11 09:47:01 2016 09:47:01 [test]>select * from t;
 
			+---------+---------+
 
			| C1      | C2      |
 
			+---------+---------+
 
			| ?       | ?       |
 
			| Mariadb | Mariadb |
 
			+---------+---------+
 
			2 rows in set (0.09 sec)

Chinese is not the first line should not appear as '?'.
This is why？

		jdbc table：
 
			"root@127.0.0.1 Tue Oct 11 09:58:06 2016 09:58:06 [test]>CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC tabname='T' CONNECTION="jdbc:oracle:thin:@172.16.110.13:1521:orcl" OPTION_LIST="User=scott,Password=tiger";       
			Query OK, 0 rows affected (0.45 sec)
 
 
 
			"root@127.0.0.1 Tue Oct 11 09:58:18 2016 09:58:18 [test]>select * from t1;    
			+---------+---------+
 
			| C1      | C2      |
 
			+---------+---------+
 
			| 呵      | 呵      |
 
			| Mariadb | Mariadb |
 
			+---------+---------+
 
			2 rows in set (0.07 sec)

jdbc table display is normal.

 Comments   
Comment by SmileJi [ 2016-10-12 ]

See that you have changed to fix, in which fixed in version 10.1?

Comment by SmileJi [ 2016-10-12 ]

My test version is mariadb 10.1.18.

Comment by Olivier Bertrand [ 2016-10-13 ]

Not fixed yet. I just begin to work on it but the issue is that I cannot create the table on my machine not have the proper code page. In the Oracle shell, when I paste your:

insert into t values('呵','呵');

It is pasted as:

insert into t values('?','?');

Even the character 呵 show on my screen, it is not really usable.
How can I create the table? Perhaps entering directly the utf8 representation in it but I don't know what it is.

What seems strange is that the JDBC table retrieves the good row while nothing indicates using utf8 in its creation. I think that this is due to the JDBC process itself. JDBC probably works in UNICODE, generally coded in some utf8 and does the decode by itself.

ODBC does not and use latin1 by default. See the Oracle documentation about the DSN parameters, there is a charset parameter that possibly could solve your problem.

Please let me know if that works. Thanks.

Comment by Olivier Bertrand [ 2016-10-13 ]

Hummm... I think the information a got on the Internet about DSN parameters was for the MySQL connector. About Oracle I did not found it. The problem is that if ODBC returns '?' characters instead of the good one, CONNECT cannot do anything about it. So if the Oracle ODBC driver cannot take in charge the utf8 charset, the only solution seems to use JDBC instead of ODBC.

Comment by SmileJi [ 2016-10-14 ]

The character set used in my oracle database is AMERICAN_AMERICA.AL32UTF8,When you create a Oracle database, you need to use the AL32UTF8 character set.

I use the Oracle documentation to connect to the Oracle is ok.MariaDB Connect engine ‘s odbc and the implementation of the document is not the same way.Oracle link :http://docs.oracle.com/database/121/ADFNS/adfns_odbc.htm#ADFNS1216

I did not see the parameters of DSN in the Oracle document, you can send a link?

Comment by SmileJi [ 2016-10-14 ]

I also tested the JDBC table, JDBC type big table is very easy to cause the database crash.
crash info：

161014  7:55:03 [ERROR] mysqld got signal 11 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.1.18-MariaDB
 
key_buffer_size=8388608
 
read_buffer_size=2097152
 
max_used_connections=24
 
max_threads=5002
 
thread_count=21
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 20599099 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x0x7feea5b14008
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7fee8005fe08 thread_stack 0x30000
 
/usr/local/mariadb10118/bin/mysqld(my_print_stacktrace+0x2e)[0xc08f5e]
 
/usr/local/mariadb10118/bin/mysqld(handle_fatal_signal+0x4ba)[0x767cfa]
 
/usr/lib/jvm/jre-1.7.0/lib/amd64/server/libjvm.so(+0x7f8188)[0x7fee679cb188]
 
/usr/lib/jvm/jre-1.7.0/lib/amd64/server/libjvm.so(JVM_handle_linux_signal+0x95)[0x7fee679d10f5]
 
/lib64/libpthread.so.0[0x326b00f7e0]
 
/usr/lib/jvm/jre-1.7.0/lib/amd64/server/libjvm.so(+0x619936)[0x7fee677ec936]
 
/usr/lib/jvm/jre-1.7.0/lib/amd64/server/libjvm.so(+0x621893)[0x7fee677f4893]
 
/usr/local/mariadb10118/lib/plugin/ha_connect.so(_ZN7JDBConn5CheckEi+0x94)[0x7feed1d91dd4]
 
/usr/local/mariadb10118/lib/plugin/ha_connect.so(_ZN7JDBConn14SetColumnValueEiPcP5VALUE+0xc1)[0x7feed1d920b1]
 
/usr/local/mariadb10118/lib/plugin/ha_connect.so(_ZN7JDBCCOL10ReadColumnEP7_global+0x49)[0x7feed1d97289]
 
/usr/local/mariadb10118/lib/plugin/ha_connect.so(_ZN6COLBLK4EvalEP7_global+0x61)[0x7feed1d07871]
 
/usr/local/mariadb10118/lib/plugin/ha_connect.so(_Z11EvalColumnsP7_globalP3TDBbb+0xe9)[0x7feed1cfdf99]
 
/usr/local/mariadb10118/lib/plugin/ha_connect.so(_Z11CntReadNextP7_globalP3TDB+0x11d)[0x7feed1cfe14d]
 
/usr/local/mariadb10118/lib/plugin/ha_connect.so(_ZN10ha_connect8rnd_nextEPh+0x6a)[0x7feed1cf34fa]
 
/usr/local/mariadb10118/bin/mysqld(_ZN7handler11ha_rnd_nextEPh+0x1b4)[0x76c354]
 
/usr/local/mariadb10118/bin/mysqld(_Z13rr_sequentialP11READ_RECORD+0x3b)[0x897dab]
 
/usr/local/mariadb10118/bin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x147)[0x5f32d7]
 
/usr/local/mariadb10118/bin/mysqld[0x5fa7cd]
 
/usr/local/mariadb10118/bin/mysqld(_ZN4JOIN10exec_innerEv+0xe5e)[0x60980e]
 
/usr/local/mariadb10118/bin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x1dc)[0x607edc]
 
/usr/local/mariadb10118/bin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x250)[0x60bac0]
 
/usr/local/mariadb10118/bin/mysqld[0x5ada18]
 
/usr/local/mariadb10118/bin/mysqld(_Z21mysql_execute_commandP3THD+0x14f7)[0x5b3687]
 
/usr/local/mariadb10118/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x354)[0x5bbb34]
 
/usr/local/mariadb10118/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x23a7)[0x5beb67]
 
/usr/local/mariadb10118/bin/mysqld(_Z10do_commandP3THD+0x2a7)[0x5bef77]
 
/usr/local/mariadb10118/bin/mysqld(_Z24do_handle_one_connectionP3THD+0x183)[0x68e3f3]
 
/usr/local/mariadb10118/bin/mysqld(handle_one_connection+0x42)[0x68e612]
 
/lib64/libpthread.so.0[0x326b007aa1]
 
/lib64/libc.so.6(clone+0x6d)[0x326ace8aad]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fee888f8020): is an invalid pointer
 
Connection ID (thread ID): 28
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=off
 
 
 
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
 
information that should help you find out what is causing the crash.
 
 
 
We think the query pointer is invalid, but we will try to print it anyway. 
Query: select * from testodbc.j3
 
 
 
161014 07:55:04 mysqld_safe Number of processes running now: 0
 
161014 07:55:04 mysqld_safe mysqld restarted

This is why?
Create statement for J3 table:

CREATE TABLE j3 ENGINE=CONNECT TABLE_TYPE=JDBC tabname='ARC_E_MP_PARA_SNAP' CONNECTION="jdbc:oracle:thin:@172.16.90.231:1521:orcl" OPTION_LIST="User=SGPMS_LN,Password=sgpms_ln,Block_Size=10000,Scrollable=true";

The table size for ARC_E_MP_PARA_SNAP in oracle database is : 2G

Comment by Olivier Bertrand [ 2016-10-14 ]

There are many entries on the Internet about using utf8 with Oracle ODBC. Most of them from people using PHP. But I did not find the exact description of the parameters to use with the data source definition.

About the JDBC crash, I tried to connect to the big table but it failed:

(1105) Connecting:java.sql.SQLRecoverableException: I/O Error: The Network Adapter could not establish the connection rc=-2

Looking in the crash report, the last command sent to JDBC by CONNECT is:

env->ExceptionCheck()

After apparently successfully retrieved a column value. Then the crash occurs in JVM code.
I cannot do much to find the cause of it.

Comment by Olivier Bertrand [ 2016-10-15 ]

I could not make the Oracle table with chinese character but I made a CONNECT CSV table based on a file manually made containing the utf8 representation of that character:

呵,呵
 
Mariadb,Mariadb

The CONNECT table is:

create table chin (
 
  c1 char(10),
 
  c2 char(10))
 
engine=CONNECT default charset=utf8 table_type=CSV file_name='D:/Ber/Develop/chinese.csv';

And when executing:

select * from chin;

I get the expected display:

c1 c2
呵 呵
Mariadb Mariadb

Now I can address this table via JDBC by:

create table jchin engine=CONNECT default charset=utf8 table_type=JDBC tabname=chin
 
connection='jdbc:mysql://localhost:3307/test?user=root&useSSL=false';
 
select * from jchin;

and get the same display result. Note that I must specify this table with "default charset=utf8"
otherwise the display is the ut8 representation of the character, not the character itself.

Now with ODBC:

create table ochin engine=CONNECT default charset=utf8 table_type=ODBC tabname=chin
 
connection='dsn=MYSQL-ANSI';
 
select * from ochin;

Returns:

c1 c2
? ?
Mariadb Mariadb

Note that using the MYSQL-Unicode driver does not work either.
The solution is:

create table ochin engine=CONNECT default charset=utf8 table_type=ODBC tabname=chin
 
connection='dsn=MYSQL-ANSI;charset=utf8';

This one returns

c1 c2
呵 呵
Mariadb Mariadb

This shows that this problem is not with CONNECT but with ODBC. I found what to do
with the MySQL ODBC connector, unfortunately I don't know what to do with Oracle.
However, I think there should be something possible to solve this with Oracle.

Comment by Olivier Bertrand [ 2016-10-15 ]

On Windows, the ODBC data sources administrator window displays all the available data sources and permit the user to configure them.
When looking in the Oracle DSN configurator, I saw nothing concerning charset. The only thing that maybe could help is under the Workarounds tab where there is a check box called:
Force SQL_WCHAR Support
Maybe checking this box does the job, but I cannot test it not having the required table.

Comment by Alexander Barkov [ 2017-10-24 ]

One needs to use the DATA_CHARSET option to specify character set of the data in the remote table.

Oracle:

drop table simple;
 
create table simple (col1 varchar2(5), col2 number);
 
insert into simple values ('Aя', 199);
 
select * from simple;
 
commit;

MariaDB:

drop  table simple_link;
 
create table simple_link  ENGINE=CONNECT DEFAULT CHARSET=utf8 DATA_CHARSET=utf8  TABLE_TYPE=ODBC tabname='SIMPLE' CONNECTION='DSN=ConnectEngineOracle2;UID=mtr;PWD=mtr' ;
 
describe simple_link;
 
select * from simple_link;


+------+------+
 
| COL1 | COL2 |
 
+------+------+
 
| Aя   |  199 |
 
+------+------+

Generated at Thu Feb 08 07:46:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.