Migrate 1.13.7 to 1.14.1 failed - Error 1071: Specified key was too long; max key length is 767 bytes

[FloMiau]

• Gitea version (or commit ref): 1.13.7 -> 1.14.1
• Git version: 2.17.1
• Operating system: Ubuntu 18.04 with amd64 binary from dl.gitea.io
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
• Log gist:

./gitea --config /home/gitea/gitea/custom/conf/app.ini migrate
2021/04/19 11:36:28 ...dules/setting/git.go:101:newGit() [I] Git Version: 2.17.1
2021/04/19 11:36:28 cmd/migrate.go:31:runMigrate() [T] AppPath: /home/gitea/gitea/gitea
2021/04/19 11:36:28 cmd/migrate.go:32:runMigrate() [T] AppWorkPath: /home/gitea/gitea
2021/04/19 11:36:28 cmd/migrate.go:33:runMigrate() [T] Custom path: /home/gitea/gitea/custom
2021/04/19 11:36:28 cmd/migrate.go:34:runMigrate() [T] Log path: /home/gitea/gitea/log
2021/04/19 11:36:28 ...om/urfave/cli/app.go:277:Run() [I] PING DATABASE mysql
2021/04/19 11:36:28 ...ations/migrations.go:357:Migrate() [I] [SQL] SELECT `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=? and `TABLE_NAME`=? [gitea version] - 2.959856ms
2021/04/19 11:36:28 models/models.go:220:NewEngine() [I] [SQL] SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? AND `COLUMN_NAME` = ? [gitea version id] - 3.08131ms
2021/04/19 11:36:28 models/models.go:220:NewEngine() [I] [SQL] SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? AND `COLUMN_NAME` = ? [gitea version version] - 1.48779ms
2021/04/19 11:36:28 ...rm.io/xorm/engine.go:1130:Get() [I] [SQL] SELECT `id`, `version` FROM `version` WHERE `id`=? LIMIT 1 [1] - 818.839µs
2021/04/19 11:36:28 ...ations/migrations.go:394:Migrate() [I] Migration[159]: update reactions constraint
2021/04/19 11:36:28 models/models.go:220:NewEngine() [I] [SQL] BEGIN TRANSACTION [] - 417.804µs
2021/04/19 11:36:28 ...s/migrations/v159.go:33:updateReactionConstraint() [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 [] - 688.16µs
2021/04/19 11:36:28 ...s/migrations/v159.go:33:updateReactionConstraint() [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`) [] - 767.418µs
2021/04/19 11:36:28 ...ations/migrations.go:447: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
2021/04/19 11:36:28 ...ations/migrations.go:397:Migrate() [I] [SQL] ROLLBACK [] - 280.417µs
2021/04/19 11:36:28 cmd/migrate.go:38:runMigrate() [F] Failed to initialize ORM engine: migrate: do migrate: Error 1071: Specified key was too long; max key length is 767 bytes

My current database version is now 159.

[amvasilyev]

I have encountered the same issue when tried to migrate to the latest release.

Upgrade was done from 1.13.7 to 1.14.1. MariaDB version:

mysql  Ver 15.1 Distrib 10.5.9-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

[zeripath]

The issue is that MySQL 5 has a rather short indexed varchar limit on utf8mb4 of 191 bytes. Later versions of MySQL/MariaDB do not suffer this.

Googling appears to indicate:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;

May help.

Xorm likely needs to detect these versions of MySQL and set its default string size to 191 in order to avoid this.

[amvasilyev]

@zeripath Thank you for your help. After changing these variables migration to 1.14.1 was successful.