SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767

[latinvm]

After I have installed the package using composer I have run

php yii migrate --migrationPath=@bedezign/yii2/audit/migrations

This was the result:

*** applying m150626_000003_create_audit_error
    > create table {{%audit_error}} ... done (time: 0.051s)
    > add foreign key fk_audit_error_entry_id: {{%audit_error}} (entry_id) references {{%audit_entry}} (id) ... done (time: 0.114s)
    > create index idx_message on {{%audit_error}} (message(256)) ...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: ALTER TABLE audit_error ADD INDEX idx_message (message(256))'

in C:\Htdocs\yii2project\vendor\yiisoft\yii2\db\Schema.php:636

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

Stack trace:
#0 C:\Htdocs\yii2project\vendor\yiisoft\yii2\db\Command.php(852): yii\db\Schema->convertException(Object(PDOException), 'ALTER TABLE au...')
#1 C:\Htdocs\yii2project\vendor\yiisoft\yii2\db\Migration.php(472): yii\db\Command->execute()
#2 C:\Htdocs\yii2project\vendor\bedezign\yii2-audit\src\migrations\m150626_000003_create_audit_error.php(25): yii\db\Migration->createIndex('idx_message', '{{%audit_error}...', Array)
#3 C:\Htdocs\yii2project\vendor\dmstr\yii2-migrate-command\MigrateController.php(554): m150626_000003_create_audit_error->up()
#4 C:\Htdocs\yii2project\vendor\dmstr\yii2-migrate-command\MigrateController.php(204): dmstr\console\controllers\MigrateController->migrateUp('m150626_000003_...', '@bedezign/yii2/...')
#5 [internal function]: dmstr\console\controllers\MigrateController->actionUp(0)
#6 C:\Htdocs\yii2project\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)
#7 C:\Htdocs\yii2project\vendor\yiisoft\yii2\base\Controller.php(156): yii\base\InlineAction->runWithParams(Array)
#8 C:\Htdocs\yii2project\vendor\yiisoft\yii2\console\Controller.php(128): yii\base\Controller->runAction('', Array)
#9 C:\Htdocs\yii2project\vendor\yiisoft\yii2\base\Module.php(523): yii\console\Controller->runAction('', Array)
#10 C:\Htdocs\yii2project\vendor\yiisoft\yii2\console\Application.php(180): yii\base\Module->runAction('migrate', Array)
#11 C:\Htdocs\yii2project\vendor\yiisoft\yii2\console\Application.php(147): yii\console\Application->runAction('migrate', Array)
#12 C:\Htdocs\yii2project\vendor\yiisoft\yii2\base\Application.php(380): yii\console\Application->handleRequest(Object(yii\console\Request))
#13 C:\Htdocs\yii2project\yii(27): yii\base\Application->run()
#14 {main}

I am using 10.1.14-MariaDB in combination with PHP 7.0.7 on Windows 10, after some googling I found that the cause of this problem is the index on the message(256) field when using INNODB tables.

According tho this article: https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes the limits are:

• INNODB utf8 VARCHAR(255)
• INNODB utf8mb4 VARCHAR(191)

I recall running into something like this earlier, the trick was to simply use MyISAM. What would be the easiest way of fixing this without us needing to modify your code?

[cornernote]

Rings a bell... Is there anything in #122 that helps?

[latinvm]

It looks like the author was planning on changing the field size, I'm not sure if this was ever done.

The solution is probably to change the following: ALTER TABLE audit_error ADD INDEX idx_message (message(255))'

[luckyalexandr]

add to migration file $tableOptions = null; if ($this->db->driverName === 'mysql') { $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB'; }

[kayro2222]

@luckyalexandr this helped me! thank you!