FWD » Core Development » Database

When using the MariaDB JDBC driver(version 3.5.3) included in fwd, I am getting Illegal mix of collations for examples like this:

for each book where entry(1, book.author, '.') = 'a':
    end.

At first, I thought it was a bug in FWD that was exposed with the newest driver version, however, now I don't really think it is a FWD bug at all. These are my set collations:

MariaDB [fwd]> SHOW VARIABLES like "%collation%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.000 sec)

I think that when that SQL query is executed, the result for entryin_itt(1, book__impl0_.author, '.') is in utf8mb4_unicode_ci(as it is executed on the server), however 'A' has utf8mb4_general_ci collation as it comes from the client, and I would assume it uses the connection collation. So, there it is the mismatch.

I also tried executing this query in the mysql command line: select book__impl0_.recid as col0_0_ from book book__impl0_ where entryin_itt(1, book__impl0_.author, '.') = 'A' COLLATE utf8mb4_unicode_ci;. The construction 'A' COLLATE utf8mb4_unicode_ci explicitly sets the collation for the 'A' literal as utf8mb4_unicode_ci. In this case, as there is no collation mismatch, the query is executed successfully.

Next, I have tried to understand how the collation_connection is set. I have looked in the documentation, however, couldn't find stated the default value, so, I don't understand how it gets to be 'utf8mb4_general_ci' I I have tried setting collation-connection = utf8mb4_unicode_ci in /etc/mysql/my.cnf, however the mariadb server fails at startup. The only why I managed to set the collation_connection is adding this to the JDBC URL: ?sessionVariables=collation_connection=utf8mb4_unicode_ci. This way also the above testcase is fixed. However, I don't think this is a really viable solution, the best option would be have collation_connection match collation_database implicitly or at least have it set up in a config file.

There could also be the question of why this is only reproducible with the newer JDBC driver. I don't really have the answer, however, I can dig deeper in that direction if necessary.

Looking back, I remember utf8mb4_general_ci was chosen, because it better matches 4GL, although it is not as accurate as unicode_ci version. The database should be created with this (#9139-52, #9104). OTOH, we did not receive a feedback on #9006.

Ovidiu, I understand that utf8mb4_general_ci would be the better choice, however the client still uses utf8mb4_unicode_ci as the default collation for the server. I think convincing them to change would be difficult. So, I guess using collation_connection=utf8mb4_unicode_ci in the JDBC URL would be the easiest fix.

So, I guess using collation_connection=utf8mb4_unicode_ci in the JDBC URL would be the easiest fix.

Is this backward compatible on the old driver?

The option collation_connection for the JDBC URL was added in 3.5.0. However, I have tried to set this option and use the old driver with version 3.1.2 and it seems it doesn't break anything. There is also this remark in their documentation (https://mariadb.com/docs/connectors/mariadb-connector-j/about-mariadb-connector-j#optional-url-parameters): General remark: Unknown options are accepted and silently ignored. So, options introduced in newer driver versions will be ignored by older versions that do not support them.