Non-ASCII characters get scrambled when retrieving objects from the REST interface

[RKrahl]

Any string attributes from an object containing non-ASCII characters get scrambled when retrieving the object from ICAT's REST interface.

Example, start with an empty ICAT test server:

>>> # Prepare some strings having non-ascii characters:
>>> fac_fullname = "Beispielzentrum f\xfcr Materialien und Energie"
>>> fac_fullname
'Beispielzentrum für Materialien und Energie'
>>> inv_title = ("\u201CTest\u201D \u2013 an investigation "
...              "having non-ascii chars in the title \u2026")
>>> inv_title
'“Test” – an investigation having non-ascii chars in the title …'

>>> # Create some objects in ICAT:
>>> facility = client.new("facility", name="BZME", fullName=fac_fullname)
>>> facility.create()
>>> inv_type = client.new("investigationType", facility=facility, name="Standard")
>>> inv_type.create()
>>> investigation = client.new("investigation", facility=facility, type=inv_type,
...                            name="test", visitId="none", title=inv_title)
>>> investigation.create()

>>> # Retrieve these objects back from ICAT, verify that the non-ascii
... # characters are still intact (note that this uses the SOAP interface):
>>> query = ("SELECT i FROM Investigation i "
...          "WHERE i.name = 'test' "
...          "INCLUDE i.facility")
>>> client.assertedSearch(query)[0]
(investigation){
   createId = "simple/root"
   createTime = 2018-07-23 13:51:08+00:00
   id = 2
   modId = "simple/root"
   modTime = 2018-07-23 13:51:08+00:00
   facility = 
      (facility){
         createId = "simple/root"
         createTime = 2018-07-23 13:51:08+00:00
         id = 3
         modId = "simple/root"
         modTime = 2018-07-23 13:51:08+00:00
         fullName = "Beispielzentrum für Materialien und Energie"
         name = "BZME"
      }
   name = "test"
   title = "“Test” – an investigation having non-ascii chars in the title …"
   visitId = "none"
 }

So far, so good, everything works fine. But this was python-icat which uses the SOAP interface. At least it proves that the object attributes are intact in the database. Now try retrieving the same objects using the same query from the ICAT Rest interface:

>>> import json
>>> from urllib.parse import urlencode
>>> import urllib.request
>>> import ssl

>>> # This is only needed for a test server having no valid certificate
>>> ssl_context = ssl.create_default_context()
>>> if not conf.checkCert:
...     ssl_context.check_hostname = False
...     ssl_context.verify_mode = ssl.CERT_NONE

>>> url = 'https://icat.example.org/icat'
>>> parameters = { "sessionId": client.sessionId, "query": query, }
>>> req = urllib.request.Request(url + "/entityManager?" + urlencode(parameters))
>>> with urllib.request.urlopen(req, context=ssl_context) as f:
...     result = json.loads(f.read().decode('utf-8'))

>>> result
[{'Investigation': {'id': 2, 'createId': 'simple/root', 'createTime': '2018-07-23T13:51:08.000Z', 'modId': 'simple/root', 'modTime': '2018-07-23T13:51:08.000Z', 'datasets': [], 'facility': {'id': 3, 'createId': 'simple/root', 'createTime': '2018-07-23T13:51:08.000Z', 'modId': 'simple/root', 'modTime': '2018-07-23T13:51:08.000Z', 'applications': [], 'datafileFormats': [], 'datasetTypes': [], 'facilityCycles': [], 'fullName': 'Beispielzentrum f��r Materialien und Energie', 'instruments': [], 'investigationTypes': [], 'investigations': [], 'name': 'BZME', 'parameterTypes': [], 'sampleTypes': []}, 'investigationGroups': [], 'investigationInstruments': [], 'investigationUsers': [], 'keywords': [], 'name': 'test', 'parameters': [], 'publications': [], 'samples': [], 'shifts': [], 'studyInvestigations': [], 'title': '���Test��� ��� an investigation having non-ascii chars in the title ���', 'visitId': 'none'}}]

>>> # We see, there is a problem with the non-ascii characters:
>>> result[0]['Investigation']['facility']['fullName']
'Beispielzentrum f��r Materialien und Energie'

>>> # The U+00FC "LATIN SMALL LETTER U WITH DIAERESIS" is replaced by two
... # Unicode U+FFFD: "REPLACEMENT CHARACTER":
>>> result[0]['Investigation']['facility']['fullName'][17] == '\ufffd'
True
>>> result[0]['Investigation']['facility']['fullName'][18] == '\ufffd'
True

