Character sets and collations #

This page is for database administrators who create the database of an Exponential site or check an existing one for encoding problems. Exponential stores and serves text as UTF-8; each engine names that differently and compares strings differently. This page answers: what to create on each engine, where the connection can differ from the tables, and how case affects lookups. Source: Database support, character sets and collations.

Contents #

1. What to create, per engine
2. The MySQL and MariaDB connection
3. Case and comparison
4. Checking a running site
5. Converting an old database
6. Checklist

What to create, per engine #

The Oracle character set is chosen when the database is created; it is a property of the whole database, so a schema for Exponential belongs in an AL32UTF8 database.

The MySQL and MariaDB connection #

site.ini [DatabaseSettings] Charset=utf-8 (the default) is sent to the server as utf8, which both servers treat as the three-byte utf8mb3. Characters outside the Basic Multilingual Plane, such as emoji, cannot pass through the connection even when the tables are utf8mb4. Keep the database in utf8mb4 anyway.

If the connection character set cannot be set, the driver keeps running and logs "could not set the connection charset"; text then comes back with ? in place of the missing characters. The warning is logged only when warnings are (site.ini [DebugSettings] AlwaysLog[]=warning). Details: MySQL and MariaDB: utf8mb4, the connection charset and InnoDB.

Case and comparison #

The consequence: a login or identifier lookup written against MySQL's case-insensitive collations may find nothing on PostgreSQL, Oracle or MongoDB. Exponential's own code handles this; extensions and your own report queries must compare lower(...) on both sides where case must not matter (Reviewing extension SQL for every engine).

User logins are a special case: exp:userlogincheck lists logins that more than one user account has (for example the same login in two cases), and --fix-index adds the unique index on ezuser.login once there are none (php bin/php/console list describes it).

Checking a running site #

• Setup > System information, Database card: the character set of the connection; the page warns when it is not UTF-8 (System information).

• MySQL and MariaDB:

  SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
    FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'exponential';
  SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES
   WHERE TABLE_SCHEMA = 'exponential' AND TABLE_COLLATION NOT LIKE 'utf8%';

• PostgreSQL: psql -l shows the encoding of each database.

• Oracle: SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

Converting an old database #

A database from an old release in a single-byte character set is converted with bin/php/ezconvertdbcharset.php (MySQL, PostgreSQL and Oracle only). Pass the collation explicitly; its default is utf8_general_ci. Run it on a copy after a backup (MySQL and MariaDB: utf8mb4).

Checklist #

• To do: The database was created in the engine's UTF-8 character set.
• To do: MySQL and MariaDB: every table is utf8mb4; no charset warning in var/log/warning.log.
• To do: Oracle: NLS_CHARACTERSET is AL32UTF8.
• To do: Extensions and reports compare case-insensitively where they must.

Related pages #

• Setting up MySQL and MariaDB, Setting up PostgreSQL, Setting up Oracle
• 9.8 Cross-engine notes
• Languages
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.