Renaming primary column fails in mysql

[ruudboon]

When renaming the the primary auto increment key in mysql the migration fails.

For example if you rename an auto index column called test_id into id you get the following error

ALTER TABLE `products` ADD `id` INT(11) NOT NULL AUTO_INCREMENT FIRSTERROR: SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

[sergeysviridenko]

@ruudboon could you please provide a bit more information and steps to reproduce?

[ruudboon]

@sergeysviridenko Steps:

• Created table in mysql with primary id: "test_id".
• phalcon migration --action=generate (1.0.0)
• Rename test_id to id
• phalcon migration --action=generate (1.0.1)
• Delete table or create empty database
• Phalcon migration --action=run

Results in:

ERROR: SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

Migration files

<?php 

use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Db\Reference;
use Phalcon\Mvc\Model\Migration;

/**
 * Class TestMigration_100
 */
class TestMigration_100 extends Migration
{
    /**
     * Define the table structure
     *
     * @return void
     */
    public function morph()
    {
        $this->morphTable('test', [
                'columns' => [
                    new Column(
                        'test_id',
                        [
                            'type' => Column::TYPE_INTEGER,
                            'notNull' => true,
                            'autoIncrement' => true,
                            'size' => 11,
                            'first' => true
                        ]
                    ),
                    new Column(
                        'name',
                        [
                            'type' => Column::TYPE_VARCHAR,
                            'notNull' => true,
                            'size' => 255,
                            'after' => 'test_id'
                        ]
                    )
                ],
                'indexes' => [
                    new Index('PRIMARY', ['test_id'], 'PRIMARY')
                ],
                'options' => [
                    'TABLE_TYPE' => 'BASE TABLE',
                    'AUTO_INCREMENT' => '1',
                    'ENGINE' => 'InnoDB',
                    'TABLE_COLLATION' => 'latin1_swedish_ci'
                ],
            ]
        );
    }

    /**
     * Run the migrations
     *
     * @return void
     */
    public function up()
    {

    }

    /**
     * Reverse the migrations
     *
     * @return void
     */
    public function down()
    {

    }

}

1.0.1

<?php 

use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Db\Reference;
use Phalcon\Mvc\Model\Migration;

/**
 * Class TestMigration_101
 */
class TestMigration_101 extends Migration
{
    /**
     * Define the table structure
     *
     * @return void
     */
    public function morph()
    {
        $this->morphTable('test', [
                'columns' => [
                    new Column(
                        'id',
                        [
                            'type' => Column::TYPE_INTEGER,
                            'notNull' => true,
                            'autoIncrement' => true,
                            'size' => 11,
                            'first' => true
                        ]
                    ),
                    new Column(
                        'name',
                        [
                            'type' => Column::TYPE_VARCHAR,
                            'notNull' => true,
                            'size' => 255,
                            'after' => 'id'
                        ]
                    )
                ],
                'indexes' => [
                    new Index('PRIMARY', ['id'], 'PRIMARY')
                ],
                'options' => [
                    'TABLE_TYPE' => 'BASE TABLE',
                    'AUTO_INCREMENT' => '1',
                    'ENGINE' => 'InnoDB',
                    'TABLE_COLLATION' => 'latin1_swedish_ci'
                ],
            ]
        );
    }

    /**
     * Run the migrations
     *
     * @return void
     */
    public function up()
    {

    }

    /**
     * Reverse the migrations
     *
     * @return void
     */
    public function down()
    {

    }

}

[sergeysviridenko]

@ruudboon thank you. I'm in process of rewriting migration part of devtools. I'll try to deal with this asap. One more thing: could you provide used SQL to?

[ruudboon]

Awesome! Thnx

[ruudboon]

@sergeysviridenko What SQL do yo need? Running the posted migration files should do the trick.

[amnuts]

I'm running into this same issue when trying to morph a new primary column and change the previous on only to be a regular index. What's the latest on the devtools?

[sergeysviridenko]

@ruudboon @amnuts Migration in devtools provide useful migration tools, but it can work with common situation. But it's very specific situation and I'm not sure it can be automated. I guess it must be user responsibility, because automation can create new problems. For example work with table data from file will be broken. There're the few ways to solve this problem. For example change a migration file, use SQL ALTER TABLE ... CHANGE first_col sec_col col_params or other. So I propose to close this issue, because it can't be implemented even in 4.0.x branch.