>>> # Same in the investigation title:
>>> result[0]['Investigation']['title']
'���Test��� ��� an investigation having non-ascii chars in the title ���'
>>> result[0]['Investigation']['title'][0] == '\ufffd'
True

We see in the result, any non-ascii character is replaced by as many U+FFFD replacement characters as the utf-8 representation of this character has bytes. It looks like the REST interface code in icat.server takes an already utf-8 encoded string from the database and tries to utf-8 encode it once again, but fails to recognize the encoded bytes and replaces them with U+FFFD.

[stuartpullinger]

Hi,

Please could you confirm something or correct me: In your example, your test investigation appears to have UTF-16 characters:

inv_title = ("\u201CTest\u201D \u2013 an investigation "
... "having non-ascii chars in the title \u2026")
inv_title
'“Test” – an investigation having non-ascii chars in the title …'

whereas, when you are accessing the REST API, your call to the decode method on the response specifies UTF-8:

with urllib.request.urlopen(req, context=ssl_context) as f:
... result = json.loads(f.read().decode('utf-8'))

Could you re-try it, using a call to decode('utf-16')?

Thanks,
Stuart

[RKrahl]

No, there is no such thing as UTF-16 characters. You need to distinguish characters at one side from character encodings at the other side. A character is a code point in some character set. Python, as most modern computer systems, uses Unicode. So a character here is basically some integer number in the range between 0x000000 and 0x10fffd. A character encoding is a particular way to represent these numbers in a medium, such as in a file or on a network channel. UTF-8 and UTF-16 are both character encodings for Unicode and you can represent any Unicode character in either encoding.

So if I look at the raw bits that icat.server sends over the line at the REST interface, I get:

>>> with urllib.request.urlopen(req, context=ssl_context) as f:
...     raw_result = f.read()
... 
>>> raw_result
b'[{"Investigation":{"id":2,"createId":"simple/root","createTime":"2018-07-23T13:51:08.000Z","modId":"simple/root","modTime":"2018-07-23T13:51:08.000Z","datasets":[],"facility":{"id":3,"createId":"simple/root","createTime":"2018-07-23T13:51:08.000Z","modId":"simple/root","modTime":"2018-07-23T13:51:08.000Z","applications":[],"datafileFormats":[],"datasetTypes":[],"facilityCycles":[],"fullName":"Beispielzentrum f\xef\xbf\xbd\xef\xbf\xbdr Materialien und Energie","instruments":[],"investigationTypes":[],"investigations":[],"name":"BZME","parameterTypes":[],"sampleTypes":[]},"investigationGroups":[],"investigationInstruments":[],"investigationUsers":[],"keywords":[],"name":"test","parameters":[],"publications":[],"samples":[],"shifts":[],"studyInvestigations":[],"title":"\xef\xbf\xbd\xef\xbf\xbd\xef\xbf\xbdTest\xef\xbf\xbd\xef\xbf\xbd\xef\xbf\xbd \xef\xbf\xbd\xef\xbf\xbd\xef\xbf\xbd an investigation having non-ascii chars in the title \xef\xbf\xbd\xef\xbf\xbd\xef\xbf\xbd","visitId":"none"}}]'

>>> # have a look at the "für" appearing in raw_result at position 412:
>>> raw_result[412:420]
b'f\xef\xbf\xbd\xef\xbf\xbdr'
>>> # These are two consecutive Unicode U+FFFD characters in UTF-8 encoding:
>>> raw_result[413:419] == '\ufffd'.encode('utf-8')*2
True

>>> # Same for the title:
>>> raw_result[750:775]
b'"title":"\xef\xbf\xbd\xef\xbf\xbd\xef\xbf\xbdTest\xef\xbf\xbd'
>>> # Again, these are three consecutive Unicode U+FFFD characters in UTF-8 encoding:
>>> raw_result[759:768] == '\ufffd'.encode('utf-8')*3
True

So this is clearly valid UTF-8 encoded text with all non-ASCII characters being set to U+FFFD.

It is by the way not UTF-16 (note that UTF-16 encodes all characters from the BMP (U+0000 – U+FFFD) using exactly two bytes):

>>> # The raw result has an odd number of bytes, so this can't possibly be UTF-16:
>>> len(raw_result)
875
>>> # Indeed:
>>> raw_result.decode('utf-16')
Traceback (most recent call last):
  File "<stdin>", line 2, in <module>
UnicodeDecodeError: 'utf-16-le' codec can't decode byte 0x5d in position 874: truncated data

[stuartpullinger]

