title: Data too long for column error with national characters
body: <p>I have to port some <strong>DBS</strong> into stand alone <strong>MySQL Version: 5.0.18</strong> running on <strong>Windows7 64bit</strong> and I got a problem I am stuck with. If I try to insert any national/unicode character into <code>varchar</code> I got error:</p>
  
  <pre><code>ERROR 1406 (22001): Data too long for column 'nam' at row 1
  </code></pre>
  
  <p><strong>Here MCVE SQL script:</strong></p>
  
  <pre><code>SET NAMES utf8;
  
  DROP TABLE IF EXISTS `tab`;
  CREATE TABLE `tab` (`ix` INT default 0,`nam` VARCHAR(1024) default '' ) DEFAULT CHARSET=utf8;
  INSERT INTO `tab` VALUES (1,'motorček');
  INSERT INTO `tab` VALUES (2,'motorcek');
  SELECT * FROM `tab`;
  </code></pre>
  
  <p><strong>And here output:</strong></p>
  
  <pre><code>mysql&gt; SET NAMES utf8;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; DROP TABLE IF EXISTS `tab`;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; CREATE TABLE `tab` (`ix` INT default 0,`nam` VARCHAR(1024) default '' ) DEFAULT CHARSET=utf8;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; INSERT INTO `tab` VALUES (1,'motorček');
  ERROR 1406 (22001): Data too long for column 'nam' at row 1
  mysql&gt; INSERT INTO `tab` VALUES (2,'motorcek');
  Query OK, 1 row affected (0.00 sec)
  
  mysql&gt; SELECT * FROM `tab`;
  +------+----------+
  | ix   | nam      |
  +------+----------+
  |    2 | motorcek |
  +------+----------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>As you can see the entry with national character <code>č</code> <code>E8h</code> is missing.</p>
  
  <p><strong>I am aware of these QAs:</strong></p>
  
  <ul>
  <li><a href="https://stackoverflow.com/q/202205/2521214">How to make MySQL handle UTF-8 properly</a></li>
  <li><a href="https://stackoverflow.com/q/18897420/2521214">“Data too long for column” - why?</a></li>
  <li><a href="https://stackoverflow.com/q/15949038/2521214">Error Code: 1406. Data too long for column - MySQL</a></li>
  </ul>
  
  <p>but they do not address this problem (no solution from any of those work for this).</p>
  
  <p>This problem is present even for single character strings. No matter the size of <code>VARCHAR</code>. So the only solution for now is change the national characters into <strong>ASCII</strong> but that would lose information which I would rather avoid.</p>
  
  <p>I tried using various character sets <code>utf8, ucs2, latin1</code> without any effect.</p>
  
  <p>I tried drop the <code>STRICT_TRANS_TABLES</code> as some of the other answers suggest but that has no effect with this either (and the string size is many times bigger than needed).</p>
  
  <p>Does anyone have any clues? May be it has something to do with fact that this <strong>MySQL</strong> server is standalone (it is not installed) it is started with this <strong>cmd</strong>:</p>
  
  <pre><code>@echo off
  
  bin\mysqld --defaults-file=bin\my.ini --standalone --console --wait_timeout=2147483 --interactive_timeout=2147483
  
  if errorlevel 1 goto error
  goto finish
  
  :error
  echo.
  echo MySQL could not be started
  pause
  
  :finish
  </code></pre>
  
  <p>and queries are done inside console started like this <strong>cmd</strong>:</p>
  
  <pre><code>@echo off
      bin\mysql.exe -uroot -h127.0.0.1 -P3306
  rem bin\mysql.exe -uroot -proot -h127.0.0.1 -P3306
  </code></pre>