phpmyadmin Question posted in
The official documentation can be found here.

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

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
Tags:

4

Answers


  1. 0

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

    app/Providers/AppServiceProvider.php

    use IlluminateSupportFacadesSchema;

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

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

    Login or Signup to reply.
  2. 0

    Read this.

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

    use IlluminateSupportFacadesSchema;

public function boot()
{
    Schema::defaultStringLength(191);
}
    Login or Signup to reply.
  3. 0

    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 IlluminateSupportFacadesSchema::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

    Login or Signup to reply.
  4. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search