database migration error in yii framework

Question

I have the following code:

<?php

use yii\db\Migration;

/**
 * Handles the creation of table `account`.
 */
class m180110_204953_create_account_table extends Migration
{
    /**
     * @inheritdoc
     */
    public function up()
    {
        $this->createTable('account', [
            'id' => $this->primaryKey(),
            'username' => $this->string()->notNull()->unique(),
            'profile_pic_url' => $this->string(),
            'full_name' => $this->string(),
            'biography' => $this->string(),
            'external_url' => $this->string(),
            'instagram_id' => $this->string(),
            'updated_at' => $this->dateTime(),
            'created_at' => $this->dateTime(),
            'monitoring' => $this->boolean()->notNull()->defaultValue(0),
        ]);
    }

    /**
     * @inheritdoc
     */
    public function down()
    {
        $this->dropTable('account');
    }
}

upon running this i got the following:

*** applying m180110_204953_create_account_table
    > create table account ...Exception 'yii\db\Exception' with message 'SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
The SQL being executed was: CREATE TABLE `account` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `username` varchar(255) NOT NULL UNIQUE,
    `profile_pic_url` varchar(255),
    `full_name` varchar(255),
    `biography` varchar(255),
    `external_url` varchar(255),
    `instagram_id` varchar(255),
    `updated_at` datetime,
    `created_at` datetime,
    `monitoring` tinyint(1) NOT NULL DEFAULT 0
)'

in /root/ig-monitoring/vendor/yiisoft/yii2/db/Schema.php:664

Error Info:
Array
(
    [0] => 42000
    [1] => 1071
    [2] => Specified key was too long; max key length is 767 bytes
)

Stack trace:
#0 /root/ig-monitoring/vendor/yiisoft/yii2/db/Command.php(1295): yii\db\Schema->convertException(Object(PDOException), 'CREATE TABLE `a...')
#1 /root/ig-monitoring/vendor/yiisoft/yii2/db/Command.php(1091): yii\db\Command->internalExecute('CREATE TABLE `a...')
#2 /root/ig-monitoring/vendor/yiisoft/yii2/db/Migration.php(323): yii\db\Command->execute()
#3 /root/ig-monitoring/migrations/m180110_204953_create_account_table.php(25): yii\db\Migration->createTable('account', Array)
#4 /root/ig-monitoring/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(725): m180110_204953_create_account_table->up()
#5 /root/ig-monitoring/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(199): yii\console\controllers\BaseMigrateController->migrateUp('m180110_204953_...')
#6 [internal function]: yii\console\controllers\BaseMigrateController->actionUp(0)
#7 /root/ig-monitoring/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#8 /root/ig-monitoring/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#9 /root/ig-monitoring/vendor/yiisoft/yii2/console/Controller.php(148): yii\base\Controller->runAction('', Array)
#10 /root/ig-monitoring/vendor/yiisoft/yii2/base/Module.php(528): yii\console\Controller->runAction('', Array)
#11 /root/ig-monitoring/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate', Array)
#12 /root/ig-monitoring/vendor/yiisoft/yii2/console/Application.php(147): yii\console\Application->runAction('migrate', Array)
#13 /root/ig-monitoring/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(yii\console\Request))
#14 /root/ig-monitoring/yii(19): yii\base\Application->run()
#15 {main}

any idea how to fix this ?

score 0 · Answer 1 · answered Aug 08 '19 at 10:53

The problem you are facing is due to the prefix size limit for the collation that your mysql server is applying in your table. You can take a look at this answer to get the complete idea. So either you should check what collation your mysql server is using and then set the varchar size according to that or you should set the collation by yourself in migration and use varchar size according to that. By using utf8_unicode_ci collation and 254 size for varchar columns your migration's up() function would look like this

public function up()
{
    // varchar coulmn size
    $string_size = 254;

    $this->createTable('account', [
        'id' => $this->primaryKey(),
        'username' => $this->string($string_size)->notNull()->unique(),
        'profile_pic_url' => $this->string($string_size),
        'full_name' => $this->string($string_size),
        'biography' => $this->string($string_size),
        'external_url' => $this->string($string_size),
        'instagram_id' => $this->string($string_size),
        'updated_at' => $this->dateTime(),
        'created_at' => $this->dateTime(),
        'monitoring' => $this->boolean()->notNull()->defaultValue(0),
    ],

    // set collation by yourself to be sure of what prefix size you should use
    'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB');
}

database migration error in yii framework

1 Answers1