Can't use emoji with MySQL v8.0 utf8mb4_unicode_ci or utf8mb4_0900_ai_ci

[SALTWOOD]

Can the issue be reproduced with the latest available release? Yes. In Gotify@2.6.1

Which one is the environment gotify server is running in?

• Docker
• Linux machine
• Windows machine

Docker startup command or config file here (please mask sensitive information)

services:
  gotify:
    image: gotify/server
    container_name: "gotify"
    restart: always
    networks:
      - shared_external_network
    ports:
      - "127.0.0.1:8688:80"
    environment:
      - GOTIFY_DATABASE_DIALECT=mysql
      - GOTIFY_DATABASE_CONNECTION=hidden:hidden@tcp(mysql_docker_instance:3306)/gotifydb?charset=utf8&parseTime=True&loc=Local
      - TZ=Asia/Shanghai
    volumes:
      - "./gotify_data:/app/data"

networks:
  shared_external_network:
    external: true # To connect to a mysql instance

Do you have an reverse proxy installed in front of gotify server? (Please select None if the problem can be reproduced without the presense of a reverse proxy)

• None
• Nginx
• Apache
• Caddy

Reverse proxy configuration (please mask sensitive information)

server {
    listen 8686 ssl;
    http2 on;
    listen 8686 quic reuseport;
    error_page 497 https://$host:$server_port$request_uri;
    ssl_protocols TLSv1.2 TLSv1.3;
    ssl_certificate /www/cert/cert.pem;
    ssl_certificate_key /www/cert/key.pem;

    server_name saltwood.top *.saltwood.top;

    location / {
        # We set up the reverse proxy
        proxy_pass         http://127.0.0.1:8688;
        proxy_http_version 1.1;

        # Ensuring it can use websockets
        proxy_set_header   Upgrade $http_upgrade;
        proxy_set_header   Connection "upgrade";
        proxy_set_header   X-Real-IP $remote_addr;
        proxy_set_header   X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header   X-Forwarded-Proto http;
        proxy_redirect     http:// $scheme://;

        # The proxy must preserve the host because gotify verifies the host with the origin
        # for WebSocket connections
        proxy_set_header   Host $http_host;

        # These sets the timeout so that the websocket can stay alive
        proxy_connect_timeout   1m;
        proxy_send_timeout      1m;
        proxy_read_timeout      1m;
    }
}%

On which client do you experience problems? (Select as many as you can see)

• WebUI
• gotify-cli
• Android Client
• 3rd-party API call (Please include your code)

What did you do?
I posted a request to /message with a token and a message body that contains an emoji like "😋"

What did you expect to see?
200 OK, and I should be able to see the message on the WebUI.

What did you see instead? (Include screenshots, android logcat/request dumps if possible)
500 Internal server error,

{"error":"Internal Server Error","errorCode":500,"errorDescription":"Error 3988: Conversion from collation utf8mb3_general_ci into utf8mb4_0900_ai_ci impossible for parameter"}

After manually changed the encoding for gotifydb.messages, it becomes to be:

{"error":"Internal Server Error","errorCode":500,"errorDescription":"Error 3988: Conversion from collation utf8mb3_general_ci into utf8mb4_unicode_ci impossible for parameter"}

Addition:
I used the following code, gotify-cli and SMSForwarder to make a test.

req = requests.post("https://example.com:xxxx/message?token=Hidden", data = f"title=&message=This%20is%20a%20test.%F0%9F%98%8B&priority=1", headers = {"Content-Type": "application/x-www-form-urlencoded"})

[eternal-flame-AD]

Some emojis are four byte UTF-8 (f0 9f 98 8b for 😋) but in MySQL (and some other database speak) 'utf8' only means up to 3 bytes long. This works for me ( reference ):

GOTIFY_DATABASE_CONNECTION=gotify:gotify@tcp(localhost:3306)/gotify?charset=utf8mb4,utf8&parseTime=True&loc=Local

Depending on the MySQL version you are using you may need to change the server-side setting too if you are not up to date, but since you mentioned you already changed it I assume it is already in utf8mb4 support.

[SALTWOOD]

My problem was successfully resolved using the method you provided. Thank you!