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 assumeutf8means 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.
If you’re curious
I’ve been building ShenDesk, a customer support chat system designed to run reliably
both online and on your own infrastructure.
- 🌐 Website: https://shendesk.com
- 📘 Documentation: https://docs.shendesk.com
You can try it for free, whether you prefer a hosted setup or self-hosting.
Feedback from developers interested in self-hosted systems, real-time communication,
and customer experience engineering is always welcome.
UI snapshots
Visitor side
Fast loading, no message loss
Agent side
Reliable, feature-rich, built for real-world support work
Web admin panel
SOCIAL SHARE CARD GENERATOR