mariadb-stretch-err1071.md

MariaDB Error 1071: Specified key was too long

Background

tank stores shows using it's name as primary key. Since this is a string it by default is mapped to a VARCHAR(255) at the database level. Some Linux distributions, i.e. Debian 9 (stretch) or Ubuntu 18.04 (bionic) have switched to the mySQL fork MariaDB. At least Debian und Ubuntu also switched to utf8mb4 as the default character-set. This is an attempt to support Emoji's stored as UTF-8 characters in the database for applications like Wordpress. When using utf8mb4 a VARCHAR(255) field can be up to 1021 bytes long. This exceeds the maximum length (767 bytes) for primary key indices on older version of the innodb file and row formats.

Possible Workarounds

There are several possible workarounds for the problem:

• Use utf8 (not utf8mb4). Either by setting store.db.Charset to utf8 or by creating the database using this command:

create database tank CHARACTER SET utf8 COLLATE utf8_general_ci;

Setting the character set using the daemon configuration is preferred because this also
makes sure that the database connection itself will use the correct settings as well.
Mind that this means you won't be able to use Emoji's in File Metadata. 😭

* Debian has [backported](https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=886756) some
changes to the MariaDB version in Debian Stretch. Because of this it is possible to switch
to the new innodb file and row format.
You can do this by adding the following to the `[mysqld]` section of
`/etc/mysql/mariadb.conf.d/50-server.cnf`:
```ini
innodb_file_format        = Barracuda
innodb_file_per_table     = On
innodb_large_prefix       = On
innodb_default_row_format = dynamic

As far as i can see Ubuntu did not add this patch to it's packages but starting with 10.2 MariaDB uses the new format by default. This means you can work around the issue by using the packages provided by the MariaDB Foundation.

• Consider switching to another DBMS:

  • tank also works with postgres
  • you may use the mySQL Community Packages from Oracle
  • Percona Server is a drop-in replacement for mySQL

• If for some reason none of the above is applicable, as a last resort, you can apply the patch below. This limits the length for show names to 191 characters which is just short enough to not exceed the limit.

diff --git a/store/types.go b/store/types.go index 8576c7b..df99125 100644 --- a/store/types.go +++ b/store/types.go @@ -69,7 +69,7 @@ func (e ErrInvalidMetadataField) Error() string { //******* Shows

type Show struct {

•   Name      string    `json:"name" gorm:"primary_key"`

•   Name      string    `json:"name" gorm:"primary_key;size:191"`
    CreatedAt time.Time `json:"created"`
    UpdatedAt time.Time `json:"updated"`

} @@ -169,7 +169,7 @@ type File struct { ID uint64 json:"id" gorm:"primary_key" CreatedAt time.Time json:"created" UpdatedAt time.Time json:"updated"

•   ShowName  string        `json:"show" gorm:"not null;index"`

•   ShowName  string        `json:"show" gorm:"not null;index;size:191"`
    Show      Show          `json:"-" gorm:"association_foreignkey:Name"`
    Source    FileSource    `json:"source" gorm:"embedded;embedded_prefix:source__"`
    Metadata  FileMetadata  `json:"metadata" gorm:"embedded;embedded_prefix:metadata__"`

@@ -194,7 +194,7 @@ type Playlist struct { ID uint64 json:"id" gorm:"primary_key" CreatedAt time.Time json:"created" UpdatedAt time.Time json:"updated"

•   ShowName  string          `json:"show" gorm:"not null;index"`

•   ShowName  string          `json:"show" gorm:"not null;index;size:191"`
    Show      Show            `json:"-" gorm:"association_foreignkey:Name"`
    Entries   []PlaylistEntry `json:"entries,omitempty"`

}