[5.x] Failed install with MariaDB Collation `utf8mb4_unicode_ci`

DavidLaridon commented 1 month ago

Describe the bug

When installing, the Character set utf8 is seen as utf8mb3 instead of utf8mb4. This causes the following error. I checked the DB and everything is set to utf8mb4 so not sure why it would fallback to utf8mb3. I did do an upgrade from Craft 4 to Craft 5 though.

An error occurred while executing the "verbb\hyper\migrations\Install migration: SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8mb3'
The SQL being executed was: CREATE TABLE `hyper_element_cache` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`fieldId` int(11),
`sourceId` int(11),
`sourceSiteId` int(11),
`sourceType` varchar(255),
`targetId` int(11),
`targetSiteId` int(11),
`targetType` varchar(255),
`title` varchar(255),
`uri` varchar(255),
`dateCreated` datetime(0) NOT NULL,
`dateUpdated` datetime(0) NOT NULL,
`uid` char(36) NOT NULL DEFAULT '0'
) ENGINE = InnoDb DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8mb4_unicode_ci

Steps to reproduce

Upgrade Craft v4 to v5
Install Hyper plugin

Craft CMS version

5.1.2

Plugin version

2.0.0-beta.8

Multi-site?

Yes

Additional context

Not sure if it is relevant, but i do use MariaDb as database for local dev.

engram-design commented 1 month ago

We don't mess around with charsets, and it's my understanding that Craft's migrations should convert all tables upon upgrade (if the database engine supports it).

Is this a fresh install of Hyper? As in, Hyper wasn't installed on Craft 4? If so, that's even stranger that it's not respecting your charset settings which are typically defined in your .env file or your config/db.php file.

Can you confirm the encoding and collation of your tables? Mine are set to DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci which I believe might be the default.

DavidLaridon commented 1 month ago

Found the issue. I had this environment variable, like it said in the upgrade docs: CRAFT_DB_CHARACTER_SET=utf8mb4

But apparently the correct variable was CRAFT_DB_CHARSET. Setting that to utf8mb4 fixed it. Strange though, because all my tables were still encoded as utf8mb4.

Thanks for your help!

verbb / hyper