Sorry for the confusion - I was conflating the '\x' hex and the '\u' codepoint sequences in your example - and thanks for the clarification. I think the issue could be caused by calls to ByteArrayOutputStream.toString(), such as here. From the Java8 docs for .toString():

"Converts the buffer's contents into a string decoding bytes using the platform's default character set...
This method always replaces malformed-input and unmappable-character sequences with the default replacement string for the platform's default character set."

I think these calls (14 in this ICATRest.java) should be replaced with .toString(String charsetName):

"Converts the buffer's contents into a string by decoding the bytes using the named charset."

Is it sufficient to use UTF-8 or do we need to discuss this with the collaboration or even make it configurable?

[stuartpullinger]

Here's a good blog post explaining the issues and how this is configured:

Java May Use UTF-8 as its Default Charset

As the article says, a future version of Java may use UTF-8 as the default file encoding. In the meantime, it should be possible to set a system property to enforce it: -Dfile.encoding=UTF-8 - which I think applies to the ByteArrayOutputStream as well.

Are you able to test it and report back?

[stuartpullinger]

On second thoughts, and reading the comments in the blog post above, changing the default file encoding may not be sensible. ICAT runs on Windows and we don't want to change the encoding for all file system interactions - we just need to ensure that the REST interface returns JSON in UTF-8.

What follows are some notes to come back to after I return from holiday.

• The call to createGenerator with an OutputStream as it's argument creates a JSON generator which converts String characters to UTF-8 bytes on-the-fly. Then we convert these bytes back into a String (UTF-16) interpreting the bytes using the system's default character encoding - whatever that is.
• So my earlier suggestion of using .toString('UTF-8') on the ByteArrayOutputStream should work, but...
• Why are we using a ByteArrayOutputStream here at all? Why take Strings, convert them to bytes in a byte array then convert it back to a String?
• Could we use a StringWriter in place of the ByteArrayOutputStream? Then the Strings stay as Strings, we return a String and we leave it to the JAX-RS to do the conversion for us.

[RKrahl]

Why are we using a ByteArrayOutputStream here at all? Why take Strings, convert them to bytes in
a byte array then convert it back to a String?

Yes, I'm also somewhat confused by this code. The general rule to avoid weird character encoding issues is to always clearly distinguish between strings and bytes. All I/O is bytes, it is what you read from and write to files and send and receive over network connections. Bytes always use some character encoding (if it's text). Strings are native to your program language, are usually Unicode and use whatever internal representation your program environment uses. Input should be decoded from bytes to string when read and strings must be encoded to bytes for output. All internal processing should only be done with strings, whenever possible.

If you look at the symptom, it seems that at some point bytes are decoded assuming it to be plain ASCII when it really is UTF-8. You may simulate the effect in Python:

>>> fac_fullname = "Beispielzentrum f\xfcr Materialien und Energie"
>>> fac_fullname.encode('utf8').decode('ascii', errors='replace')
'Beispielzentrum f��r Materialien und Energie'
>>> inv_title = ("\u201CTest\u201D \u2013 an investigation "
...              "having non-ascii chars in the title \u2026")
>>> inv_title.encode('utf8').decode('ascii', errors='replace')
'���Test��� ��� an investigation having non-ascii chars in the title ���'

Anyway, I have an update:

• Import and export using POST and GET to port in the REST interface is also concerned.
• It seem to be related to the database backend: I did the tests above with a MariaDB backend. I do not have a test server using Oracle, but when retrieving data from our current production server, using Oracle backend, the issue does not occur.

[RKrahl]

… I double checked at the interactive database prompt that the character set for the database and all tables is indeed set to utf8:

MariaDB [icat]> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+                                                                                       
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |                                                                                       
+--------------+--------------------+----------------------------+------------------------+----------+                                                                                       
| def          | icat               | utf8                       | utf8_general_ci        | NULL     |                                                                                       
| def          | icat_authn_db      | utf8                       | utf8_general_ci        | NULL     |                                                                                       
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |                                                                                       
| def          | mysql              | utf8                       | utf8_general_ci        | NULL     |                                                                                       
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |                                                                                       
| def          | topcat             | utf8                       | utf8_general_ci        | NULL     |                                                                                       
+--------------+--------------------+----------------------------+------------------------+----------+                                                                                       
6 rows in set (0.00 sec)

