| Bug #31242 | SUBSTRING not multi-byte safe | ||
|---|---|---|---|
| Submitted: | 27 Sep 2007 14:36 | Modified: | 23 Oct 2007 20:19 |
| Reporter: | Bob Kline | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
| Version: | 5.0.45, 5.0.32 | OS: | Any |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
| Tags: | substr, substring, UTF-8 | ||
[27 Sep 2007 14:46]
Bob Kline
"Run the following script ..." with db user-id password as required command-line arguments.
[28 Sep 2007 8:03]
Sveta Smirnova
Thank you for the report. Bug version 5.0.32 is quite old. Please upgrade to current version 5.0.45, try with it and say us result.
[28 Sep 2007 13:00]
Bob Kline
Same problem on 5.0.45:
(('5.0.45-community-nt',),)
(1, 'PDQ\xc2\xae Protocols')
((1L, 'PDQ\xc2'),)
[8 Oct 2007 6:00]
Alexander Barkov
MySQL uses latin1 by default.
Please add "SET NAMES" here:
...
conn = MySQLdb.connect(db = db, user = user, passwd = passwd)
cursor = conn.cursor()
cursor.execute("SET NAMES utf8");
cursor.execute("SELECT VERSION()")
...
After this change, the result is as you expected:
((1L, 'PDQ\xc2\xae'),)
[8 Oct 2007 14:11]
Bob Kline
Thanks, Alexander. This provides a workaround to the problem. However, I have a number of comments:
1. "SET NAMES" is an odd name for a command which affects values as well
as names.
2. It is unfortunate that this oddly-named command is not mentioned in
the documentation for the substring command.
3. The documentation for SET NAMES does not correspond to the observed
behavior reported for this issue. That documentation says "SET
NAMES indicates what character set the client will use to send SQL
statements to the server." This would not have any affect on the
query being sent in this case, as the query itself ("SELECT id,
SUBSTR(name, 1, 4) FROM utf8test" contains only 7-bit ASCII
characters. The documentation continues "It also specifies the
character set that the server should use for sending results back
to the client." The important distinction to note is that "sending
results back to the client" is not the same operation as determining
what those results are. The server has been told that the values
stored in the utf8test table are stored using utf-8. Therefore,
the SUBSTR() function should be extracting the first 4 Unicode
characters from the `name` column with the understanding that some
of the characters can be represented by more than one byte. At
this stage of processing, the server should have the 4 characters
P, D, Q, and U+00AE as the value returned by the SUBSTR() function.
The next step, "sending results back to the client" involves
converting this Unicode string to the character set to be used for
the client. This stage has no knowledge of the length parameter
passed to the SUBSTR() function, and therefore there is no rational
explanation (consistent with the documentation) for what was
actually done in the process of converting the results to the Latin-1
character set, which was to discard one of the two bytes used for
representing the character U+00AE. While it is not always possible
for the server to represent all Unicode characters using Latin-1
characters, there is no difficulty in conveying the value in this
case to the client with no loss of information by sending the 4th
character as its Latin-1 equivalent. Therefore I must conclude that,
in spite of what the documentation says about "SET NAMES," the
command is affecting not how the results are conveyed to the client,
but rather how the results are computed further upstream in the
process. This being so, I would say that even if we are not dealing
with a bug in the server, we at least have a bug in the documentation,
in addition to the odd mismatch between the name of the command and
its effect.
[9 Oct 2007 6:29]
Alexander Barkov
Everything works as expected and quite straightforward. Your client is configured to use latin1, which means Microsoft version of latin1 (aka cp1252) in MySQL. The column character set is utf8. This is how it works, step by step: 1. Client sends this string: PDQ\xc2\xae Or in HEX: \x50\x44\x51\xC2\xAE Server interprets it according to this mapping table: 0x50 0x0050 #LATIN CAPITAL LETTER P 0x44 0x0044 #LATIN CAPITAL LETTER D 0x51 0x0051 #LATIN CAPITAL LETTER Q 0xC2 0x00C2 #LATIN CAPITAL LETTER A WITH CIRCUMFLEX 0xAE 0x00AE #REGISTERED SIGN 2. Server notices that the column is UTF8, and converts everything to UTF8. After conversion it writes this utf8 string into the column: \x50\x44\x51\xC3\x82\xC2\xAE the parts of which are: 0x50 #LATIN CAPITAL LETTER P 0x44 #LATIN CAPITAL LETTER D 0x51 #LATIN CAPITAL LETTER Q 0xC382 #LATIN CAPITAL LETTER A WITH CIRCUMFLEX 0xC2AE #REGISTERED SIGN 3. Then you do "SELECT id, SUBSTR(name, 1, 4) FROM utf8test". Server reads four leftmost characters: 0x50 #LATIN CAPITAL LETTER P 0x44 #LATIN CAPITAL LETTER D 0x51 #LATIN CAPITAL LETTER Q 0xC382 #LATIN CAPITAL LETTER A WITH CIRCUMFLEX 4. Then server notices that the client is running in latin1 and converts this string from utf8 to latin1. You get this string on the client side: 0x50 0x0050 #LATIN CAPITAL LETTER P 0x44 0x0044 #LATIN CAPITAL LETTER D 0x51 0x0051 #LATIN CAPITAL LETTER Q 0xC2 0x00C2 #LATIN CAPITAL LETTER A WITH CIRCUMFLEX
[9 Oct 2007 6:34]
Alexander Barkov
When you configure client to use utf8 (by issuing SET NAMES utf8), it works as follows: 1. Client sends this string: PDQ\xc2\xae Or in HEX: \x50\x44\x51\xC2\xAE Server interprets it as utf8: 0x50 #LATIN CAPITAL LETTER P 0x44 #LATIN CAPITAL LETTER D 0x51 #LATIN CAPITAL LETTER Q 0xC2AE #REGISTERED SIGN 2. Server notices that both client and column use UTF8, not conversion happens. So this utf8 string is written as is into the column: \x50\x44\x51\xC2\xAE 3. Then you do "SELECT id, SUBSTR(name, 1, 4) FROM utf8test". Server reads four leftmost characters: 0x50 #LATIN CAPITAL LETTER P 0x44 #LATIN CAPITAL LETTER D 0x51 #LATIN CAPITAL LETTER Q 0xC2AE #REGISTERED SIGN 4. Then server notices that the client is running in utf8, no conversion is needed from utf8 to utf8, so it sends the string back as is, and you get this string on the client side: \x50\x44\x51\xC2\xAE
[9 Oct 2007 7:00]
Alexander Barkov
Let me answer your comments:
> 1. "SET NAMES" is an odd name for a command which affects values as
> well
> as names.
I agree with you that this command may sound odd,
but this is the Standard SQL command to tell
client's character set.
Take a look into a copy of the standards SQL99 or SQL2003.
This is an excerpt from SQL2003:
> .ISO/IEC 9075-2:2003 (E) 18.7 <set names statement>
>
> 18.7 <set names statement>
> Function
> Set the default character set name for <character string literal>s
> in <preparable statement>s that are prepared in the current SQL-session
> by an <execute immediate statement> or a <prepare statement> and in
> <direct SQL statement>s that are invoked directly.
>
> Format
> <set names statement> ::= SET <character set name characteristic>
> <character set name characteristic> ::= NAMES <value specification>
MySQL just follows the Standard, instead of inventing its own commands.
>
> 2. It is unfortunate that this oddly-named command is not mentioned in
> the documentation for the substring command.
I'll ask our doc team to review the related manual sections.
>
> 3. The documentation for SET NAMES does not correspond to the
> observed
> behavior reported for this issue. That documentation says "SET
> NAMES indicates what character set the client will use to send SQL
> statements to the server." This would not have any affect on the
> query being sent in this case, as the query itself ("SELECT id,
> SUBSTR(name, 1, 4) FROM utf8test" contains only 7-bit ASCII
> characters.
Right, this query doesn't have any literals with non-ASCII characters.
However, the previous query with INSERT does. So "SET NAMES" does
affect the INSERT statement. I can't see any problems with manuual here.
> The documentation continues "It also specifies the
> character set that the server should use for sending results back
> to the client."
That's true. This is how "SET NAMES" affects the SELECT statement.
Everything seems correct.
> The important distinction to note is that
> "sending
> results back to the client" is not the same operation as
> determining
> what those results are. The server has been told that the values
> stored in the utf8test table are stored using utf-8. Therefore,
> the SUBSTR() function should be extracting the first 4 Unicode
> characters from the `name` column with the understanding that some
> of the characters can be represented by more than one byte.
This is exactly how it works. The SUBSTRING() function, which is
a part of the SELECT query, operates with utf8 values. "SET NAMES"
does not affect it in any ways. "SET NAMES" makes server convert
to the client character set only at the very end of processing,
after SUBSTRING() is done.
Please see my previous comments, how conversion on INSERT and SELECT works.
> At
> this stage of processing, the server should have the 4 characters
> P, D, Q, and U+00AE as the value returned by the SUBSTR()
> function.
> The next step, "sending results back to the client" involves
> converting this Unicode string to the character set to be used for
> the client. This stage has no knowledge of the length parameter
> passed to the SUBSTR() function, and therefore there is no
> rational
> explanation (consistent with the documentation) for what was
> actually done in the process of converting the results to the
> Latin-1
> character set, which was to discard one of the two bytes used for
> representing the character U+00AE. While it is not always
> possible
> for the server to represent all Unicode characters using Latin-1
> characters, there is no difficulty in conveying the value in this
> case to the client with no loss of information by sending the 4th
> character as its Latin-1 equivalent. Therefore I must conclude
> that,
> in spite of what the documentation says about "SET NAMES," the
> command is affecting not how the results are conveyed to the
> client,
> but rather how the results are computed further upstream in the
> process. This being so, I would say that even if we are not
> dealing
> with a bug in the server, we at least have a bug in the
> documentation,
> in addition to the odd mismatch between the name of the command
> and
> its effect.
>
I hope my previous comments explain why you get not what you expected.
I can't see any problems neither with MySQL behavior, nor with manual.
Changing status to "Documenting".
Dear doc team: please consider extending or rephrasing
the related manual sections, then set status to "Not a bug".
Thanks!
[9 Oct 2007 12:33]
Bob Kline
Alexander: Thanks for your explanations. I see what's happening now. I made the mistake of believing the following statement in the documentation [9.3, Specifying Character Sets and Collations]: "There are default settings for character sets and collations at four levels: server, database, table, and column. The following description may appear complex, but it has been found in practice that multiple-level defaulting leads to natural and obvious results." The actual truth is that there are five levels of defaults, not four, as you very carefully explained. Not being aware of this fifth level, I assumed that all I had to do was: 1. Tell MySQL that I was storing UTF-8 characters in the tables 2. Carefully encode my Unicode string values using UTF-8 3. Store the encoded values in the database I'll hazard a guess that I'm not the only developer who's been bitten by this problem. In fact, because in some cases even if one is unaware of this fifth level of defaults it will appear that the storage and retrieval of UTF-8 encoded values is working properly (until, for example a call to SUBSTR() hits the middle of a character), I'll guess further that there are developers who have fallen into this trap and aren't even aware of it (yet). So now I have to modify a bunch of software to assure MySQL that I really am storing UTF-8 encoded values, and figure out how to undo the double UTF-8 encoding which has been applied to the values that are in the database (and figure out if any corruption has been introduced by the double encoding). Thanks again. Bob
[17 Oct 2007 13:21]
Paul DuBois
Bob, Could you clarify what you're referring to as the fifth level of default in your comment of [9 Oct 14:33]? Thanks.
[17 Oct 2007 14:20]
Bob Kline
Hi, Paul. I'm referring to the fact that if "SET NAMES utf8" (or the functional equivalent) is not specified, the connection CHARSET defaults to latin-1. Bob
[23 Oct 2007 19:19]
Paul DuBois
I've updated section 9.3 to point out that you also need to be aware of the communication character sets.
[23 Oct 2007 20:19]
Bob Kline
Excellent, thanks!
Description: The SUBSTRING() function is returning N bytes when N characters are requested for a utf-8 encoded Unicode value, even though the documentation claims that the function is "multi-byte safe" and that the "... forms with a len argument return a substring len characters long from string str ...." How to repeat: Run the following script: #!/usr/bin/python import MySQLdb, sys db = sys.argv[1] user = sys.argv[2] passwd = sys.argv[3] conn = MySQLdb.connect(db = db, user = user, passwd = passwd) cursor = conn.cursor() cursor.execute("SELECT VERSION()") print cursor.fetchall() # (('5.0.32-Debian_7etch1-log',),) try: cursor.execute("DROP TABLE utf8test") conn.commit() except: pass cursor.execute("""\ CREATE TABLE utf8test (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(32) NOT NULL) CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB""") conn.commit() value = u"PDQ\u00ae Protocols".encode('utf-8') print repr(value) # 'PDQ\xc2\xae Protocols' cursor.execute("INSERT INTO utf8test (name) VALUES (%s)", value) conn.commit() cursor.execute("SELECT id, SUBSTR(name, 1, 4) FROM utf8test") print cursor.fetchall() # ((1L, 'PDQ\xc2'),) cursor.execute("DROP TABLE utf8test") conn.commit() Suggested fix: Count characters, not bytes.