{"action":"created","issue":{"url":"https://api.github.com/repos/ivaldi/brimir/issues/188","labels_url":"https://api.github.com/repos/ivaldi/brimir/issues/188/labels{/name}","comments_url":"https://api.github.com/repos/ivaldi/brimir/issues/188/comments","events_url":"https://api.github.com/repos/ivaldi/brimir/issues/188/events","html_url":"https://github.com/ivaldi/brimir/issues/188","id":73794380,"number":188,"title":"Error importing non UTF-8 characters in emails","user":{"login":"JWeiry","id":5743761,"avatar_url":"https://avatars.githubusercontent.com/u/5743761?v=3","gravatar_id":"","url":"https://api.github.com/users/JWeiry","html_url":"https://github.com/JWeiry","followers_url":"https://api.github.com/users/JWeiry/followers","following_url":"https://api.github.com/users/JWeiry/following{/other_user}","gists_url":"https://api.github.com/users/JWeiry/gists{/gist_id}","starred_url":"https://api.github.com/users/JWeiry/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/JWeiry/subscriptions","organizations_url":"https://api.github.com/users/JWeiry/orgs","repos_url":"https://api.github.com/users/JWeiry/repos","events_url":"https://api.github.com/users/JWeiry/events{/privacy}","received_events_url":"https://api.github.com/users/JWeiry/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":6,"created_at":"2015-05-07T01:23:20Z","updated_at":"2015-05-08T01:38:29Z","closed_at":null,"body":"Some emails sent to BRIMIR appear to be encoded with UTF-8, however they seem to contain non UTF-8 characters.\r\nIt is possible to track some of the characters using http://www.i18nqa.com/debug/utf8-debug.html\r\n\r\nAn example of the email content trying to be inserted is ```Youâ€™ll need to``` when you can clearly see that the content was supposed to have been \"You'll need to\"\r\nHere are some frequent occurrences\r\n```\r\nâ€ªâ€ªâ€« â€€ \r\nAdministrator\\n\\n\\n\\n                        â€«*Important Notice*\\n\r\nKind regards,â€ªâ€ªâ€«\\n>\\n> â€ªâ€ªâ€« â€€\\n>\\n> Name Here\r\n```\r\nThis is the error log from logs/production.log\r\n*(This is from a spam marketing email as an example. Most emails with errors will contain many more encoding issues.)*\r\n```\r\nI, [2015-03-20T16:54:37.254368 #15585]  INFO -- : Started POST \"/tickets.json\" for 127.0.0.1 at 2015-03-20 16:54:37 +1100\r\nI, [2015-03-20T16:54:37.258107 #15585]  INFO -- : Processing by TicketsController#create as JSON\r\nI, [2015-03-20T16:54:37.258773 #15585]  INFO -- :   Parameters: {<snipped json>}\r\nD, [2015-03-20T16:54:37.573504 #15585] DEBUG -- :   (0.6ms)  SELECT COUNT(*) FROM `email_addresses` WHERE (`email_addresses`.`verification_token` IS NOT NULL)ESC[0m\r\nI, [2015-03-20T16:54:37.573948 #15585]  INFO -- : Received mail (251.2ms)\r\nD, [2015-03-20T16:54:37.574004 #15585] DEBUG -- : <email content>\r\nD, [2015-03-20T16:54:37.581005 #15585] DEBUG -- :   User Load (1.1ms)  SELECT  `users`.* FROM `users` WHERE `users`.`email` = '<email>'  ORDER BY `users`.`id` ASC LIMIT 1\r\nD, [2015-03-20T16:54:37.720004 #15585] DEBUG -- :    (0.4ms)BEGIN\r\nD, [2015-03-20T16:54:37.722914 #15585] DEBUG -- :   User Exists (0.4ms)  SELECT  1 AS one FROM `users` WHERE `users`.`email` = BINARY '<email>' LIMIT 1\r\nD, [2015-03-20T16:54:37.726141 #15585] DEBUG -- :   SQL (0.7ms) INSERT INTO `users` (`email`, `encrypted_password`, `created_at`, `updated_at`) VALUES ('<email>', '<password>', '2015-03-20 05:54:37.723497', '2015-03-20 05:54:37.723497')\r\nD, [2015-03-20T16:54:37.729406 #15585] DEBUG -- :    (1.8ms)  COMMIT\r\nD, [2015-03-20T16:54:37.731635 #15585] DEBUG -- :    (0.1ms)  BEGIN\r\nD, [2015-03-20T16:54:37.734863 #15585] DEBUG -- :   SQL (0.7ms)  INSERT INTO `tickets` [....]\r\nI, [2015-03-20T16:54:37.738072 #15585]  INFO -- : Completed 500 Internal Server Error in 479ms\r\nF, [2015-03-20T16:54:37.742106 #15585] FATAL -- : ActiveRecord::StatementInvalid (\r\nMysql2::Error: Incorrect string value: '\\xE2\\x80\\x8B\\x0D\\x0A\\x0D...' for column 'content' at row 1: \r\nINSERT INTO `tickets` (`user_id`, `subject`, `content`, `message_id`, `content_type`, `created_at`, `updated_at`) \r\nVALUES (67, '<domain>', \r\n'Hi,\\r\\n\\r\\nWe specialise in social-media-management and work with ma\r\nny companies like  \\r\\nyours by taking care of their Facebook, Twitter, Linkedin and other rele  \\r\\nvant social media sites.\\r\\n\\r\\n\\r\\nExposure to these sites can be vital to\r\n the health of your business and the  \\r\\nright marketing campaign can launch your business to a new level benefiting  \\r\\nfrom the thousands of visitors to these sites.\\r\\n\\r\\\r\nn\\r\\nWe\\'d like to talk to you to listen to your needs to see if we can become a  \\r\\nvital asset to your company by managing your social media campaign. We have  \\r\\nvery affo\r\nrdable packages in place which are designed to take care of all  \\r\\nyour web needs and create more enquiries,sales&profit.\\r\\n\\r\\n\\r\\nIf you would like to have a no-obligation\r\n discussion with me, please reply  \\r\\nwith your phone number?\\r\\n\\r\\n\\r\\nBest regards,\\r\\n\\r\\n<E2><80><8B>\\r\\n\\r\\n<name>\\r\\n\\r\\nSocial-Media-Specialists\\r\\n\\r\\n\\r\\n\\r\\n<C5>\r\n<A1>mm\\r\\nSMM Pty Ltd\\r\\nHeadquarters: <details>\\r\\nOther Offices: <details>\\r\\n\\r\\n\\r\\n\\r\\n\\r\\n', \r\n'047d7b2e501a8909280512e15299@google.com', \r\n'text', \r\n'2015-03-20 05:54:37.732659', \r\n'2015-03-20 05:54:37.732659')):\r\n  app/mailers/ticket_mailer.rb:94:in `receive'\r\n  app/controllers/tickets_controller.rb:117:in `create'\r\n```\r\nIn the above, you can see the text at fault ```<E2><80><8B>``` which matches to ```\\xE2\\x80\\x8B``` in the query error.\r\nThis is an example of how some of the content can appear in the logs/production.log\r\n```\r\n<p class=3DMsoNormal style=3D'text-autospace:none'><span s=\r\ntyle=3D'font-size:13.0pt;font-family:\"Arial\",sans-serif;mso-fareast-langu=\r\nage:EN-AU'>=E2=80=AA=E2=80=AA=E2=80=AB</span><span style=3D'mso-fareast-l=\r\nanguage:EN-AU'><o:p></o:p></span></p>\r\n```\r\n\r\nIn order to get around some of the issue, we have had to change the MySQL collation from ```latin1_swedish_ci``` to ```utf8_general_ci``` to be able to manually import the queries using the failed SQL queries from the log. However changing the collation doesn't allow the mails to be imported automatically, only via a manual process.\r\n\r\nSystem Information:\r\nUbuntu 14.04.2 LTS\r\nmysql  Ver 14.14 Distrib 5.5.41, for debian-linux-gnu (x86_64) using readline 6.3\r\n\r\nMail Alias:\r\n```\r\nbrimir: \"|/bin/bash /home/brimir/brimir/script/post-mail https://localhost:3000/tickets.json\"\r\nsupport: support, brimir\r\n```\r\n\r\nStartup Script:\r\n```\r\n#!/bin/bash\r\nBRIMIR=/home/brimir/brimir\r\nPASSENGER=/usr/bin/passenger\r\nexport RAILS_ENV=production\r\n$PASSENGER start \\\r\n--ssl \\\r\n--ssl-certificate-key=/etc/ssl/private/<ssl>.key \\\r\n--ssl-certificate=/etc/ssl/private/<ssl>.crt -d \\\r\n--user brimir \\\r\n--log-file $BRIMIR/brimir.log \\\r\n--pid-file $BRIMIR/brimir.pid\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/ivaldi/brimir/issues/comments/100067697","html_url":"https://github.com/ivaldi/brimir/issues/188#issuecomment-100067697","issue_url":"https://api.github.com/repos/ivaldi/brimir/issues/188","id":100067697,"user":{"login":"JWeiry","id":5743761,"avatar_url":"https://avatars.githubusercontent.com/u/5743761?v=3","gravatar_id":"","url":"https://api.github.com/users/JWeiry","html_url":"https://github.com/JWeiry","followers_url":"https://api.github.com/users/JWeiry/followers","following_url":"https://api.github.com/users/JWeiry/following{/other_user}","gists_url":"https://api.github.com/users/JWeiry/gists{/gist_id}","starred_url":"https://api.github.com/users/JWeiry/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/JWeiry/subscriptions","organizations_url":"https://api.github.com/users/JWeiry/orgs","repos_url":"https://api.github.com/users/JWeiry/repos","events_url":"https://api.github.com/users/JWeiry/events{/privacy}","received_events_url":"https://api.github.com/users/JWeiry/received_events","type":"User","site_admin":false},"created_at":"2015-05-08T01:38:29Z","updated_at":"2015-05-08T01:38:29Z","body":"Will try to verify shortly. We are requesting some of the clients which had the problem to send new test emails through to the system as we can't seem to generate them ourselves."}}