Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17501

Connect-Storage engine: character set failure

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.3.6, 10.3.7, 10.3.8, 10.3.9
    • 10.3.12
    • Storage Engine - Connect
    • 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

          Ascending order - Click to sort in descending order
          View 28 older comments
          bar Alexander Barkov added a comment -

          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:

          DROP TABLE IF EXISTS t1;
          		 
          CREATE TABLE t1 (
          		 
            a VARCHAR(4000)
          		 
          ) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 CONNECTION='DSN=HHZ1' TABLE_TYPE='ODBC' DATA_CHARSET=latin1 tabname='CHZ.T1';
          		 
          SELECT a, HEX(a) FROM t1;

          bar Alexander Barkov added a comment - 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: DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( a VARCHAR (4000) ) ENGINE= CONNECT DEFAULT CHARSET=utf8mb4 CONNECTION = 'DSN=HHZ1' TABLE_TYPE= 'ODBC' DATA_CHARSET=latin1 tabname= 'CHZ.T1' ; SELECT a, HEX(a) FROM t1;
          KB_Heinze Karsten Budde added a comment -

          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

          KB_Heinze Karsten Budde added a comment - 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
          bar Alexander Barkov added a comment - - edited

          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!

          bar Alexander Barkov added a comment - - edited 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!
          bertrandop Olivier Bertrand added a comment -

          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.

          bertrandop Olivier Bertrand added a comment - 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.
          bar Alexander Barkov added a comment -

          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.

          bar Alexander Barkov added a comment - 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 .

          People

            bertrandop Olivier Bertrand
            KB_Heinze Karsten Budde
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.