<p>Your database server is telling you that it doesn't know the <code>utf8mb4_0900_ai_ci</code> collation. You need to configure a collation that your database server actually supports.</p>
<p>You can set the collation either in your <code>config/db.php</code> or as an environment variable <code>CRAFT_DB_COLLATION</code>, usually through your <code>.env</code> file.</p>
<p>If you leave this setting out, Craft will use an appropriate default. To solve your issue, you can simply remove the explicit collation that you're setting in your config and let Craft figure it out. Or set the correct collation depending on your database:</p>
<h2>MySQL</h2>
<ul>
<li>For MySQL &lt;8.0, use <code>utf8mb4_general_ci</code>.</li>
<li>Upgrading to MySQL 8 and sticking with <code>utf8mb4_0900_ai_ci</code> is the better solution.</li>
</ul>
<h2>MariaDB</h2>
<p>Use <code>uca1400_ai_ci</code> if you use a recent version of MariaDB, or <code>utf8mb4_unicode_520_ci</code> for older versions.</p>

CC BY-SA 4.0
2024-12-23T04:28:38.113Z
Craft says it's using MariaDB 10.5.22. Should I still make that change? @moritzlost
4midori
CC BY-SA 4.0
2024-12-25T19:23:59.797Z
@4midory Ah, in this case you need a different collation, MariaDB supports different collations than MySQL. Keep in mind that MariaDB isn't a drop-in replacement for MySQL anymore, so you need to configure your system for the database server you're intending to use. I've updated my answer with recommendations for both MySQL and MariaDB.
MoritzLost
CC BY-SA 4.0
2024-12-31T03:03:09.737Z
@moriztlost I tried all three options in my .env (no collation, uca1400_ai_ci and utf8mb4_unicode_520_ci) but Craft failed on each one. Do I need to convert to MySQL?
4midori
CC BY-SA 4.0
2024-12-22T17:59:12.593Z
2024-12-25T19:22:39.313Z
2024-12-25T19:22:39.313Z
MoritzLost
<p>Updating  craft 5.2.8 =&gt; 5.5.7</p>
<pre><code>php craft update craft
</code></pre>
<p>yields</p>
<blockquote>
<p>create table {{%sso_identities}} ...Exception: SQLSTATE[HY000]:
General error: 1273 Unknown collation: 'utf8mb4_0900_ai_ci' The SQL
being executed was: CREATE TABLE <code>sso_identities</code> (
<code>provider</code> varchar(255) NOT NULL,
<code>identityId</code> varchar(255) NOT NULL,
<code>userId</code> int(11) NOT NULL,
<code>dateCreated</code> datetime(0) NOT NULL,
<code>dateUpdated</code> datetime(0) NOT NULL,
PRIMARY KEY(<code>provider</code>, <code>identityId</code>, <code>userId</code>) ) ENGINE = InnoDb DEFAULT CHARACTER SET = utf8mb4 DEFAULT COLLATE =
utf8mb4_0900_ai_ci
(/home/domains/versa/craft/vendor/yiisoft/yii2/db/Schema.php:676)</p>
</blockquote>
<p>Ideas how to resolve? Do I need to change collation of the table in question? It so, how would I do that?</p>

<database><updates><craft5>
'Unknown Collation' error on upgrade
# 'Unknown Collation' error on upgrade

(Asked by: username_0 on 2024-12-19)

Updating  craft 5.2.8 => 5.5.7
php craft update craft

yields

create table {{%sso_identities}} ...Exception: SQLSTATE[HY000]:
General error: 1273 Unknown collation: 'utf8mb4_0900_ai_ci' The SQL
being executed was: CREATE TABLE sso_identities (
provider varchar(255) NOT NULL,
identityId varchar(255) NOT NULL,
userId int(11) NOT NULL,
dateCreated datetime(0) NOT NULL,
dateUpdated datetime(0) NOT NULL,
PRIMARY KEY(provider, identityId, userId) ) ENGINE = InnoDb DEFAULT CHARACTER SET = utf8mb4 DEFAULT COLLATE =
utf8mb4_0900_ai_ci
(/home/domains/versa/craft/vendor/yiisoft/yii2/db/Schema.php:676)

Ideas how to resolve? Do I need to change collation of the table in question? It so, how would I do that?

**Tags:** database, updates, craft5


---

## 1 answers

### Answer by username_1 on 2024-12-22. Score: 1 | Accepted answer

Your database server is telling you that it doesn't know the `utf8mb4_0900_ai_ci` collation. You need to configure a collation that your database server actually supports.

You can set the collation either in your `config/db.php` or as an environment variable `CRAFT_DB_COLLATION`, usually through your `.env` file.

If you leave this setting out, Craft will use an appropriate default. To solve your issue, you can simply remove the explicit collation that you're setting in your config and let Craft figure it out. Or set the correct collation depending on your database:

## MySQL

* For MySQL <8.0, use `utf8mb4_general_ci`.
* Upgrading to MySQL 8 and sticking with `utf8mb4_0900_ai_ci` is the better solution.

## MariaDB

Use `uca1400_ai_ci` if you use a recent version of MariaDB, or `utf8mb4_unicode_520_ci` for older versions.

> **Comments:**
> **username_0**: Craft says it's using MariaDB 10.5.22. Should I still make that change? @moritzlost  
> **username_1**: @4midory Ah, in this case you need a different collation, MariaDB supports different collations than MySQL. Keep in mind that MariaDB isn't a drop-in replacement for MySQL anymore, so you need to configure your system for the database server you're intending to use. I've updated my answer with recommendations for both MySQL and MariaDB.  
> **username_0**: @moriztlost I tried all three options in my .env (no collation, uca1400_ai_ci and utf8mb4_unicode_520_ci) but Craft failed on each one. Do I need to convert to MySQL?  

---

