Edgewall Software

Opened 4 years ago

Last modified 7 months ago

#12363 new enhancement

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

Reported by: Jun Omae Owned by:
Priority: normal Milestone: next-stable-1.2.x
Component: database backend Version:
Severity: normal Keywords: mysql
Cc: Branch:
Release Notes:
API Changes:

Description (last modified by 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@

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
  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__
  File "/venv/trac/1.0.10/lib/python2.5/site-packages/trac/env.py", line 602, in create
  File "/venv/trac/1.0.10/lib/python2.5/site-packages/trac/db/api.py", line 253, in init_db
  File "/venv/trac/1.0.10/lib/python2.5/site-packages/trac/db/mysql_backend.py", line 117, in init_db
  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)

Attachments (0)

Change History (9)

comment:1 by Jun Omae, 4 years ago

Description: modified (diff)
Owner: set to Jun Omae
Status: newassigned

comment:2 by Jun Omae, 4 years ago

Description: modified (diff)
Milestone: next-stable-1.0.x1.0.11
Summary: Support utf16/utf32 charset and utf16_bin/utf32_bin collation in MySQLSupport ucs2/utf16/utf32 charset and utf16_bin/utf32_bin collation in MySQL

Proposed changes in log:jomae.git@t12363.

comment:3 by Ryan J Ollos, 4 years ago


comment:4 by Jun Omae, 4 years ago

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].

comment:5 by Ryan J Ollos, 4 years ago


comment:6 by Ryan J Ollos, 3 years ago


Ticket moved forward so that 1.0.13 can be released.

comment:7 by Jun Omae, 3 years ago


comment:8 by Ryan J Ollos, 3 years ago

Milestone: 1.2.1next-stable-1.2.x

comment:9 by Ryan J Ollos, 7 months ago

Owner: Jun Omae removed
Status: assignednew

Modify Ticket

Change Properties
Set your email in Preferences
as new The ticket will remain with no owner.
The ticket will be disowned. Next status will be 'new'.
as The resolution will be set. Next status will be 'closed'.
The owner will be changed from (none) to anonymous. Next status will be 'assigned'.

Add Comment

E-mail address and name can be saved in the Preferences .
Note: See TracTickets for help on using tickets.