when i try to import sql file in c panel phpmyadmin - Specified key was too long; max key length is 767 bytes #1071

Question

there are some similar questions but not what i exactly want. how can i increase this limit. what is file destination where can i replace this number (767 bytes) with another.

-- Dumping structure for table jofr.categories
CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `order` int(11) NOT NULL DEFAULT '1',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `categories_slug_unique` (`slug`),
  KEY `categories_parent_id_foreign` (`parent_id`),
  CONSTRAINT `categories_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
MySQL said: Documentation

#1071 - Specified key was too long; max key length is 767 bytes

Answer 1

You could set the default string length inside the AppServiceProvider.php file

app/Providers/AppServiceProvider.php

use Illuminate\Support\Facades\Schema;

public function boot() 
{
    Schema::defaultStringLength(191); 
}

For more information, you can reference a similar question asked in Stackoverflow

Answer 2

This is a MySQL InnoDB limit, not a PHP one. You can only work around the limit using code, or you can potentially increase the limit using a database option.

The issue is with your index on slug. slug is defined as 255 characters, and you're using utf8mb4, which uses 4 bytes per character, so your index on slug would require 1020 bytes.

There are a couple workaround options.

1. Reduce the size of your field.

Instead of making your slug 255 characters, make it 191. 191 * 4 = 764 < 767. You can do this by specifying the field length in your migration, or by not specifying the length and setting \Illuminate\Support\Facades\Schema::defaultStringLength(191); as mentioned by others.

2. Reduce the size of your index.

You can keep your field size at 255, but tell MySQL to only index the first 191 characters. I don't know if Laravel migrations support this, but you can always try.

$table->index('slug(191)');

3. Enable the innodb_large_prefix database option with DYNAMIC row formats.

The innodb_large_prefix database option increases the key length limit to 3072 bytes. However, this option only affects tables that have a row format of DYNAMIC or COMPRESSED.

If you're on MySQL >= 5.7.7, the innodb_large_prefix option is enabled by default.

If you're on MySQL >= 5.7.9, the default row format is DYNAMIC, and the innodb_large_prefix option is enabled by default, so you wouldn't be having this issue, unless you've changed the defaults.

If you're on MySQL < 5.7.9, the default row format is COMPACT, so you'd need to figure out how to tell Laravel to use the DYNAMIC row format. If you want to do this for all tables, you can set 'engine' => 'InnoDB ROW_FORMAT=DYNAMIC', in your database config. If you only want to do this for one table, you'll need to run raw DB create statements in your migration file.

References:
MySQL create index documentation - information on key size, row formats, and partial indexes
MySQL innodb_large_prefix option
MySQL innodb_default_row_format option

Answer 3

problem is on your unique columns with a length bigger than 191

`slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

in this line replace varchar(255) with varchar(191) and this will be resolved

Answer 4

Read this.

edit your AppServiceProvider.php file and inside the boot method set a default string length:

use Illuminate\Support\Facades\Schema;

public function boot()
{
    Schema::defaultStringLength(191);
}