How to Fix Latin1, CP1253 and UTF-8 Encoding Before a vBulletin Migration

Greek vBulletin 3 forums are where we have seen this at its worst. The pattern goes like this. The forum looks fine in the browser and has for years. The database says every text column is latin1. The bytes inside are something else: Windows-1253 Greek, UTF-8, or a mix of both depending on when each row was written. It only ever displayed correctly because vBulletin, PHP and the browser all made the same wrong assumption.

Discourse will not make those assumptions. Its importer reads text through the mysql2 driver, which connects as utf8mb4 unless told otherwise, so MySQL converts every column from its declared character set to UTF-8 on the way out. If the declaration is wrong, the import is wrong, and no importer setting fixes it.

This article is the encoding companion to our step-by-step vBulletin 3 to Discourse migration guide. It covers how to find out what is really stored, how to test a repair without risk, and how to normalize to utf8mb4 afterwards. Greek and CP1253 are the worked example because that is where we hit it hardest, but the method applies to any forum with non-ASCII text. Not every vBulletin database has these problems. Plenty of English-only forums can skip straight to the import. The point is to find out, not to assume.

Charset labels versus stored bytes

A MySQL text column has a declared character set. When a client connects, it also declares a connection character set. MySQL converts between the two whenever they differ. That is the whole mechanism, and the failure mode follows from it.

For years, a typical vBulletin 3 setup had latin1 tables and a PHP connection that also said latin1, because that was the default. The forum’s HTML pages declared some other charset, often windows-1253 on Greek boards or UTF-8 on boards that switched later. Browsers submitted text in the page’s charset. PHP passed those bytes to MySQL over a latin1 connection into a latin1 column. Since the connection and the column agreed, MySQL converted nothing and stored the bytes as they arrived. On the way back out, the same unconverted bytes went to the browser, which decoded them using the page’s charset. Everything looked right.

The database, meanwhile, now held Greek bytes under a label that says Western European. Nothing noticed until something tried to convert the data for real.

Two MySQL details matter here:

• MySQL’s latin1 is Windows-1252, not strict ISO-8859-1. It assigns characters to bytes 0x80 to 0x9F and maps the five bytes Windows-1252 leaves undefined to control characters. In practice this means any byte sequence round-trips through latin1 without loss, which is what makes the repairs below possible.
• MySQL and MariaDB have a greek character set, which is ISO-8859-7. They have no CP1253.

The encodings you will meet

ISO-8859-7 and Windows-1253

Both are single-byte Greek encodings, and they agree on the Greek alphabet. The word “Καλημέρα” is CA E1 EB E7 EC DD F1 E1 in either one. They disagree in a few places that show up constantly in real text:

Character	CP1253 byte	ISO-8859-7 byte	That CP1253 byte read as ISO-8859-7
Ά (capital alpha with tonos)	A2	B6	a quotation mark, not Ά
€	80	none at 80	a control code, not €
‘ ’ “ ”	91 92 93 94	none	control codes
en dash, em dash, ellipsis	96 97 85	none	control codes

Windows users typed curly quotes and dashes without knowing it, because word processors and some keyboard layouts produce them automatically. A CP1253 forum is full of them. Push that data through MySQL’s greek character set and every Ά becomes an apostrophe and every smart quote becomes a question mark or a control character.

UTF-8 stored as latin1

When a forum switched its pages to UTF-8 but kept latin1 tables and a latin1 connection, browsers sent UTF-8 and MySQL stored the bytes unchanged. “Καλημέρα” becomes CE 9A CE B1 CE BB CE B7 CE BC CE AD CF 81 CE B1, two bytes per Greek letter. Read as latin1, it looks like ΚαλημέÏ followed by an invisible control character and α.

Double-encoded UTF-8

This is what happens when someone runs a conversion over the previous case. MySQL reads each of those UTF-8 bytes as a separate latin1 character and encodes each one as UTF-8 again. CE B1 (α) becomes C3 8E C2 B1. The column now says utf8 and holds valid UTF-8, which happens to spell α. Every check that only asks “is this valid UTF-8?” passes.

Mojibake as a diagnostic

