Chao Shu Shen

Posted on Dec 15

A UTF-8 Trap I Hit While Building a Customer Support Chat System

#devjournal #programming #softwareengineering

For years, I’ve been building an independent customer support chat system in my spare time.

It didn’t start big. In the early days, only a handful of users tried it out of curiosity. Over time, it slowly grew into something real: a system running in production, used daily, deployed both as a hosted service and in on-premises environments. Along the way, I learned a lot—not just about writing code, but about operating and maintaining a long-lived product.

More than once, after releasing a new version, I caught myself thinking:

“This one is rock solid. Nothing can break it.”

And almost every time, reality responded a few weeks later with:

“Wait… how is this even possible?”

A few years ago, those moments happened all the time.
This year, only occasionally.
And recently, after upgrading several customer environments to the latest version without a single incident, I genuinely thought:

“Alright. This might finally be stable.”

Even my own production environment had changed.
User numbers were growing. Concurrent visitors were increasing.
Yet bug reports had almost disappeared.

That’s usually when you get careless.

“Why Is Yesterday’s Chat History Completely Gone?”

The report came in quietly:

“Why are yesterday’s conversations missing?”

No crash.
No alerts.
No errors reported by the application.

I started digging through logs late at night and found something unsettling:
the database was returning zero records—cleanly, normally. No timeout. No permission error.

It was as if the conversation had never existed.

Scrolling further back, I noticed something interesting.
The last message sent by the agent was:

“Alright, please wait a moment 🥲”

That single emoji turned out to be the culprit.

One small character had silently destroyed the entire chat record.

The Symptom: INSERT Looked Successful, but Data Was Gone

At first glance, everything looked fine.
The application logged “message saved successfully”.
No exception bubbled up.

But under the hood, the SQL statement had failed.

Here’s how the table was originally created:

CREATE TABLE chat_message (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    visitor_id VARCHAR(50),
    content TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
    created_at DATETIME
);

Looks reasonable, right?

Until a user sends a message containing a 4-byte character—like an emoji.

INSERT INTO chat_message (visitor_id, content, created_at)
VALUES ('A123', 'Alright, please wait 🥲', NOW());
-- Error: Incorrect string value: '\xF0\x9F\xA5\xB2' for column 'content'

The insert failed.
But the application didn’t notice.

The Hidden Problem: “utf8” in MySQL Is Not UTF-8

This is the trap.

In MySQL, utf8 is not full UTF-8.
It only supports 1–3 byte characters (the BMP).

Emoji live outside that range.

🥲 = U+1F972
UTF-8 bytes = F0 9F A5 B2

Result: the database rejects the value.

And here’s where it got worse.

The “Fake Success” at the Application Layer

I was using a .NET MySQL connector with default settings.
Combined with incomplete exception handling, the driver swallowed the failure.

try
{
    await db.ExecuteAsync(
        "INSERT INTO chat_message (visitor_id, content, created_at) VALUES (@v, @c, @t)",
        new { v = visitorId, c = content, t = DateTime.UtcNow }
    );
    logger.Info("Message stored: " + content);
}
catch (Exception ex)
{
    // Only logging ex.Message, no SQL error code
    logger.Warn("Message error: " + ex.Message);
}

From the application’s point of view: success.
From the database’s point of view: nothing was saved.

So the agent thought the message was stored.
The next day, they opened the chat history—and found nothing.

The Fix: Switching Everything to utf8mb4

The solution itself was straightforward, once the root cause was clear.

Step 1: Update database and tables

ALTER DATABASE mychat
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

ALTER TABLE chat_message
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Step 2: Explicitly set charset in the connection string

var connStr =
  "Server=localhost;Database=mychat;Uid=root;Pwd=xxx;CharSet=utf8mb4;";

Step 3: Verify with real emoji data

var testMessage = "Welcome to our support system 🥳🔥";

await db.ExecuteAsync(
    "INSERT INTO chat_message (visitor_id, content, created_at) VALUES (@v, @c, @t)",
    new { v = "T001", c = testMessage, t = DateTime.UtcNow }
);

var result = await db.QuerySingleAsync<string>(
    "SELECT content FROM chat_message ORDER BY id DESC LIMIT 1"
);

Console.WriteLine(result);
// Output: Welcome to our support system 🥳🔥

This time, it worked.

One More Trap: Index Length and utf8mb4

Switching to utf8mb4 introduced another surprise.

ALTER TABLE chat_message
ADD INDEX idx_v_c(visitor_id, content);
-- Error: Specified key was too long; max key length is 767 bytes

Each utf8mb4 character can take up to 4 bytes.
Indexing a long VARCHAR or TEXT column can easily exceed InnoDB limits.

The fix was to use a prefix index:

ALTER TABLE chat_message
ADD INDEX idx_v_c(visitor_id, content(100));

Or, depending on the use case, a full-text index.

Lessons Learned (the Hard Way)

Never assume utf8 means UTF-8 in MySQL. It doesn’t.
Always log real SQL error codes. Silent failures are worse than crashes.
Test with emoji. Real users will use them—constantly.

I ended up adding this test to our deployment pipeline:

it("should store emoji without errors", async () => {
    const message = "Emoji test 🐱🐶🔥";
    await api.sendMessage({ visitorId: "U999", content: message });
    const saved = await api.getLastMessage("U999");
    expect(saved.content).toBe(message);
});

It runs every time we deploy.

Because no system—no matter how many years it’s been running—
should ever lose history to a single 🥲 again.

Wrapping up

ShenDesk is still evolving.

If you’ve ever built or deployed a real-time chat system, I’d genuinely love to hear your experience—
how you handled live updates, load balancing, or flexible deployment models in production.

Let’s compare notes.