Thai Data Migration Traps: VARCHAR, Idempotency, and OCR Vowels

Recently, I took on a large data import job, bringing old data from several sources into one database — manually filled xlsx files, old PDFs that needed OCR, and another provider's API. Together, they added up to hundreds of thousands of rows. At first, I thought it was just grunt work: write an insert loop and be done. It turned out the real trouble was not the volume. It was the small details that corrupted the data without producing any visible errors. By the time I caught them, the totals were already wrong. I have written up four traps I ran into, in case anyone is about to tackle similar work.

The table names and numbers in this article are examples, but the failure mechanisms and checks are things I encountered firsthand.

Trap 1: An undersized VARCHAR silently truncates synthetic keys into collisions

This dataset had no natural primary key, so I built a synthetic key from a base code followed by a variant suffix, such as IT100802093025A_A0073. I then casually set the column to VARCHAR(20), eyeballing it as "probably enough."

The problem was that the actual key was 21 characters long, exactly 1 character over the limit. If strict mode is not enabled in sql_mode, MySQL/MariaDB will silently truncate the last character, leaving only a warning that nobody reads — two entries differing only in their last character become the same key and collide.

-- คอลัมน์เดิม กว้างไม่พอสำหรับ synthetic key
CREATE TABLE items (
  id         BIGINT PRIMARY KEY AUTO_INCREMENT,
  item_code  VARCHAR(20) NOT NULL,   -- ปัญหาอยู่ตรงนี้
  name       VARCHAR(255) NOT NULL,
  UNIQUE KEY uq_code (item_code)
);

-- key จริงสองตัว ยาว 21 ตัวเท่ากัน ต่างแค่ตัวสุดท้าย
--   'IT100802093025A_A0073'
--   'IT100802093025A_A0074'
-- ถูกตัดเหลือ 20 ตัว กลายเป็น 'IT100802093025A_A007' ทั้งคู่ -> ชนกัน

With a UNIQUE KEY in place, the second entry cannot be inserted (duplicate) — if my code silently swallows the error or uses INSERT ... ON DUPLICATE KEY UPDATE, it simply overwrites the first entry. The result is the same: 1 entry disappears without anyone knowing. I only find out when the customer asks for something that should be there.

I fixed this in two layers. The first was to expand the schema with room to spare (VARCHAR(40) outright; no need to be stingy, since VARCHAR allocates storage based on actual usage). The second, more important layer was to always check lengths in Python before inserting. Never assume the DB will warn you, because some machines have strict mode enabled and others do not.

from collections import Counter

def preflight_keys(keys, col_maxlen):
    # 1. มี key ตัวไหนยาวเกินคอลัมน์ไหม -> ถ้ามี ต้องขยาย schema ก่อน อย่าปล่อยให้ DB ตัด
    toolong = [(k, len(k)) for k in keys if len(k) > col_maxlen]
    assert not toolong, f"key ยาวเกิน VARCHAR({col_maxlen}): {toolong[:5]}"

    # 2. หลังมั่นใจว่าไม่มีอะไรถูกตัด ค่อยเช็คว่า key ซ้ำกันเองไหม
    dup = [k for k, n in Counter(keys).items() if n > 1]
    assert not dup, f"key ซ้ำกันเอง: {dup[:5]}"

    return max(len(k) for k in keys)  # ไว้เทียบกับ schema

Trap 2: Idempotency — a "replace" script must DELETE before INSERT

This was the most painful trap. When I find bad data and write a script to fix it, the nature of migration work means I will run the same script repeatedly — run it, find a bug, fix it, rerun it. Sometimes that cycle repeats ten times. If the script is not idempotent (rerunning it does not produce the same result), the data grows with every run.

The worst case was the item_weights table, which stores the "weight" of each component, with a total that should be around 100 per item. I wrote a script to fill in missing weights but forgot that the previous run had already inserted the old values. When I reran it, it inserted another overlapping set — the total shot up to 200%. The same component was counted twice.