The garbage has a recognizable look, and it tells you what went wrong:

You see	Likely stored	Likely cause
ÊáëçìÝñá	Single-byte Greek (CP1253 or ISO-8859-7)	Greek bytes displayed as latin1
α, ε, τ	UTF-8 Greek	UTF-8 bytes displayed as latin1
α	UTF-8 Greek encoded twice, displayed as latin1	Two layers of wrong conversion
é, ö	UTF-8 Western European text	Same problem, other alphabet
???	Question marks really stored	Conversion already lost data

The last row is the bad one. Literal 3F bytes in HEX() output mean the characters were replaced during an earlier conversion and are gone from this copy. Your only hope for those rows is an older backup.

Why ALTER TABLE … CONVERT can make things worse

ALTER TABLE vb3_post CONVERT TO CHARACTER SET utf8mb4;

This statement does exactly what it says: it reads every text column as its declared character set and writes it out as utf8mb4. For a latin1 column that really holds latin1, that is correct. For a latin1 column holding UTF-8 bytes, it produces double encoding. For a latin1 column holding CP1253, it turns Greek into accented Latin letters encoded as UTF-8, ÊáëçìÝñá stored permanently.

None of these are reversible by running another ALTER. They can be reversed with careful byte-level work if you know exactly what happened, but on the only copy of a database with mixed history you usually do not know exactly what happened. Hence the rule: repair first, normalize last.

Inspect bytes with HEX()

Your terminal is not a reliable witness. What the mysql client shows depends on its connection character set and your terminal’s encoding, and both can hide or create mojibake. Connect with a known setting:

mysql --default-character-set=utf8mb4 vbulletin_stage

and then look at bytes, which cannot lie:

SELECT
    threadid,
    title,
    HEX(title)
FROM vb3_thread
WHERE threadid = 12345;

HEX() on a column returns the stored bytes in the column’s own character set. With a known Greek word in hand, the patterns above identify the encoding immediately.

Inspect each column separately

Columns in one vBulletin table were written by different code paths at different times, and they do not always agree. List what MySQL claims:

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'vbulletin_stage'
  AND CHARACTER_SET_NAME IS NOT NULL
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Then check the columns the importer reads, one by one:

• user.username: written at registration, sometimes edited by admins through a different form
• forum.title and forum.description: written through the Admin CP, which may have had its own charset setting
• thread.title
• post.title and post.pagetext: the bulk of the data
• pmtext.title and pmtext.message
• usergroup.title, poll.question and poll.options

Plugin tables need the same treatment. A plugin installed in 2011 may have created its tables with whatever default the server had in 2011.

Detect mixed historical data

A single sample tells you about a single row. To see the shape of a whole column, classify every row by byte pattern. This query works on a column declared latin1 and is tuned for Greek, so treat its categories as heuristics and confirm each one by eye:

SELECT
    SUM(HEX(title) NOT REGEXP '^(..)*[89A-F]')                AS ascii_only,
    SUM(HEX(title) REGEXP '^(..)*C[EF][89AB][0-9A-F]')        AS looks_utf8_greek,
    SUM(HEX(title) REGEXP '^(..)*C38[EF]')                    AS looks_double_encoded,
    SUM(HEX(title) REGEXP '^(..)*[89A-F]'
        AND HEX(title) NOT REGEXP '^(..)*C[EF][89AB][0-9A-F]'
        AND HEX(title) NOT REGEXP '^(..)*C38[EF]')            AS other_non_ascii,
    COUNT(*)                                                  AS total
FROM vb3_thread;

The ^(..)* prefix keeps each match aligned to byte boundaries in the hex string, so CE only matches a real CE byte and not the end of one byte plus the start of the next.

On a Greek forum, other_non_ascii is mostly single-byte Greek. looks_utf8_greek is UTF-8 stored as latin1. looks_double_encoded needs a closer look. If more than one category has significant counts, the column is mixed and needs row-level repair, not a column-wide one.

It also pays to see when the encoding changed:

