[Bug] Upgrading Blitz to v2.1 - Specified key was too long; max key length is 767 bytes

[HelgeSverre]

I got the following error when trying to upgrade to Blitz 2.1.0 on a Craft CMS 3.2.0 install
I'm running on MariaDB 10.1.37.

Output from php craft migrate/all:

    > alter column uri in table {{%blitz_caches}} to string(500) NOT NULL ...Exception: 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 `blitz_caches` CHANGE `uri` `uri` varchar(500) NOT NULL (C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\db\Schema.php:664)
#0 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\db\Command.php(1295): yii\db\Schema->convertException(Object(PDOException), 'ALTER TABLE `bl...')
#1 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\db\Command.php(1091): yii\db\Command->internalExecute('ALTER TABLE `bl...')
#2 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\db\Migration.php(420): yii\db\Command->execute()
#3 C:\xampp\htdocs\customer-website\vendor\putyourlightson\craft-blitz\src\migrations\m190518_120000_alter_uri_column.php(23): yii\db\Migration->alterColumn('{{%blitz_caches...', 'uri', Object(craft\db\mysql\ColumnSchemaBuilder))
#4 C:\xampp\htdocs\customer-website\vendor\craftcms\cms\src\db\Migration.php(56): putyourlightson\blitz\migrations\m190518_120000_alter_uri_column->safeUp()
#5 C:\xampp\htdocs\customer-website\vendor\craftcms\cms\src\db\MigrationManager.php(243): craft\db\Migration->up(true)
#6 C:\xampp\htdocs\customer-website\vendor\craftcms\cms\src\db\MigrationManager.php(163): craft\db\MigrationManager->migrateUp(Object(putyourlightson\blitz\migrations\m190518_120000_alter_uri_column))
#7 C:\xampp\htdocs\customer-website\vendor\craftcms\cms\src\services\Updates.php(225): craft\db\MigrationManager->up()
#8 C:\xampp\htdocs\customer-website\vendor\craftcms\cms\src\console\controllers\MigrateController.php(259): craft\services\Updates->runMigrations(Array)
#9 [internal function]: craft\console\controllers\MigrateController->actionAll()
#10 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)
#11 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\base\Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#12 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\console\Controller.php(148): yii\base\Controller->runAction('all', Array)
#13 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\base\Module.php(528): yii\console\Controller->runAction('all', Array)
#14 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\console\Application.php(180): yii\base\Module->runAction('migrate/all', Array)
#15 C:\xampp\htdocs\customer-website\vendor\craftcms\cms\src\console\Application.php(93): yii\console\Application->runAction('migrate/all', Array)
#16 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\console\Application.php(147): craft\console\Application->runAction('migrate/all', Array)
#17 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\base\Application.php(386): yii\console\Application->handleRequest(Object(craft\console\Request))
#18 C:\xampp\htdocs\customer-website\craft(22): yii\base\Application->run()
#19 {main}
Exception 'craft\errors\MigrateException' with message 'An error occurred while migrating Blitz.'

in C:\xampp\htdocs\customer-website\vendor\craftcms\cms\src\services\Updates.php:233

Stack trace:
#0 C:\xampp\htdocs\customer-website\vendor\craftcms\cms\src\console\controllers\MigrateController.php(259): craft\services\Updates->runMigrations(Array)
#1 [internal function]: craft\console\controllers\MigrateController->actionAll()
#2 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)
#3 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\base\Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#4 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\console\Controller.php(148): yii\base\Controller->runAction('all', Array)
#5 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\base\Module.php(528): yii\console\Controller->runAction('all', Array)
#6 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\console\Application.php(180): yii\base\Module->runAction('migrate/all', Array)
#7 C:\xampp\htdocs\customer-website\vendor\craftcms\cms\src\console\Application.php(93): yii\console\Application->runAction('migrate/all', Array)
#8 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\console\Application.php(147): craft\console\Application->runAction('migrate/all', Array)
#9 C:\xampp\htdocs\customer-website\vendor\yiisoft\yii2\base\Application.php(386): yii\console\Application->handleRequest(Object(craft\console\Request))
#10 C:\xampp\htdocs\customer-website\craft(22): yii\base\Application->run()
#11 {main}

Does anyone know of a workaround?

I know that this is a known issue in Laravel land (ref), but I am not quite sure how to fix this in Craft.

[putyourlightson]

I'm surprised you were able to install Craft in the first place, as the install migration contains 500 character columns as well. Was this an issue at all?

Maybe this answer will help you solve it: https://stackoverflow.com/a/43403017/1769259

[HelgeSverre]

@putyourlightson Installing craft itself was not an issue at all, I will check out the link you suggested and report back with a workaround if i can figure it out.

[putyourlightson]

Yes, please do.

[peteeveleigh]

I had this same error on my local dev environment using MAMP 4.5 with MySQL 5.6.x
Upgrading MAMP to version 5 which uses MySQL 5.7 appears to have resolved the issue for me.

[jasonmccallister]

@HelgeSverre what is your database character set?

[jasonmccallister]

also, this might be a db change? https://github.com/dektrium/yii2-user/issues/543

[kylecotter]

Have this same issue running MySQL 5.6 in our Docker setup.

[jasonmccallister]

Can someone try switching to innodb on their database server and see if it corrects the issue?

[HelgeSverre]

@HelgeSverre what is your database character set?

latin1

[peteeveleigh]

FYI. I deployed the same project to an existing staging server that was using MySQL 5.6 last night and got the same problem. Provisioned a new server with MySQL 5.7 and everything deployed and updated fine. Wasn't able to try switching to InnoDB.

Encoding on local is UTF-8 Unicode (utf8) and on the new box is UTF-8 Unicode (utf8mb4)

[putyourlightson]

Thanks for the responses.

So for anyone still experiencing this, please note that Craft requires MySQL 5.5+ with InnoDB and recommends a default character set of utf8 and collation of utf8_unicode_ci.

https://docs.craftcms.com/v3/requirements.html

https://docs.craftcms.com/v3/installation.html#step-4-create-a-database

[heidkaemper]

Hmm, same error here. I run a local dev environment with:

XAMPP 7.3.5
PHP 7.3.5
MySQL 5.7
DB Tables are InnoDB and utf8_general_ci

Log:

Database Exception: 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 blitz_caches CHANGE uri uri varchar(500) NOT NULL

[peteeveleigh]

@putyourlightson In my case at least my environment(s) meet those requirements.
I may be misunderstanding the error (likely!) but could it not be fixed by changing blitz_caches.uri column from varchar(500) to varchar(255)? Does it need 500 characters?

[putyourlightson]

It was changed to 500 characters to address an issue someone was having with long URIs. Have changed it back to 255 in version 2.1.1.