[SOLVED] MySql Error 1064 while running first install


#1

Hello, I’m trying to install gogs on my server, but I am getting the following error:

Database setting is not correct: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘utf8’ at line 1

when I submit the /install initial form.
I installed everything following this tutorial https://www.digitalocean.com/community/tutorials/how-to-set-up-gogs-on-ubuntu-14-04 I just replaced nginx proxy with an apache 2 proxy.

I can access the /install page both from the server’s IP and the custom proxied domain name.
The mySql DB has been created correctly, and I tested the user login, it looks like it’s working.
I also checked that the DB was configured to use INNODB engine, and that the default collation is utf8mb4_general_ci and both are true.
MySql’s error log is empty.
/var/log/gogs/stdout.log doesn’t show any error, while /var/log/gogs/stderror.log is empty.

It looks like I am completely stuck. Any help will be appreciated :slight_smile:

System: Ubuntu 16.04
Gogs version: Gogs version 0.11.33.1119
MySql version: mysql Ver 14.14 Distrib 5.6.33, for debian-linux-gnu (x86_64) using EditLine wrapper


#2

Hi you need another update to 0.11 34


#3

Thanks for your answer, just finished the upgrade, now I get a different mySql error :slight_smile:

Database setting is not correct: Error 1071: Specified key was too long; max key length is 767 bytes

Thanks!
S.


#4

Hmm … Never met such error… You may want to do some googling.


#5

It’s related to the max size of the indexes allowed for every table in MySQL.
Can you help me by pointing me in the source code where the CREATE TABLE statements are? I see they the query fails after creating the users table.
Maybe I can edit the CREATE statement and check if it works.
Thanks.


#6

Gogs uses xorm to automatically create tables, you should see logs in log/xorm.log file


#7

Thanks.
Looks like I don’t have that file in /var/log.
But I suppose I found a workaround, I’m trying now and then I’ll let you know.


#8

So, this link
https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
says that

If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.

This makes me think that either the row format is wrong, or the innodb_large_prefix is off, or both. Now I am trying to understand if I can change this options, and then I’ll let you know.


#9

I finally solved, I’ll write here my solution because it might be helpful to others: mySql 5.6 and lower have this index size limit that prevent gogs from creating required DB tables.

It has been a real pain, but at the end I’ve been able to upgrade mysql server to 5.7 and now it works, at least it installs correctly. I’ll check if repo creation, and usual git operations work properly later.

For some reason, my Ubuntu 16.04 was shipped with mySql 5.6 and no upgrade was available on standard repos. Adding the mySql own apt repo and upgrading the installation has been painful because for some reason the installation failed and cause a lot of conflicts between packages. I’ve been forced to completely remove mySql and all its modules before re-installing everything from scratch.


#10

I am having the same issue, but on CentOS 7.4 (1708).

I have upgraded all the way to MariaDB 10.1.29, but I am still getting the Error 1071: Specified key was too long; max key length is 767 bytes error.

I have checked the database and the setting for innodb_large_prefix appears to be set to OFF, although I think the default should be set to ON

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'innodb_large%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF  |
+---------------------+-------+

I have set this to ON, but the issue still persists.

I am running GoGS 0.11.34 and this is how my global variables are set

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+

Any suggestions @leeppolis or @Unknwon?


MySql Error 1071 while running first install & Error 1709
#11

I have the same error on Fedora 26.
Set to large prefixes via this howto, still having this issue.