[5.x]: Cannot install or upgrade DB - encoding issue

[mofman]

What happened?

Cannot get Craft CMS 5 up and running on my local machine despite using the new DB encoding standard.

Using Mariadb from 11.2.2

CREATE TABLE `Test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Dispite doing this, I get the following message when upgrading or creating a fresh DB.

Caused by: Exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb3_unicode_ci' is not valid for CHARACTER SET 'utf8mb4''

Using MariaDB

Craft CMS version

5.0.0 beta 1

PHP version

PHP 8.2.15

Operating system and version

MacOS 14.2 Sanoma

Database type and version

MariaDB 11.2.2

Image driver and version

No response

Installed plugins and versions

[brandonkelly]

Do you have a CRAFT_DB_COLLATION environment variable set to utf8mb3_unicode_ci? If not, are you setting the collation setting from config/db.php instead?

[mofman]

Ah I guess not, I didn't realise I needed to set this manually. I set it to utf8mb4_unicode_ci and it worked no problem.

Thanks for clarifying.

[angrybrad]

It looks like MariaDB 11.2+ added the ability to change the default collation associated with a charset: https://mariadb.com/kb/en/setting-character-sets-and-collations/#changing-default-collation

Maybe that’s what happened here? Your MariaDB install has utf8mb3_unicode_ci as the default collation for utf8mb4. Technically, that wouldn’t make sense, but you’re overriding that behavior by explicitly setting it in Craft.

[brandonkelly]

Actually now I’m really confused.

• Where was utf8mb4_general_ci coming from in the CREATE TABLE SQL in the OP, if the collation wasn’t being set to begin with? (Craft will use utf8mb4_unicode_ci or utf8mb4_0900_ai_ci by default, not utf8mb4_general_ci, per this code.) Are you sure that wasn’t being set somewhere, like from an environment variable?
• Why would MySQL be giving you an error about utf8mb3_unicode_ci if the SQL was explicitly saying to use utf8mb4_general_ci?

[mofman]

Ah just noticed db.php is setting

<?php
/**
 * Database Configuration
 *
 * All of your system's database connection settings go in here. You can see a
 * list of the available settings in vendor/craftcms/cms/src/config/DbConfig.php.
 *
 * @see craft\config\DbConfig
 */

use craft\helpers\App;


return [
    'dsn' => App::env('DB_DSN') ?: null,
    'driver' => App::env('DB_DRIVER'),
    'server' => App::env('DB_SERVER'),
    'port' => App::env('DB_PORT'),
    'database' => App::env('DB_DATABASE'),
    'user' => App::env('DB_USER'),
    'password' => App::env('DB_PASSWORD'),
    'schema' => App::env('DB_SCHEMA'),
    'tablePrefix' => App::env('DB_TABLE_PREFIX'),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
];

Would this cause the issue?

[brandonkelly]

That wouldn’t explain how it was set to utf8mb4_general_ci to begin with, per your initial CREATE TABLE SQL code.

[MisterMike]

I encountered the same issue when trying to upgrade from 4.5 to 5.0.0 (Beta3). Removing the collation from config/db.phpdid the job (was set to 'collation' => 'utf8_unicode_ci').

EDIT: I found I didn't set the .env file with the recommended settings (as per here) prior the upgrade. Maybe this could be simplied?

CRAFT_DB_CHARSET="utf8mb3"
CRAFT_DB_COLLATION="utf8mb3_general_ci"

The error reported by the upgrade was:

Database Exception: SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb3_unicode_ci' is not valid for CHARACTER SET 'utf8mb4'
The SQL being executed was: CREATE TABLE entries_authors (
entryId int(11) NOT NULL,
authorId int(11) NOT NULL,
sortOrder smallint(6) UNSIGNED NOT NULL,
PRIMARY KEY(entryId, authorId)
) ENGINE = InnoDb DEFAULT CHARACTER SET = utf8mb4 DEFAULT COLLATE = utf8_unicode_ci

Migration: craft\migrations\m221101_115859_create_entries_authors_table

Output:

> dropping {{%entries_authors}} if it exists ... done (time: 0.007s)
> create table {{%entries_authors}} ...Exception: SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb3_unicode_ci' is not valid for CHARACTER SET 'utf8mb4'
The SQL being executed was: CREATE TABLE entries_authors (
entryId int(11) NOT NULL,
authorId int(11) NOT NULL,
sortOrder smallint(6) UNSIGNED NOT NULL,
PRIMARY KEY(entryId, authorId)
) ENGINE = InnoDb DEFAULT CHARACTER SET = utf8mb4 DEFAULT COLLATE = utf8_unicode_ci (/var/www/html/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'CREATE TABLE e...')
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('CREATE TABLE e...')
#2 /var/www/html/vendor/yiisoft/yii2/db/Migration.php(322): yii\db\Command->execute()
#3 /var/www/html/vendor/craftcms/cms/src/migrations/m221101_115859_create_entries_authors_table.php(20): yii\db\Migration->createTable('{{%entries_auth...', Array)
#4 /var/www/html/vendor/craftcms/cms/src/db/Migration.php(49): craft\migrations\m221101_115859_create_entries_authors_table->safeUp()
#5 /var/www/html/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#6 /var/www/html/vendor/craftcms/cms/src/db/MigrationManager.php(149): craft\db\MigrationManager->migrateUp(Object(craft\migrations\m221101_115859_create_entries_authors_table))
#7 /var/www/html/vendor/craftcms/cms/src/services/Updates.php(245): craft\db\MigrationManager->up()
#8 /var/www/html/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(499): craft\services\Updates->runMigrations(Array)
#9 /var/www/html/vendor/craftcms/cms/src/controllers/UpdaterController.php(207): craft\controllers\BaseUpdaterController->runMigrations(Array, 'restore-db')
#10 [internal function]: craft\controllers\UpdaterController->actionMigrate()
#11 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#12 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#13 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('migrate', Array)
#14 /var/www/html/vendor/craftcms/cms/src/web/Application.php(317): yii\base\Module->runAction('updater/migrate', Array)
#15 /var/www/html/vendor/craftcms/cms/src/web/Application.php(705): craft\web\Application->runAction('updater/migrate')
#16 /var/www/html/vendor/craftcms/cms/src/web/Application.php(244): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#17 /var/www/html/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#18 /var/www/html/public/index.php(12): yii\base\Application->run()
#19 {main}

[engram-design]

FYI, just ran into this issue on a fresh install, says to refer to logs and there's nothing to be found. I was only able to figure this out by looking in the Network tab of web developer tools.

Not sure what might be going on there! But to be clear, it was an issue of having collation set in my db.php file, but a friendlier (or earlier?) warning would be great.

[brandonkelly]

I’m guessing this is related to #14027 which was resolved in 5.0.0-beta.4.

@engram-design What version are you on?

[engram-design]

@brandonkelly I was on 5.0.0, brand new database, but I was using some files from a Craft 3 install, which explains some of the old settings hanging around in the db.php file. Maybe this isn't an issue for most people. Just thought to mention that a better error message, or actual logs would be great on this one.

[brandonkelly]

At least in @engram-design’s case, this was caused by collation being explicitly set to utf8_unicode_ci. Fixed for the next release.

[brandonkelly]

Craft 5.0.1 is out now with that fix.