Unable to create an issue with these emojis 🍎 🤖 on MySQL 8 DB cluster in digitalocean #8178

New Issue

GiteaMirror · 2025-11-02T07:56:09-06:00

GiteaMirror commented

2025-11-02 07:56:09 -06:00

Originally created by @aqos156 on GitHub (Nov 26, 2021).

Gitea Version

1.15.6

Git Version

No response

Operating System

No response

How are you running Gitea?

Docker

Database

MySQL

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Description

An error occurs while creating an issue containing one of these two emojis 🍎 🤖 on MySQL 8 DB cluster on digitalocean.

Log

2021/11/26 09:14:16 Started POST /_ORG_/project/issues/new for _IP_:0
2021/11/26 09:14:16 ...rs/web/repo/issue.go:980:NewIssuePost() [E] NewIssue: newIssue: Error 3988: Conversion from collation utf8mb4_0900_ai_ci into utf8_general_ci impossible for parameter
2021/11/26 09:14:16 Completed POST /_ORG_/project/issues/new 500 Internal Server Error in 220.592707ms

Screenshots

No response

Originally created by @aqos156 on GitHub (Nov 26, 2021). ### Gitea Version 1.15.6 ### Git Version _No response_ ### Operating System _No response_ ### How are you running Gitea? Docker ### Database MySQL ### Can you reproduce the bug on the Gitea demo site? No ### Log Gist _No response_ ### Description An error occurs while creating an issue containing one of these two emojis 🍎 🤖 on MySQL 8 DB cluster on digitalocean. #### Log ``` 2021/11/26 09:14:16 Started POST /_ORG_/project/issues/new for _IP_:0 2021/11/26 09:14:16 ...rs/web/repo/issue.go:980:NewIssuePost() [E] NewIssue: newIssue: Error 3988: Conversion from collation utf8mb4_0900_ai_ci into utf8_general_ci impossible for parameter 2021/11/26 09:14:16 Completed POST /_ORG_/project/issues/new 500 Internal Server Error in 220.592707ms ``` ### Screenshots _No response_

GiteaMirror added the issue/needs-feedback label 2025-11-02 07:56:09 -06:00

GiteaMirror closed this issue

2025-11-02 07:56:09 -06:00

GiteaMirror commented

2025-11-02 07:56:10 -06:00

@wxiaoguang commented on GitHub (Nov 26, 2021):

What is your database charset setting? You need utf8mb4 for databases and tables in MySQL

@wxiaoguang commented on GitHub (Nov 26, 2021): What is your database charset setting? You need utf8mb4 for databases and tables in MySQL

GiteaMirror commented

2025-11-02 07:56:10 -06:00

@silverwind commented on GitHub (Nov 26, 2021):

Convert your database to a supported charset, utf8_general_ci (3-byte) is not supported and can not hold emoji data (4-byte). Also see this FAQ entry:

https://docs.gitea.io/en-us/faq/#why-are-emoji-broken-on-mysql

@silverwind commented on GitHub (Nov 26, 2021): Convert your database to a supported charset, `utf8_general_ci` (3-byte) is not supported and can not hold emoji data (4-byte). Also see this FAQ entry: https://docs.gitea.io/en-us/faq/#why-are-emoji-broken-on-mysql

GiteaMirror commented

2025-11-02 07:56:10 -06:00

@aqos156 commented on GitHub (Nov 26, 2021):

I can confirm it was indeed the issue. Our DB had correct encoding, but we migrated from an older instance of gitea that was running on a server with utf8_general_ci encoding. This is why I was confused. Most of the tables and columns had incorrect encoding.

Here is a quick snippet of sql that can be useful to anyone who needs to change the encoding.

