[go: up one dir, main page]
Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Closed
danystad opened this issue Nov 19, 2024 · 5 comments
Closed

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

danystad opened this issue Nov 19, 2024 · 5 comments
Labels
bug

Comments

@danystad
Copy link

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
@danystad danystad added the bug label Nov 19, 2024
@brandonkelly
Copy link
Member

Run the following SQL query:

SHOW CREATE TABLE craft_categorygroups

What is the Create Table response?

@danystad
Copy link
Author

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
Copy link
Member

Huh, we’re a little stumped. Any chance you can send your database over to support@craftcms.com so we can look into it on our end?

@danystad
Copy link
Author

I just sent an email to support@craftcms.com with a copy of the database. Thank you.

@angrybrad
Copy link
Member

For posterity, this was because there were four tables (category_groups being the first) that had ROW_FORMAT=COMPACT defined on the table.

Removing that and going with the default row format solved it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@brandonkelly @angrybrad @danystad