-- ผิด: insert อย่างเดียว รันซ้ำเมื่อไหร่ weight ซ้อนทันที
INSERT INTO item_weights (item_id, component, weight) VALUES (?, ?, ?);
-- รันรอบสอง -> item เดียวกันมี weight สองชุด -> SUM ทะลุ 100

-- ถูก: สำหรับ operation แบบ "replace" ต้องลบของเก่าตาม scope ก่อน แล้วค่อย insert ทั้งก้อน
START TRANSACTION;
DELETE FROM item_weights WHERE item_id = ?;      -- ล้างของเก่าเฉพาะ item นี้
INSERT INTO item_weights (item_id, component, weight) VALUES
  (?, 'A', 40), (?, 'B', 35), (?, 'C', 25);      -- ใส่ชุดใหม่ทั้งชุด
COMMIT;

My firm rule after this job is: whenever an operation means "replace," always DELETE-then-INSERT; do not merge by simply adding more rows. A merge is idempotent only if I check whether every row already exists, which is much easier to get wrong. For INSERT, ON DUPLICATE KEY UPDATE can help if a unique key covers everything needed, but for multiple weight rows per item like this, DELETE-then-INSERT is more straightforward.

After every import, I always run this audit first. If any rows appear, I know immediately that data has been inserted twice:

-- item ไหนที่ยอด weight เกิน 100 = โดน insert ซ้อนแน่ๆ
SELECT item_id, ROUND(SUM(weight), 2) AS total
FROM item_weights
GROUP BY item_id
HAVING total > 100.5      -- เผื่อ error ปัดเศษเล็กน้อย
ORDER BY total DESC
LIMIT 20;

Trap 3: The utf8mb4_unicode_ci collation treats su001 and SU001 as equal

This one was the most deceptive, because it is an intentional "feature" of the collation. I set most text columns to utf8mb4_unicode_ci, where _ci means case-insensitive — comparisons treat su001, SU001, and Su001 as the same value.

The problem appeared when I tried to find duplicates with an ordinary WHERE — it fooled me into believing there were "no duplicates," even though the table actually stored both su001 and SU001 (separate rows from different sources), because I had no unique key covering that column alone when inserting.

To actually find them, I have to force exact byte comparisons with BINARY; otherwise, GROUP BY still folds the cases together:

-- หา component ที่ต่างกันแค่ case (su001 vs SU001) ในตารางเดียว
SELECT UPPER(component)              AS norm,
       COUNT(DISTINCT BINARY component) AS variants,
       GROUP_CONCAT(DISTINCT component) AS seen
FROM item_weights
GROUP BY UPPER(component)
HAVING variants > 1;
-- variants > 1 = มีหลาย casing ของ key เดียวกันปนอยู่

In my scripts, I stopped trusting WHERE = for deduplication. I now normalize in Python before comparing — apply .lower() to the key, then compare it myself in a set or dict. That lets me decide explicitly whether to treat values as equal, instead of leaving it to the column's collation:

# dedupe ฝั่งแอป: ตัดสินใจเองว่า case-insensitive หรือไม่ ไม่ฝากไว้กับ collation
seen = {}
for row in incoming:
    norm = row["component"].strip().lower()   # กติกาชัดเจน: lower = ถือว่าเท่ากัน
    if norm in seen:
        # เจอ case-variant ของตัวเดิม -> merge/ข้าม ตามนโยบายที่ตั้งไว้
        continue
    seen[norm] = row

I consider _ci useful for "user-facing searches" but dangerous for "proving uniqueness" — whenever I need to verify that something is truly unique, I always compare with BINARY or normalize it myself in code. Do not trust =.

Trap 4: Thai vowels corrupted by OCR — "การเงนิ" is not "การเงิน"

The data from old PDFs had to go through OCR first, and Thai OCR tends to swap the positions of above- or below-base vowels and consonants. At a glance, they look identical, but at the byte level they are different strings. Put them through GROUP BY or JOIN, and they immediately become separate entries. Here is an example I encountered in the actual job:

