Specifying Character Set and Collation for Clusters

Setting Character Set and Collation Engine Variables

Specify the character set for the cluster by setting the character_set_server engine variable.

Specify the collation for the cluster by setting one of the collation_connection, collation_database, and collation_server engine variables.

Important

Although these variables are session variables, these variables must be set globally to avoid undesired behavior. If one of these variables is set in a session to a value that does not match the value set globally, an error is generated. Change the value globally first, then change it in the session to match.

character_set_server

Controls the default character set of the cluster. Can be set to utf8mb4, utf8, or binary.

If character_set_server is set to a value that is different from its previous value, the associated collation variables collation_connection, collation_database, and collation_server are set to that character set’s default collation.

collation_connection, collation_database, and collation_server

Controls the default collation of the cluster. Can be set to the values returned by the SHOW COLLATION command.

When any of the collation_connection, collation_database, and collation_server, variables are set, the other two variables are set to the same value and the collation is applied to the nodes.

In addition, when any of these three variables is set, the variable character_set_server is set to a value that corresponds to the collation.

Set character_set_server

To set the character_set_server variable cluster-wide, use the following instructions.

Using a SingleStore client, run SET CLUSTER (the equivalent of SET GLOBAL) to set the character_set_server variable across the cluster. For example:

SQL

SET CLUSTER character_set_server = 'utf8';

Query OK, 0 rows affected (0.03 sec)

If the character_set_server variable is set using SET CLUSTER (or its equivalent SET GLOBAL) or SET AGGREGATOR, additionally run FLUSH CONNECTION POOLS on every aggregator node and restart the SQL session for the new value to take effect.

SingleStore offers commands to set engine variables cluster-wide, on all aggregator nodes, or on leaf nodes. Refer to Sync Variables and Engine Variables for more information.

Set collation_connection, collation_database, and collation_server

To set the collation variables cluster-wide, use the following instructions.

Using a SingleStore client, run SET CLUSTER (the equivalent of SET GLOBAL) to set the collation variable across the cluster. For example:

SQL

SET CLUSTER collation_server = 'utf8_general_ci';

Query OK, 0 rows affected (0.03 sec)

If a collation variable is set using SET CLUSTER (or its equivalent SET GLOBAL) or SET AGGREGATOR, additionally run FLUSH CONNECTION POOLS on every aggregator node and restart the SQL session for the new value to take effect.

SingleStore offers commands to set engine variables cluster-wide, on all aggregator nodes, or on leaf nodes. Refer to Sync Variables and Engine Variables for more information.

Warning

The following SingleStore engine variables exist for backwards compatibility with MySQL and are non-operational in SingleStore. These variables are available for read only; they are not available for write.

character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results
character_set_system
character_sets_dir

Reading Character Set and Collation Engine Variables

To retrieve values of the character set or the collation in current use, use the @@ selector with the required variable in a SELECT statement.

SQL

SELECT @@collation_server;

+--------------------+
| @@collation_server |
+--------------------+
| utf8mb4_general_ci |
+--------------------+
1 row in set (0.00 sec)

Collation Example

The following example shows that utf8_general_ci collation is case-insensitive. The output is 1, indicating that A and a match in this collation.

SQL

SET collation_connection = 'utf8_general_ci';
SELECT 'A' = 'a';

+-----------+
|'A' = 'a'  |
+-----------+
| 1         |
+-----------+

The following example shows that utf8_bin collation is case-sensitive. The output is 0, indicating that A and a do not match in this collation.

SQL

SET collation_connection = 'utf8_bin';
SELECT 'A' = 'a';

+-----------+
|'A' = 'a'  |
+-----------+
| 0         |
+-----------+