Twitter Emoji issue in Drupal 7 | Trickyworld

Twitter Emoji issue in Drupal 7

05 Feb 2018
Submitted bytrickyworld
Drupal Emoji issue

Hey Guys, Recently i am facing issue while inserting twitter embed code in drupal body field, it's showing

"The website encountered an unexpected error. Please try again later."

I just worried and searching solution for this issue. Drupal show following error:

PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x87\xAE\xF0\x9F...' 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] => 1149194 [:db_insert_placeholder_2] => 1177511 [:db_insert_placeholder_3] => story [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] =>

[:db_insert_placeholder_7] => [:db_insert_placeholder_8] => icon_format ) in field_sql_storage_field_storage_write() (line 514 of /var/www/html/itgcms/modules/field/modules/field_sql_storage/field_sql_storage.module).

Twitter Emoji
Embed Code in Body field

 

Drupal Error
Drupal Front end error

 

Backend error

 

 

There are two solutions to fix this issue.

1. Remove this special charactors using pre_replace. But Emojy will remove from twitter.

2. Change database column "Collation" From utf8_general_ci to utf8mb4_general_ci.

Database Column

 

Solution #1

To remove Special charectors from Node value you have to replace char from hook_node_presave()

$node->body[LANGUAGE_NONE][0]['value'] = replace_emoji($node->body[LANGUAGE_NONE][0]['value']);

function replace_emoji($text){
   $string = str_replace("?", "{%}", $text);
  $string = mb_convert_encoding($string, "ISO-8859-1", "UTF-8");
  $string = mb_convert_encoding($string, "UTF-8", "ISO-8859-1");
  $string = str_replace(array("?", "? ", " ?"), array(""), $string);
  $string = str_replace("{%}", "?", $string);
  return trim($string);
}

 

Please see this link for more reference:

https://www.drupal.org/forum/support/post-installation/2013-07-16/removing-emoji-code

Solution #2

We are recommend to follow second step. Don't worry existing data will not be affected by change from utf8_general_ci to utf8mb4_general_ci.

We have to change this in following tables with Column:

1. field_data_body (body_value and body_summary)

2. field_revision_body (body_value and body_summary)

 

If you have inserting node body value in different table column , then you have to change this.

You can do this using run query in mysql or using create update hook in drupal.

For mysql query:

  1. ALTER TABLE field_data_body MODIFY body_value longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

  2. ALTER TABLE field_data_body MODIFY body_summary longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

  3. ALTER TABLE field_revision_body MODIFY body_value longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

  4. ALTER TABLE field_revision_body MODIFY body_summary longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

 

If you want to do this using Drupal : hook_update

 

just create hook_update_N()

 

function hook_update_N(&$sandbox) {

db_query("ALTER TABLE {field_data_body} MODIFY body_value longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci");

db_query("ALTER TABLE {field_data_body} MODIFY body_summary longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci");

db_query("ALTER TABLE {field_revision_body} MODIFY body_value longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci");

db_query("ALTER TABLE {field_revision_body} MODIFY body_summary longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci");

field_cache_clear(TRUE);

}

 

Note: I recommend to use mysql, if you have large database.

Good news for Drupal 8 Developer. This issue is fixed on Drupal 8. In Drupal they already set Coolate utf8mb4_general_ci. So no need to worry

 

Recent comments

No comments available.

Contact

General Inquiries

Most Welcome if you have any query regarding coding & programming in above mentioned language, Mail me anytime, i would love to help you. Afterall developers are brother from another mother.

vishal1610kamal@gmail.com