title: Too many auto increments with ON DUPLICATE KEY UPDATE
body: <p>I have a basic table with columns:</p>
  
  <ul>
  <li>id (primary with AI)</li>
  <li>name (unique)</li>
  <li>etc</li>
  </ul>
  
  <p>If the unique column doesn't exist, INSERT the row, otherwise UPDATE the row....</p>
  
  <pre><code>INSERT INTO pages (name, etc)
  VALUES
    'bob',
    'randomness'
  ON DUPLICATE KEY UPDATE
   name = VALUES(name),
   etc = VALUES(etc)
  </code></pre>
  
  <p>The problem is that if it performs an UPDATE, the auto_increment value on the id column goes up. So if a whole bunch of UPDATES are performed, the id auto_increment goes through the roof.</p>
  
  <p>Apparently it was a bug: <a href="http://bugs.mysql.com/bug.php?id=28781" rel="nofollow noreferrer">http://bugs.mysql.com/bug.php?id=28781</a></p>
  
  <p>...but I'm using InnoDB on mySQL 5.5.8 on shared hosting.</p>
  
  <p>Other people having issues with no solution years ago:
  <a href="https://stackoverflow.com/questions/5924762/prevent-autoincrement-on-mysql-duplicate-insert">prevent autoincrement on MYSQL duplicate insert</a> and 
  <a href="https://stackoverflow.com/questions/2787910/why-does-mysql-autoincrement-increase-on-failed-inserts">Why does MySQL autoincrement increase on failed inserts?</a></p>
  
  <p>Ideas on a fix? Have I maybe structured the database incorrectly somehow?</p>
  
  <p>******EDIT****: It appears adding innodb_autoinc_lock_mode = 0 to your my.ini file fixes the problem but what options do I have for shared hosting?</p>
  
  <p>******EDIT 2******: OK, I think my only option is to change to MyISAM as the storage engine. Being a mega mySQL newbie, I hope that doesn't cause many issues. Yeah?</p>