為什麼修改 MySQL 的 character_set_server 後仍需重啟？從 mysql-connector-net 探討字元集的陷阱

在近期處理一個與 MySQL 字元集相關的問題時，我深入研究了 MySQL Server 的 Handshake 機制以及 mysql-connector-net 原始碼，發現了一個容易被忽略但可能會造成重大錯誤的細節——即使 character_set_server 是動態參數，但實際上修改後仍需要重啟 MySQL Server，否則會造成驅動端的解碼錯誤。

問題背景：為什麼驅動程式仍使用舊的字元集？

根據 MySQL Server 的設計，當 client 端連線時，Server 會在 Handshake Initial Packet 中回傳一些基本資訊，其中就包括伺服器的預設字元集（character_set_server）。這段資訊是透過以下的程式碼取得：

🔗 MySQL Source Code 參考連結

1

packet->append_int1(default_charset_info->number);

根據 MySQL 官方文件，這段資訊會被封裝在 Handshake v10 Protocol 中傳送（官方文件）。

然而，這個值其實是在 MySQL Server 啟動時就被載入的，即使你在執行中動態修改 character_set_server，重新連線後 greeting packet 裡的字元集值仍然不會更新。

驅動程式的行為：根據 Greeting 決定後續欄位解碼

Here’s a well-structured draft for your technical blog post based on the provided Rust + pgrx Foreign Data Wrapper (FDW) code.

🚀 Building a Simple PostgreSQL FDW with Rust and pgrx

PostgreSQL Foreign Data Wrappers (FDW) enable PostgreSQL to query external data sources as if they were regular tables. Traditionally, FDWs are written in C, but with pgrx, we can now build PostgreSQL extensions — including FDWs — in Rust, unlocking safety and modern tooling.

In this post, we’ll walk through creating a simple FDW using Rust and pgrx that simulates reading rows from an external source (e.g., Redis or API). While it’s a stub, it demonstrates how to implement the core FDW lifecycle.

🛠️ What Can You Build with pgrx?

• ✅ SQL Functions: Scalar, aggregate, and set-returning functions.
• ✅ Custom Types: Define composite types or enums in Rust.
• ✅ Foreign Data Wrappers (FDWs): Like the one in your example — connect PostgreSQL to external systems (Redis, APIs, file systems, etc.).
• ✅ Index Access Methods: Implement new index types.
• ✅ Background Workers: Run tasks in the background inside PostgreSQL.
• ✅ Hooks: Intercept or modify PostgreSQL internal behavior (like planner or executor hooks).

🌐 Under the hood:

• PostgreSQL communicates via C APIs.
• pgrx provides Rust-safe bindings to these APIs.
• Memory management is handled carefully via PgMemoryContexts, matching PostgreSQL’s memory context model.
• Rust functions are exposed to PostgreSQL as SQL-callable functions with the #[pg_extern] macro.

🏗️ Key Components of an default_fdw

PostgreSQL FDWs consist of several callback functions that handle different phases of query planning and execution:

什麼是 MCP?

MCP(Model Context Protocol)是一種協定，用於在工具之間進行通訊與協作。透過 MCP，可以讓各種獨立的工具(如模型、插件、服務)以一致的格式互相交換資料與指令。MCP Server 是提供特定功能的伺服器端程式，能與支援 MCP 的前端進行互動。

Weather MCP Server 是什麼？

Weather MCP Server 是一個基於 MCP 協定開發的天氣資訊伺服器，利用 Open-Meteo API 提供免費的天氣資料。透過這個伺服器，你可以查詢：

• 某城市的即時天氣資訊
• 某城市在特定時間範圍內的天氣預報
• 指定時區的目前時間

mcp_weather_server source code
smithery AI

使用此 MCP Server 搭配 AI Model 可以輕易搭建出即時天氣小助手, 如下我的 AI Bot

功能特色

• 查詢指定城市的即時天氣
• 查詢指定日期區間的天氣預測
• 查詢目前時間(支援指定時區)

簡介

最近我開發了 MessageWorkerPool 專案。其主要概念是提供一個平台框架，使使用者能夠快速且輕鬆地在 Worker 內實作邏輯。該設計高度靈活，允許基於我創建的 Worker 通訊協議，以多種程式語言實作 Worker。目前，我已提供使用 C#、Rust 和 Python 編寫的 Worker 範例。

這個函式庫在多進程環境中處理任務表現優異。此外，它還支援優雅關閉 (graceful shutdown)，確保在隨時 consumer worker 能順利終止處理程序。

MessageWorkerPool GitHub

為什麼選擇 ProcessPool 而非 ThreadPool?

