title: SQL Server dump of varchar data to file loads in mysql but not mariadb
body: <p>I dumped a lot of string data from a SQL Server database into a text file as INSERTS on my Windows10 workstation using the SQLCMD via the cygwin command line. </p>
  
  <p>It loads into mysql fine, but in mariadb I get this error:</p>
  
  <pre><code>ERROR 1366 (22007) at line 384: 
      Incorrect string value: '\x87ao Su...' for column 'description' at row 1995
  </code></pre>
  
  <p>My SQL Server DB has these settings:</p>
  
  <pre><code>SELECT DISTINCT C.collation_name 
  FROM   sys.tables AS T 
         INNER JOIN sys.columns C 
                 ON T.object_id = C.object_id 
  WHERE  collation_name IS NOT NULL; 
  
  collation_name: Latin1_General_CI_AS
  
  SELECT DISTINCT C.collation_name 
  FROM   sys.tables AS T 
         INNER JOIN sys.columns C 
                 ON T.object_id = C.object_id 
  WHERE  collation_name IS NOT NULL; 
  collation_name: Latin1_General_CI_AS
  </code></pre>
  
  <p>and this is MySQL (where it inserts happily):</p>
  
  <pre><code>SHOW FULL COLUMNS FROM forecast
  --------------
  
  +--------------------+------------------+-----------------+------+-----+-------------------+----------------+---------------------------------+---------+
  | Field              | Type             | Collation       | Null | Key | Default           | Extra          | Privileges                      | Comment |
  +--------------------+------------------+-----------------+------+-----+-------------------+----------------+---------------------------------+---------+
  | description        | varchar(255)     | utf8_unicode_ci | YES  |     | NULL              |                | select,insert,update,references |         |
  
  SELECT @@character_set_database, @@collation_database, @@collation_connection
  --------------
  
  +--------------------------+----------------------+------------------------+
  | @@character_set_database | @@collation_database | @@collation_connection |
  +--------------------------+----------------------+------------------------+
  | utf8                     | utf8_unicode_ci      | utf8_general_ci        |
  +--------------------------+----------------------+------------------------+
  1 row in set (0.01 sec)
  </code></pre>
  
  <p>and this is from MariaDB where it gives that error above:</p>
  
  <pre><code>SHOW FULL COLUMNS FROM forecast
  --------------
  
  +--------------------+------------------+-----------------+------+-----+---------------------+----------------+---------------------------------+---------+
  | Field              | Type             | Collation       | Null | Key | Default             | Extra          | Privileges                      | Comment |
  +--------------------+------------------+-----------------+------+-----+---------------------+----------------+---------------------------------+---------+
  | description        | varchar(255)     | utf8_unicode_ci | YES  |     | NULL                |                | select,insert,update,references |         |
  +--------------------+------------------+-----------------+------+-----+---------------------+----------------+---------------------------------+---------+
  10 rows in set (0.01 sec)
  
  MariaDB [tardis]&gt; SELECT @@character_set_database, @@collation_database, @@collation_connection;
  --------------
  SELECT @@character_set_database, @@collation_database, @@collation_connection
  --------------
  
  +--------------------------+----------------------+------------------------+
  | @@character_set_database | @@collation_database | @@collation_connection |
  +--------------------------+----------------------+------------------------+
  | utf8                     | utf8_unicode_ci      | utf8_general_ci        |
  +--------------------------+----------------------+------------------------+
  1 row in set (0.01 sec)
  </code></pre>
  
  <p>As far as I can tell, MariaDB should behave the same as MySQL but it doesn't.</p>
  
  <p>What do I have to do? </p>
  
  <p>I could convert the text with a SQL Server function - at the moment I am just escaping \' characters - but how?</p>
  
  <p><strong>[UPDATE]</strong> Just discovered that MariaDB doesn't have any problems with the data when I run the import SQL commands from file. </p>
  
  <p>It's only when I pipe it directly on the command line from SQLCMD to mysql.exe that I get the error:</p>
  
  <pre><code>$ SQLCMD.EXE -i "c:\dev\workspace\sql\generate-inserts-for-migration.sql" \
         -h-1 -S "myhost" -d MyDBName -r1 -W \
         -b -f o:65001 | mysql -h myhost.amazonaws.com -u adam -p 
  </code></pre>
  
  <p>I wanted to do it like that so that I don't have to create a 10GB SQL file on my hard drive, or I could even run it in a docker container (with limited GB space) on the same network on the cloud.</p>