title: First column of INSERT INTO over linked server MS SQL -> MySQL always empty string
body: <p>I have a MS SQL 2005 server with a linked server connection to a MySQL 5.1.46 server, using the ODBC Connector 5.1.8 driver.</p>
  
  <p>I want to, from a MS SQL job, truncate and then rebuild a remote MySQL table based on the records contained in an identically-structured local MS SQL table.</p>
  
  <pre><code>Key_ID  varchar(10)  NOT NULL  Primary
  FieldA  varchar(64)
  FieldB  text
  
  Key_ID   FieldA   FieldB
  ======   ======   ======
  ID1      A        A
  ID2      B        B
  ID3      C        C
  </code></pre>
  
  <p>The problem is, when I run the following query from MS SQL, I get an error saying "Duplicate entry '' for key 'PRIMARY'."  If I remove the primary key, it will add all the records but the remote MySQL Key_ID field always returns an empty string, even though no records in the local table are NULL or empty strings.</p>
  
  <pre><code>EXECUTE ('DELETE FROM DB1.Table1') AT REMOTESERVER
  
  INSERT INTO
   OPENQUERY( REMOTESERVER, 'SELECT * FROM DB1.Table1' )
  SELECT
   Key_ID,
   FieldA,
   FieldB
  FROM
   Table1
  </code></pre>
  
  <p>returns</p>
  
  <pre><code>Key_ID   FieldA   FieldB
  ======   ======   ======
           A        A
           B        B
           C        C
  </code></pre>
  
  <p>This makes absolutely no sense.</p>
  
  <p>If I add a dummy field to the beginning of the remote MySQL table, then modify my query to insert a literal value or NULL into it, that field always comes up as an empty string and my Key_ID field returns as expected.</p>
  
  <pre><code>Dummy   varchar(64)
  Key_ID  varchar(10)  NOT NULL  Primary
  FieldA  varchar(64)
  FieldB  text
  </code></pre>
  
  <p>and</p>
  
  <pre><code>EXECUTE ('DELETE FROM DB1.Table1') AT REMOTESERVER
  
  INSERT INTO
   OPENQUERY( REMOTESERVER, 'SELECT * FROM DB1.Table1' )
  SELECT
   'srsly wtf?',
   Key_ID,
   FieldA,
   FieldB
  FROM
   Table1
  </code></pre>
  
  <p>returns</p>
  
  <pre><code>Dummy   Key_ID   FieldA   FieldB
  =====   ======   ======   ======
          ID1      A        A
          ID2      B        B
          ID3      C        C
  </code></pre>
  
  <p>As far as I can tell, the leftmost first column is always returning an empty string.  Does anybody have any possible idea why, or can give me ideas for workarounds that don't involve a dummy field that serves no purpose other than acting as a buffer field?</p>
  
  <p>UPDATE:
  I enabled the debug logging on the ODBC driver, and found that it translates my SQL statement into:</p>
  
  <pre><code>INSERT INTO `DB1`.`Table1` (`Key_ID`, `FieldA`, `FieldB`) VALUES ( DEFAULT, 'A', 'A' )
  </code></pre>
  
  <p>If I add the dummy field, it runs:</p>
  
  <pre><code>INSERT INTO `DB1`.`Table1` (`Dummy`, `Key_ID`, `FieldA`, `FieldB`) VALUES ( DEFAULT, 'ID1', 'A', 'A' )
  </code></pre>
  
  <p>I have no idea what to think now, other than that the driver assumes that the first supplied field is always an incrementing primary key?</p>