# Convert a Specify database to UTF-8

**URL:** <https://speciforum.org/t/convert-a-specify-database-to-utf-8/3467>\
**Category:** Configuration & Installation\
**Tags:** Specify-7\
**Created:** [March 24, 2026, 8:53pm UTC](https://speciforum.org/t/convert-a-specify-database-to-utf-8/3467 "2026-03-24T20:53:29Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![jason\_m](https://avatars.discourse-cdn.com/v4/letter/j/8e8cbc/32.png) [@jason\_m](https://speciforum.org/u/jason_m)\
**Post date:** [March 24, 2026, 8:53pm UTC](https://speciforum.org/t/convert-a-specify-database-to-utf-8/3467/1 "2026-03-24T20:53:29Z")

</div>

> 💡 While this guide walks through and explains the processes involved with inspecting and changing character encoding within a MariaDB database, a basic familiarity with database management and character encoding is assumed and recommended

# Introduction

Many Specify databases were created some time ago, using relatively old versions of MySQL or MariaDB.  
These older versions of MySQL and MariaDB commonly defaulted to character sets and collations that have limited support for some characters with diacritics, non-western languages, and general symbols.

This can result in databases that do not support characters that commonly appear in names for people and places, different characters in non-western languages, etc.

# What is a Character Set and Collation?

- **Character Set** : Similar to character encoding, the character set defines which characters/symbols are supported and maps each supported character to its representation in bytes.
- **Collation** : Each character set can have one or more collations, which define how characters/symbols are compared and sorted. i.e., the collation defines the rules that state whether `a = A`, `a < b`, etc.

Modern applications and their users, including Specify databases, should strongly prefer a character set like `utf8mb4`, which fully supports the Unicode standard.

Review the MariaDB docs for additional information:

> **[Character Set and Collation Overview | Server | MariaDB Documentation](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets/character-set-and-collation-overview)**
>
> Learn the core concepts of character sets and collations in MariaDB, including how they define string storage and sorting rules.

# Viewing Current Character Set and Collation

## Connection/Client Level

The connection character set and collation determine how data is interpreted between the client and server.  
Specifically, MariaDB will assume text coming from the client is encoded with the Connection/Client level character set and convert characters to their correct character set as determined by the database, table, column, etc. when needed.

> 💡 Specify 7 defaults to the utf8mb3 character set for its connections, which is the default used by 4.2 Django.

Below is a query that can be used to view the current character set and collation at the connection/client level:

```sql
SELECT VARIABLE_NAME, SESSION_VALUE 
    FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
    VARIABLE_NAME LIKE 'character_set_con%' OR 
    VARIABLE_NAME LIKE 'character_set_cl%' OR 
    VARIABLE_NAME LIKE 'character_set_re%' OR 
    VARIABLE_NAME LIKE 'collation_c%';

```

Example results:

```auto
+--------------------------+-----------------------+
| VARIABLE_NAME | SESSION_VALUE |
+--------------------------+-----------------------+
| CHARACTER_SET_CONNECTION | utf8mb4 |
| CHARACTER_SET_CLIENT | utf8mb4 |
| COLLATION_CONNECTION | utf8mb4_uca1400_ai_ci |
| CHARACTER_SET_RESULTS | utf8mb4 |
+--------------------------+-----------------------+

```

See the MariaDB docs on:

> **[SET NAMES | Server | MariaDB Documentation](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-names)**
>
> Configure the character set and collation for the current connection. This ensures the server correctly interprets data sent by the client application.

## System Level

By default, MariaDB will use the system character set and collation.  
MariaDB versions less than 11.6 will commonly default to the `latin1` character set and `latin1_swedish_ci` collation.  
MariaDB versions \>= 11.6 will commonly default to the `utf8mb4` character set and `utf8mb4_uca1400_ai_ci` collation.

Some distributions of MariaDB will use other default character sets and collations. See the MariaDB documentation:

> **[utf8mb4 - Setting Character Sets and Collations | Server | MariaDB Documentation](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets/setting-character-sets-and-collations#current)**
>
> The default character set is utf8mb4 and the default collation is utf8mb4\_uca1400\_ai\_ci. This may differ in some distros, see for example Differences in MariaDB in Debian. | Complete Setting Character Sets and Collations data type guide for MariaDB....

The following query can be used to retrieve the system level character set

```sql
SELECT VARIABLE_NAME,
    VARIABLE_VALUE
FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
WHERE VARIABLE_NAME LIKE 'character_set_sys%';

```

Example result:

```auto
+----------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------+----------------+
| CHARACTER_SET_SYSTEM | utf8mb3 |
+----------------------+----------------+

```

## Server Level

The character set and collation can be defined at the server level.  
The server-level character set and collation defines the default character set and collation used when creating new databases.  
If the server-level character set and collation is unset, MariaDB will fallback to the system character set and its default collation.

Below is a query that can be used to view the server level character set and collation:

```sql
SELECT VARIABLE_NAME, SESSION_VALUE 
    FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
    VARIABLE_NAME LIKE 'character_set_ser%' OR 
    VARIABLE_NAME LIKE 'collation_s%';

```

Example results:

```auto
+----------------------+--------------------+
| VARIABLE_NAME | SESSION_VALUE |
+----------------------+--------------------+
| CHARACTER_SET_SERVER | utf8mb4 |
| COLLATION_SERVER | utf8mb4_general_ci |
+----------------------+--------------------+

```

## Database Level

Each database can define its own default character set and collation. If not specified within the [CREATE DATABASE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-database) statement or later changed with an [ALTER DATABASE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-database) statement, then the database will assume the character set and collation defined at the server level.

The database character set and collation defines the default character set and collation for tables created within the database.

The following query can be used to retrieve the character set and collation for each database in the database manager.

```sql
SELECT SCHEMA_NAME,
    DEFAULT_CHARACTER_SET_NAME,
    DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME NOT IN (
        'sys',
        'information_schema',
        'performance_schema',
        'mysql',
        'innodb',
        'tmp'
    );

```

Example results:

```auto
+--------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------+----------------------------+------------------------+
| db3 | utf8mb4 | utf8mb4_general_ci |
| db2 | utf8mb4 | utf8mb4_uca1400_ai_ci |
| db1 | latin1 | latin1_swedish_ci |
| specify | utf8mb4 | utf8mb4_general_ci |
+--------------+----------------------------+------------------------+

```

Reminder that even if a database does not explicitly define a character set or collation, it will use the defaults defined at the server level.

## Table Level

Each table within a database can define its own collation (and character set implicitly). Each text-based column defined on a table will use the collation of the table (unless the collation is overridden at a lower level).  
If a table does not explicitly define its own character set and collation, it will assume the collation of the database.

The following query can be used to retrieve the collation (and its character set) of each database in MariaDB:

```sql
SELECT tbls.TABLE_SCHEMA,
    tbls.TABLE_NAME,
    cols.CHARACTER_SET_NAME,
    tbls.TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES tbls
    JOIN INFORMATION_SCHEMA.COLLATIONS cols ON cols.COLLATION_NAME = tbls.TABLE_COLLATION
WHERE TABLE_SCHEMA NOT IN (
        'sys',
        'information_schema',
        'performance_schema',
        'mysql',
        'innodb',
        'tmp'
    )
ORDER BY tbls.TABLE_SCHEMA,
    tbls.TABLE_NAME;

```

Example results (truncated for brevity):

```auto
+--------------+----------------------------------+--------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | CHARACTER_SET_NAME | TABLE_COLLATION |
+--------------+----------------------------------+--------------------+--------------------+
| specify | absoluteage | utf8mb4 | utf8mb4_general_ci |
| specify | absoluteageattachment | utf8mb4 | utf8mb4_general_ci |
| specify | absoluteagecitation | utf8mb4 | utf8mb4_general_ci |
| specify | accession | utf8mb3 | utf8mb3_general_ci |
| specify | attachmentdataset | utf8mb3 | utf8mb3_unicode_ci |
| specify | attachmentimageattribute | utf8mb3 | utf8mb3_general_ci |
| specify | attachmentmetadata | utf8mb3 | utf8mb3_general_ci |
+--------------+----------------------------------+--------------------+--------------------+

```

## Column Level

Each text column (`CHAR`, `VARCHAR`, `TEXT`, etc.) can define its own character set and collation.  
If the character set and collation is not explicitly defined on a column, it will assume the character set and collation defined at the table level.

The following query can be used to return all tables in database manager:

```sql
SELECT tbs.TABLE_SCHEMA,
    tbs.TABLE_NAME,
    tbs.TABLE_COLLATION,
    cols.COLUMN_NAME,
    cols.COLUMN_TYPE,
    cols.CHARACTER_SET_NAME,
    cols.COLLATION_NAME
FROM information_schema.tables tbs
    JOIN information_schema.columns cols ON tbs.TABLE_NAME = cols.TABLE_NAME
    AND cols.TABLE_SCHEMA = tbs.TABLE_SCHEMA
WHERE tbs.TABLE_SCHEMA NOT IN (
        'sys',
        'information_schema',
        'performance_schema',
        'mysql',
        'innodb',
        'tmp'
    )
    AND cols.CHARACTER_SET_NAME IS NOT NULL
ORDER BY tbs.TABLE_SCHEMA,
    tbs.TABLE_NAME,
    cols.COLUMN_NAME;

```

Example results (truncated for brevity):

```auto
+--------------+----------------------------------+-----------------------+--------------------------------------+---------------+--------------------+-----------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | COLUMN_NAME | COLUMN_TYPE | CHARACTER_SET_NAME | COLLATION_NAME |
+--------------+----------------------------------+-----------------------+--------------------------------------+---------------+--------------------+-----------------------+
| specify | collectingtripattribute | utf8mb4_uca1400_ai_ci | Text5 | varchar(100) | utf8mb4 | utf8mb4_uca1400_ai_ci |
| specify | collectingtripattribute | utf8mb4_uca1400_ai_ci | Text6 | varchar(50) | utf8mb4 | utf8mb4_uca1400_ai_ci |
| specify | collectingtripattribute | utf8mb4_uca1400_ai_ci | Text7 | varchar(50) | utf8mb4 | utf8mb4_uca1400_ai_ci |
| specify | collectingtripattribute | utf8mb4_uca1400_ai_ci | Text8 | varchar(50) | utf8mb4 | utf8mb4_uca1400_ai_ci |
| specify | collectingtripattribute | utf8mb4_uca1400_ai_ci | Text9 | varchar(50) | utf8mb4 | utf8mb4_uca1400_ai_ci |
| specify | collectingtripauthorization | utf8mb4_uca1400_ai_ci | Remarks | mediumtext | utf8mb4 | utf8mb4_uca1400_ai_ci |
| specify | collection | utf8mb4_uca1400_ai_ci | CatalogFormatNumName | varchar(64) | utf8mb4 | utf8mb4_uca1400_ai_ci |
| specify | collection | utf8mb4_uca1400_ai_ci | Code | varchar(50) | utf8mb4 | utf8mb4_uca1400_ai_ci |
| specify | collection | utf8mb4_uca1400_ai_ci | CollectionName | varchar(50) | utf8mb4 | utf8mb4_uca1400_ai_ci |
| specify | collection | utf8mb4_uca1400_ai_ci | CollectionType | varchar(32) | utf8mb4 | utf8mb4_uca1400_ai_ci |
| specify | collection | utf8mb4_uca1400_ai_ci | DbContentVersion | varchar(32) | utf8mb4 | utf8mb4_uca1400_ai_ci |
| specify | collection | utf8mb4_uca1400_ai_ci | Description | mediumtext | utf8mb4 | utf8mb4_uca1400_ai_ci |
| specify | collection | utf8mb4_uca1400_ai_ci | DevelopmentStatus | varchar(32) | utf8mb4 | utf8mb4_uca1400_ai_ci |
+--------------+----------------------------------+-----------------------+--------------------------------------+---------------+--------------------+-----------------------+

```

# Changing Character Set and Collation

> ⚠ **Important** : Always take a full backup before making these changes.  
> These operations can be destructive if misapplied or interrupted.

Table level:

## Connection Level

The SET NAMES command can be used to alter the `character-set_client`, `character_set_connection`, `character_set_results`, and `collation_connection` system variables to tell MariaDB that textual data sent to the server will be with a specific character set/collation.

Below are two example queries which set the connection character set and collation:

```sql
SET NAMES utf8mb4;

```

```sql
SET NAMES utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;

```

Once the connection level character set and collation is set, MariaDB will assume strings sent to the server are encoded with the defined character set.  
When necessary, MariaDB will handle converting characters sent with the connection character set to the effective character set of the column.

See the MariaDB docs for more information:

> **[SET NAMES | Server | MariaDB Documentation](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-names)**
>
> Configure the character set and collation for the current connection. This ensures the server correctly interprets data sent by the client application.

## Server Level

The server level character set and collation can be configured via the `character_set_server` and `collation_server`, options respectively.

For example:

```sql
-- Set the character set and collation for the current connection
SET character_set_server = 'utf8mb4';
SET collation_server = 'utf8mb4_uca1400_ai_ci';

```

```sql
-- Set the character set and collation globally, meaning all new connections
-- will use these values
SET GLOBAL character_set_server = 'utf8mb4';
SET GLOBAL collation_server = 'utf8mb4_uca1400_ai_ci';

```

See the MariaDB documentation on setting character sets at the server level:

> **[hashtagServer Level - Setting Character Sets and Collations | Server | MariaDB...](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets/setting-character-sets-and-collations#server-level)**
>
> The default character set is utf8mb4 and the default collation is utf8mb4\_uca1400\_ai\_ci. This may differ in some distros, see for example Differences in MariaDB in Debian. | Complete Setting Character Sets and Collations data type guide for MariaDB....

The settings can be persisted by saving them to a MariaDB/MySQL configuration file that is read on service start/restart.

Consider the following sample of a configuration file that sets the server character set and collation:

```ini
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_uca1400_ai_ci

```

For more information about option files, read the following MariaDB documentation:

> **[Configuring MariaDB with Option Files | Server | MariaDB Documentation](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/configuring-mariadb/configuring-mariadb-with-option-files)**
>
> Complete guide to MariaDB option files. Complete my.cnf reference with configuration groups, parameter syntax, and file hierarchy for production use.

## Database Level

> ⚠ As the Database character set and collation is used when creating new tables, changing the character set and collation at the database level does not change the character set and collation for existing tables and columns.  
> Existing tables and columns need to be migrated and converted separately for a complete migration

The character set and collation can be configured for an individual database via an [ALTER DATABASE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-database) statement.

Below are all examples of setting the default character set and collation on a `specify` database:

```sql
ALTER DATABASE `specify` CHARACTER SET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- If the collation is omitted, the default collation for the character set is
-- inferred
-- defaults to utf8mb4_uca1400_ai_ci, as that is the default collation for utf8mb4
ALTER DATABASE `specify` CHARACTER SET=utf8mb4;
-- If the character set is omitted but collation provided, MariaDB sets the
-- character set to the character set of the collation
-- Sets the character set to latin1, as that is the character set of the
-- latin1_swedish_ci collation
ALTER DATABASE `specify` COLLATE=latin1_swedish_ci;

```

For more information, view the MariaDB documentation on setting the character set and collation at the database level:

> **[hashtagDatabase Level - Setting Character Sets and Collations | Server | MariaDB...](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets/setting-character-sets-and-collations#database-level)**
>
> The default character set is utf8mb4 and the default collation is utf8mb4\_uca1400\_ai\_ci. This may differ in some distros, see for example Differences in MariaDB in Debian. | Complete Setting Character Sets and Collations data type guide for MariaDB....

Additionally, review the ALTER DATABASE documentation:

> **[ALTER DATABASE | Server | MariaDB Documentation](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-database)**
>
> Modify database characteristics. Learn how to change global properties like the default character set and collation for a specific database.

If there are multiple databases that need converting on the same instance of MariaDB, the following query can be used and modified to generate the SQL queries for each:

```sql
SELECT CONCAT(
        'ALTER DATABASE `',
        SCHEMA_NAME,
        '` CHARACTER SET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;'
    ) AS 'Alter Database Statements'
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME NOT IN (
        'sys',
        'information_schema',
        'performance_schema',
        'mysql',
        'innodb',
        'tmp'
    );

```

Example results:

```auto
+------------------------------------------------------------------------------------+
| Alter Database Statements |
+------------------------------------------------------------------------------------+
| ALTER DATABASE `db1` CHARACTER SET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; |
| ALTER DATABASE `db2` CHARACTER SET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; |
| ALTER DATABASE `specify` CHARACTER SET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; |
+------------------------------------------------------------------------------------+

```

## Table Level

The character set and collation for an existing table can be changed with an [ALTER TABLE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table) statement.  
The ALTER TABLE statement will convert all columns in the table that do not have an explicit character set and collation defined on the column level.

The following SQL statement converts an `agent` table in the `specify` database to the `utf8mb4` character set and `utf8mb4_uca1400_ai_ci` collation:

```sql
ALTER TABLE `specify`.`agent` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;

```

The below SQL statement can be used and modified as desired to generate SQL statement to convert all tables in multiple databases:

```sql
SELECT CONCAT(
        'ALTER TABLE `',
        TABLE_SCHEMA,
        '`.`',
        TABLE_NAME,
        '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;'
    ) AS 'Alter Table Statements'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN (
        'sys',
        'information_schema',
        'performance_schema',
        'mysql',
        'innodb',
        'tmp'
    )
ORDER BY TABLE_SCHEMA,
    TABLE_NAME;

```

Example results (truncated for brevity):

```auto
+-------------------------------------------------------------------------------------------------------------------------------+
| Alter Table Statements |
+-------------------------------------------------------------------------------------------------------------------------------+
| ALTER TABLE `specify`.`agentattachment` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci; |
| ALTER TABLE `specify`.`agentgeography` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci; |
| ALTER TABLE `specify`.`agentidentifier` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci; |
| ALTER TABLE `specify`.`agentspecialty` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci; |
| ALTER TABLE `specify`.`agentvariant` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci; |
| ALTER TABLE `specify`.`appraisal` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci; |
| ALTER TABLE `specify`.`attachment` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci; |
+-------------------------------------------------------------------------------------------------------------------------------+

```

## Column Level

> ⚠ Nearly all columns in Specify utilize the table’s character set and collation, so altering the character set and collation at the table level is sufficient.  
> Some columns which explicitly diverge from the table’s default are expected to maintain the general sorting behavior of their current collation.  
> For example the `spdataset`.`visualorder` column by default has the `utf8mb4_bin` collation, which differs significantly from the `utf8mb4_uca1400_ai_ci` and other `utf8mb4` collations  
> Be mindful to maintain or consider the collation’s sorting behavior when modifying the character set and collation at the column level.

The [ALTER TABLE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table) statement can be used to explicitly define a

The following SQL query modifies the `agent` table in the `specify` database to use the `utf8mb4` character set and `utf8mb4_uca1400_ai_ci` collation:

```sql
ALTER TABLE `specify`.`agent` MODIFY `LastName` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;

```

For more information, view the MariaDB documentation on setting character set and collation at the column level:

> **[hashtagColumn Level - Setting Character Sets and Collations | Server | MariaDB...](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets/setting-character-sets-and-collations#column-level)**
>
> The default character set is utf8mb4 and the default collation is utf8mb4\_uca1400\_ai\_ci. This may differ in some distros, see for example Differences in MariaDB in Debian. | Complete Setting Character Sets and Collations data type guide for MariaDB....