Issues with keys on MySql about migrations

[gmansilla]

Can you add a real example? please.

[krugvs]

Please see generated and edited files of the migration.

[krugvs]

Generated file:

<?php
class WrongAlterTable extends CakeMigration {

/**
 * Migration description
 *
 * @var string
 * @access public
 */
    public $description = '';

/**
 * Actions to be performed
 *
 * @var array $migration
 * @access public
 */
    public $migration = array(
        'up' => array(
            'create_field' => array(
                'calendar_event_types' => array(
                    'order' => array('type' => 'integer', 'null' => false, 'default' => '0', 'after' => 'id'),
                    'active' => array('type' => 'boolean', 'null' => false, 'default' => '1', 'after' => 'order'),
                ),
                'calendar_holidays' => array(
                    'indexes' => array(
                        'date' => array('column' => array('date', 'type'), 'unique' => 1),
                    ),
                ),
            ),
            'drop_field' => array(
                'calendar_event_types' => array('name', 'created', 'modified',),
                'calendar_holidays' => array('', 'indexes' => array('type', 'date')),

            ),
        ),
        'down' => array(
            'drop_field' => array(
                'calendar_event_types' => array('order', 'active',),
                'calendar_holidays' => array('', 'indexes' => array('date')),
            ),
            'create_field' => array(
                'calendar_event_types' => array(
                    'name' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 100, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
                    'created' => array('type' => 'datetime', 'null' => false, 'default' => NULL),
                    'modified' => array('type' => 'datetime', 'null' => false, 'default' => NULL),
                ),
                'calendar_holidays' => array(
                    'indexes' => array(
                        'type' => array('column' => 'type', 'unique' => 0),
                        'date' => array(),
                    ),
                ),
            ),
        ),
    );

/**
 * Before migration callback
 *
 * @param string $direction, up or down direction of migration process
 * @return boolean Should process continue
 * @access public
 */
    public function before($direction) {
        return true;
    }

/**
 * After migration callback
 *
 * @param string $direction, up or down direction of migration process
 * @return boolean Should process continue
 * @access public
 */
    public function after($direction) {
        return true;
    }
}

Edited file:

<?php
class GoodAlterTable extends CakeMigration {

/**
 * Migration description
 *
 * @var string
 * @access public
 */
    public $description = '';

/**
 * Actions to be performed
 *
 * @var array $migration
 * @access public
 */
    public $migration = array(
        'up' => array(
            'drop_field' => array(
                'calendar_event_types' => array('name', 'created', 'modified',),
                'calendar_holidays' => array('indexes' => array('type', 'date')),
            ),
            'create_field' => array(
                'calendar_event_types' => array(
                    'order' => array('type' => 'integer', 'null' => false, 'default' => '0', 'after' => 'id'),
                    'active' => array('type' => 'boolean', 'null' => false, 'default' => '1', 'after' => 'order'),
                ),
                'calendar_holidays' => array(
                    'indexes' => array(
                        'date' => array('column' => array('date', 'type'), 'unique' => 1),
                    ),
                ),
            ),
        ),
        'down' => array(
            'drop_field' => array(
                'calendar_event_types' => array('order', 'active',),
                'calendar_holidays' => array('indexes' => array('date')),
            ),
            'create_field' => array(

                'calendar_event_types' => array(
                    'name' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 100, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
                    'created' => array('type' => 'datetime', 'null' => false, 'default' => NULL),
                    'modified' => array('type' => 'datetime', 'null' => false, 'default' => NULL),
                ),

                'calendar_holidays' => array(
                    'indexes' => array(
                        'type' => array('column' => 'type', 'unique' => 0),
                        'date' => array('column' => 'date', 'unique' => 0),
                    ),
                ),
            ),
        ),
    );

/**
 * Before migration callback
 *
 * @param string $direction, up or down direction of migration process
 * @return boolean Should process continue
 * @access public
 */
    public function before($direction) {
        return true;
    }

/**
 * After migration callback
 *
 * @param string $direction, up or down direction of migration process
 * @return boolean Should process continue
 * @access public
 */
    public function after($direction) {
        return true;
    }
}

[gmansilla]

Ok 2 more questions:

1. How did you generated that file? I mean, what command did you type
2. Can you give me the sql dump with the structure of this table?

[krugvs]

1. Ubuntu: lib/Cake/Console/cake Migrations.migration generate
2. Sql (after apply migration):

--
-- Table structure for table `calendar_event_types`
--

DROP TABLE IF EXISTS `calendar_event_types`;
CREATE TABLE IF NOT EXISTS `calendar_event_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order` int(11) NOT NULL DEFAULT '0',
  `active` tinyint(1) NOT NULL DEFAULT '1'
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `calendar_holidays`
--

DROP TABLE IF EXISTS `calendar_holidays`;
CREATE TABLE IF NOT EXISTS `calendar_holidays` (
  `id` char(36) NOT NULL,
  `date` date NOT NULL,
  `type` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `date` (`date`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Store days';

[gmansilla]

I'm sorry, I meant the sql structe before running migrations.

And I have these comments:

In your sql structure, there's a missing ", " in "calendar_event_types" after defining "active" field.

I want to see the sql structure before running this migration file because you have a drop field instruction in "calendar_event_types" with a field that does not exist (name)

[krugvs]

Sorry, I cannot prepare sql right now, you can get it run right migration down. Please try. If not, tell me, I'll do it when I will on my computer.

[gmansilla]

No, I couldn't. I am interested in reproducing the exact scenario you are describing, that's why I would like to get the sql structure before you generated the migration file. Thanks!

[gmansilla]

Closing issue since I wasn't able to reproduce the bug and the reporter didn't give the information needed