The short answer is to not set connection collation at all and instead upgrade drivers to the most recent version.
If you do need to set a collation sequence, you should set an utf8mb4 collation (such as utf8mb4_spanish_ci ).
Please note that drivers before version 2.6.1 may use 'utf8' (not 'utf8mb4') if server charset defaults to 'utf8'. In this special case, this may result in issues using 4 bytes utf8 encoded characters, like most of the smileys for example (thanks Diego :-).
Still, using an older driver, it may be possible to resolve this by setting the following session variables in the connection string (thanks Martie :-)
Database Connection > Properties > Driver Properties > SessionVariables:
Workaround: MySQL Driver
If this doesn't work, you may be able to use the MySQL jdbc driver, not the MariaDB driver (even though the database itself is MariaDB), and set the following driver properties:
- characterEncoding utf-8
- connectionCollation latin1_spanish_ci
Depending on the version of the database and driver, you may experience other problems when running MariaDB using a MySQL driver; we recommend using the MariaDB driver when connecting to a MariaDB server.