Special characters stripped off while saving data in DB through raw sql_query

Question

I am importing a csv with some special characters in my CSV. and i am inserting the row values one by one through direct sql query in magento.

The raw query printed while debugging is

INSERT IGNORE INTO `custom_table` (parent_id, options_id, value, partsfinder_id) VALUES (0,1,'Motò 6.5 / Stark 650', '1')

and the code that i use to insert the data is :

$resource = Mage::getSingleton('core/resource');
$writeAdapter = $resource->getConnection('core_write');
$sql = "INSERT IGNORE INTO `custom_table` (parent_id, options_id, value, partsfinder_id) VALUES (0,1,'Motò 6.5 / Stark 650', '1')";
$writeAdapter->query($sql);

Now the issue i face is in DB, the value Motò 6.5 / Stark 650 is not stored properly. Only the value Mot is stored in the field. i.e. all characters after the special characters are stripped off.

Can anyone tell me the solution for this.

Thanks in advance.

Answer 1

This is a common issue with invalid encoding. You'd likely see the same thing if you copied the query and ran it directly from the mysql cli. If you did that though, you'd probably see there was 1 warning. Which is Data truncated for column xxx.

You need to make sure that the connection, table/column and source string are all valid UTF-8. Non-recognised UTF-8 characters causing truncation is a fairly common/known issue.