Specified key was too long; max key length is 767 bytes

ServiceStack.OrmLite MySql
1

I’m getting this error while it tries to create the ApiKey table.

Specified key was too long; max key length is 767 bytes

the database is
10.1.44-MariaDB-0+deb9u1 - Debian 9.1
with utf8mb4_general_ci for the database

I think i have fixed by manually creating the table with the following sql, by specifying a utf8_general_ci collation, i had the same problem with the ormlitecache, i think i should do the same.

CREATE TABLE `ApiKey` 
(
  `Id` VARCHAR(255) COLLATE utf8_general_ci  PRIMARY KEY, 
  `UserAuthId` VARCHAR(255)  COLLATE utf8_general_ci NULL, 
  `Environment` VARCHAR(255)  COLLATE utf8_general_ci NULL, 
  `KeyType` VARCHAR(255)  COLLATE utf8_general_ci NULL, 
  `CreatedDate` DATETIME NOT NULL, 
  `ExpiryDate` DATETIME NULL, 
  `CancelledDate` DATETIME NULL, 
  `Notes` VARCHAR(255)  COLLATE utf8_general_ci  NULL, 
  `RefId` INT(11) NULL, 
  `RefIdStr` VARCHAR(255)  COLLATE utf8_general_ci  NULL, 
  `Meta` VARCHAR(255)  COLLATE utf8_general_ci  NULL 
)
2

I’ve tested it on MySql 10.4 and it works without issue:

CREATE TABLE `ApiKey` 
(
  `Id` VARCHAR(255) PRIMARY KEY, 
  `UserAuthId` VARCHAR(255) NULL, 
  `Environment` VARCHAR(255) NULL, 
  `KeyType` VARCHAR(255) NULL, 
  `CreatedDate` DATETIME NOT NULL, 
  `ExpiryDate` DATETIME NULL, 
  `CancelledDate` DATETIME NULL, 
  `Notes` VARCHAR(255) NULL, 
  `RefId` INT(11) NULL, 
  `RefIdStr` VARCHAR(255) NULL, 
  `Meta` LONGTEXT NULL 
);

There’s nothing special about ApiKey other than maybe the usage of LONGTEXT which it’s going to use for complex types:

public class ApiKey : IMeta
{
    public string Id { get; set; }
    public string UserAuthId { get; set; }

    public string Environment { get; set; }
    public string KeyType { get; set; }

    public DateTime CreatedDate { get; set; }
    public DateTime? ExpiryDate { get; set; }
    public DateTime? CancelledDate { get; set; }
    public string Notes { get; set; }

    //Custom Reference Data
    public int? RefId { get; set; }
    public string RefIdStr { get; set; }
    public Dictionary<string, string> Meta { get; set; }
}

If it fails for this simple POCO Type, It’s likely to fail in many others where you may need to look at changing the default character set & collation.

3

Thanks for the reference, i will check with the customer. Some time unluckily i have to deal with really bad databases :sweat_smile: