title: Why can't I set the auto_increment value in my magento setup script?
body: <p>I am creating a custom module with a few custom database tables.  I need to set the auto_increment value to 5000 rather than having the default of 1.  This can be accomplished pretty easily, but I am running into problems when trying to do it via a Magento install script.  I want to know why, and how to work around the issue.  Here are more details.</p>
  
  <p>When I run the following create statement from a regular mysql client (like Heidi SQL, or the standard cli) the auto_increment value gets set correctly.  It gets set to 5000. </p>
  
  <pre><code>CREATE TABLE mytable (
    myid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    other_column INTEGER NULL
  ) ENGINE=InnoDb DEFAULT CHARSET=UTF8 AUTO_INCREMENT=5000;
  </code></pre>
  
  <p>But when I put that <em>exact same query</em> into a magento install script the auto_increment is set to 1 after it runs.  To be clear, the table is created as I expect, except for the fact that the auto_increment isn't set to 5000.  Here is the code in the install script.</p>
  
  <p>file: app/code/local/Mycompany/Mymodule/sql/mymodule_setup/mysql4-install-0.0.1.php</p>
  
  <pre><code>&lt;?php
  $installer = $this;
  $installer-&gt;startSetup();
  $installer-&gt;run("
      CREATE TABLE {$this-&gt;getTable('mytable')} (
        myid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
        other_column INTEGER NULL
      ) ENGINE=InnoDb DEFAULT CHARSET=UTF8 AUTO_INCREMENT=5000;
  ");
  $installer-&gt;endSetup();
  </code></pre>
  
  <p>Why is this happening?  Are there any workarounds?</p>
  
  <p>(I'll also mention that I have tried to set the auto_increment with an ALTER statement, and I get the same problem)</p>