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:
   ```mysql
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](https://downloads.mariadb.org/mariadb/repositories/).

 * Consider switching to another DBMS:
   * `tank` also works with postgres
   * you may use the mySQL Community Packages from [Oracle](https://dev.mysql.com/doc/refman/5.7/en/linux-installation-debian.html)
   * [Percona Server](https://www.percona.com/software/mysql-database/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.

   ```patch
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"`
 }
   ```