[SOLVED][Drupal] PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE2\x81\x842 t...

In one of the Drupal sites were working on, the members of the site frequently found that the nodes they had created were crashing. They would enter content into the node fields and try to save the node. The node would appear to save with apparently no error message. They found that it was not accessible at all afterwards. This wasn't occurring for everyone. We checked the dblog and we saw that the users were getting the error PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect string value:. Read on to know how we solved this issue.

We checked the nodes that the users who faced the problem had created. There was an entry for the node in the Drupal node table and in the node revisions table. This means that the node had been assigned a node id and a node revision id. However we could not access the node through the usual "/node/nid" in the URL which was leading to page not found. This seemed pretty strange to us as we had never encountered such a scenario before.

However the error message in the Drupal dblog did leave clues behind. It seems that the error was generated when inserting the content of a field of the node into the database. This lead us to the hypothesis that the field or possibly the content in the field was the culprit. Something in the content saved in the field was preventing MySQL from accepting it. We tried all possible combinations of text we believed the user would use to save the content but we could not recreate the error.

We finally made a breakthrough as the site was using inspectlet which records a users session and we found that the user was copy pasting content from another editor. The site was not supporting full html at that point of time. The content contained the html entity "⁄" used to represent fractions. This was the text that was causing issues.

We checked into why this character was causing issues by inspecting the Drupal database structure of the table of the field. It seemed that the collation of the whole Drupal database was set to latin1_swedish_ci instead of the recommended utf8_general_ci. Converting the table collation to utf8_general_ci solved the problem for us.

So if you are facing the same problem in your Drupal site please have a look at the collations of your Drupal 7 database and ensure that it it set to utf8_general_ci.