Support ucs2/utf16/utf32 charset and utf16_bin/utf32_bin collation in MySQL

[Jun Omae]

In Trac 1.0.10 with MySQL ucs2/utf16/utf32, initenv command crashes:

Database connection string [sqlite:db/trac.db]> mysql://tracuser:password@127.0.0.1/trac_utf16

Creating and Initializing Project
Initenv for '/dev/shm/trac-1.0.10-mysql-utf16' failed.
Failed to create environment.
(1231, "Variable 'character_set_client' can't be set to the value of 'utf16'")
Traceback (most recent call last):
  File "/venv/trac/1.0.10/lib/python2.5/site-packages/trac/admin/console.py", line 461, in do_initenv
    options=options)
  File "/venv/trac/1.0.10/lib/python2.5/site-packages/trac/core.py", line 124, in __call__
    self.__init__(*args, **kwargs)
  File "/venv/trac/1.0.10/lib/python2.5/site-packages/trac/env.py", line 280, in __init__
    self.create(options)
  File "/venv/trac/1.0.10/lib/python2.5/site-packages/trac/env.py", line 602, in create
    DatabaseManager(self).init_db()
  File "/venv/trac/1.0.10/lib/python2.5/site-packages/trac/db/api.py", line 253, in init_db
    connector.init_db(**args)
  File "/venv/trac/1.0.10/lib/python2.5/site-packages/trac/db/mysql_backend.py", line 117, in init_db
    params)
  File "/venv/trac/1.0.10/lib/python2.5/site-packages/trac/db/mysql_backend.py", line 98, in get_connection
    cnx = MySQLConnection(path, log, user, password, host, port, params)
  File "/venv/trac/1.0.10/lib/python2.5/site-packages/trac/db/mysql_backend.py", line 374, in __init__
    cnx.query("SET NAMES %s" % self.charset)
OperationalError: (1231, "Variable 'character_set_client' can't be set to the value of 'utf16'")

If supplementary characters is needed, we have to use utf8mb4 charset prior to MySQL 5.5. Since MySQL 5.5, can use utf16/utf32. However, max size of column would be short because 1 character requires 4 bytes for index. Since MySQL 5.5.3, utf16 supports supplementary characters using surrogate pair. 1 character in utf16 requires 2 bytes for index.

mysql> SHOW CHARACTER SET WHERE Charset = 'ucs2' OR Charset LIKE 'utf%';
+---------+----------------+--------------------+--------+
| Charset | Description    | Default collation  | Maxlen |
+---------+----------------+--------------------+--------+
| utf8    | UTF-8 Unicode  | utf8_general_ci    |      3 |
| ucs2    | UCS-2 Unicode  | ucs2_general_ci    |      2 |
| utf8mb4 | UTF-8 Unicode  | utf8mb4_general_ci |      4 |
| utf16   | UTF-16 Unicode | utf16_general_ci   |      4 |
| utf32   | UTF-32 Unicode | utf32_general_ci   |      4 |
+---------+----------------+--------------------+--------+
5 rows in set (0.01 sec)

[Jun Omae]

Proposed changes in log:jomae.git@t12363.

[Jun Omae]

I noticed MySQLConnector uses 1000 bytes as maximum key length. The limitation is actually 3072 bytes. See ​14.9.7 Limits on InnoDB Tables.

The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. This limit applies to the length of the combined index key in a multi-column index.

Proposed changes for this in [304f04b3/jomae.git].

[Ryan J Ollos]

Ticket moved forward so that 1.0.13 can be released.

[Ryan J Ollos]

Milestone renamed