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;

[zxvfxwing]

Hi

set global innodb_large_prefix = `ON`

did the trick for me.

[marlemion]

Same here, on most recent Arch. All the suggestions above did not help!

2018/08/22 17:32:39 [I] Log Mode: File(Trace)
2018/08/22 17:32:39 [I] XORM Log Mode: File(Trace)
2018/08/22 17:32:39 [I] Cache Service Enabled
2018/08/22 17:32:39 [I] Session Service Enabled
2018/08/22 17:32:39 [I] Migration: Reformat and remove incorrect topics
2018/08/22 17:32:39 [I] This migration could take up to minutes, please be patient.
2018/08/22 17:32:39 [...itea/routers/init.go:60 GlobalInit()] [E] Failed to initialize ORM engine: migrate: do migrate: Sync2: Error 1709: Index column size too large. The maximum column size is 767 bytes.

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.1.35-MariaDB MariaDB Server

Any other idea?

[rugk]

One issue for me was the release table.

The command returned this:

> ALTER TABLE 'release' ROW_FORMAT=DYNAMIC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''release' ROW_FORMAT=DYNAMIC' at line 1

So release seems to be some kind of reserved keyword. As such, you need to run

ALTER TABLE `release` ROW_FORMAT=DYNAMIC;

… instead, which syntactically makes it clear to MariaDB, that this is the table name.

[jives]

I'm on MariaDB 10.1.35 and I had to set

innodb_default_row_format=dynamic

in addition to (which I had set already)

innodb_file_format=Barracuda
innodb_large_prefix=1 

to deal with the exact same error.

[rugk]

Oh, yeah, i already commented that, but GitHub's markdown parsing of course used the backticks for ending my code part. 😄

[rugk]

done

[ostaszewskik]

Upgrading my Debian 9 stretch mariadb from 10.1 to 10.3 fixed the issue for me.

[JPabloSA]

install mariadb 10.3 works "debian 9.5"

--Install dependency packages
sudo apt-get install software-properties-common dirmngr

--Add MariaDB 10.3 repository and Import GPG key
sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://mirror.zol.co.zw/mariadb/repo/10.3/debian stretch main'

--install
sudo apt-get update && sudo apt-get install mariadb-server

[ChangePlaces]

@JPabloSA your solution won't work on pi devices as new repositories can't be added

[cdknight]

I just realized that set global innodb_default_row_format=dynamic should fix this instead of manually doing the ALTER TABLE table_name ROW_FORMAT=DYNAMIC for each table. Keep in mind you have to also have innodb_file_format=Barracuda.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

[zeripath]

This is another utf8mb4 issue.

We've added a few prs to help with this #7144 #6992

This particular issue is with old versions of Maria DB which do not provide a large enough index space. We simply cannot support utf8mb4 on those versions. Therefore I will close this won't fix.

[Rychu-Pawel]

For me it was necessary to execute

SET GLOBAL innodb_default_row_format=DYNAMIC;

But probably also adding to the config what @jives wrote should also work innodb_default_row_format=dynamic.

[asnodj]

I'm on MariaDB 10.1.35 and I had to set

innodb_default_row_format=dynamic

in addition to (which I had set already)

innodb_file_format=Barracuda
innodb_large_prefix=1 

to deal with the exact same error.

Work in MariaDB 9.8 ! Thanks !!!!