title: MySQL - Invalid utf8mb4 character string on update
body: <p>My question is almost exactly the same as the one found here</p>
  
  <p><a href="https://stackoverflow.com/questions/18769315/mysql-1300-invalid-utf8-character-string-on-update">MySQL - 1300 - Invalid utf8 character string on update</a></p>
  
  <p>No solution was presented and the help from the person (creating a temporary table) didn't seem to help. Here is the select statement I am using:</p>
  
  <pre><code>SELECT
      CONVERT(line_1 USING utf8mb4),
      CONVERT(line_1 USING latin1),
      HEX(line_1)
  FROM address
  WHERE ((CAST(CONVERT(line_1 USING latin1) AS CHAR)) &lt;&gt; (CAST(line_1 AS CHAR)))
      AND CONVERT(line_1 USING utf8mb4) IS NULL;
  +-------------------------------+------------------------------+----------------------------------------------------+
  | CONVERT(line_1 USING utf8mb4) | CONVERT(line_1 USING latin1) | hex(line_1)                                        |
  +-------------------------------+------------------------------+----------------------------------------------------+
  | NULL                          | Högbergsgatan 97             | 48F6676265726773676174616E203937                   |
  | NULL                          | Zücherstrasse 161            | 5AFC636865727374726173736520313631                 |
  | NULL                          | 2275, Rue de l'Université    | 323237352C20527565206465206C27556E69766572736974E9 |
  | NULL                          | Högbergsgatan 97             | 48F6676265726773676174616E203937                   |
  +-------------------------------+------------------------------+----------------------------------------------------+
  </code></pre>
  
  <p>When I try to run the following update command I get:</p>
  
  <pre><code>UPDATE address
  SET line_1 = CONVERT(CAST(CONVERT(line_1 USING latin1) AS CHAR) USING utf8mb4)
  WHERE (CAST(CONVERT(line_1 USING latin1) AS CHAR) &lt;&gt; CAST(line_1 AS CHAR))
      AND CONVERT(line_1 USING utf8mb4) IS NULL;
  ERROR 1300 (HY000): Invalid utf8mb4 character string: 'F66762'
  </code></pre>
  
  <p>I tried setting the line in the following ways, all producing the same error:</p>
  
  <pre><code>SET line_1 = CAST(CONVERT(line_1 USING latin1) AS CHAR)
  SET line_1 = CONVERT(line_1 USING latin1)
  </code></pre>
  
  <p>I also looked at <a href="http://jonisalonen.com/2012/fixing-doubly-utf-8-encoded-text-in-mysql/" rel="nofollow noreferrer">http://jonisalonen.com/2012/fixing-doubly-utf-8-encoded-text-in-mysql/</a> to see if maybe it was a double encoding issue, but none of these worked and I kept getting the same character string error.</p>
  
  <p>Additionally, I looked at <a href="https://mathiasbynens.be/notes/mysql-utf8mb4" rel="nofollow noreferrer">https://mathiasbynens.be/notes/mysql-utf8mb4</a> in order to help with the conversion steps, but utf8mb4 and utf8 are causing the exact same issues. (At first I thought it was a utf8 thing so I switched to utf8mb4 and when I still kept getting the same issues, I knew there was a deeper problem)</p>
  
  <p>As you can see, something weird is going on. Looking at my show create address table, I can verify that the charset is set properly:</p>
  
  <pre><code>SHOW CREATE TABLE address;
  | address | CREATE TABLE `address` (
    `addressid` bigint(20) NOT NULL AUTO_INCREMENT,
    `addressuuid` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
    `line_1` blob,
    PRIMARY KEY (`addressid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=48970 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='All potential addresses' |
  </code></pre>
  
  <p>Additionally, you can see my character variables are correct in my instance:</p>
  
  <pre><code>mysql&gt; show variables like 'char%';
  +--------------------------+----------------------------+
  | Variable_name            | Value                      |
  +--------------------------+----------------------------+
  | character_set_client     | utf8mb4                    |
  | character_set_connection | utf8mb4                    |
  | character_set_database   | utf8                       |
  | character_set_filesystem | binary                     |
  | character_set_results    | utf8mb4                    |
  | character_set_server     | utf8mb4                    |
  | character_set_system     | utf8                       |
  | character_sets_dir       | /usr/share/mysql/charsets/ |
  +--------------------------+----------------------------+
  8 rows in set (0.00 sec)
  </code></pre>
  
  <p><strong>How did I get here</strong></p>
  
  <p>So it might be beneficial to give some background information on the issue just in case it is something in the background that caused the issue.</p>
  
  <p>I had a database that originally was set to latin1 encoding everything. I then ran the following code:</p>
  
  <pre><code>SET NAMES 'latin1';
  
  /* We must change things to blob and then back again */
  ALTER TABLE `address` CHANGE line_1 line_1 BLOB;
  ALTER TABLE `address` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  ALTER TABLE `address` CHANGE line_1 line_1 VARCHAR(64);
  </code></pre>
  
  <p>The reason for switching to blob and then back to varchar is the normal recommended procedure. (www.percona.com/blog/2013/10/16/utf8-data-on-latin1-tables-converting-to-utf8-without-downtime-or-double-encoding/)</p>
  
  <p>Let me know if that helps, and if there is any more information that can be provided. I'm using MySQL 5.6 so theoretically it should handle things better, but who knows. As there are only 4 lines I could just manually update each one, but theoretically there is a greater underlying issue and since I do actually have quite a few more columns to go through, it would be nice to make sure I have a functional way to handle these cases in case I get something with quite a few lines.</p>