Skip to content

A MySQL source's text and binary columns

A MySQL table’s text columns are each in a character set (utf8mb4, latin1…), and its binary columns hold bytes. Each value reaches your destination as MySQL itself reads it: text as the same text, in UTF-8, and bytes as the same bytes. This page says what we carry, and the two cases that stop a pipeline.

Your MySQL column What reaches your destination
VARCHAR, CHAR, TEXT (every size) in utf8mb4 or utf8mb3 the same text
the same in latin1 the same text, as MySQL reads latin1 (which is Windows cp1252: the byte 0x80 is €)
the same in ascii the same text
BINARY, VARBINARY, BLOB (every size) the same bytes. BINARY(n) arrives n bytes long, padded with zero bytes as MySQL returns it
ENUM its label, as text; the empty string where MySQL holds none (a server outside its strict mode stores that for a value that is not one of the labels, and returns it as '')
SET its labels, in the order the column declares them, joined by commas, as MySQL returns it

A CHAR value arrives without its trailing spaces, as MySQL returns it too.

Make a binary column’s destination column bytea: it takes the bytes as they are. A PostgreSQL text (or varchar) column judges them as text, under your pipeline’s answer to values your destination cannot take:

The bytes Under Change the value and keep syncing Under Stop the table
valid UTF-8 written as that text written as that text
valid UTF-8 apart from NUL bytes (a BINARY(n) shorter than n is padded with them) written with each NUL removed, reported as a changed value, the original kept whole in the record when the pipeline keeps one the pipeline stops
not valid UTF-8 the pipeline stops the pipeline stops

A MySQL pipeline syncs one table, so where the table stops, the pipeline stops; it does not restart on its own, and its history names the column. To carry on, change that destination column to bytea, then start the pipeline again: it writes the same change again, as the bytes your source holds. Where the bytes are valid UTF-8 apart from NULs and the pipeline is set to stop, setting it to Change the value and keep syncing and starting it again also gets past them. Correcting the value at your source does not, because the pipeline reads that change again from where it stopped. A NUL in a key column is never removed, under either answer: there too, bytea gets past it.

The same holds for bytes that are not valid in a text column’s own character set. MySQL refuses to store them in its strict mode, which is its default, so they are rare. When one arrives, it reaches your destination as the bytes MySQL holds, never changed into other text.

A column in any other character set stops the pipeline

Section titled “A column in any other character set stops the pipeline”

We read utf8mb4, utf8mb3, latin1 and ascii. A text, ENUM or SET column in any other character set (cp1251, utf16, gbk, sjis and the rest) stops the pipeline at the first change to its table, whatever the change holds in that column. Nothing of that table is written in another character set’s place. The pipeline does not restart on its own, and its history says, once, which table, which column and which character set.

MySQL’s binary log keeps each change in the character set it was written in, so starting the pipeline again meets the same change, and so does a new pipeline for the table: a new MySQL pipeline starts at the oldest binary log the server keeps. To carry on:

  1. Convert the column at your source, for example ALTER TABLE shop.orders MODIFY note VARCHAR(200) CHARACTER SET utf8mb4 (with the column’s own type).
  2. Let the server drop the binary log files written before the conversion. It does this on its own: a file older than binlog_expire_logs_seconds (30 days by default) is removed at the server’s next start or the next time the binary log is flushed, which FLUSH BINARY LOGS does. That removes nothing the server’s own setting would keep, so your replicas, backups and every other reader of the server are as safe as they already are. SHOW BINARY LOGS lists the files the server still keeps.
  3. Delete the pipeline and create a new one for the table. It syncs the changes made from then on. A MySQL pipeline copies nothing, so load the table’s earlier rows from your own copy of them.

PURGE BINARY LOGS drops those files sooner, and they are then gone for every reader that has not read them yet. Run it only once you have checked each of these is past the conversion:

  • every replica of the server: SHOW REPLICA STATUS on each says which file it reads. A replica that is behind, or not connected, cannot replicate again once a file it has not read is gone;
  • your backups: a point-in-time recovery that replays binary log files cannot run without them;
  • every other reader of the server’s binary log: another of our pipelines on the same server (whatever table it syncs) that has not read past them stops, because the changes it had not read are gone, and another tool loses them the same way.

Then run FLUSH BINARY LOGS, and PURGE BINARY LOGS TO '<the file SHOW BINARY LOG STATUS names>' (MySQL: PURGE BINARY LOGS).

So check before you create a pipeline. This lists every column in the database that we do not read:

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND CHARACTER_SET_NAME NOT IN ('utf8mb4', 'utf8mb3', 'latin1', 'ascii');

A column you convert after creating the pipeline is the case above: the binary log still holds the changes made before the conversion until they leave it.

Until this release, a MySQL pipeline wrote a latin1 character outside plain ASCII (an accented letter, €) as the replacement character �, and the bytes of a binary column as text in which every byte that was not valid UTF-8 was replaced the same way. A column in another character set was written the same way rather than stopping. A MySQL pipeline copies nothing, so rows written before this release keep those values until the row changes at your source, or until you put the table’s rows back from your own copy of them.