SELECT
    YEAR(FROM_UNIXTIME(dateline)) AS year,
    SUM(HEX(title) REGEXP '^(..)*C[EF][89AB][0-9A-F]') AS utf8_rows,
    SUM(HEX(title) REGEXP '^(..)*[89A-F]'
        AND HEX(title) NOT REGEXP '^(..)*C[EF][89AB][0-9A-F]') AS single_byte_rows
FROM vb3_thread
GROUP BY year
ORDER BY year;

A clean switch from one to the other in a particular year usually matches a vBulletin upgrade, a language pack change or a server move. Knowing the date lets you check the forum’s history and gives you a sanity check for the classification.

These queries scan every row and run HEX() over it. On post.pagetext for a large forum, run them against ranges of postid rather than the whole table at once.

Test transformations with SELECT first

Every repair starts as a SELECT that shows the original next to the candidate. Nothing is written until the candidate is right for every category of row you found.

UTF-8 bytes in a latin1 column

CAST(... AS BINARY) strips the character set label without touching the bytes. CONVERT(... USING utf8mb4) then reads those bytes as UTF-8:

SELECT
    threadid,
    title AS original,
    CONVERT(CAST(title AS BINARY) USING utf8mb4) AS candidate
FROM vb3_thread
WHERE HEX(title) REGEXP '^(..)*C[EF][89AB][0-9A-F]'
LIMIT 50;

The expression from one specific migration

On one Greek migration, the affected columns held UTF-8 byte sequences under a latin1 label. This is the expression we used to read them correctly:

CONVERT(
    CAST(
        CONVERT(field USING latin1)
        AS BINARY
    )
    USING utf8mb4
)

Test it as a SELECT with HEX() on both sides:

SELECT
    threadid,
    title AS original,
    HEX(title) AS original_hex,
    CONVERT(CAST(CONVERT(title USING latin1) AS BINARY) USING utf8mb4) AS candidate,
    HEX(CONVERT(CAST(CONVERT(title USING latin1) AS BINARY) USING utf8mb4)) AS candidate_hex
FROM vb3_thread
WHERE threadid IN (12345, 12346, 12347);

For Greek, a correct candidate shows CE or CF lead bytes in candidate_hex and readable Greek in candidate.

Single-byte Greek in a latin1 column

If the bytes are pure ISO-8859-7, MySQL can relabel them as greek and convert from there:

SELECT
    threadid,
    title AS original,
    CONVERT(CONVERT(CAST(title AS BINARY) USING greek) USING utf8mb4) AS candidate
FROM vb3_thread
WHERE threadid = 12345;

If they are CP1253, which on Windows-era forums they usually are, this is close but wrong in the places shown in the table earlier: Ά, the euro sign, curly quotes, dashes and ellipses. Search the candidates for those characters before you trust it. A single REPLACE of byte A2 with B6 before the conversion fixes Ά, but there is no ISO-8859-7 byte for a curly quote in MySQL’s greek, so those cannot be rescued inside MySQL this way.

For an exact CP1253 repair, decode outside MySQL with a real CP1253 codec. A short script that reads the primary key and the raw bytes, decodes them, and writes them to a utf8mb4 column does the job. In Python, for example:

import pymysql

conn = pymysql.connect(host="MYSQL_SERVER", user="repair_user", password="STRONG_RANDOM_PASSWORD",
                       database="vbulletin_stage", charset="utf8mb4")
with conn.cursor() as read, conn.cursor() as write:
    read.execute("SELECT threadid, CAST(title AS BINARY) FROM vb3_thread WHERE threadid IN (%s)",
                 (12345,))
    for threadid, raw in read.fetchall():
        try:
            text = raw.decode("cp1253")
        except UnicodeDecodeError as err:
            print(threadid, "not valid CP1253:", err)
            continue
        print(threadid, text)
        # once the output is verified:
        # write.execute("UPDATE vb3_thread SET title_utf8 = %s WHERE threadid = %s", (text, threadid))
# conn.commit()

A strict decoder is also a test. Bytes that CP1253 leaves undefined raise an error rather than silently becoming something, which flags rows that are not CP1253 at all.

Apply the repair, reproducibly

Once the candidate is right for every category, write the change as a script that runs against a fresh restore. Typical shapes:

