Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Randomly seeing database entries truncated to 253 characters on a php site and can't figure out why or if it could be malicious or a corruption issue. Any thoughts would be appreciated.

share|improve this question
2  
What do the column definitions and character set configuration look like? I'm thinking storing utf-8 data in a latin1 column, perhaps? –  Michael - sqlbot Feb 9 '13 at 22:40
    
I know historically that inserting a string longer than the size of the field specified in the table definition in MySQL would silently truncate the data and succeed, whereas most normal RDBMSs return an error. What's the table definition you're inserting to? Specifically, what's the column datatype for the field that's being truncated? –  Bacon Bits Feb 10 '13 at 2:09
add comment

1 Answer

If not configured (by adding strict_trans_tables to your sql_mode settings) to throw errors when presented with invalid data, such as a character value that is longer than the definition of a VARCHAR column allows, MySQL truncates it to fit, and only throws a warning.

My guess is that you've got a VARCHAR(255) column and at some point in the process, there's an inconsistency in character set configuration, and you have some UTF-8 characters that at some point in your system are being treated as latin1, causing the system to misinterpret the number of characters and truncate the value.

Wait, what? How is the number of "characters" different than the number of "bytes?"

In UTF-8 encoding, character values from 0x7F (decimal 127) downward are still encoded as their 1-byte ASCII equivalent. Larger values are encoded as 2 or 3 bytes. Common symbols like © (ASCII 169 0xA9) and ® (ASCII 174 0xAE) are encoded as two bytes, 0xC2 0xA9 and 0xC2 0xAE respectively.

When fully configured for UTF-8, MySQL understands this; using "®" as an example:

mysql> SELECT LENGTH('®'), CHAR_LENGTH('®'), HEX('®');
+--------------+-------------------+-----------+
| LENGTH('®')  | CHAR_LENGTH('®')  | HEX('®')  |
+--------------+-------------------+-----------+
|            2 |                 1 | C2AE      |
+--------------+-------------------+-----------+

The LENGTH() function returns the number of bytes, while CHAR_LENGTH() returns the number of characters.

A VARCHAR column restricts not the number of storable bytes but the number of characters you can store in it, regardless of the byte-width of each character... so a VARCHAR(2) column could actually end up containing 6 bytes of data (plus requiring 1 byte of storage overhead) if the two characters happened to be both 3-byte-wide characters.

However... 0xC2 and 0xAE are both codes for valid characters in the default latin1 character set, so if you were configured with latin1 but sent those characters in their 2-byte unicode encoding, the server would interpret that single unicode character as if it were two characters -- both valid -- thereby reducing the number of printable characters that could be stored in the VARCHAR(255) column by one character for each 2-byte encoding, or reducing it by two characters for each 3-byte encoding.

In the example above, all of my character_set_* values (except character_set_filesystem, which is set to BINARY) were set as utf8, and the CHAR_LENGTH() of a 2-byte character was correctly reported as being "1."

Now, I'll change these settings, to illustrate:

mysql> set character_set_client = latin1, character_set_results = latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select CHAR_LENGTH('®');
+-------------------+
| CHAR_LENGTH('®')  |
+-------------------+
|                 2 |
+-------------------+

Note that I didn't actually change anything about my actual client's behavor -- each time the "®" symbol appears, the character was UTF-8 encoded and there were two bytes being sent and received over the wire. All that changed is that I only told MySQL that what I was sending and what I expected to receive should be interpreted as being sent to the server (character_set_client) and returned to me (character_set_results) as latin1.

http://dev.mysql.com/doc/refman/5.1/en/charset.html

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.