# \[solved\] Database issue with SQL queries containing emoji **URL:** \ **Category:** Install/Maintain/Configure\ **Created:** [December 4, 2020, 9:28am UTC](https://forum.gitea.com/t/solved-database-issue-with-sql-queries-containing-emoji/2713 "2020-12-04T09:28:08Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![OCram85](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.gitea.com/ocram85/32/4947_2.png) [@OCram85](https://forum.gitea.com/u/OCram85)\ **Post date:** [December 4, 2020, 9:28am UTC](https://forum.gitea.com/t/solved-database-issue-with-sql-queries-containing-emoji/2713/1 "2020-12-04T09:28:08Z")
Hi folks, I recently ran into the [Emoji mysql issue](https://github.com/go-gitea/gitea/pull/6992). I notices the error only occures if the browser / chrome replaces the emoji instead of keeping the `:emoji:` syntax. Here is log output when trying to add an issue containing the emoji data: ```bash 2020/12/04 09:48:02 ...m.io/xorm/core/tx.go:130:ExecContext() [I] [SQL] INSERT INTO `issue` (`repo_id`,`poster_id`,`original_author`,`original_author_id`,`name`,`content`,`milestone_id`,`priority`,`is_closed`,`is_pull`,`num_comments`,`ref`,`deadline_unix`,`created_unix`,`updated_unix`,`closed_unix`,`is_locked`,`index`) SELECT ?,?,?,?,?,?,?,?,false,false,?,?,?,?,?,?,false,coalesce(MAX(`index`),0)+1 FROM `issue` WHERE (repo_id=?) [1 1 0 parsed emoji 💣 testhing this\01503d \01503d 0 0 0 0 1607075282 1607075282 0 1] - 991.735µs 2020/12/04 09:48:02 ...o/xorm/session_tx.go:46:Rollback() [I] [SQL] ROLL BACK [] - 448.716µs 2020/12/04 09:48:02 models/issue.go:956:NewIssue() [E] NewIssue: error attempting to insert the new issue; will retry. Original error: Error 1366: Incorrect string value: '\xF0\x9F\x92\xA3 ...' for column `giteadb`.`issue`.`content` at row 1 ``` Whats the state of this issue? - Are the emojis now supported wir mariadb + utf8mb4 ? - Or did I missed anything in the install instructions? my `docker-compose` looks like this: (I didn’t paste the full one with traefik, secrets and network details) ```yaml ... ... services: db: image: mariadb:10 restart: unless-stopped secrets: - "MYSQL_ROOT_PASSWORD" - "MYSQL_PASSWORD" environment: - "MYSQL_ROOT_PASSWORD_FILE=/run/secrets/MYSQL_ROOT_PASSWORD" - "MYSQL_DATABASE=foo" - "MYSQL_USER=bar" - "MYSQL_PASSWORD_FILE=/run/secrets/MYSQL_PASSWORD" volumes: - db:/var/lib/mysql gitea: image: gitea/gitea:1.12 ports: - "3000:3000" - "222:22" depends_on: - db restart: unless-stopped environment: - "APP_NAME=MyGitea" - "RUN_MODE=prod" - "DOMAIN=foobar.com" - "SSH_PORT=222" - "SSH_LISTEN_PORT=22" - "DISABLE_SSH=true" - "ROOT_URL=https://gitea.foobar.com" - "LFS_START_SERVER=true" - "DB_TYPE=mysql" - "DB_HOST=db:3306" - "DB_NAME=foo" - "DB_USER=bar" #- "DB_PASSWD=``" - "REQUIRE_SIGIN_VIEW=true" ... ... ``` The default encoding seems to be fine: ![image](https://us1.discourse-cdn.com/flex020/uploads/gitea/original/2X/9/9e5a34fdbde467bcacf4cf3691370cda3b285a90.png) But tables like `issue` still differs: ```auto SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "giteadb" AND T.table_name = "issue"; +--------------------+ | character_set_name | +--------------------+ | utf8 | +--------------------+ ``` `issue` table: columns details: ```auto +--------------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--------------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | id | bigint(20) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | repo_id | bigint(20) | NULL | YES | MUL | NULL | | select,insert,update,references | | | index | bigint(20) | NULL | YES | | NULL | | select,insert,update,references | | | poster_id | bigint(20) | NULL | YES | MUL | NULL | | select,insert,update,references | | | original_author | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | original_author_id | bigint(20) | NULL | YES | MUL | NULL | | select,insert,update,references | | | name | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | content | text | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | milestone_id | bigint(20) | NULL | YES | MUL | NULL | | select,insert,update,references | | | priority | int(11) | NULL | YES | | NULL | | select,insert,update,references | | | is_closed | tinyint(1) | NULL | YES | MUL | NULL | | select,insert,update,references | | | is_pull | tinyint(1) | NULL | YES | MUL | NULL | | select,insert,update,references | | | num_comments | int(11) | NULL | YES | | NULL | | select,insert,update,references | | | ref | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | deadline_unix | bigint(20) | NULL | YES | MUL | NULL | | select,insert,update,references | | | created_unix | bigint(20) | NULL | YES | MUL | NULL | | select,insert,update,references | | | updated_unix | bigint(20) | NULL | YES | MUL | NULL | | select,insert,update,references | | | closed_unix | bigint(20) | NULL | YES | MUL | NULL | | select,insert,update,references | | | is_locked | tinyint(1) | NULL | NO | | 0 | | select,insert,update,references | | +--------------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ ``` > I’m really not a sql pro. So I’m not quite sure if it depende on the encoding, collation … * * * Update: If found the doc section [database-prep](https://docs.gitea.io/en-us/database-prep/). Does there instructions also affect / apply to a docker based installation? - In this case there really should be a link to the topic! ---
**Author:** ![lunny](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.gitea.com/lunny/32/4701_2.png) [@lunny](https://forum.gitea.com/u/lunny)\ **Post date:** [December 6, 2020, 2:20am UTC](https://forum.gitea.com/t/solved-database-issue-with-sql-queries-containing-emoji/2713/2 "2020-12-06T02:20:32Z")
You should also set ```auto [database] charset=utf8mb4 ``` and covert the original tables from utf8 to utf8mb4 ---
**Author:** ![OCram85](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.gitea.com/ocram85/32/4947_2.png) [@OCram85](https://forum.gitea.com/u/OCram85)\ **Post date:** [December 6, 2020, 11:15am UTC](https://forum.gitea.com/t/solved-database-issue-with-sql-queries-containing-emoji/2713/3 "2020-12-06T11:15:00Z")
Do you mean the mysql client or server config? ---
**Author:** ![lunny](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.gitea.com/lunny/32/4701_2.png) [@lunny](https://forum.gitea.com/u/lunny)\ **Post date:** [December 6, 2020, 3:36pm UTC](https://forum.gitea.com/t/solved-database-issue-with-sql-queries-containing-emoji/2713/4 "2020-12-06T15:36:58Z")
I mean Gitea’s configuration file. ---
**Author:** ![OCram85](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.gitea.com/ocram85/32/4947_2.png) [@OCram85](https://forum.gitea.com/u/OCram85)\ **Post date:** [December 6, 2020, 6:53pm UTC](https://forum.gitea.com/t/solved-database-issue-with-sql-queries-containing-emoji/2713/5 "2020-12-06T18:53:34Z")
Thanks, I’ll try this tomorrow. Would it help other users, if I create a PR which contains this in the example docker config? ---
**Author:** ![OCram85](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.gitea.com/ocram85/32/4947_2.png) [@OCram85](https://forum.gitea.com/u/OCram85)\ **Post date:** [December 7, 2020, 9:52am UTC](https://forum.gitea.com/t/solved-database-issue-with-sql-queries-containing-emoji/2713/6 "2020-12-07T09:52:06Z")
Just an update: I could my problems with: - setting the database charset in the `app.ini` to `utf8mb4` - Exporting the database with adminer tool. - Setting the correct default server collation (`utf8_general_ci`) and encoding (`utf8mb4`) - Replacing the table charset value from `utf8` in to `utf8mb4` Example: ![image](https://us1.discourse-cdn.com/flex020/uploads/gitea/original/2X/8/89c92296d7c2f0acfd4cd0e7b845c64db252475a.png) - And finally importing the sql dump again. > Thanks again @lunny for your help! 🍻 ---
**Author:** ![lunny](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.gitea.com/lunny/32/4701_2.png) [@lunny](https://forum.gitea.com/u/lunny)\ **Post date:** [December 8, 2020, 12:10am UTC](https://forum.gitea.com/t/solved-database-issue-with-sql-queries-containing-emoji/2713/7 "2020-12-08T00:10:22Z")
I’m sorry I have forgot we have a command to do that. `./gitea convert` could help you to convert the database automatically. You can type `./gitea convert --help` to know how to use it. ---
**Author:** ![techknowlogick](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.gitea.com/techknowlogick/32/4706_2.png) [@techknowlogick](https://forum.gitea.com/u/techknowlogick)\ **Post date:** [December 10, 2020, 8:18pm UTC](https://forum.gitea.com/t/solved-database-issue-with-sql-queries-containing-emoji/2713/8 "2020-12-10T20:18:24Z")