Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.6, 10.3.7, 10.3.8, 10.3.9
-
None
-
CentOS7, Oracle 11g, Character Set Oracle WE8ISO8859P1, Character Set MariaDB utf8mb4
Description
Hello,
in MariaDB 10.2.11 I create a connect-table from mariadb to orcacle with the following statement:
CREATE TABLE `hzEdatValues` ( |
`EDAT_DEF_ID` int(10) unsigned NOT NULL, |
`VALUE` varchar(40) NOT NULL, |
`TEXT_K` varchar(512) NOT NULL, |
`TEXT_E` varchar(4000) DEFAULT NULL, |
`CODES` varchar(4000) DEFAULT NULL, |
`SORT` int(10) unsigned NOT NULL, |
`SP_KZ` varchar(1) NOT NULL, |
`SP_TEXT` varchar(255) DEFAULT NULL, |
`NEU_DATUM` datetime NOT NULL, |
`NEU_USR_ID` int(10) unsigned NOT NULL, |
`AEND_DATUM` datetime NOT NULL, |
`AEND_USR_ID` int(10) unsigned NOT NULL |
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 CONNECTION='DSN=HHZ1' `TABLE_TYPE`='ODBC' `DATA_CHARSET`=latin1 `tabname`='CHZ.HZ_A_EDAT_VALUES' |
In oracle the character set is WE8ISO8859P1 and in mariadb utf8mb4.
Characters as 'ä', 'Ü' was shown correct in the connect-table.
Then we update mariadb to 10.3.6. Now the characters as 'ä' or 'Ü' are shown wrong in the connect-table: 'ä' -> 'a' and 'Ü' -> 'U'.
Now we use mariadb 10.3.9 and the characters are still wrong.
Can you help me?
Attachments
Activity
Alexander,
I am glad, your last tip led to success.
Here is the output on the console:
MariaDB [test]> DROP TABLE IF EXISTS t1;
|
Query OK, 0 rows affected (0.002 sec)
|
|
|
MariaDB [test]> CREATE TABLE t1 ( a VARCHAR(4000) ) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 CONNECTION='DSN=HZO1' TABLE_TYPE='ODBC' DATA_CHARSET=latin1 tabname='BUDDE.T1';
|
Query OK, 0 rows affected (0.002 sec)
|
|
|
MariaDB [test]> SELECT a, HEX(a) FROM t1;
|
+------+--------+
|
| a | HEX(a) |
|
+------+--------+
|
| â–’ | C384 |
|
| â–’ | C385 |
|
| â–’ | C386 |
|
+------+--------+
|
3 rows in set (0.148 sec)
|
Here the output in DBeaver:
a |HEX(a)
|
------|------
|
Ä |C384
|
Ã… |C385
|
Æ |C386
|
Thank you very much for your help.
Karsten
Karsten, you're welcome. Thank you for cooperation and fast feedback.
I think this should be documented.
bertrandop, can you please make sure that this is documented in the ConnectSE related manual sections?
Thanks!
All this seems very specific to Oracle and perhaps some Linux operating systems. Describing character handling with ODBC in general might require a complete book.
How do you think this should be documented for CONNECT?
I suggest, for users having character recognition problems with Oracle, just to add a link to this web page in the documentation.
Documented here:
https://mariadb.com/kb/en/library/connect-odbc-table-type-accessing-tables-from-another-dbms/#non-ascii-character-sets-with-oracle
Thanks for KennethDyer.
It appears that a systemd service does not respect /etc/environment.
After some googling, I found this way to add an environment variable to a service:
1. Open /usr/lib/systemd/system/mariadb.service in a text editor
2. Find the section "[Service]" and add the environment variable as follows:
[Service]
Environment=NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1
3. Reload systemd units:
systemctl daemon-reload
4. Restart MariaDB server:
sudo service mariadb restart
5. Run the test SQL script again: