Migrating and Troubleshooting Collations

Impact of Changing collation_server

When the default collation is changed for the cluster using any of the following engine variables:

• collation_connection

• collation_database

• collation_server

the other two are automatically set to the same value and character_set_server is set to the corresponding character set. These collation_* variables must be set globally. If any of these variables are set in a session to a value other than the current global setting, SingleStore returns an error.

After changing the collation for the cluster with SET CLUSTER or SET GLOBAL commands, run the FLUSH CONNECTION POOLS command on every aggregator in the cluster to apply the new collation.

Changing the collation does not automatically rewrite the collation of existing tables or columns. It changes the default collation used by new sessions and objects that inherit the current default. Existing metadata remains unchanged until the objects are altered or rebuilt.

Key considerations before changing the collation:

• Check the currently active collation.

  SQL

  Copy

  SELECT @@character_set_server, @@collation_server;
  SHOW VARIABLES LIKE 'collation%';

• List the tables and columns that use an older (or specific) collation.

  SQL

  Copy

  SELECT table_schema,
         table_name,
         column_name,
         character_set_name,
         collation_name
  FROM information_schema.columns
  WHERE table_schema NOT IN ('information_schema')
  ORDER BY table_schema, table_name, ordinal_position;

• Update the client application. For example, when changing the collation from utf8mb4_general_ci to utf8mb4_bin, rewrite the application queries that rely on case-insensitive behavior.

• Ensure that the client sessions and connection pools can be restarted after the change. The new global collation configuration is not reliably applied until all the connection pools are flushed and sessions reconnect.

Collation Mismatch Behavior and Handling

A mismatch happens when a column, session default, or a literal or expression in a predicate condition (for example in the WHERE clause) does not resolve to the same effective data type or collation. Mismatched data types or collations can cause warnings in the query EXPLAIN and prevent SingleStore from using an index match. Such comparisons may involve unsafe conversions, degrade performance, and prevent index matching, which can lead to high CPU usage. For example, comparing an indexed column to a value or expression that resolves to a different collation can force a scan instead of an index lookup, i.e., the query optimizer may not be able to efficiently use the index.

To prevent these issues, including query performance degradation, SingleStore recommends the following:

• Match the collation of the literal or expression with the indexed column's collation instead of applying a different collation to the indexed column within the predicate.

• Use explicit casts or COLLATE clauses instead of relying on implicit conversions.

• Align the column collation with the expected application behavior.

CREATE TABLE grades (
  student_id INT,
  lastname VARCHAR(25) COLLATE utf8mb4_general_ci
);

INSERT INTO grades VALUES
  (1, 'williams'),
  (2, 'Williams'),
  (3, 'Browning');

SELECT lastname :> text COLLATE utf8mb4_bin AS lastname_cs
FROM grades
GROUP BY 1
ORDER BY 1;

+-------------+
| lastname_cs |
+-------------+
| Browning    |
| Williams    |
| williams    |
+-------------+

CREATE TABLE grades1 (
  student_id INT,
  lastname VARCHAR(25) COLLATE utf8mb4_general_ci,
  KEY idx_lastname (lastname) USING HASH
);

INSERT INTO grades1 VALUES
  (1, 'williams'),
  (2, 'Williams'),
  (3, 'Browning');

SELECT * FROM grades1
WHERE lastname = 'williams' COLLATE utf8mb4_general_ci;

+------------+----------+
| student_id | lastname |
+------------+----------+
|          2 | Williams |
|          1 | williams |
+------------+----------+

EXPLAIN SELECT * FROM grades1
WHERE lastname = 'williams' COLLATE utf8mb4_general_ci;

+---------------------------------------------------------------------------------------+
| EXPLAIN                                                                               |
+---------------------------------------------------------------------------------------+
| Gather partitions:all alias:remote_0 parallelism_level:segment                        |
| Project [grades1.student_id, grades1.lastname]                                        |
| ColumnStoreFilter [grades1.lastname = 'williams' COLLATE `utf8mb4_general_ci`  index] |
| ColumnStoreScan demo.grades1, SORT KEY __UNORDERED () table_type:sharded_columnstore  |
+---------------------------------------------------------------------------------------+

