gogs: MySql /Mariadb error: max key length is 767 byte

Here a short description how i get it working… it is very laborious but it works for me:

1. Create your gogs database as usual
2. Visit the install page
3. Type in your data (database, user, etc.)
4. Click on Button “Install” -> It will fail with: ‘Error 1709: Index column size too large. The maximum column size is 767 bytes.’ BUT the tables were partially created.

To see which tables are currently present: use gogs; (gogs = your gogs database name!) SHOW TABLE STATUS;

1. Alter the tables with the following command: ALTER TABLE _tablename_ ROW_FORMAT=DYNAMIC;

2. Now go back to step 2 and repeat it. Always alter all “new” tables which were created.

After 4 or 5 rounds you get every needed table and it’s done.

I am not completely sure whether you have to set the above mentioned options. I have done that.

Good luck.

I have the same error. It seems to be a similar problem which is also described here: https://discuss.gogs.io/t/solved-mysql-error-1064-while-running-first-install/1604/3

I tried these options in mysql (mariadb): SET GLOBAL innodb_file_format=Barracuda; SET GLOBAL innodb_file_per_table=ON; innodb_large_prefix=1

But the create statements of the tables should have (which is not the case): ROW_FORMAT=DYNAMIC;

Because of mysql 5.6 (includes prior versions) InnoDB max index length is 767 bytes, mysql 5.7.7 is up to 3072 bytes. If some varchar column’s length is 255, when the character format is utf-8 needs 255*3=765 bytes for index length, It’s OK. But, an utf8mb needs 255*4=1020 bytes for index length.

Solutions:

• UPGRADE the mysql to 5.7.7(Mariadb 10.2.2)
• Change the utf8mb column length to 191 (191*4=764)
• Change the utf8mb to utf8
• set innodb_file_format=Barracuda, innodb_large_prefix=on and create table using ROW_FORMAT=DYNAMIC or COMPRESSED (default for 5.7.7)

I will fix it later.

Just to add that, for me, I had to also alter database and table’s character set.

The full list of commands was:

ALTER TABLE `access` ROW_FORMAT=DYNAMIC;
ALTER TABLE `access_token` ROW_FORMAT=DYNAMIC;
ALTER TABLE `action` ROW_FORMAT=DYNAMIC;
ALTER TABLE `attachment` ROW_FORMAT=DYNAMIC;
ALTER TABLE `collaboration` ROW_FORMAT=DYNAMIC;
ALTER TABLE `comment` ROW_FORMAT=DYNAMIC;
ALTER TABLE `deploy_key` ROW_FORMAT=DYNAMIC;
ALTER TABLE `follow` ROW_FORMAT=DYNAMIC;
ALTER TABLE `hook_task` ROW_FORMAT=DYNAMIC;
ALTER TABLE `issue` ROW_FORMAT=DYNAMIC;
ALTER TABLE `issue_label` ROW_FORMAT=DYNAMIC;
ALTER TABLE `issue_user` ROW_FORMAT=DYNAMIC;
ALTER TABLE `label` ROW_FORMAT=DYNAMIC;
ALTER TABLE `login_source` ROW_FORMAT=DYNAMIC;
ALTER TABLE `milestone` ROW_FORMAT=DYNAMIC;
ALTER TABLE `mirror` ROW_FORMAT=DYNAMIC;
ALTER TABLE `protect_branch` ROW_FORMAT=DYNAMIC;
ALTER TABLE `protect_branch_whitelist` ROW_FORMAT=DYNAMIC;
ALTER TABLE `public_key` ROW_FORMAT=DYNAMIC;
ALTER TABLE `pull_request` ROW_FORMAT=DYNAMIC;
ALTER TABLE `release` ROW_FORMAT=DYNAMIC;
ALTER TABLE `repository` ROW_FORMAT=DYNAMIC;
ALTER TABLE `star` ROW_FORMAT=DYNAMIC;
ALTER TABLE `two_factor` ROW_FORMAT=DYNAMIC;
ALTER TABLE `two_factor_recovery_code` ROW_FORMAT=DYNAMIC;
ALTER TABLE `upload` ROW_FORMAT=DYNAMIC;
ALTER TABLE `user` ROW_FORMAT=DYNAMIC;
ALTER TABLE `watch` ROW_FORMAT=DYNAMIC;
ALTER TABLE `webhook` ROW_FORMAT=DYNAMIC;

ALTER DATABASE `gogs` CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE `access` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `access_token` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `action` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `attachment` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `collaboration` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `comment` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `deploy_key` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `email_address` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `follow` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `hook_task` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `issue` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `issue_label` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `issue_user` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `label` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `login_source` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `milestone` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `mirror` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `notice` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `org_user` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `protect_branch` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `protect_branch_whitelist` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `public_key` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `pull_request` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `release` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `repository` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `star` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `team` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `team_repo` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `team_user` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `two_factor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `two_factor_recovery_code` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `upload` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `user` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `watch` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `webhook` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

10.1.26 is MariaDB version. Please update to MariaDB 10.2

I don’t know what you saying, PHP is not MySQL.

About backup and restore gogs datas.

Backup: $ ./gogs backup

Restore: $ ./gogs restore --from gogs-backup-[timestamp].zip

Restore SSH and Repo-hooks:

1. Administrator account login gogs
2. Goto “Admin Panel” - “Operations”
3. Click “Rewrite ‘.ssh/authorized-keys’ file”
4. Click “Resync pre-receive, update and post-receive hooks of all repositories”

Didn’t work! 😦 Details at: https://github.com/gogits/gogs/issues/4891#issuecomment-362862801

MariaDB [gogs]> show table status;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| user | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2018-02-03 23:57:08 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

MariaDB [gogs]> ALTER TABLE _user_ ROW_FORMAT=DYNAMIC;
ERROR 1146 (42S02): Table 'gogs._user_' doesn't exist