title: Why INSERT IGNORE increments the auto_increment primary key?
body: <p>I wrote a java program that accesses a MySQL innodb database. </p>
  
  <p>Whenever an INSERT IGNORE statement encounters a duplicate entry the Auto Increment primary key is incremented.</p>
  
  <p>Is this behaviour the expected? I think it shouldn't happen with IGNORE. That means that IGNORE actually incurs an extra overhead for writing the new primary key value.</p>
  
  <p>The table is the following:</p>
  
  <pre><code>CREATE TABLE `tablename` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `rowname` varchar(50) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `rowname` (`rowname`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>Thank you!</p>