Migration Fails with Exception: SQLSTATE[42000]: Syntax error or access violation: 1071

Hello, and thanks for such a great Yii2 extension!

Running the settings migration on my local machine worked perfectly. However, when I go to run the migration on my testing server, I am receiving the following error: create unique index settings_unique_key_section on {{%settings}} (section,key) ...Exception: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes

I've briefly researched this SQL error, and it seems to be related to the database encoding and field lengths, but I can't say that I understand it completely. The following posts seem to address this issue: http://stackoverflow.com/questions/10642429/error-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-mysql http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes/1814594#1814594

I ended up creating a custom migration file that I can run during deployment. You'll note in the migration file below that I had to explicitly set the encoding to be utf8 via the tableOptions. By explicitly setting the utf8 encoding, the error went away and my testing server was happy again :)

Just thought I'd share this info in case anyone else runs into a similar challenge.

class m160521_165503_pheme_settings_tables extends \yii\db\Migration
{
    public function up()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB';
        }

        $this->createTable(
            '{{%settings}}',
            [
                'id' => $this->primaryKey(),
                'type' => $this->string(255)->notNull(),
                'section' => $this->string(255)->notNull(),
                'key' => $this->string(255)->notNull(),
                'value' => $this->text(),
                'active' => $this->boolean(),
                'created' => $this->dateTime(),
                'modified' => $this->dateTime(),
            ],
            $tableOptions
        );
        $this->createIndex('settings_unique_key_section', '{{%settings}}', ['section', 'key'], true);
    }

    public function down()
    {
        $this->dropIndex('settings_unique_key_section', '{{%settings}}');
        $this->dropTable('{{%settings}}');
    }
}

phemellc / yii2-settings

Migration Fails with Exception: SQLSTATE[42000]: Syntax error or access violation: 1071 #52