MySQL/PDO truncates the data

Question

$book is a 7kb string. If this query is executed using PHP PDO exec, the monograph column (LONGTEXT) data gets truncated at 6765 character:

echo strlen($book); // output 7157

$db->exec("UPDATE `chemicals` SET `monograph` = {$db->quote($book)} WHERE `id` = {$db->quote($c['id'])};");

However, if I print the query and execute it using SQL client (bypassing PHP), it inserts all the data to the database. Which makes me think it is PHP setting that I am not yet familiar to.

Note that the same is happening if I use prepared statements (incl. with PDO::PARAM_LOB).

$book value dumped before exec https://gist.github.com/79d5fe1050bbb0e2fac8 (7157). The actual data that ends up at the database https://gist.github.com/df49d4a9707660b8b60b (6765). I don't understand how such data truncation is technically possible since the whole query is passed to MySQL (otherwise SQL syntax error would pop).

echo "UPDATE `chemicals` SET `monograph` = {$db->quote($book)} WHERE `id` = {$db->quote($c['id'])};";

If I execute the output (https://gist.github.com/a05fe4c033e74897b82b) using SQL client, this is the data that ends up in the database https://gist.github.com/88870fe26a3ae40e991e (7157, expected).

PDO is initiated using UTF8 connection.

new PDO('mysql:dbname=[..];host=localhost;charset=utf8', 'root', '[..]', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';"));

---

UPDATE 2012 07 25 04:11 EST

Now I know that's encoding issue.

$db->exec("UPDATE `chemicals` SET `monograph` = {$db->quote(utf8_decode($book))} WHERE `id` = {$db->quote($c['id'])};");

However, I am not quiet sure what to do about it. My connection to MySQL is unicode already.

/etc/my.cnf is configured to use the following settings server-wide.

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

Answer 1

There are two points to be made here. One is that ideally all character encodings must be UTF8 - that's server, client, connection, and table. Two is that PHP's strlen function counts bytes, not characters.

Your table character set may not be set to UTF8. You can do

SHOW CREATE TABLE chemicals;

to check that. You should also add these to your my.cnf:

[mysqld]
character-set-client=utf8
character-set-results=utf8

Read more about MySQL character sets here:

MySQL character sets

Answer 2

It turned out to be that it is encoding issue. The are two solutions. The most obvious is to fix the encoding to match the database/connection settings. In my case, I was getting a iso-8859-1 string and interpreting it as a unicode.

However, it shouldn't be an issue anyway. I went further to discover that PDO::ATTR_EMULATE_PREPARES is set to TRUE by default.

• You will find this topic as a useful summary to understand what does the latter setting mean, PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?.
• This topic will explain why the encoding messed up the query, The way PDO parametrized query works.