Migration from 1.13.7 to 1.14.1 failed with migrate: do migrate: Error 1071: Specified key was too long; max key length is 1000 bytes

[tanrui8765]

• Gitea version (or commit ref): 1.13.7 -> 1.14.0/1.14.2
• Git version: 2.23.0.windows.1
• Operating system: Windows Server 2008 R2 Enterprise SP1 64bit
• Gitea is downloaded as "gitea-1.13.7-windows-4.0-amd64.exe", "gitea-1.14.2-windows-4.0-amd64.exe", run as windows service.
• Database (use [x]):
  • PostgreSQL
  • MySQL 5.7.26
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
• Log gist:

Description

I tried to upgrade from 1.13.7 to 1.14.0/1.14.2 directly, but failed. The info when I execute “doctor —all —fix” is the same with the GitHub issue #15505. The upgrade steps are as follows:

1. Stop the gitea windows service.
2. Copy and replace the gitea.exe with newer version.
3. Restart the gitea windows service. （Failed! 1.13.7 -> 1.14.0/1.14.2, Error 1067: The process terminated unexpectedly）
4. Get the doctor information. (./gitea.exe doctor --all --fix)

Screenshots

[tanrui8765]

Hello @zeripath @6543 @lunny , the #15505 issue probably still exists.

[jolheiser]

Unfortunately the error is missing in the doctor log. (#15813)

Can you give us your gitea logs as well for startup?

[tanrui8765]

Sure, but would you please tell me where to find this log? or all logs in Gitea/log/ folder? @jolheiser

[zeripath]

We need to the results and output messages that are returned from gitea migrate (or the first few messages that come out when you try to start gitea.)

It's likely that the log files will not contain this data as it is run before logs are set up.

So can you please just give us that output.

---

However I'm going to go out on a limb and guess that your error is coming from MySQL and it is reporting "error 1071".

In your mysql db console whilst connected to the gitea db run:

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

[tanrui8765]

Hello @zeripath, I restored the Gitea version and data to V1.13.7 for team development yesterday. I'll try to get the MySQL information you suggested later in a few days.

I had some findings when restoring the Gitea to 1.13.7 on "error 1071". I followed instruction in Restore Doc. The restoring MySQL steps:

1. Delete all the Gitea database's tables in phpMyAdmin
2. Import gitea-db.sql to Gitea database in phpMyAdmin
3. Error 1071 pops out about table "deleted_branch"
4. Locate the error, it comes from a line of SQL code: CREATE UNIQUE INDEX UQE_deleted_branch_s ON deleted_branch (repo_id,name,commit);
5. Compare this Gitea database's tables to another Gitea server with 1.13.7. I found the restored tables' type are all 'MyISAM', whereas table "deleted_branch" should be 'InnoDB'.
6. Change "deleted_branch" table from MyISAM to InnoDB with code: alter table deleted_branch engine=innodb;
7. Execute code in step 4, and there are no error anymore: CREATE UNIQUE INDEX UQE_deleted_branch_s ON deleted_branch (repo_id,name,commit);
8. Change some other table types from MyISAM to InnoDB.
9. Run .\gitea.exe doctor --all [2] & [3] are reported OK.

[tanrui8765]

Ah, I just see this error 1071 topic in Upgrade errors with MySQL

[zeripath]

I apologize if I wasn't clear.

Let me try again.

I need to see the error that happens when you try to upgrade. That is, when you try to run gitea web or gitea migrate using 1.14.2.

You still have not told us what the exact error is and without that error we cannot help.

Therefore, please could you give me the result of running with 1.14.2:

gitea migrate

[tanrui8765]

Sure, I see, I will try it in a few days.

[tanrui8765]

Hello @zeripath , here is what I got while upgrading 1.13.7 to 1.14.2, executing 1.14.2 gitea migrate & gitea web before start the windows service.

GiteaUpgradeFail_1_13_7_to_1_14_2_web_feedback_20210516a.txt
GiteaUpgradeFail_1_13_7_to_1_14_2_migrate_feedback_20210516a.txt

[zeripath]

The important error is here :

2021/05/16 15:14:13 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 1000 bytes

and is found right at the end of the migrate feedback.

As I suspected this is a MySQL error 1071, but worse it indicates that you are running your db with ISAM tables.

MyISAM tables are not supported by Gitea and have never been supported. You need to use InnoDB as ISAM does not have transaction support.

As it states in our FAQ https://docs.gitea.io/en-us/faq/#upgrade-errors-with-mysql you must run:

ALTER TABLE table_name ENGINE=InnoDB;

for each table in the database.

Then you will likely need to run:

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

[6543]

@tanrui8765 try gitea convert bevore migration

[zeripath]

unfortunately gitea convert doesn't do the InnoDB conversion as it was assumed that no-one would be running with ISAM.

[vitalif]

I get the same with InnoDB.

2021/05/16 14:38:51 ...ations/migrations.go:394:Migrate() [I] Migration[159]: update reactions constraint
2021/05/16 14:38:51 models/models.go:220:NewEngine() [I] [SQL] BEGIN TRANSACTION [] - 131.62µs
2021/05/16 14:38:51 ...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 [] - 260.682µs
2021/05/16 14:38:51 ...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`) [] - 213.659µs
2021/05/16 14:38:51 ...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/05/16 14:38:51 ...ations/migrations.go:397:Migrate() [I] [SQL] ROLLBACK [] - 94.23µs
2021/05/16 14:38:51 routers/init.go:157:GlobalInit() [F] ORM engine initialization failed: migrate: do migrate: Error 1071: Specified key was too long; max key length is 767 bytes

[vitalif]

Ok, issue solved, I had innodb_large_prefix off. Sorry