Database encoding problems

[Robin]

Long story short, I've got some problems with encoding.

See the following Github issue: https://github.com/go-gitea/gitea/issues/16016
Which links to the following solution: Are you using MySQL? If so you probably need to convert the tables to utf8mb4, gitea convert can help with that but take a backup before doing it.

I don't remember enough about MySQL anymore to know how to look into this myself, but I'm happy to try help test if you want to try hold my hand, @girish

[girish]

@robin What's a good way to reproduce/test this? Just paste some emojis?

Cloudron uses utf8mb4 by default in mysql. So, the issue is probably something else.

[girish]

On a new gitea install:

mysql> SHOW VARIABLES LIKE "character_set_database";
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.02 sec)

mysql> show create table webhook;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| webhook | CREATE TABLE `webhook` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `repo_id` bigint DEFAULT NULL,
  `org_id` bigint DEFAULT NULL,
  `is_system_webhook` tinyint(1) DEFAULT NULL,
  `url` text,
  `signature` text,
  `http_method` varchar(255) DEFAULT NULL,
  `content_type` int DEFAULT NULL,
  `secret` text,
  `events` text,
  `is_ssl` tinyint(1) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT NULL,
  `type` varchar(16) DEFAULT NULL,
  `meta` text,
  `last_status` int DEFAULT NULL,
  `created_unix` bigint DEFAULT NULL,
  `updated_unix` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_webhook_org_id` (`org_id`),
  KEY `IDX_webhook_is_active` (`is_active`),
  KEY `IDX_webhook_created_unix` (`created_unix`),
  KEY `IDX_webhook_updated_unix` (`updated_unix`),
  KEY `IDX_webhook_repo_id` (`repo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[Robin]

My existing install has this:

ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |

So perhaps this might have been a bug that they corrected in a release sometime after I had already installed?

[Robin]

Searching around their issues, I find this: https://github.com/go-gitea/gitea/issues/11081

[girish]

@robin indeed, looks like old installations had incorrect encoding. Does that gitea convert help? I don't know if ALTER TABLE works. Something like this : ALTER TABLE webhooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

[Robin]

@girish Yeah, I ran:

/home/git/gitea/gitea convert -c /run/gitea/app.ini as user git, and now things seem converted:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC

On the downside, the convert tool seems to unconditionally convert, and is quite slow, so I guess it can't easily be run as part of the startup process or something