MariaDB [icat]> show TABLE STATUS;
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name                    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| APPLICATION             | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2018-07-23 12:35:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| DATACOLLECTION          | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2018-07-23 12:35:43 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| DATACOLLECTIONDATAFILE  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| DATACOLLECTIONDATASET   | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| DATACOLLECTIONPARAMETER | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| DATAFILE                | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        49152 |         0 |              1 | 2018-07-23 12:35:44 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| DATAFILEFORMAT          | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| DATAFILEPARAMETER       | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| DATASET                 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        49152 |         0 |              1 | 2018-07-23 12:35:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| DATASETPARAMETER        | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| DATASETTYPE             | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| FACILITY                | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |        16384 |         0 |              4 | 2018-07-23 12:35:44 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| FACILITYCYCLE           | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2018-07-23 12:35:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| GROUPING                | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2018-07-23 12:35:44 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| INSTRUMENT              | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2018-07-23 12:35:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| INSTRUMENTSCIENTIST     | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:47 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| INVESTIGATION           | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |        32768 |         0 |              3 | 2018-07-23 12:35:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| INVESTIGATIONGROUP      | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| INVESTIGATIONINSTRUMENT | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| INVESTIGATIONPARAMETER  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:47 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| INVESTIGATIONTYPE       | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |        32768 |         0 |              4 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| INVESTIGATIONUSER       | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| JOB                     | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        49152 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| KEYWORD                 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| PARAMETERTYPE           | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| PERMISSIBLESTRINGVALUE  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:47 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| PUBLICATION             | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2018-07-23 12:35:44 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| PUBLICSTEP              | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2018-07-23 12:35:44 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| RELATEDDATAFILE         | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:47 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| RULE_                   | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2018-07-23 12:35:47 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| SAMPLE                  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| SAMPLEPARAMETER         | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| SAMPLETYPE              | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2018-07-23 12:35:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| SESSION_                | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2018-07-23 12:35:44 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| SHIFT                   | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| STUDY                   | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2018-07-23 12:35:46 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| STUDYINVESTIGATION      | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| USERGROUP               | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-07-23 12:35:45 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| USER_                   | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2018-07-23 12:35:44 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
39 rows in set (0.00 sec)

But the default for the connection seem to be latin1, when no locale variables are set:

MariaDB [icat]> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | latin1                       |
| character_set_connection | latin1                       |
| character_set_database   | utf8                         |
| character_set_filesystem | binary                       |
| character_set_results    | latin1                       |
| character_set_server     | utf8                         |
| character_set_system     | utf8                         |
| character_sets_dir       | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.00 sec)

I don't know how to check what is set in the DB connection from icat.server. Maybe we need to add some parameters in the jdbc connection string in setup.properties to tell icat.server to set utf8 in the database connection?

[alastairduncan]

This is the connection string that is used by another system here at STFC for connection to a mysql system.

jdbc:mysql://:3306/?useUnicode=true&characterEncoding=UTF-8

The system uses Jetty and JPA but does not use the enterprise mechanisms in J2EE systems such as glassfish/payara. You could try using this in the url property for the jdbc connection pool for the mariadb database that you are having problems with and see if it solves your problem.

[RKrahl]

@alastairduncan, thanks for the hint. I tried that and finally managed to set these properties on the jdbc-connection-pool as confirmed checking the domain.xml in the Payara domain config dir:

<jdbc-connection-pool fail-all-connections="true" ping="true" steady-pool-size="2" datasource-classname="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" name="icat" res-type="javax.sql.DataSource">
      <property name="useUnicode" value="true"></property>
      <property name="user" value="icat"></property>
      <property name="password" value="secret"></property>
      <property name="url" value="jdbc:mysql://mysql.example.org:3306/icat"></property>
      <property name="characterEncoding" value="utf8"></property>
</jdbc-connection-pool>

(it's not just adding the values to the jdbc connection string, one needs to patch setup_utils.py in order to add the properties to arguments in the asadmin create-jdbc-connection-pool command.)

However, it didn't have any visible effect. The error as described above remains the same.

[RKrahl]

I did some experimenting on when this issue occurs and when not. It turns out that it may also occur with Oracle, so it does not depend on the database backend as suggested above.

I finally found what triggers the issue: if the environment variable LC_ALL is set to en_US.UTF-8 (or some other UTF-8 locale) for Payara (e.g. the environment variable must be set during startup of the Payara process), the issue does not occur and non-ASCII characters remain intact. (Alternatively, the environment variable LANG seem to work as well.) Obviously, this also requires the locale data to be installed on the machine (package glibc-locale, glibc-common, or locales-all, depending on the Linux distribution). If either the environment variable is not set or the locale data is not installed, the non-ASCII characters get scrambled.

As this is not a bug in icat.server, but rather a configuration issue in the environment, I'll close as invalid.