Whole column, one encoding, UTF-8 bytes in latin1. Relabel through a binary type so MySQL does not convert anything:

ALTER TABLE vb3_thread MODIFY title VARBINARY(250);
ALTER TABLE vb3_thread MODIFY title VARCHAR(250)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';

Take the length, nullability and default from SHOW CREATE TABLE, not from this example. TEXT and MEDIUMTEXT columns go through BLOB and MEDIUMBLOB. If the table has a MyISAM FULLTEXT index over the column (common on post.title and pagetext when vBulletin’s full-text search was enabled), drop it on staging first. The importer does not need it, and MySQL will not convert a full-text indexed column to a binary type.

Mixed column. Add a new utf8mb4 column, fill it per category with the UPDATE matching each tested SELECT, check that every row was filled exactly once, then swap the columns. Writing to a new column keeps the original intact in the same table until you are sure.

Keep every one of these statements in a single script in version control, with the classification queries before and after. The production dump will be taken weeks later, and you will run the same script against it on cutover day. A repair you did interactively will be missing.

Validate the repair

After the script has run on staging:

• rerun the classification queries; the expected categories should be gone and ascii_only unchanged
• compare row counts per table before and after
• compare CHAR_LENGTH distributions; a repaired Greek title should be roughly half the byte length it was as UTF-8-in-latin1
• check a fixed list of known rows, including the oldest, newest and some from each year
• read usernames, forum titles and descriptions, thread titles, post bodies and PM subjects and bodies, as text, in a UTF-8 terminal or in the browser after a test import
• search for leftover markers: Î, Ï, Ã, Â and literal ? sequences in the repaired columns

The same list belongs in the validation section of your migration plan. Encoding errors that survive into Discourse are much harder to fix there, because the text has been converted to Markdown, cooked into HTML and indexed for search.

Final utf8mb4 normalization

Only now, with the stored bytes matching what you want them to mean, can the labels be made honest:

ALTER DATABASE vbulletin_stage
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

ALTER TABLE vb3_user
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Run the table conversions only for tables whose columns are all verified. At this point CONVERT TO does what it promises, because every latin1 column really does contain latin1, and every repaired column already is utf8mb4.

Then return to the main guide and continue with the Discourse installation and import. The encoding steps are also listed in the printable vBulletin to Discourse migration checklist.

Frequently asked questions

Why does my vBulletin text turn into à or Πcharacters after conversion?

Those sequences are UTF-8 bytes being read as latin1. Usually the column was declared latin1 but actually held UTF-8, and a conversion to utf8mb4 trusted the label, encoding the bytes a second time. The text is still recoverable as long as no further lossy conversion has happened, by reversing exactly one layer of encoding.

Does MySQL support Windows-1253 (CP1253)?

No. MySQL and MariaDB have a greek character set, which is ISO-8859-7, but no CP1253. The two agree on the Greek letters but differ on capital alpha with tonos and on the smart quotes, dashes and euro sign Windows users typed. Converting CP1253 data through the greek character set damages those characters, so exact repairs are best done with a real CP1253 decoder outside MySQL.

Is it safe to run ALTER TABLE CONVERT TO CHARACTER SET utf8mb4 on a vBulletin database?

Only after the stored bytes match the declared character set. CONVERT TO CHARACTER SET reads each column as its declared charset and rewrites it. If a latin1 column holds UTF-8 or CP1253 bytes, the result is permanent mojibake. Prove the real encoding with HEX(), repair it on a copy, and normalize afterwards.

How do I find out which encoding a column really uses?

Look at the bytes, not the label. Select HEX() of non-ASCII rows and compare the patterns with known encodings: UTF-8 Greek letters start with CE or CF, single-byte Greek uses values from C1 to FE, and double-encoded UTF-8 shows C3 8E or C3 8F repeating. Sample old and new rows, because the encoding often changed during the forum’s life.

Need a second pair of eyes on a legacy database?

Encoding repair is the part of a forum migration where a mistake is quiet and permanent. GOZEN Host can help with database preparation, staging servers and the full vBulletin to Discourse migration. Contact our team before running conversions against data you cannot restore.