Failed to initialize OAuth2 support: Error 1071: Specified key was too long; max key length is 767 bytes

[silverwind]

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

Description

After updating to today's master, I got the mentioned error related to commit 5a62eb3. Database is MariaDB 10.1.32 with default character set utf8mb4. Reverting the commit fixes the error.

CC: @lafriks.

[lunny]

utf8mb4 is not supported currently

[silverwind]

Which of these should I change to support gitea? (They are all at their default)

localhost> show variables like '%character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

[silverwind]

Tracked the issue down to this line which defines a indexed VARCHAR(400). Is it an option to change that to VARCHAR(191)? Note that my other tables use the old utf8 charset, not sure why it's attempting to use utf8mb4 on this table.

[silverwind]

Found a workaround from #3516 (comment):

innodb_file_format = Barracuda
innodb_large_prefix = 1
innodb_default_row_format = dynamic

In summary, mariadb < 10.2 and mysql < 5.7 don't support longer indexed colums by default. above setting apply the settings that are default on newer versions. I realize that upgrading mariadb would be the proper solution, but the Arch package still seems to be stuck on 10.1 and I don't want to compile it myself.

[drsect0r]

Found another workaround, create the table manually with an adjusted primary index length.

CREATE TABLE `oauth2_session` (
  `id` varchar(400) NOT NULL,
  `data` text,
  `created_unix` bigint(20) DEFAULT NULL,
  `updated_unix` bigint(20) DEFAULT NULL,
  `expires_unix` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `oauth2_session`
  ADD PRIMARY KEY (`id`(191));
COMMIT;

[thehowl]

@drsect0r you don't need the second query. The first one will be fine, the problem is the fact that our mariadb installs use utf8mb4 as the default charset - and in this case it leads to problem. The solution is simply to create the table with utf8 instead of utf8mb4 - so the first query will suffice.

This issue will probably create problems for other users who want to upgrade and have this kind of setup. I've created lafriks/xormstore#5 for this reason.

[silverwind]

create the table with utf8 instead of utf8mb4

Wouldn't that still be longer than that 767 bytes limit of old InnoDB? VARCHAR(400) should amount to up to 1200 bytes of storage as utf8 (aka utf8mb3).

[thehowl]

Yes, it looks like I didn't add the primary key in the end 🤦‍♂️ You are indeed right - the maximum you can go with the 767 bytes and utf8 limit is 255 characters.