MySql/MariaDB issue: Error 1709: Index column size too large. The maximum column size is 767 bytes.

[ulorentz]

• Gitea version (or commit ref): 1.2.3
• Git version: 2.11.0
• Operating system: Debian 9
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
  • Not relevant
• Log gist:

Description

While trying to install from the binary version, I had this error message: " Error 1709: Index column size too large. The maximum column size is 767 bytes."
I run Debian 9, where it is installed MariaDB (equivalent of MySQL) database.
I tried running:
SET GLOBAL innodb_large_prefix = ON;
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
but nothing happened.

It seems to be a mariaDB related issue. I have just tried in a virtual machine with Debian 8 (wich runs mysql 5.5, very old...). I had no problem: installation succeeded! The fact is that in Debian 9 mariaDB comes in place of mysql, so I guess every user using Debian 9 should have this issue (I'v tried in different virtua machine running Debian 9, even in a fresh install, and all produced that error), it's not only a problem of Gitea, also Gogs produces the same error.

What else to do?

[lafriks]

Please use latest stable version 1.2.3 as 1.0.1 is quite old

[ulorentz]

I upgrated to 1.2.3, same issue. Nothing changed.

[lafriks]

Did you try set global innodb_large_prefix = ON;?

[ulorentz]

Yes, sorry i didn't write in the first post, but I used this command before the other two. I'll add in the first post.

[lafriks]

@lunny can this be related to xorm?

[ulorentz]

It seems to be a mariaDB related issue. I have just tried in a virtual machine with Debian 8 (wich runs mysql 5.5, very old...). I had no problem: installation succeeded! The problem is that in Debian 9 mariaDB comes in place of mysql, so I guess every user using Debian 9 should have this issue (I tried in different virtuamachine running Debian 9, even a fresh install and all produced that error), it's not only a problem of Gitea, also Gogs produces the same error.

[lunny]

I will try with mariabd

[ulorentz]

I opened a thread issue on gogs repository. An user answered me, giving a method that solved the problem. It's very laborious, but maybe it gives you an hint on what to change in gitea code to work properly:
"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.
5- Now connect to your mariadb, use your gogs database and alter the tables with the following command:
ALTER TABLE tablename ROW_FORMAT=DYNAMIC;
6- 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."
After it it works.
Any idea of what's wrong between mariadb and gitea/gogs?

[lunny]

I cannot reproduce this issue on my macOS with mariadb docker on gitea master and gitea v1.2.3.

[appleboy]

Please upgrade MariaDB to 10.2.14. The 10.1 version is not working for me.

[SagePtr]

Another temporary solution for MariaDB or for older MySQL (if you can't update it) is to put innodb_large_prefix into my.cnf (which is default starting from certain versions of MySQL and MariaDB).

[CountMurphy]

I had to do a little bit more to get this working.
See https://stackoverflow.com/a/43403017

Also this sql for those who don't want to type it out:
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 commit_status ROW_FORMAT=DYNAMIC;
ALTER TABLE deleted_branch ROW_FORMAT=DYNAMIC;
ALTER TABLE deploy_key ROW_FORMAT=DYNAMIC;
ALTER TABLE email_address ROW_FORMAT=DYNAMIC;
ALTER TABLE external_login_user ROW_FORMAT=DYNAMIC;
ALTER TABLE follow ROW_FORMAT=DYNAMIC;
ALTER TABLE gpg_key ROW_FORMAT=DYNAMIC;
ALTER TABLE hook_task ROW_FORMAT=DYNAMIC;
ALTER TABLE issue ROW_FORMAT=DYNAMIC;
ALTER TABLE issue_assignees ROW_FORMAT=DYNAMIC;
ALTER TABLE issue_label ROW_FORMAT=DYNAMIC;
ALTER TABLE issue_user ROW_FORMAT=DYNAMIC;
ALTER TABLE issue_watch ROW_FORMAT=DYNAMIC;
ALTER TABLE label ROW_FORMAT=DYNAMIC;
ALTER TABLE lfs_lock ROW_FORMAT=DYNAMIC;
ALTER TABLE lfs_meta_object ROW_FORMAT=DYNAMIC;
ALTER TABLE login_source ROW_FORMAT=DYNAMIC;
ALTER TABLE milestone ROW_FORMAT=DYNAMIC;
ALTER TABLE mirror ROW_FORMAT=DYNAMIC;
ALTER TABLE notice ROW_FORMAT=DYNAMIC;
ALTER TABLE notification ROW_FORMAT=DYNAMIC;
ALTER TABLE org_user ROW_FORMAT=DYNAMIC;
ALTER TABLE protected_branch ROW_FORMAT=DYNAMIC;
ALTER TABLE public_key ROW_FORMAT=DYNAMIC;
ALTER TABLE pull_request ROW_FORMAT=DYNAMIC;
ALTER TABLE reaction ROW_FORMAT=DYNAMIC;
ALTER TABLE release ROW_FORMAT=DYNAMIC;
ALTER TABLE repo_indexer_status ROW_FORMAT=DYNAMIC;
ALTER TABLE repo_redirect ROW_FORMAT=DYNAMIC;
ALTER TABLE repo_unit ROW_FORMAT=DYNAMIC;
ALTER TABLE repository ROW_FORMAT=DYNAMIC;
ALTER TABLE star ROW_FORMAT=DYNAMIC;
ALTER TABLE stopwatch ROW_FORMAT=DYNAMIC;
ALTER TABLE team ROW_FORMAT=DYNAMIC;
ALTER TABLE team_repo ROW_FORMAT=DYNAMIC;
ALTER TABLE team_user ROW_FORMAT=DYNAMIC;
ALTER TABLE topic ROW_FORMAT=DYNAMIC;
ALTER TABLE tracked_time ROW_FORMAT=DYNAMIC;
ALTER TABLE two_factor ROW_FORMAT=DYNAMIC;
ALTER TABLE u2_f_registration ROW_FORMAT=DYNAMIC;
ALTER TABLE upload ROW_FORMAT=DYNAMIC;
ALTER TABLE user ROW_FORMAT=DYNAMIC;
ALTER TABLE user_open_id ROW_FORMAT=DYNAMIC;
ALTER TABLE version ROW_FORMAT=DYNAMIC;
ALTER TABLE watch ROW_FORMAT=DYNAMIC;
ALTER TABLE webhook ROW_FORMAT=DYNAMIC;