mariadb-stretch-err1071.md 4.2 KB
Newer Older
1
2
# MariaDB `Error 1071: Specified key was too long`

3
4
## Background

5
6
7
8
9
10
11
12
13
`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.
14
15
16
17
18

## Possible Workarounds

There are several possible workarounds for the problem:

Christian Pointner's avatar
Christian Pointner committed
19
20
 * Use `utf8` (not `utf8mb4`). Either by setting `store.db.Charset` to `utf8` or by
   creating the database using this command:
21
   ```mysql
22
   create database tank CHARACTER SET utf8 COLLATE utf8_general_ci;
23
   ```
Christian Pointner's avatar
Christian Pointner committed
24
   Setting the character set using the daemon configuration is preferred because this also
Christian Pointner's avatar
Christian Pointner committed
25
   makes sure that the database connection itself will use the correct settings as well.
Christian Pointner's avatar
Christian Pointner committed
26
   Mind that this means you won't be able to use Emoji's in File Metadata. 😭
27
28
29

 * 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
30
   to the new innodb file and row format.
31
32
33
   You can do this by adding the following to the `[mysqld]` section of
   `/etc/mysql/mariadb.conf.d/50-server.cnf`:
   ```ini
34
35
36
37
   innodb_file_format        = Barracuda
   innodb_file_per_table     = On
   innodb_large_prefix       = On
   innodb_default_row_format = dynamic
38
   ```
39
40
41
   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/).
42

Christian Pointner's avatar
Christian Pointner committed
43
 * Consider switching to another DBMS:
44
45
   * `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)
46
47
   * [Percona Server](https://www.percona.com/software/mysql-database/percona-server) is a drop-in
     replacement for mySQL
48

49
50
51
 * 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.
Christian Pointner's avatar
Christian Pointner committed
52

Christian Pointner's avatar
Christian Pointner committed
53
   ```patch
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
   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"`
    }
Christian Pointner's avatar
Christian Pointner committed
85
   ```