Common pairs: การเงนิ and การเงิน, ศลิปกรรม and ศิลปกรรม, สาธารณสขุ and สาธารณสุข, สอื่สาร and สื่อสาร, รังสติ and รังสิต, นวิตกรรม and นวัตกรรม — these kept recurring, so I built a standard normalization map directly into the insert process. Besides the map, I also need to collapse whitespace, because the other half of the duplicates I found came from newlines or double spaces introduced during parsing, such as 'หลักสูตร\nสาขาวิชา...' and 'หลักสูตร สาขาวิชา...'.

import re

# แผนที่ typo สระ/วรรณยุกต์ไทยจาก OCR ที่เจอซ้ำๆ — เติมได้เรื่อยๆ ตามที่ audit เจอ
OCR_FIX = {
    "การเงนิ":   "การเงิน",
    "ศลิปกรรม":  "ศิลปกรรม",
    "สาธารณสขุ": "สาธารณสุข",
    "สอื่สาร":   "สื่อสาร",
    "รังสติ":    "รังสิต",
    "นวิตกรรม":  "นวัตกรรม",
    "อตุสาหกรรม": "อุตสาหกรรม",
}

def normalize_th(name: str) -> str:
    name = name.replace("​", "")        # ตัด zero-width space
    name = re.sub(r"\s+", " ", name).strip()  # ยุบ newline/space ซ้อน เหลือช่องเดียว
    for bad, good in OCR_FIX.items():
        name = name.replace(bad, good)
    return name

# กติกา: ทุกค่าที่จะ insert ต้องผ่าน normalize_th() ก่อนเสมอ ไม่มีข้อยกเว้น

A map like this only fixes "known" typos. I still need a way to track down unknown ones. I use a technique called squashed comparison — remove all whitespace, then compare. If two names differ by only a few bytes and fall into a group of similar names, I can inspect them visually in pairs:

from collections import defaultdict

# หาชื่อที่ "เกือบเหมือนกัน" — ต่างแค่ space หรือสระเพี้ยน ยังไม่อยู่ใน OCR_FIX
buckets = defaultdict(set)
for r in rows:
    squashed = re.sub(r"\s+", "", r["name"])   # ตัด space/newline ทิ้งหมด
    buckets[squashed].add(r["name"])

for squashed, variants in buckets.items():
    if len(variants) > 1:
        print("NEAR-DUP:", variants)           # เอามาดูตา แล้วตัดสินใจเติมเข้า map

Whenever I find a pair that I am sure represents the same thing, I add it to OCR_FIX and rerun normalization. I repeat this until the near-duplicate groups contain only genuinely different entries.

A checklist for before and after every import

These four traps share one thing: they are silent — no error pops up to tell me. I only find out when the totals are wrong or a customer reports something missing. So I made the checks a mandatory gate: run preflight before inserting, then audit afterward. If they fail, I do not let the data go into production.

1. Check every key's length against the column maximum before inserting — if it is too long, expand the schema instead of letting the DB truncate it.
2. Check for duplicate keys only after normalization.
3. Normalize every Thai text value — always map OCR vowel errors and collapse whitespace before inserting.
4. For "replace" operations, DELETE within the WHERE scope, then INSERT the entire set, wrapped in a single transaction.
5. postflight: audit SUM per item, find case duplicates with BINARY, and track down near-duplicate names with squashed comparison.

Put everything into one script and run it every time before touching live data:

# preflight: ผ่านหมดค่อยปล่อย import ตัวจริง
python preflight_import.py \
  --check-keys      \
  --check-length 40 \
  --normalize-th    \
  --dry-run

# postflight: หลัง import เสร็จ audit ทันที
mysql migration_db < audit_after_import.sql

The biggest lesson I took from this job was: do not assume "no errors = correct data" — VARCHAR truncation, collation folding case, and OCR swapping vowels are all normal behaviors of tools doing what they are designed to do. They are not bugs the system will report. Preventing these problems is my responsibility: write the full set of preflight checks and audits, and run them every time. Do not trust your eyes.

If you want to adapt this to your own work, change the table names and tier numbers as needed. The approach of "check before insert, audit after insert, DELETE before replace, normalize before comparing" applies to every kind of migration, not just Thai data. If I have missed anything in the explanation or made a typo, I will come back and correct it later.