Repairing corrupted surrogate-pair characters in JSON columns

{question}

How can I repair corrupted surrogate-pair characters in JSON columns after upgrading to SingleStore 10?

{question}

{answer}

Overview

In SingleStore versions earlier than 10, LOAD DATA and INSERT operations could store JSON values containing supplementary-plane characters (such as 😀) in a corrupted form. When these values are read, clients display each surrogate half as a replacement character, producing a pattern. The corruption is stored on disk, so it appears in any version, including SingleStore 10, until the affected rows are rewritten.

SingleStore 10 fixes the underlying ingestion behavior: well-formed surrogate pairs are combined into a single code point on insert, and malformed input is rejected. New inserts on SingleStore 10 return the characters correctly, but existing rows written on earlier versions are not rewritten by the upgrade.

SingleStore 10 provides the repair_surrogate_pairs UDF to rewrite JSON values that contain surrogate pairs stored in the corrupted form.

Background

Characters outside the Basic Multilingual Plane (BMP), with code points U+10000 and above, are encoded in JSON string literals as a UTF-16 surrogate pair. The pair consists of two \uXXXX escapes: a high surrogate in the range \uD800–\uDBFF, followed by a low surrogate in the range \uDC00–\uDFFF.

A correct JSON decoder combines the pair into a single code point and stores it as one 4-byte UTF-8 sequence.

For SingleStore versions earlier than 10, SingleStore decoded the high and low surrogates independently. Each half was stored as its own 3-byte UTF-8 sequence, which is not a valid character on its own. Clients displayed each half as a replacement character, producing the 😀 pattern.

SingleStore version 10 provides the following format:

• Well-formed surrogate pairs are combined into the correct code point on insert.
• Malformed input, such as a lone high or low surrogate or a pair in the wrong order, is rejected at insert time.

Data that was already stored in the corrupted form on an earlier version is not rewritten by the upgrade. Use the UDF in the Workaround section to repair the existing data.

Example

The following query may return a JSON value with replacement characters instead of the original emoji:

SELECT payload FROM events WHERE id = 42;
+-----------------------+
| payload               |
+-----------------------+
| {"reaction":"😀"} |
+-----------------------+

The corruption may be visible in SELECT output, client libraries, and exports.

Problem

The affected data contains a UTF-16 surrogate pair that was decoded and stored as two separate 3-byte UTF-8 sequences at ingestion time.

This corruption occurs only in data written by LOAD DATA or INSERT on SingleStore versions earlier than 10. New inserts on version 10 combine well-formed surrogate pairs correctly. Re-ingesting an affected value on version 10 also stores it correctly. Upgrading to version 10 without re-ingesting or repairing the existing rows does not change their on-disk representation.

Workaround

Set the advanced regexp engine before creating the UDF:

SET GLOBAL regexp_format = advanced;

The UDF uses a byte-class regular expression ([\xED][\xA0-\xAF]…) that requires the advanced regexp engine.

If you leave the default regexp_format, the UDF will still be created, but calls will return an error or fail to match the corrupted byte sequences.

Create the following UDF in each database that contains affected JSON columns:

CREATE OR REPLACE FUNCTION repair_surrogate_pairs(input_json JSON COLLATE utf8mb4_bin)
RETURNS JSON AS
DECLARE
   raw_bin    LONGBLOB = input_json :> LONGBLOB;
   res_bin    LONGBLOB = '';
   curr_pos   INT = 1;
   found_pos  INT;
   chunk ARRAY(TINYINT UNSIGNED NOT NULL);
   hi INT; lo INT; cp INT;
BEGIN
   IF input_json IS NULL THEN RETURN NULL; END IF;
   LOOP
       -- A split high surrogate is [0xED][0xA0-0xAF][0x80-0xBF];
       -- a split low surrogate is [0xED][0xB0-0xBF][0x80-0xBF].
       -- Match the two 3-byte sequences back to back.
       found_pos = REGEXP_INSTR(
           raw_bin,
           '[\\xED][\\xA0-\\xAF][\\x80-\\xBF][\\xED][\\xB0-\\xBF][\\x80-\\xBF]',
           curr_pos);
       IF found_pos = 0 THEN
           res_bin = CONCAT(res_bin, SUBSTR(raw_bin, curr_pos));
           EXIT;
       END IF;
       res_bin = CONCAT(res_bin, SUBSTR(raw_bin, curr_pos, found_pos - curr_pos));
       chunk = STRING_BYTES(SUBSTR(raw_bin, found_pos, 6));
       hi = ((chunk[0] & 0x0F) << 12) | ((chunk[1] & 0x3F) << 6) | (chunk[2] & 0x3F);
       lo = ((chunk[3] & 0x0F) << 12) | ((chunk[4] & 0x3F) << 6) | (chunk[5] & 0x3F);
       cp = 0x10000 + ((hi - 0xD800) << 10) + (lo - 0xDC00);
       res_bin = CONCAT(res_bin, UNHEX(CONCAT(
           HEX(0xF0 | ((cp >> 18) & 0x07)),
           HEX(0x80 | ((cp >> 12) & 0x3F)),
           HEX(0x80 | ((cp >> 6)  & 0x3F)),
           HEX(0x80 | ( cp         & 0x3F))
       )));
       curr_pos = found_pos + 6;
   END LOOP;
   RETURN res_bin :> JSON;
