title: Converting iso-8859-1 data to UTF-8 in UTF8 and Latin1 tables
body: <p><strong>Problem Summary:</strong></p>
  
  <p>While trying to convert a site with mysql database from latin1 to utf8, some special characters are not displaying correctly despite ensuring charsets are all utf8 system wide. </p>
  
  <p><strong>Problem Details:</strong></p>
  
  <p>This is a common problem. But I seem to have an added complexity.</p>
  
  <p>Years ago, a oblivious developer (me), put a site together with MySQL. Some tables were setup with latin1_swedish_ci and utf8_general_ci. All input/display was done via pages with iso-8859-1 charset.</p>
  
  <p>Now, I have the task of turning all this data into utf-8 and thus finally uniforming the encoding. However, i'm having issues with a number of special characters in both instances (ie: ü). The characters don't seem to display correctly on a UTF-8 page. They display as �.Instead  When viewing the data in a utf8 table in mysql query browser, a correctly entered utf8'd 'u' displays as some special characters, while an incorrectly latin1 'u' displays as it should appear on page. But it doesn't.</p>
  
  <p>I've tried a number of things:</p>
  
  <ol>
  <li>Percona script: <a href="https://github.com/rlowe/mysql_convert_charset" rel="noreferrer">https://github.com/rlowe/mysql_convert_charset</a></li>
  <li>converting col to binary and then to utf8</li>
  <li>converting utf8 tables to latin and then repeat above process</li>
  </ol>
  
  <p>Nothing seems to cure the data.</p>
  
  <p>Dumping the entire database and important isn't really a viable option as it's a huge database now and downtime is restricted.</p>
  
  <p><strong>UPDATE (22-Oct-2013)</strong></p>
  
  <p>I've taken @deceze suggestions and reviewed all my content encoding areas as per <a href="http://kunststube.net/frontback/" rel="noreferrer">http://kunststube.net/frontback/</a>. I did find a few places in which I was still passing/encoding data in latin1. So, i've now changed it all over to UTF-8. However, the data is still displaying incorrectly in a particular field. In a table which is in utf8 (no columns have implicit encoding), field1 is in latin1. I can confirm this by running the following which displays the text correctly:</p>
  
  <blockquote>
    <p>select convert(cast(convert(field1 using latin1) as binary) using
    utf8) from my table WHERE id = 1</p>
  </blockquote>
  
  <p>This will convert HahnemÃ¼hle to Hahnemühle. </p>
  
  <p>In field2, it appears the data is in a different (unknown) encoding. The query above, when used on field2 converts Hahnemï¿½hle to Hahnem�hle. I've gone through all the charsets on <a href="http://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html</a> replacing latin1 but none seem to spit out the data correctly.</p>