gogs: MySql /Mariadb error: max key length is 767 byte
- Gogs version (or commit ref): 0.11.34
- Git version: 2.11.0
- Operating system: Debian 9
- Database (use
[x]
):- PostgreSQL
- MySQL
- MSSQL
- SQLite
- Can you reproduce the bug at https://try.gogs.io:
- Yes (provide example URL)
- No
- Not relevant
- Log gist (usually found in
log/gogs.log
):
Description
I tried to install from binary, I created the database on MariaDB, I launched ./gogs web and opened the installing page on browser. I inserted all needed but I received the error “Database setting is not correct: Error 1709: Index column size too large. The maximum column size is 767 bytes.” I think it’s a problem related to MariaDB char set size, but I wasn’t able to solve it. …
About this issue
- Original URL
- State: closed
- Created 7 years ago
- Comments: 31 (11 by maintainers)
Here a short description how i get it working… it is very laborious but it works for me:
To see which tables are currently present: use gogs; (gogs = your gogs database name!) SHOW TABLE STATUS;
Alter the tables with the following command: ALTER TABLE
_tablename_
ROW_FORMAT=DYNAMIC;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:
innodb_file_format=Barracuda
,innodb_large_prefix=on
and create table using ROW_FORMAT=DYNAMIC
orCOMPRESSED
(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 DATABASE `gogs` 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:
Didn’t work! 😦 Details at: https://github.com/gogits/gogs/issues/4891#issuecomment-362862801