[5.x]: Exception error when converting character set after upgrade to Craft 5

[danystad]

What happened?

Description

After upgrading a Craft 4 website to Craft 5 and running the php craft db/convert-charset command as described in the Upgrading from Craft 4, I'm running into an Exception 'yii\db\Exception' with message 'SQLSTATE[42000] error.

Steps to reproduce

1. Upgrade site from Craft 4 to Craft 5
2. Run php craft db/convert-charset

Expected behavior

All database tables should be converted to utf8mb4 character set and utf8mb4_0900_ai_ci collation.

Actual behavior

The following error:

ddev php craft db/convert-charset
Which character set should be used? [utf8mb4] 
Which collation should be used? [utf8mb4_0900_ai_ci] 
Converting craft_addresses ... done
Converting craft_announcements ... done
Converting craft_assetindexdata ... done
Converting craft_assetindexingsessions ... done
Converting craft_assets ... done
Converting craft_assets_sites ... done
Converting craft_authenticator ... done
Converting craft_categories ... done
Converting craft_categorygroups ... Exception 'yii\db\Exception' with message 'SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
The SQL being executed was: ALTER TABLE `craft_categorygroups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci'

in /var/www/html/vendor/yiisoft/yii2/db/Schema.php:676

Error Info:
Array
(
    [0] => 42000
    [1] => 1071
    [2] => Specified key was too long; max key length is 767 bytes
)

Caused by: Exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes'

in /var/www/html/vendor/yiisoft/yii2/db/Command.php:1320

Stack trace:
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1320): PDOStatement->execute()
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1120): yii\db\Command->internalExecute()
#2 /var/www/html/vendor/craftcms/cms/src/console/controllers/DbController.php(407): yii\db\Command->execute()
#3 [internal function]: craft\console\controllers\DbController->actionConvertCharset()
#4 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#5 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()
#6 /var/www/html/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction()
#7 /var/www/html/vendor/craftcms/cms/src/console/ControllerTrait.php(88): yii\console\Controller->runAction()
#8 /var/www/html/vendor/craftcms/cms/src/console/Controller.php(216): craft\console\Controller->traitRunAction()
#9 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): craft\console\Controller->runAction()
#10 /var/www/html/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction()
#11 /var/www/html/vendor/craftcms/cms/src/console/Application.php(91): yii\console\Application->runAction()
#12 /var/www/html/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction()
#13 /var/www/html/vendor/craftcms/cms/src/console/Application.php(122): yii\console\Application->handleRequest()
#14 /var/www/html/vendor/yiisoft/yii2/base/Application.php(384): craft\console\Application->handleRequest()
#15 /var/www/html/craft(13): yii\base\Application->run()
#16 {main}
Failed to run php craft db/convert-charset: exit status 1

Craft CMS version

Craft Pro 5.5.2

PHP version

8.3.12

Operating system and version

Linux 6.11.9-orbstack-00279-g4cf512143f2e (DDEV)

Database type and version

MySQL 8.0.36

Image driver and version

Imagick 3.7.0 (ImageMagick 6.9.11-60)

Installed plugins and versions

• CKEditor 4.4.0
• CP Field Inspect 2.0.2
• Empty Coalesce 5.0.0
• Minify 5.0.0
• Mix v1.7.2
• Preparse 3.0.0-alpha.2
• Retour 5.0.3
• SEOmatic 5.1.6
• Sprig 3.5.1
• Template Comments 5.0.3

[brandonkelly]

Run the following SQL query:

SHOW CREATE TABLE craft_categorygroups

What is the Create Table response?

[danystad]

Here is the Create Table response

CREATE TABLE `craft_categorygroups` (
  `id` int NOT NULL AUTO_INCREMENT,
  `structureId` int NOT NULL,
  `fieldLayoutId` int DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL,
  `handle` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL,
  `defaultPlacement` enum('beginning','end') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'end',
  `dateCreated` datetime NOT NULL,
  `dateUpdated` datetime NOT NULL,
  `dateDeleted` datetime DEFAULT NULL,
  `uid` char(36) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `craft_categorygroups_structureId_fk` (`structureId`),
  KEY `craft_categorygroups_fieldLayoutId_fk` (`fieldLayoutId`),
  KEY `craft_idx_zppslhqcpokkmeisgznyvoeeqiplfgcxbocl` (`dateDeleted`),
  KEY `craft_idx_sninfyatqphofeiodkvdfzykrxkoapqshlvu` (`name`),
  KEY `craft_idx_lapqmhdzaxuvmhestsczuvwsfktnyoqupoeg` (`handle`),
  CONSTRAINT `craft_categorygroups_fieldLayoutId_fk` FOREIGN KEY (`fieldLayoutId`) REFERENCES `craft_fieldlayouts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `craft_categorygroups_structureId_fk` FOREIGN KEY (`structureId`) REFERENCES `craft_structures` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci ROW_FORMAT=COMPACT

[brandonkelly]

Huh, we’re a little stumped. Any chance you can send your database over to [email protected] so we can look into it on our end?

[danystad]

I just sent an email to [email protected] with a copy of the database. Thank you.