-- Correct table encoding
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` COLLATE "_____FILL_IN_YOUR_ENCONDING_OF_CHOICE_____";')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '____FILL_IN_YOUR_DB_NAME____' AND TABLE_TYPE != 'VIEW';

-- Correct column formatting where needed
SELECT 
    CONCAT('ALTER TABLE `', t.TABLE_NAME,'` CHANGE ', c.COLUMN_NAME,' ', c.COLUMN_NAME,' ', c.COLUMN_TYPE ,' COLLATE "_____FILL_IN_YOUR_ENCONDING_OF_CHOICE_____";')
FROM information_schema.TABLES as t 
LEFT JOIN INFORMATION_SCHEMA.COLUMNS as c 
    ON t.TABLE_NAME = c.TABLE_NAME
WHERE 
    t.TABLE_SCHEMA = '____FILL_IN_YOUR_DB_NAME____' AND 
    c.TABLE_SCHEMA = t.TABLE_SCHEMA AND 
    t.TABLE_TYPE != 'VIEW'
    AND c.COLLATION_NAME is not NULL;

@aqos156 commented on GitHub (Nov 26, 2021): I can confirm it was indeed the issue. Our DB had correct encoding, but we migrated from an older instance of gitea that was running on a server with `utf8_general_ci` encoding. This is why I was confused. Most of the tables and columns had incorrect encoding. Here is a quick snippet of sql that can be useful to anyone who needs to change the encoding. ```sql -- Correct table encoding SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` COLLATE "_____FILL_IN_YOUR_ENCONDING_OF_CHOICE_____";') FROM information_schema.TABLES WHERE TABLE_SCHEMA = '____FILL_IN_YOUR_DB_NAME____' AND TABLE_TYPE != 'VIEW'; -- Correct column formatting where needed SELECT CONCAT('ALTER TABLE `', t.TABLE_NAME,'` CHANGE ', c.COLUMN_NAME,' ', c.COLUMN_NAME,' ', c.COLUMN_TYPE ,' COLLATE "_____FILL_IN_YOUR_ENCONDING_OF_CHOICE_____";') FROM information_schema.TABLES as t LEFT JOIN INFORMATION_SCHEMA.COLUMNS as c ON t.TABLE_NAME = c.TABLE_NAME WHERE t.TABLE_SCHEMA = '____FILL_IN_YOUR_DB_NAME____' AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND t.TABLE_TYPE != 'VIEW' AND c.COLLATION_NAME is not NULL; ```

GiteaMirror commented

2025-11-02 07:56:10 -06:00

@zeripath commented on GitHub (Nov 26, 2021):

Or you could just use:

gitea convert

as the FAQ suggests:

Why Are Emoji Broken On MySQL

Unfortunately MySQL's utf8 charset does not completely allow all possible UTF-8 characters, in particular Emoji.
They created a new charset and collation called utf8mb4 that allows for emoji to be stored but tables which use
the utf8 charset, and connections which use the utf8 charset will not use this.

Please run gitea convert, or run ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
for the database_name and run ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
for each table in the database.

You will also need to change the app.ini database charset to CHARSET=utf8mb4.

@zeripath commented on GitHub (Nov 26, 2021): Or you could just use: ``` gitea convert ``` as the [FAQ](https://docs.gitea.io/en-us/faq/#why-are-emoji-broken-on-mysql) suggests: ## Why Are Emoji Broken On MySQL Unfortunately MySQL's `utf8` charset does not completely allow all possible UTF-8 characters, in particular Emoji. They created a new charset and collation called `utf8mb4` that allows for emoji to be stored but tables which use the `utf8` charset, and connections which use the `utf8` charset will not use this. Please run `gitea convert`, or run `ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;` for the database_name and run `ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;` for each table in the database. You will also need to change the app.ini database charset to `CHARSET=utf8mb4`.

GiteaMirror referenced this issue

2025-11-02 09:15:56 -06:00

Activity Heatmap: Count commits instead of pushes #10700

GiteaMirror referenced this issue

2025-11-02 09:15:57 -06:00

Activity Heatmap: Count commits instead of pushes #10700

GiteaMirror referenced this issue

2025-11-02 11:20:05 -06:00

contributions and heatmap information from git commit #14686

Sign in to join this conversation.