END

Verification

Before running the UDF against a live table, verify that it repairs a synthetic corrupted value.

The byte sequence 0xED 0xA0 0xBD 0xED 0xB8 0x80 is the split-surrogate form of U+1F600 (😀):

SELECT repair_surrogate_pairs(CONCAT('"', 0xeda0bdedb88080, '"') :> JSON) AS repaired;
+----------+
| repaired |
+----------+
| "😀"     |
+----------+

If the result is "😀", the UDF and advanced regexp mode are configured correctly.

Identify affected rows

Before running the repair UPDATE, count and inspect the rows that will be modified. The following query counts affected rows in an events.payload column:

SELECT COUNT(*) AS affected_rows
FROM events
WHERE payload :> LONGBLOB REGEXP
      '[\\\xED][\\\xA0-\\\xAF][\\\x80-\\\xBF][\\\xED][\\\xB0-\\\xBF][\\\x80-\\\xBF]';

To sample the affected rows and preview the repair before applying it:

SELECT id,
       payload AS current_value,
       repair_surrogate_pairs(payload) AS repaired_value
FROM events
WHERE payload :> LONGBLOB REGEXP
      '[\\\xED][\\\xA0-\\\xAF][\\\x80-\\\xBF][\\\xED][\\\xB0-\\\xBF][\\\x80-\\\xBF]'
LIMIT 10;

Repeat these queries for each table and JSON column you plan to repair, substituting the table and column names.

Repair an affected column

Before running the repair UPDATE, back up the affected tables. The repair rewrites JSON values in place, and while the UDF is idempotent and the WHERE clause is narrow, having a backup lets you recover if a downstream consumer depends on the exact previous byte representation. Use BACKUP DATABASE or export the affected tables using your standard backup procedure.

For each table and JSON column that contains corrupted values, run an UPDATE.

For example, to repair a payload column in an events table:

UPDATE events
SET    payload = repair_surrogate_pairs(payload)
WHERE  payload :> LONGBLOB REGEXP
       '[\\xED][\\xA0-\\xAF][\\x80-\\xBF][\\xED][\\xB0-\\xBF][\\x80-\\xBF]';

The WHERE clause matches only rows that contain the split-surrogate byte pattern, so the update does not modify other rows.

Run the update in a maintenance window if the table is large or actively written to.

Explanation

The UDF:

• Reads the JSON value as raw bytes.
• Searches for a high-surrogate and low-surrogate pair stored as two 3-byte UTF-8 sequences.
• Reconstructs the original Unicode code point.
• Encodes the code point as the correct 4-byte UTF-8 sequence.
• Returns the repaired value as JSON.

The input parameter is declared JSON COLLATE utf8mb4_bin. The UDF is intended for JSON values stored with the utf8mb4 character set. If a utf8 JSON value is passed, the returned JSON is utf8mb4.

The function returns NULL for a NULL input.

Additional Notes

Well-formed pairs only

The UDF repairs a high surrogate immediately followed by a low surrogate.

If an earlier version accepted a lone high or low surrogate, or a pair in the wrong order, those bytes are left in place. From version 10, malformed input is rejected at insert time, so no new malformed values can be introduced. Existing rows containing malformed values remain unchanged after running this UDF.

JSON values only

The UDF is scoped to the JSON datatype. If corrupted surrogate pairs were written into a TEXT or VARCHAR column, do not use this UDF. Open a support case instead.

Idempotent

Running the UDF twice on the same value produces the same output as running it once. It is safe to re-run if an UPDATE is interrupted.

If the repair does not resolve the symptom

Open a support case with the following information:

• The SingleStore version (SELECT @@memsql_version;).
• The output of the verification query:

SELECT repair_surrogate_pairs(CONCAT('"', 0xeda0bdedb880, '"') :> JSON);

• A minimal example of a corrupted value that the UDF did not repair, including the raw bytes:

SELECT HEX(<column> :> LONGBLOB)
FROM <table>
WHERE …;

Reference Documentation

• JSON Type
• User-Defined Scalar-Valued Functions (UDFs)
• Regular Expression Functions

{answer}