What is the default server characterset in mariadb inside the Swisscom Cloud and how can it be changed?

Question

This is a request for clarification concerning the Swisscom Cloud MariaDB Service. This is not about the generic MariaDB default charactersets.

1. I would like to know what the default character_set_server for the MariaDB Service on the Swisscom Cloud is defined as.
2. Assuming that it is actually latin1, I would like to know if this default can be changed (to utf-8 or more accurately utf8mb4 as it is for the debian mariadb package).

The main reason for my question is that I am having trouble creating backups that can easily be re-imported and I am getting widely different size output when extracting via mysqldump ... -r backup.sql compared to an export through Sequel Pro.

Answer 1

I don't use the tool Sequel Pro.

The legacy MariaDB/Galera cluster uses latin1 as default character set when you don't set it. This default setting is from upstream MariaDB developers. Most developers and DBAs don't like this decision.

Here is the CREATE TABLE syntax (see COLLATE and CHARACTER SET) :

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

Server settings (this statement can every customer execute, no admin rights needed):

MariaDB [(none)]> show global variables like 'character_set%';
+--------------------------+-------------------------------------+
| Variable_name            | Value                               |
+--------------------------+-------------------------------------+
| character_set_client     | latin1                              |
| character_set_connection | latin1                              |
| character_set_database   | latin1                              |
| character_set_filesystem | binary                              |
| character_set_results    | latin1                              |
| character_set_server     | latin1                              |
| character_set_system     | utf8                                |
| character_sets_dir       | /usr/share/mariadb-galera/charsets/ |
+--------------------------+-------------------------------------+
8 rows in set (0.00 sec)

If you still use legacy MariaDB, please hurry up and migrate your service instance. The legacy cluster will be in one or two days read-only.

We have a completely new MariaDB service in store for you. It comes with many improvements (e.g. MariaDB version 10) and we recommend to upgrade as soon as possible.

This step-by-step guide was created Aug 16, 2017.

The new cluster uses better default settings:

MariaDB [(none)]> show global variables like 'character_set%';
+--------------------------+------------------------------------------------------------------------------------------+
| Variable_name            | Value                                                                                    |
+--------------------------+------------------------------------------------------------------------------------------+
| character_set_client     | utf8                                                                                     |
| character_set_connection | utf8                                                                                     |
| character_set_database   | utf8                                                                                     |
| character_set_filesystem | binary                                                                                   |
| character_set_results    | utf8                                                                                     |
| character_set_server     | utf8                                                                                     |
| character_set_system     | utf8                                                                                     |
| character_sets_dir       | /var/vcap/data/packages/mariadb/95a1896c4bcdccb4e1abd81b7d00eb33aedb0da0/share/charsets/ |
+--------------------------+------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)

If you wish to change your character set and collation before importing the data into new cluster.

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

We did this many times. Please keep in mind that you shouldn't do this with > 20000 records.

Please also have a look at our documentation for MariaDB

Please contact Swisscom Application Cloud support when you need to issue long running DDL changes (for example ALTER TABLE with thousends of records). Long running DDL causes an outage for all customers on the cluster.