can't add emoji to issues

[helmut72]

• Gitea version (or commit ref): 1.6.3
• Database (use [x]):
  • MySQL

Description

Can't add emoji to issues. I get this error:

[...outers/repo/issue.go:1192 NewComment()] [E] CreateIssueComment: CreateComment: Error 1366: Incorrect string value: '\xF0\x9F\xA5\xB6' for column 'content' at row 1

[techknowlogick]

@helmut72 Is your mysql DB utf8, or utf8mb4?

[helmut72]

I've created every db with utf8mb4 last year, but I see that most gitea tables was utf8. Now it's utf8mb4. Found this issue: #3513 and converted it to utf8mb4.

But it doesn't help.

[helmut72]

Can I help with some more log files? I have completely reinstalled Gitea (1.7.2). Even if I create utf8mb4 database in MariaDB, Gitea creates utf8 tables after the first start. Looks like this is the default behavior.

[helmut72]

Tried another installation and use:
CREATE DATABASE IF NOT EXISTS gitea CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Instead of:
CREATE DATABASE IF NOT EXISTS gitea CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Found it on this Wiki:
https://wiki.archlinux.org/index.php/Gitea#MariaDB/MySQL

But also doesn't work.

What is the supported database in Gitea? Is SQLite enough for 10-20 users?

[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.

[lunny]

We recommend you to use Mysql / Postgres when non personal use.

[helmut72]

Thanks, then I'd like to continue with MySQL. It's not that we use that many Emojis, but a 500 error isn't the best user experience.

[zeripath]

The issue is that we currently use utf8 tables on mysql not utf8mb4. Therefore you can't have emojis on mysql

It's not as simple as just changing the tables yourself - as xorm will just change them back!

If you want emojis use postgres. If you can help make utf8mb4 work that would be excellent.

[lunny]

I have made utf8mb4 work on gitea now. But @zeripath I don't think xorm will change them back. I think we have to change two things:

• if tables created, change all the tables charset to utf8mb4 and COLLATE to utf8mb4_unicode_ci, if tables are not created, change your my.cnf to add

[client]
default-character-set = utf8mb4

[mysqld]
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4

• change the code on models.go:226 to connStr = fmt.Sprintf("%s:%s@%s(%s)/%s%scharset=utf8mb4&collation=utf8mb4_unicode_ci&parseTime=true&tls=%s", DbCfg.User, DbCfg.Passwd, connType, DbCfg.Host, DbCfg.Name, Param, tls)

restart gitea.

And xorm should also support customerize charset and collation when create tables. I will send some PRs to xorm and gitea.