當你需要強大的隔離性，以防止某個任務影響其他任務時，應該選擇 ProcessPool，特別是針對關鍵操作或容易崩潰的任務。雖然 ThreadPool 較為輕量（因為執行緒共用記憶體並且具有較低的上下文切換開銷），但 ProcessPool 能夠提供更靈活的解決方案，允許使用不同的程式語言來實作 Worker。

安裝

要安裝 MessageWorkerPool 套件，請使用以下 NuGet 指令：

1

PM > Install-Package MessageWorkerPool

若要手動安裝此函式庫，可克隆儲存庫並建置專案：

Thank you for the clarification! Based on the actual purpose of your project — a Linux sandboxing tool using OverlayFS, cgroups, and namespaces — here’s a corrected and well-structured Markdown tech blog post introducing RustBox:

🧪 Introducing RustBox: Lightweight Linux Sandboxing in Pure Rust

Are you looking for a secure way to run untrusted programs on Linux, or want to learn how containers isolate processes using kernel features?

Meet RustBox — a minimal, educational, and practical sandboxing tool built entirely in Rust. Powered by OverlayFS, cgroups v2, and Linux namespaces, it lets you isolate processes with fine-grained control — just like Docker, but lightweight and hackable.

🚀 What Is RustBox?

RustBox is a lightweight sandboxing utility that isolates and constrains programs in a secure environment using:

• 🗂 OverlayFS for ephemeral and isolated filesystems
• 🧠 cgroups v2 for memory limits
• 🔐 Linux namespaces for process, network, user, and IPC isolation
• 🦀 Written in Rust (safe and unsafe), with nix and std — no external runtimes or daemons

This project is ideal for:

• Running untrusted or potentially harmful code
• Educational use to learn Linux sandbox internals
• Building lightweight, Docker-like containers without the overhead

🧰 Features

introduce

If you’ve ever struggled to instrument legacy apps or non-standard services for observability, OpenTelemetry_SideCar is here to help. This project offers a non-intrusive way to export metrics and traces via OpenTelemetry using a sidecar approach — no SDK required in your main app.

🌐 Project Overview

📦 Repo: isdaniel/OpenTelemetry_SideCar

OpenTelemetry_SideCar is a standalone proxy service that collects metrics and traces outside of your application and forwards them to a telemetry backend (e.g., Prometheus, Jaeger, or Azure Monitor).

💡 Why a Sidecar?

In cloud-native systems, a sidecar is a helper container or process that runs alongside your main application. It can observe, extend, or enhance app behavior without changing application code. This pattern is ideal for adding observability when:

• You can’t modify the original code (e.g., closed-source, legacy binaries).
• You want to centralize telemetry logic.
• You’re aiming for a unified instrumentation strategy.

Foreword

TLS (Transport Layer Security) is a cryptographic protocol that provides secure communication over a network, commonly used to secure HTTP traffic (i.e., HTTPS). Here’s a high-level overview of the TLS workflow, which includes handshake and data transfer phases.

After TCP handshake, it will execute TLS handshake if client require.

Below image is my experiment TLS (TLS 1.2) workflow from PostgreSQL server, Red-frame represent TCP 3 handshake, and yellow-frame represent TLS handshake.

In the beginning, client will send a request to require sslmode connection (SSL/TLS), if server support it will reply (‘S’).

Eventually, processing below steps to do TLS handshake.

1. ClientHello → 2. ServerHello → 3. Server Certificate → 4. ServerHelloDone → 5. Client Key Exchange

TLS work-flow

Foreword

In PostgreSQL, there isn’t a native foreach loop construct in C, because C itself doesn’t have a foreach loop as you might find in higher-level languages like Python or PHP. However, PostgreSQL often implements loop iterations over elements using Macros that simplify the handling of data structures, such as linked lists, which are commonly used within its codebase.

Common Loop Macros in PostgreSQL

1. lfirst(lc):

   • This macro retrieves the data stored in a ListCell. The ListCell structure typically contains a union that can hold various types of pointers (like void*, int, etc.). The ptr_value is a generic pointer that can point to any node or structure, and lfirst simply casts it back from the void *.

2. lfirst_node(type, lc):

   • This macro is used when the list elements are known to be of a specific node type, which is common in the parser and planner where lists often contain specific types of nodes (e.g., expression or plan nodes). lfirst_node uses castNode to cast the pointer retrieved by lfirst to the specified type, ensuring type safety and readability in the code.

3. castNode(_type_, nodeptr):

   • A simple cast to the specified type _type_. It enhances readability and ensures that the casting is explicit in the code, which is crucial for understanding that a type conversion is taking place, particularly when navigating complex data structures common in PostgreSQL’s internals.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#define lfirst(lc)				((lc)->ptr_value)
