Why can't I set the auto_increment value in my magento setup script?

Question

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.

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.

CREATE TABLE mytable (
  myid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  other_column INTEGER NULL
) ENGINE=InnoDb DEFAULT CHARSET=UTF8 AUTO_INCREMENT=5000;

But when I put that exact same query 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.

file: app/code/local/Mycompany/Mymodule/sql/mymodule_setup/mysql4-install-0.0.1.php

<?php
$installer = $this;
$installer->startSetup();
$installer->run("
    CREATE TABLE {$this->getTable('mytable')} (
      myid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
      other_column INTEGER NULL
    ) ENGINE=InnoDb DEFAULT CHARSET=UTF8 AUTO_INCREMENT=5000;
");
$installer->endSetup();

Why is this happening? Are there any workarounds?

(I'll also mention that I have tried to set the auto_increment with an ALTER statement, and I get the same problem)

score 0 · Answer 1 · edited May 23 '17 at 10:34

You can setup your install script as an .sql file instead of .php mysql4-install-0.0.1.sql

Checkout Mage/Core/Model/Resource/Setup.php _modifyResourceDb

            try {
                switch ($fileType) {
                    case 'sql':
                        $sql = file_get_contents($sqlFile);
                        if ($sql!='') {
                            $result = $this->run($sql);
                        } else {
                            $result = true;
                        }
                        break;
                    case 'php':
                        $conn = $this->_conn;
                        $result = include($sqlFile);
                        break;
                    default:
                        $result = false;
                }

Short answer: Script wise I don't think its possible, and using the .sql method may not work as well since it is still calling the the run(); method.

Add an auto_increment column in Magento setup script without using SQL

You can also take a look at lib/Varient/Db/Adapter/Pdo/Mysql.php for more deeper reference to whats going on in the background like, multi_query and _splitMultiQuery

Some more further reading for connection methods can be found here also:

ALTER TABLE in Magento setup script without using SQL

More than likely your going to have to go outside the realm of "The Magento Way" of doing things and after your module is installed make a custom post-install script that would run on your table and adjust the auto_increment directly.

Trying out the .sql method was a good idea but I got the same problem. This module needs to be compatible with Magento 1.3.x (should have mentioned that before!), so the links you provided aren't of any use unfortunately - I think that class was added in 1.4.x. I am thinking that your last paragraph is right, that I'll need to do something outside of the normal magento way to get this to work. — shaune, Jul 11 '11 at 20:29

score 0 · Answer 2 · edited Oct 29 '12 at 16:35

I don't think you can set the primary key that way. All my code is built the fillowing way and it works perfectly fine:

    <?php
$installer = $this;
$installer->startSetup();
$installer->run("
    CREATE TABLE {$this->getTable('mytable')} (
      myid INTEGER NOT NULL auto_increment,
      other_column INTEGER NULL,
      PRIMARY KEY (`myid`)
    ) ENGINE=InnoDb DEFAULT CHARSET=UTF8 AUTO_INCREMENT=5000;
");
$installer->endSetup();

Why can't I set the auto_increment value in my magento setup script?

2 Answers2