500 internal server error when using emoji in issue #6497

Closed
opened 2025-11-02 06:57:46 -06:00 by GiteaMirror · 2 comments
Owner

Originally created by @aanderse on GitHub (Dec 10, 2020).

Description

Exactly this issue. I would like to know how to fix an existing install. Even after correcting the default character set and collation the existing tables and columns have an incorrect character set+collation, so what columns should be updated to use the utf8mb4 character set?

Screenshots

MariaDB [(none)]> select table_name, column_name, character_set_name, collation_name from information_schema.columns where table_schema = 'gitea' and (character_set_name is not null or collation_name is not null);
+---------------------------+--------------------------------+--------------------+--------------------+
| table_name                | column_name                    | character_set_name | collation_name     |
+---------------------------+--------------------------------+--------------------+--------------------+
| milestone                 | name                           | utf8               | utf8_general_ci    |
| milestone                 | content                        | utf8               | utf8_general_ci    |
| two_factor                | secret                         | utf8               | utf8_general_ci    |
| two_factor                | scratch_salt                   | utf8               | utf8_general_ci    |
| two_factor                | scratch_hash                   | utf8               | utf8_general_ci    |
| two_factor                | last_used_passcode             | utf8               | utf8_general_ci    |
| issue                     | name                           | utf8               | utf8_general_ci    |
| issue                     | content                        | utf8               | utf8_general_ci    |
| issue                     | ref                            | utf8               | utf8_general_ci    |
| issue                     | original_author                | utf8               | utf8_general_ci    |
| repo_unit                 | config                         | utf8               | utf8_general_ci    |
| access_token              | name                           | utf8               | utf8_general_ci    |
| access_token              | token_hash                     | utf8               | utf8_general_ci    |
| access_token              | token_salt                     | utf8               | utf8_general_ci    |
| access_token              | token_last_eight               | utf8               | utf8_general_ci    |
| commit_status             | state                          | utf8               | utf8_general_ci    |
| commit_status             | sha                            | utf8               | utf8_general_ci    |
| commit_status             | target_url                     | utf8               | utf8_general_ci    |
| commit_status             | description                    | utf8               | utf8_general_ci    |
| commit_status             | context_hash                   | utf8               | utf8_general_ci    |
| commit_status             | context                        | utf8               | utf8_general_ci    |
| notification              | commit_id                      | utf8               | utf8_general_ci    |
| action                    | ref_name                       | utf8               | utf8_general_ci    |
| action                    | content                        | utf8               | utf8_general_ci    |
| release                   | tag_name                       | utf8               | utf8_general_ci    |
| release                   | lower_tag_name                 | utf8               | utf8_general_ci    |
| release                   | target                         | utf8               | utf8_general_ci    |
| release                   | title                          | utf8               | utf8_general_ci    |
| release                   | sha1                           | utf8               | utf8_general_ci    |
| release                   | note                           | utf8               | utf8_general_ci    |
| release                   | original_author                | utf8               | utf8_general_ci    |
| public_key                | name                           | utf8               | utf8_general_ci    |
| public_key                | fingerprint                    | utf8               | utf8_general_ci    |
| public_key                | content                        | utf8               | utf8_general_ci    |
| webhook                   | url                            | utf8               | utf8_general_ci    |
| webhook                   | signature                      | utf8               | utf8_general_ci    |
| webhook                   | http_method                    | utf8               | utf8_general_ci    |
| webhook                   | secret                         | utf8               | utf8_general_ci    |
| webhook                   | events                         | utf8               | utf8_general_ci    |
| webhook                   | meta                           | utf8               | utf8_general_ci    |
| project_board             | title                          | utf8mb4            | utf8mb4_general_ci |
| gpg_key_import            | key_id                         | utf8               | utf8_general_ci    |
| gpg_key_import            | content                        | utf8               | utf8_general_ci    |
| hook_task                 | uuid                           | utf8               | utf8_general_ci    |
| hook_task                 | url                            | utf8               | utf8_general_ci    |
| hook_task                 | signature                      | utf8               | utf8_general_ci    |
| hook_task                 | payload_content                | utf8               | utf8_general_ci    |
| hook_task                 | http_method                    | utf8               | utf8_general_ci    |
| hook_task                 | event_type                     | utf8               | utf8_general_ci    |
| hook_task                 | request_content                | utf8               | utf8_general_ci    |
| hook_task                 | response_content               | utf8               | utf8_general_ci    |
| pull_request              | conflicted_files               | utf8               | utf8_general_ci    |
| pull_request              | head_branch                    | utf8               | utf8_general_ci    |
| pull_request              | base_branch                    | utf8               | utf8_general_ci    |
| pull_request              | merge_base                     | utf8               | utf8_general_ci    |
| pull_request              | merged_commit_id               | utf8               | utf8_general_ci    |
| pull_request              | changed_protected_files        | utf8               | utf8_general_ci    |
| gpg_key                   | key_id                         | utf8               | utf8_general_ci    |
| gpg_key                   | primary_key_id                 | utf8               | utf8_general_ci    |
| gpg_key                   | content                        | utf8               | utf8_general_ci    |
| gpg_key                   | emails                         | utf8               | utf8_general_ci    |
| email_address             | email                          | utf8               | utf8_general_ci    |
| oauth2_authorization_code | code                           | utf8               | utf8_general_ci    |
| oauth2_authorization_code | code_challenge                 | utf8               | utf8_general_ci    |
| oauth2_authorization_code | code_challenge_method          | utf8               | utf8_general_ci    |
| oauth2_authorization_code | redirect_uri                   | utf8               | utf8_general_ci    |
| language_stat             | commit_id                      | utf8               | utf8_general_ci    |
| language_stat             | language                       | utf8               | utf8_general_ci    |
| lfs_meta_object           | oid                            | utf8               | utf8_general_ci    |
| comment                   | old_title                      | utf8               | utf8_general_ci    |
| comment                   | new_title                      | utf8               | utf8_general_ci    |
| comment                   | tree_path                      | utf8               | utf8_general_ci    |
| comment                   | content                        | utf8               | utf8_general_ci    |
| comment                   | patch                          | utf8               | utf8_general_ci    |
| comment                   | commit_sha                     | utf8               | utf8_general_ci    |
| comment                   | original_author                | utf8               | utf8_general_ci    |
| comment                   | old_ref                        | utf8               | utf8_general_ci    |
| comment                   | new_ref                        | utf8               | utf8_general_ci    |
| external_login_user       | external_id                    | utf8               | utf8_general_ci    |
| external_login_user       | raw_data                       | utf8               | utf8_general_ci    |
| external_login_user       | provider                       | utf8               | utf8_general_ci    |
| external_login_user       | email                          | utf8               | utf8_general_ci    |
| external_login_user       | name                           | utf8               | utf8_general_ci    |
| external_login_user       | first_name                     | utf8               | utf8_general_ci    |
| external_login_user       | last_name                      | utf8               | utf8_general_ci    |
| external_login_user       | nick_name                      | utf8               | utf8_general_ci    |
| external_login_user       | description                    | utf8               | utf8_general_ci    |
| external_login_user       | avatar_url                     | utf8               | utf8_general_ci    |
| external_login_user       | location                       | utf8               | utf8_general_ci    |
| external_login_user       | access_token                   | utf8               | utf8_general_ci    |
| external_login_user       | access_token_secret            | utf8               | utf8_general_ci    |
| external_login_user       | refresh_token                  | utf8               | utf8_general_ci    |
| repository                | lower_name                     | utf8               | utf8_general_ci    |
| repository                | name                           | utf8               | utf8_general_ci    |
| repository                | description                    | utf8               | utf8_general_ci    |
| repository                | website                        | utf8               | utf8_general_ci    |
| repository                | default_branch                 | utf8               | utf8_general_ci    |
| repository                | topics                         | utf8               | utf8_general_ci    |
| repository                | avatar                         | utf8               | utf8_general_ci    |
| repository                | original_url                   | utf8               | utf8_general_ci    |
| repository                | owner_name                     | utf8               | utf8_general_ci    |
| repo_indexer_status       | commit_sha                     | utf8               | utf8_general_ci    |
| email_hash                | hash                           | utf8               | utf8_general_ci    |
| email_hash                | email                          | utf8               | utf8_general_ci    |
| attachment                | uuid                           | utf8               | utf8_general_ci    |
| attachment                | name                           | utf8               | utf8_general_ci    |
| oauth2_application        | name                           | utf8               | utf8_general_ci    |
| oauth2_application        | client_id                      | utf8               | utf8_general_ci    |
| oauth2_application        | client_secret                  | utf8               | utf8_general_ci    |
| oauth2_application        | redirect_uris                  | utf8               | utf8_general_ci    |
| review                    | content                        | utf8               | utf8_general_ci    |
| review                    | commit_id                      | utf8               | utf8_general_ci    |
| review                    | original_author                | utf8               | utf8_general_ci    |
| u2f_registration          | name                           | utf8               | utf8_general_ci    |
| notice                    | description                    | utf8               | utf8_general_ci    |
| lfs_lock                  | path                           | utf8               | utf8_general_ci    |
| project                   | title                          | utf8mb4            | utf8mb4_general_ci |
| project                   | description                    | utf8mb4            | utf8mb4_general_ci |
| topic                     | name                           | utf8mb4            | utf8mb4_general_ci |
| protected_branch          | branch_name                    | utf8               | utf8_general_ci    |
| protected_branch          | whitelist_user_i_ds            | utf8               | utf8_general_ci    |
| protected_branch          | whitelist_team_i_ds            | utf8               | utf8_general_ci    |
| protected_branch          | merge_whitelist_user_i_ds      | utf8               | utf8_general_ci    |
| protected_branch          | merge_whitelist_team_i_ds      | utf8               | utf8_general_ci    |
| protected_branch          | approvals_whitelist_user_i_ds  | utf8               | utf8_general_ci    |
| protected_branch          | approvals_whitelist_team_i_ds  | utf8               | utf8_general_ci    |
| protected_branch          | status_check_contexts          | utf8               | utf8_general_ci    |
| protected_branch          | protected_file_patterns        | utf8               | utf8_general_ci    |
| user                      | lower_name                     | utf8               | utf8_general_ci    |
| user                      | name                           | utf8               | utf8_general_ci    |
| user                      | full_name                      | utf8               | utf8_general_ci    |
| user                      | email                          | utf8               | utf8_general_ci    |
| user                      | passwd                         | utf8               | utf8_general_ci    |
| user                      | login_name                     | utf8               | utf8_general_ci    |
| user                      | location                       | utf8               | utf8_general_ci    |
| user                      | website                        | utf8               | utf8_general_ci    |
| user                      | rands                          | utf8               | utf8_general_ci    |
| user                      | salt                           | utf8               | utf8_general_ci    |
| user                      | language                       | utf8               | utf8_general_ci    |
| user                      | description                    | utf8               | utf8_general_ci    |
| user                      | avatar                         | utf8               | utf8_general_ci    |
| user                      | avatar_email                   | utf8               | utf8_general_ci    |
| user                      | diff_view_style                | utf8               | utf8_general_ci    |
| user                      | theme                          | utf8               | utf8_general_ci    |
| user                      | email_notifications_preference | utf8               | utf8_general_ci    |
| user                      | passwd_hash_algo               | utf8               | utf8_general_ci    |
| team                      | lower_name                     | utf8               | utf8_general_ci    |
| team                      | name                           | utf8               | utf8_general_ci    |
| team                      | description                    | utf8               | utf8_general_ci    |
| deleted_branch            | name                           | utf8               | utf8_general_ci    |
| deleted_branch            | commit                         | utf8               | utf8_general_ci    |
| task                      | payload_content                | utf8               | utf8_general_ci    |
| task                      | errors                         | utf8               | utf8_general_ci    |
| oauth2_session            | id                             | utf8               | utf8_general_ci    |
| oauth2_session            | data                           | utf8               | utf8_general_ci    |
| repo_redirect             | lower_name                     | utf8               | utf8_general_ci    |
| deploy_key                | name                           | utf8               | utf8_general_ci    |
| deploy_key                | fingerprint                    | utf8               | utf8_general_ci    |
| login_source              | name                           | utf8               | utf8_general_ci    |
| login_source              | cfg                            | utf8               | utf8_general_ci    |
| user_open_id              | uri                            | utf8               | utf8_general_ci    |
| upload                    | uuid                           | utf8               | utf8_general_ci    |
| upload                    | name                           | utf8               | utf8_general_ci    |
| label                     | name                           | utf8               | utf8_general_ci    |
| label                     | description                    | utf8               | utf8_general_ci    |
| label                     | color                          | utf8               | utf8_general_ci    |
| reaction                  | type                           | utf8               | utf8_general_ci    |
| reaction                  | original_author                | utf8               | utf8_general_ci    |
+---------------------------+--------------------------------+--------------------+--------------------+
168 rows in set (0.001 sec)
Originally created by @aanderse on GitHub (Dec 10, 2020). ## Description Exactly [this](https://github.com/go-gitea/gitea/issues/9336) issue. I would like to know how to fix an **existing** install. Even after correcting the default character set and collation the existing tables and columns have an incorrect character set+collation, so what columns should be updated to use the `utf8mb4` character set? ## Screenshots ``` MariaDB [(none)]> select table_name, column_name, character_set_name, collation_name from information_schema.columns where table_schema = 'gitea' and (character_set_name is not null or collation_name is not null); +---------------------------+--------------------------------+--------------------+--------------------+ | table_name | column_name | character_set_name | collation_name | +---------------------------+--------------------------------+--------------------+--------------------+ | milestone | name | utf8 | utf8_general_ci | | milestone | content | utf8 | utf8_general_ci | | two_factor | secret | utf8 | utf8_general_ci | | two_factor | scratch_salt | utf8 | utf8_general_ci | | two_factor | scratch_hash | utf8 | utf8_general_ci | | two_factor | last_used_passcode | utf8 | utf8_general_ci | | issue | name | utf8 | utf8_general_ci | | issue | content | utf8 | utf8_general_ci | | issue | ref | utf8 | utf8_general_ci | | issue | original_author | utf8 | utf8_general_ci | | repo_unit | config | utf8 | utf8_general_ci | | access_token | name | utf8 | utf8_general_ci | | access_token | token_hash | utf8 | utf8_general_ci | | access_token | token_salt | utf8 | utf8_general_ci | | access_token | token_last_eight | utf8 | utf8_general_ci | | commit_status | state | utf8 | utf8_general_ci | | commit_status | sha | utf8 | utf8_general_ci | | commit_status | target_url | utf8 | utf8_general_ci | | commit_status | description | utf8 | utf8_general_ci | | commit_status | context_hash | utf8 | utf8_general_ci | | commit_status | context | utf8 | utf8_general_ci | | notification | commit_id | utf8 | utf8_general_ci | | action | ref_name | utf8 | utf8_general_ci | | action | content | utf8 | utf8_general_ci | | release | tag_name | utf8 | utf8_general_ci | | release | lower_tag_name | utf8 | utf8_general_ci | | release | target | utf8 | utf8_general_ci | | release | title | utf8 | utf8_general_ci | | release | sha1 | utf8 | utf8_general_ci | | release | note | utf8 | utf8_general_ci | | release | original_author | utf8 | utf8_general_ci | | public_key | name | utf8 | utf8_general_ci | | public_key | fingerprint | utf8 | utf8_general_ci | | public_key | content | utf8 | utf8_general_ci | | webhook | url | utf8 | utf8_general_ci | | webhook | signature | utf8 | utf8_general_ci | | webhook | http_method | utf8 | utf8_general_ci | | webhook | secret | utf8 | utf8_general_ci | | webhook | events | utf8 | utf8_general_ci | | webhook | meta | utf8 | utf8_general_ci | | project_board | title | utf8mb4 | utf8mb4_general_ci | | gpg_key_import | key_id | utf8 | utf8_general_ci | | gpg_key_import | content | utf8 | utf8_general_ci | | hook_task | uuid | utf8 | utf8_general_ci | | hook_task | url | utf8 | utf8_general_ci | | hook_task | signature | utf8 | utf8_general_ci | | hook_task | payload_content | utf8 | utf8_general_ci | | hook_task | http_method | utf8 | utf8_general_ci | | hook_task | event_type | utf8 | utf8_general_ci | | hook_task | request_content | utf8 | utf8_general_ci | | hook_task | response_content | utf8 | utf8_general_ci | | pull_request | conflicted_files | utf8 | utf8_general_ci | | pull_request | head_branch | utf8 | utf8_general_ci | | pull_request | base_branch | utf8 | utf8_general_ci | | pull_request | merge_base | utf8 | utf8_general_ci | | pull_request | merged_commit_id | utf8 | utf8_general_ci | | pull_request | changed_protected_files | utf8 | utf8_general_ci | | gpg_key | key_id | utf8 | utf8_general_ci | | gpg_key | primary_key_id | utf8 | utf8_general_ci | | gpg_key | content | utf8 | utf8_general_ci | | gpg_key | emails | utf8 | utf8_general_ci | | email_address | email | utf8 | utf8_general_ci | | oauth2_authorization_code | code | utf8 | utf8_general_ci | | oauth2_authorization_code | code_challenge | utf8 | utf8_general_ci | | oauth2_authorization_code | code_challenge_method | utf8 | utf8_general_ci | | oauth2_authorization_code | redirect_uri | utf8 | utf8_general_ci | | language_stat | commit_id | utf8 | utf8_general_ci | | language_stat | language | utf8 | utf8_general_ci | | lfs_meta_object | oid | utf8 | utf8_general_ci | | comment | old_title | utf8 | utf8_general_ci | | comment | new_title | utf8 | utf8_general_ci | | comment | tree_path | utf8 | utf8_general_ci | | comment | content | utf8 | utf8_general_ci | | comment | patch | utf8 | utf8_general_ci | | comment | commit_sha | utf8 | utf8_general_ci | | comment | original_author | utf8 | utf8_general_ci | | comment | old_ref | utf8 | utf8_general_ci | | comment | new_ref | utf8 | utf8_general_ci | | external_login_user | external_id | utf8 | utf8_general_ci | | external_login_user | raw_data | utf8 | utf8_general_ci | | external_login_user | provider | utf8 | utf8_general_ci | | external_login_user | email | utf8 | utf8_general_ci | | external_login_user | name | utf8 | utf8_general_ci | | external_login_user | first_name | utf8 | utf8_general_ci | | external_login_user | last_name | utf8 | utf8_general_ci | | external_login_user | nick_name | utf8 | utf8_general_ci | | external_login_user | description | utf8 | utf8_general_ci | | external_login_user | avatar_url | utf8 | utf8_general_ci | | external_login_user | location | utf8 | utf8_general_ci | | external_login_user | access_token | utf8 | utf8_general_ci | | external_login_user | access_token_secret | utf8 | utf8_general_ci | | external_login_user | refresh_token | utf8 | utf8_general_ci | | repository | lower_name | utf8 | utf8_general_ci | | repository | name | utf8 | utf8_general_ci | | repository | description | utf8 | utf8_general_ci | | repository | website | utf8 | utf8_general_ci | | repository | default_branch | utf8 | utf8_general_ci | | repository | topics | utf8 | utf8_general_ci | | repository | avatar | utf8 | utf8_general_ci | | repository | original_url | utf8 | utf8_general_ci | | repository | owner_name | utf8 | utf8_general_ci | | repo_indexer_status | commit_sha | utf8 | utf8_general_ci | | email_hash | hash | utf8 | utf8_general_ci | | email_hash | email | utf8 | utf8_general_ci | | attachment | uuid | utf8 | utf8_general_ci | | attachment | name | utf8 | utf8_general_ci | | oauth2_application | name | utf8 | utf8_general_ci | | oauth2_application | client_id | utf8 | utf8_general_ci | | oauth2_application | client_secret | utf8 | utf8_general_ci | | oauth2_application | redirect_uris | utf8 | utf8_general_ci | | review | content | utf8 | utf8_general_ci | | review | commit_id | utf8 | utf8_general_ci | | review | original_author | utf8 | utf8_general_ci | | u2f_registration | name | utf8 | utf8_general_ci | | notice | description | utf8 | utf8_general_ci | | lfs_lock | path | utf8 | utf8_general_ci | | project | title | utf8mb4 | utf8mb4_general_ci | | project | description | utf8mb4 | utf8mb4_general_ci | | topic | name | utf8mb4 | utf8mb4_general_ci | | protected_branch | branch_name | utf8 | utf8_general_ci | | protected_branch | whitelist_user_i_ds | utf8 | utf8_general_ci | | protected_branch | whitelist_team_i_ds | utf8 | utf8_general_ci | | protected_branch | merge_whitelist_user_i_ds | utf8 | utf8_general_ci | | protected_branch | merge_whitelist_team_i_ds | utf8 | utf8_general_ci | | protected_branch | approvals_whitelist_user_i_ds | utf8 | utf8_general_ci | | protected_branch | approvals_whitelist_team_i_ds | utf8 | utf8_general_ci | | protected_branch | status_check_contexts | utf8 | utf8_general_ci | | protected_branch | protected_file_patterns | utf8 | utf8_general_ci | | user | lower_name | utf8 | utf8_general_ci | | user | name | utf8 | utf8_general_ci | | user | full_name | utf8 | utf8_general_ci | | user | email | utf8 | utf8_general_ci | | user | passwd | utf8 | utf8_general_ci | | user | login_name | utf8 | utf8_general_ci | | user | location | utf8 | utf8_general_ci | | user | website | utf8 | utf8_general_ci | | user | rands | utf8 | utf8_general_ci | | user | salt | utf8 | utf8_general_ci | | user | language | utf8 | utf8_general_ci | | user | description | utf8 | utf8_general_ci | | user | avatar | utf8 | utf8_general_ci | | user | avatar_email | utf8 | utf8_general_ci | | user | diff_view_style | utf8 | utf8_general_ci | | user | theme | utf8 | utf8_general_ci | | user | email_notifications_preference | utf8 | utf8_general_ci | | user | passwd_hash_algo | utf8 | utf8_general_ci | | team | lower_name | utf8 | utf8_general_ci | | team | name | utf8 | utf8_general_ci | | team | description | utf8 | utf8_general_ci | | deleted_branch | name | utf8 | utf8_general_ci | | deleted_branch | commit | utf8 | utf8_general_ci | | task | payload_content | utf8 | utf8_general_ci | | task | errors | utf8 | utf8_general_ci | | oauth2_session | id | utf8 | utf8_general_ci | | oauth2_session | data | utf8 | utf8_general_ci | | repo_redirect | lower_name | utf8 | utf8_general_ci | | deploy_key | name | utf8 | utf8_general_ci | | deploy_key | fingerprint | utf8 | utf8_general_ci | | login_source | name | utf8 | utf8_general_ci | | login_source | cfg | utf8 | utf8_general_ci | | user_open_id | uri | utf8 | utf8_general_ci | | upload | uuid | utf8 | utf8_general_ci | | upload | name | utf8 | utf8_general_ci | | label | name | utf8 | utf8_general_ci | | label | description | utf8 | utf8_general_ci | | label | color | utf8 | utf8_general_ci | | reaction | type | utf8 | utf8_general_ci | | reaction | original_author | utf8 | utf8_general_ci | +---------------------------+--------------------------------+--------------------+--------------------+ 168 rows in set (0.001 sec) ```
Author
Owner

@jolheiser commented on GitHub (Dec 10, 2020):

There is the convert command, and also make sure your database config has CHARSET set to utf8mb4

@jolheiser commented on GitHub (Dec 10, 2020): There is the [convert](https://docs.gitea.io/en-us/command-line/#convert) command, and also make sure your [database config](https://docs.gitea.io/en-us/config-cheat-sheet/#database-database) has `CHARSET` set to `utf8mb4`
Author
Owner

@aanderse commented on GitHub (Dec 10, 2020):

Wonderful! Thank you @jolheiser 🎉

@aanderse commented on GitHub (Dec 10, 2020): Wonderful! Thank you @jolheiser :tada:
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/gitea#6497