mysql-latin1-codec 1.0

Overview

This project provides a Python string codec for MySQL’s latin1 encoding, and an accompanying iconv-like command line script for use in shell pipes.

Rationale

• MySQL defaults to using the latin1 encoding for all its textual data, but its latin1 encoding is not actually latin1 but a MySQL-specific variant.

• Due to improperly written applications or wrongly configured databases, many existing databases keep data in MySQL latin1 columns, even if that data is not actually latin1 data.

• MySQL will not complain about this, so this often goes unnoticed. The problems only appear when you try to access the database from another application, or when you try to grep through database dumps produced by mysqldump.

• Many libraries do not support this encoding properly, and using the real latin1 encoding leads to corruption when processing data, especially when the database contains text that is not in a West-European language.

• This string codec makes it possible to reconstruct the exact bytes as they were stored in MySQL. This is a valuable tool for fixing text encoding issues with such databases. For convenience, a command line interface that behaves like iconv is also included.

Installation

$ pip install mysql-latin1-codec

This should work for Python 2 and 3.

Usage

In Python code, simply import the module named mysql_latin1_codec. A string codec named mysql_latin1 will be registered in Python’s codec registry, and you can use it using the normal .decode() and .encode() methods on (byte)strings, and you can also specify it as the encoding argument to various I/O functions like io.open(). Example:

# A simple import will register the codec
import mysql_latin1_codec

# String encoding/decoding round-trip
s1 = u'foobar'
b = text.encode('mysql_latin1')
s2 == b.decode('mysql_latin1')
assert s1 == s2

# Reading files (Python 3; use codecs.open() in Python 2)
with open('/path/to/file', 'r', encoding='mysql_latin1') as fp:
    data = fp.read()

There is also a command line tool that behaves like iconv:

$ python -m mysql_latin1_codec --help
usage: mysql_latin1_codec.py [-h] [-f encoding] [-t encoding] [-o filename]
                             [-c]
                             [inputs [inputs ...]]

iconv-like tool to encode or decode data using MySQL's "latin1" dialect,
which, despite the name, is a mix of cp1252 and ISO-8859-1.

positional arguments:
  inputs                Input file(s) (defaults to stdin)

optional arguments:
  -h, --help            show this help message and exit
  -f encoding, --from encoding
                        Source encoding (uses MySQL's "latin1" if omitted)
  -t encoding, --to encoding
                        Target encoding (uses MySQL's "latin1" if omitted)
  -o filename, --output filename
                        Output file (defaults to stdout)
  -c, --skip-invalid    Omit invalid characters from output (not the default)

Practical examples

The example below ‘fixes’ dumps that contain doubly encoded UTF-8 data, i.e. real UTF-8 data stored in a MySQL latin1 table. By default mysqldump makes UTF-8 dumps, but if MySQL thinks the data is latin1, it will convert it again, resulting in double encoded data.

$ cat backup-of-broken-database-produced-by-mysqldump.sql \
  | python -m mysql_latin1_codec -f UTF-8 \
  | iconv -c -f UTF-8 -t UTF-8 \
  > legible-text-in-utf8.sql

The iconv pipe in this example removes invalid UTF-8 sequences, while keeping the valid parts as-is. MySQL truncates values whose size exceeds the column’s maximum size, but if MySQL doesn’t know that it is handling UTF-8 data (because the database schema and the broken application did not tell it to do so) it truncates the byte sequence, not the character sequence. This may result in incomplete UTF-8 sequences when a multi-byte sequence is truncated somewhere in the middle. Since those characters cannot be recovered anyway, removing them is the right solution in this case.

In code you can do something similar to the example above:

original = b'...'  # byte string containing doubly-encoded UTF-8 data
s = original.decode('UTF-8').encode('mysql_latin1').decode('UTF-8', 'replace')

Another example to ‘fix’ a dump that contains GB2312 (Simplified Chinese) data stored in a MySQL latin1 column, again misinterpreted and encoded to UTF-8 by mysqldump:

$ cat mojibake-crap.sql \
  | python -m mysql_latin1_codec -f UTF-8 \
  | iconv -f GB2312 -t UTF-8 \
  > legible-text-in-utf8.sql

Technical background

WL#1494 notes:

We'll use cp1252 instead of iso-8859-1.
cp1252 contains printable characters in the range 0x80-0x9F.
In ISO 8859-1, these code points have no associated printable
characters. Therefore, by converting from CP1252 to ISO 8859-1,
one would lose the euro (for instance). Since most people are
unaware of the difference, and since we don't really want a
"Windows ANSI" to differ from a "Unix ANSI", we will:

 - continue to pretend the latin1 character set is ISO 8859-1
 - actually allow the storage of euro etc. so it's actually cp1252

Also we'll map these five undefined cp1252 character:
  0x81, 0x8D, 0x8F, 0x90, 0x9D
into corresponding control characters:
   U+0081, U+008D, U+008F, U+0090, U+009D.
like ISO-8859-1 does. Otherwise, loading "mysqldump"
output doesn't reproduce these undefined characters.

I have no clue what this is all about!

No worries, it’s okay.