EXPLAIN SELECT * FROM grades1
WHERE lastname = 'williams';

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| WARNING: Comparisons between mismatched datatypes which may involve unsafe datatype conversions and/or degrade performance. Consider changing the datatypes, or adding explicit typecasts. See https://docs.memsql.com/docs/mismatched-datatypes for more information.                            |
|                                                                                                                                                                                                                                                                                                   |
| WARNING: Comparison between mismatched datatypes: (`grades1`.`lastname` = 'williams'). Types 'varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL' vs 'longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL'. May degrade performance because an index match is not possible |
|                                                                                                                                                                                                                                                                                                   |
| Gather partitions:all alias:remote_0 parallelism_level:segment                                                                                                                                                                                                                                    |
| Project [grades1.student_id, grades1.lastname]                                                                                                                                                                                                                                                    |
| ColumnStoreFilter [grades1.lastname = 'williams']                                                                                                                                                                                                                                                 |
| ColumnStoreScan demo.grades1, SORT KEY __UNORDERED () table_type:sharded_columnstore                                                                                                                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SELECT * FROM sets
WHERE sets.json_field::$x :> text COLLATE utf8mb4_bin = 'string1'
AND sets.json_field::$y :> text COLLATE utf8mb4_general_ci = 'string2';

Migrate from utf8mb4_general_ci to utf8mb4_bin Collation

SingleStore recommends performing the following actions, in order, to change the collation:

1. Inspect the current state: Check the current default collation for the cluster and identify the affected objects.

   SQL

   Copy

   SELECT @@character_set_server, @@collation_server;
   
   SELECT table_schema,
          table_name,
          column_name,
          character_set_name,
          collation_name
   FROM information_schema.columns
   WHERE collation_name = 'utf8mb4_general_ci' AND table_schema NOT IN ('information_schema');

2. Change the collation: Change the default collation for the cluster.

   SQL

   Copy

   SET CLUSTER collation_server = 'utf8mb4_bin';

3. Reset the connections: Run the FLUSH CONNECTION POOLS command on each aggregator in the cluster to close all the open connections and the idle pooled connections.

   SQL

   Copy

   FLUSH CONNECTION POOLS;

4. Preserve existing column data and then migrate columns: Before changing the collation of an existing column, preserve its data until the migration has been validated. Especially for columnstore tables, do not rename or drop the original column until copied values are successfully validated.

   • Rowstore Tables

     For rowstore tables, modify an existing string column directly.

     SQL

     Copy

     ALTER TABLE t1
     MODIFY COLUMN col1 VARCHAR(128) COLLATE utf8mb4_bin;

     If the column has a secondary index, drop and recreate the index after changing the column collation. For example:

     SQL

     Copy

     ALTER TABLE t1 DROP INDEX idx_c1;
     ALTER TABLE t1
     MODIFY COLUMN col1 VARCHAR(128) COLLATE utf8mb4_bin;
     ALTER TABLE t1 ADD INDEX idx_c1 (c1);

   • Columnstore Tables

     For columnstore tables, existing string columns cannot be modified in place. Use an add-copy-swap-drop workflow, for example:

     SQL

     Copy

     ALTER TABLE towns ADD COLUMN town_name_2 VARCHAR(64) COLLATE utf8mb4_bin NOT NULL;
     UPDATE towns SET town_name_2 = town_name;

     After copying the data, validate the replacement column before continuing. Do not drop or rename the original column until validation succeeds. For example:

     SQL

     Copy

     -- Check for mismatched rows
     SELECT COUNT(*) AS mismatched_rows
     FROM towns
     WHERE town_name != town_name_2;
     
     -- Review mismatched rows
     SELECT id, town_name, town_name_2
     FROM towns
     WHERE town_name != town_name_2;

     After successful validation, swap the columns and drop the original column. For example:

     SQL

     Copy

     ALTER TABLE towns CHANGE town_name town_name_to_delete;
     ALTER TABLE towns CHANGE town_name_2 town_name;
     ALTER TABLE towns DROP COLUMN town_name_to_delete;

     Re-create any indexes on the original column after swapping the columns.

     Refer to ALTER TABLE to Modify Column Data Types, Sizes, and Collation for an example.

5. Test existing queries and applications: Re-run the existing queries with EXPLAIN and check plan warnings for mismatched data types or collations. Test existing workloads and applications and verify that everything is working as expected.