#define lfirst_node(type,lc)	castNode(type, lfirst(lc))
#define castNode(_type_, nodeptr) ((_type_ *) (nodeptr))
#define true	1
#define false	0
#define foreach(cell, lst)	\
	for (ForEachState cell##__state = {(lst), 0}; \
		 (cell##__state.l != NIL && \
		  cell##__state.i < cell##__state.l->length) ? \
		 (cell = &cell##__state.l->elements[cell##__state.i], true) : \
		 (cell = NULL, false); \
		 cell##__state.i++)

#define NIL						((List *) NULL)

The ListCell union consists of a single member, ptr_value, which is a generic pointer (void *).

This pointer can hold a reference to any type of data, allowing for flexibility in what kind of data the list can contain.
This structure is useful for managing lists of generic data types.

The List structure represents a dynamic list in PostgreSQL.
It contains:

• length: An integer that specifies the current number of elements in the list.
• elements: A pointer to an array of ListCell elements, which holds the actual data in the list. This array can be re-allocated as the list grows or shrinks, allowing for dynamic resizing.
• The comment suggests that sometimes ListCell elements may be allocated directly alongside the List structure itself. This can optimize memory usage and improve performance.

1
2
3
4
5
6
7
8
9
10

typedef union ListCell
{
	void	   *ptr_value;
} ListCell;

typedef struct List
{
	int			length;			/* number of elements currently present */
	ListCell   *elements;		/* re-allocatable array of cells */
} List;

Foreword

Using gdb for command-line debugging still feels inconvenient. I initially wanted to find a simpler way to directly debug the PostgreSQL source code under Windows. After searching for a while, I found that Visual Studio (VS) was the only option available, but it is heavy and the steps are quite complex. Since most real environments run on Linux, it is better to debug the PostgreSQL source code under Linux.

How to build & install PostgreSQL from source code.

I used Ubuntu Linux environment, the first step we might need to install pre-requirement tool for PostgreSQL build.

1
2

sudo apt-get update
sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache libsystemd-dev -y

download PostgreSQL source code.

1
2
3

wget https://ftp.postgresql.org/pub/source/v14.8/postgresql-14.8.tar.gz
tar xvfz postgresql-14.8.tar.gz
cd postgresql-14.8

we would need to make sure the path (--prefix) exist in your system.

1
2
3
4
5
6
7
8

./configure --prefix=/home/daniel/postgresql-14.8/pgsql --with-icu --with-openssl --with-systemd --with-libxml --enable-debug

#or debug -g3 mode

./configure --prefix=/home/daniel/postgresql-14.8/pgsql --with-icu --with-openssl --with-systemd --with-libxml --enable-debug CFLAGS="-DGCC_HASCLASSVISIBILITY -O0 -Wall -W -g3 -gdwarf-2"

make -j 8
make install

we must build with --enable-debug parameter, otherwise we can’t debug with our source code.

Introduction

This ariticle will guide us how to do long-term backup (more than 35 days) on Azure PostgreSQL Flexible.

Prerequisites:

• A Postgres flexible server and an Azure VM (Linux (ubuntu 20.04)) that has access to it.
• A MI (Managed Identity) in your subscription.
• Please kindly make sure backup Postgres flexible server version align with pg_dump version.

Here is the sample code of Azure PostgreSQL Flexible long term backup with Managed Identity.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

echo 'start program'
echo '====================='
echo 'start installing postgresql client suit'
sudo apt update
sudo apt -y install postgresql-client
echo '======================'
echo 'postgresql client install ends'
echo 'start mount storage'

wget https://packages.microsoft.com/config/ubuntu/20.04/packages-microsoft-prod.deb
sudo dpkg -i packages-microsoft-prod.deb
sudo apt-get update
sudo apt-get install blobfuse -y
sudo apt-get install jq -y

if [ ! -f blob.conf  ]; then
    echo 'accountName <<Your blob accountName>>' >> blob.conf 
	echo 'authType MSI' >> fuse_connection.cfg
	echo 'identityObjectId <<Your blob accountKey>>' >> blob.conf 
	echo 'containerName <<Your blob Container Name>>' >> blob.conf 
fi
#create a folder which can mount to blob
mkdir ~/data
sudo blobfuse ~/data --tmp-path=/mnt/resource/mycontainer  --config-file=./blob.conf -o attr_timeout=240 -o entry_timeout=240 -o negative_timeout=120

echo '======= starting backup============'
DNAME=`date +%Y%m%d%H%M%S`
export PGPASSWORD=`curl -s 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fossrdbms-aad.database.windows.net&client_id=<<MI client ID>>' -H Metadata:true | jq -r .access_token`
pg_dump --host=test-conn.postgres.database.azure.com --username='MI-Demo' -Fc -c  testdb > ~/data/dump$DNAME.sql

Experiments:

Here is the guideline for using managed identity to connect to your DB server in VM.

We would make sure the Authentication setting that was PostgreSQL and Azure Active Directory authentication.