Cannot start - tmp_recreate__reaction. Error: Error 1071: Specified key was too long; max key length is 767 bytes

[gsantner]

• Gitea version (or commit ref): master
• Git version: 2.17.1
• Operating system: Ubuntu 18.04
• Database (use [x]):
  • PostgreSQL
  • MySQL -- MariaDB 10.1.47-0ubuntu0.18.04
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
• Log gist:

logfile.log

Description

Hello, since about 6 days I cannot start latest version of gitea anymore.
Checked log, and found this in log:

2020/11/16 19:16:39 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] SELECT `id`, `version` FROM `version` WHERE `id`=? LIMIT 1 [1] - 354.86µs
2020/11/16 19:16:39 ...ations/migrations.go:344:Migrate() [I] Migration[159]: update reactions constraint
2020/11/16 19:16:39 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] BEGIN TRANSACTION [] - 98.283µs
2020/11/16 19:16:39 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] CREATE TABLE IF NOT EXISTS `tmp_recreate__reaction` (`id` BIGINT(20) PRIMARY KEY AUTO_INCREMENT NOT NULL, `type` VARCHAR(255) NOT NULL, `issue_id` BIGINT(20) NOT NULL, `comment_id` BIGINT(20) NULL, `user_id` BIGINT(20) NOT NULL, `original_author_id` BIGINT(20) DEFAULT 0 NOT NULL, `original_author` VARCHAR(255) NULL, `created_unix` BIGINT(20) NULL) DEFAULT CHARSET utf8mb4 ROW_FORMAT=DYNAMIC [] - 195.574µs
2020/11/16 19:16:39 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] CREATE UNIQUE INDEX `UQE_tmp_recreate__reaction_s` ON `tmp_recreate__reaction` (`type`,`issue_id`,`comment_id`,`user_id`,`original_author_id`,`original_author`) [] - 146.654µs
2020/11/16 19:16:39 ...ations/migrations.go:395:recreateTable() [E] Unable to create uniques for table tmp_recreate__reaction. Error: Error 1071: Specified key was too long; max key length is 767 bytes
2020/11/16 19:16:39 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] ROLLBACK [] - 79.173µs
2020/11/16 19:16:39 routers/init.go:85:initDBEngine() [E] ORM engine initialization attempt #1/10 failed. Error: migrate: do migrate: Error 1071: Specified key was too long; max key length is 767 bytes
2020/11/16 19:16:39 routers/init.go:86:initDBEngine() [I] Backing off for 3 seconds

So seemingly some database/orm mapper issue due too big key length.

I have these versions locally from gitea download site (current/master). The latest working version for me is from 10. November, so the bug has been introduced with a commit on/after that day.

./gitea-2020-11-10 --version  # <--- Working
Gitea version 1.14.0+dev-181-g8bae34c4b built with GNU Make 4.1, go1.15.4 : bindata, sqlite, sqlite_unlock_notify

./gitea-2020-11-13 --version # <-- Not working
Gitea version 1.14.0+dev-197-gc2e05d9f9 built with GNU Make 4.1, go1.15.4 : bindata, sqlite, sqlite_unlock_notify

./gitea-2020-11-16 --version # <-- Not working
Gitea version 1.14.0+dev-220-g0de546009 built with GNU Make 4.1, go1.15.5 : bindata, sqlite, sqlite_unlock_notify

[gsantner]

@zeripath

potential (also matches with time frame since error happens) https://github.com/go-gitea/gitea/blob/master/models/migrations/v159.go
#13505

[lafriks]

Best solution would be to upgrade mariadb to 10.2.2+

[lafriks]

It just database limitation and there is not much to do on Gitea side :(

[gsantner]

Checked apt, thats the latest available version on the LTS. Though didn't have any issue with gitea or other applications so far with DB.
I'm very sure to be not the only one whos gonna get locked out due if not fixed. Ubuntu 18.04 in production environment is not that old/dated.

It just database limitation and there is not much to do on Gitea side :(

Well, use smaller keys? Use small key (numeric id for example) and additional field if required?
I don't even use reactions at all^^.

Potentially then also affects Sqlite

[lafriks]

If you are not going to migrate repositories from other sources you can just alter originalauthor in reactions table to smaller varchar length, that would fix issue for you

[lafriks]

We can't really make it smaller because originalauthor is authors username from external migrated source

[lafriks]

Sqlite does not have such limit imho

[lafriks]

We could probably detect mysql version and use different key for it...

[gsantner]

If you are not going to migrate repositories from other sources you can just alter originalauthor in reactions table to smaller varchar length, that would fix issue for you

I have lots of mirrored repos, but simple git-http, no github/gitlab/bitbucket special integration.
So a potential mirror run would break everything I guess?

We can't really make it smaller because originalauthor is authors username from external migrated source

Ehm, why use originalauthor even as key?

[mrsdizzie]

Should be able to get around this in older versions with innodb_large_prefix (https://mariadb.com/kb/en/innodb-system-variables/#innodb_large_prefix) See https://stackoverflow.com/a/43403017 too

[lafriks]

I have lots of mirrored repos, but simple git-http, no github/gitlab/bitbucket special integration.
So a potential mirror run would break everything I guess?

No, that's only if you migrate issues or pull requests also

Ehm, why use originalauthor even as key?

Because when migrating from github we don't have all github users locally in Gitea so we store githubs username beside user id for gitea and we need to limit that there are no duplicate values. We need that for display and also to later be able to associate comments, reactions etc to that user if he authenticates in Gitea using github oauth provider

[gsantner]

I've manually issued create table statement, and aftwards create index with just one field.

However, gitea on it's own ignores that and wants to recreate the index still. (no IF NOT EXISTS)

@lafriks
Ok no, I don't use that. My reactions table is/was also empty. However issue is about CREATE INDEX, not CREATE TABLE. It's due the composition of the keys which in sum is too big. Having index only on i.e. two fields would potentially work, however as gitea not checks if the index already exists and skips index creation, I cannot manually do that.

[zeripath]

How many times people?

Read the FAQ.

Use gitea convert