MySQL Specified key was too long

[happydude]

I think the change made for https://github.com/simplesamlphp/simplesamlphp-module-webauthn/issues/64 broke creating the credentials table on mysql.

The sql that tries to run is

CREATE TABLE IF NOT EXISTS credentials (
    creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_id VARCHAR(80) NOT NULL,
    credentialId VARCHAR(1024) NOT NULL,
    credential MEDIUMBLOB NOT NULL,
    algo INT DEFAULT NULL,
    presenceLevel INT DEFAULT NULL,
    isResidentKey BOOLEAN DEFAULT NULL,
    signCounter INT NOT NULL,
    friendlyName VARCHAR(100) DEFAULT 'Unnamed Token',
    hashedId VARCHAR(128) DEFAULT '---',
    aaguid VARCHAR(64) DEFAULT NULL,
    attLevel ENUM('None', 'Basic', 'Self', 'AttCA') NOT NULL DEFAULT 'None',
    CONSTRAINT credentials_user_id_credentialId_key UNIQUE (user_id , credentialId)
)

and the response is

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

I think the issue is because of the newer default character set ut8mb4, the largest key length is 768 characters. Unfortunately that means the credentialId cant be larger than 688 when combined with user_id.

I wonder if it would make sense to specify CHARACTER SET 'binary' for that column? That allowed me to create the table (with credentialId VARCHAR(1024) CHARACTER SET 'binary' NOT NULL,)

[restena-sw]

The query does not specify the intended character set. We could of course set that to utf8mb3 and keep going. It would resolve the issue at hand, and it is what was de facto set with older versions of MySQL/MariaDB (when "utf8" was an alias for "utf8mb3").

Out of curiosity... do you have an idea when the utf8 default switched from utf8mb3 to utf8mb4 ? We are still running a MariaDB 10.11 LTS here, which created the table with utf8mb3. So, the change was some time during MariaDB 11 ?

[happydude]

Looks like for MariaDB 11.8 https://mariadb.com/kb/en/unicode/

In MySQL utf8mb3 is deprecated, not sure if it is on MariaDB

[restena-sw]

Ah, that's very recent then. Okay, let's not go the utf8mb3 route, if it's already deprecated in MySQL. I guess BINARY should do the trick; I just wonder about deployed base. Do we need to supply update procedures to convert existing tables out there? I'm hoping that MySQL/MariaDB can cope with both charsets on its own, as the queries during runtime are charset-agnostic.

[happydude]

I think the data is base64 encoded, so I think it won't matter