configuring drupal to use unicode characters

Question

I'm working on a site for mathematics so every so often I have to deal with Unicode characters like "𝔅". Right off the bat, this causes trouble for my Drupal 7.15. For example: I create a Basic Page with Full HTML format, and paste that character in, and I get this error:

PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect string value:
'\xF0\x9D\x94\x85' for column 'body_value' at row 1: INSERT INTO {field_data_body}
(entity_type, entity_id, revision_id, bundle, delta, language, body_value,
body_summary, body_format) VALUES (:db_insert_placeholder_0,
:db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3,
:db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6,
:db_insert_placeholder_7, :db_insert_placeholder_8); Array (
[:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 86234
[:db_insert_placeholder_2] => 29768 [:db_insert_placeholder_3] => page
[:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => en
[:db_insert_placeholder_6] => 𝔅 [:db_insert_placeholder_7] =>
[:db_insert_placeholder_8] => full_html ) in field_sql_storage_field_storage_write()
(line 448 of /home/joe/staging/drupal_planetary/modules/field/modules/field_sql_storage/field_sql_storage.module).

Now, I recognized that this was probably something to do with the way the database was set up, so I followed some guides around the internet, like this one: http://cameronyule.com/2008/07/configuring-mysql-to-use-utf-8/ and things are looking good, after a fresh install:

mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

I even adjusted the way the database is invoked inside settings.php:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'MYDB',
      'username' => 'MYUSER',
      'password' => 'MYPASS',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
      'pdo' => array(
                     PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
                     )
    ),
  ),
);

But, no luck, I still get the error when pasting my 𝔅. Are there further database tweaks I should try?

One further note: I've tried running utf8_encode(...) on the content that I expect to have unicode symbols in it, and this works as a temporary work around. In other words, I can save content, the pages display without problem. But then I wait a while (24 hours?) and come back and the pages are full of garbled unicode expressions!

Can you help me figure out how to either make this workaround unnecessary, or how to make it work and stay working?

Answer 1

After further investigation: "𝔅" and other characters beyond the Basic Multilingual Plane need to be stored in a database set up with the "utf8mb4" charset. The general configuration steps are very nicely described here:

http://mathiasbynens.be/notes/mysql-utf8mb4

In addition to updating MySQL to 5.5.28, and doing the basic tweaks outlined in that link, in order to get this working with Drupal, I needed to take some further steps:

• Replace SET NAMES utf8 with SET NAMES utf8mb4 in includes/database/mysql/database.inc

• Modify settings.php to include the following

  $databases = array (
    'default' => 
    array (
      'default' => 
      array (
        'database' => 'DB',
        'username' => 'UN',
        'password' => 'PW',
        'collation' => 'utf8mb4_general_ci',
        'host' => 'localhost',
        'port' => '',
        'driver' => 'mysql',
        'prefix' => '',
        'pdo' => array(
                       PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4",
                       )
      ),
    ),
  );
  

• And modify the table and column (per the page linked to above) where the fancy UTF8 characters are to be stored:

  ALTER TABLE tn CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  ALTER TABLE tn CHANGE cn cn longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

Answer 2

I am thinking that converting the whole txt file to UTF-format and parsing the CSV is not a good idea. The issue happens when the character set is not in UTF-8 format.
If you got the error while using the db_insert() then you can use the following code, when it is just matter of some field values.

$val = mb_check_encoding($val, 'UTF-8') ? $val : utf8_encode($val);