1071 add unique `translations_table_name_column_name_foreign_key_locale_unique - 小众知识

1071 add unique `translations_table_name_column_name_foreign_key_locale_unique

2024-03-20 06:14:09 苏内容
  标签: mysql
阅读:730
  • Laravel Version: 5.6.*
  • Voyager Version: latest
  • PHP Version: 7.2.4
  • Database Driver & Version: mysql 5.7.21

Description:

I have created a database with the collation utf8mb4_unicode_ci
I added this code to the AppServiceProvider

use Illuminate\Support\Facades\Schema;
	public function boot()
	{
		Schema::defaultStringLength(191);
	}

and still can't install voyager and get the same message

Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table translations add unique translations_table_name_column_name_foreign_key_locale_unique(table_name, column_name, foreign_key, locale))



I found the solution at this:
#901 (comment)
it's about specifying the database engin.
'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',
but why it is not clear at the documentation!



  • Laravel Version: 5.5
  • Voyager Version: 1.0
  • PHP Version: 7.0.11
  • Database Driver & Version: MySQL 5.7.14

Description:

I have a fresh new installation of Laravel 5.5, i added Voyager's dependency via "composer require tcg/voyager".

The problem occur when i try to install voyager with "php artisan voyager:install" so i have these errors :

1-   [Illuminate\Database\QueryException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table `translations` add unique `translati
  ons_table_name_column_name_foreign_key_locale_unique`(`table_name`, `column_name`, `foreign_key`, `locale`))
2 - [Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is ```
1000 bytes

3 -   [PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes


No worries. I definitely understand the frustration.

The original issue is because laravel(or one of it's dependencies) changed the default character encoding used, which means each character requires 4 bytes instead of 1-2, leading to less available space for indices to make use of.

The reason I say this is our problem to fix is because we use a (unique) index that combines 4 fields, whose total length violates the size limit.

This was supposedly fixed in MySQL 5.7.7(or mariadb 10.2.2), but we need to be cognizant of what we're using for indices.


i made this change and worked for me
`$table->string('table_name')->unique();
$table->string('column_name')->unique();
$table->integer('foreign_key')->unsigned()->unique();
$table->string('locale')->unique();

        $table->text('value');

        //$table->unique(['table_name', 'column_name', 'foreign_key', 'locale']);`



There is a way to try:

database/migrations/2017_01_14_005015_create_translations_table.php

    public function up()
    {        Schema::create('translations', function (Blueprint $table) {            $table->increments('id');            $table->string('table_name', 80);            $table->string('column_name', 80);            $table->integer('foreign_key')->unsigned();            $table->string('locale', 80);            $table->text('value');            $table->unique(['table_name', 'column_name', 'foreign_key', 'locale']);            $table->timestamps();
        });
    }

table_name: 80 * 4bytes = 320bytes
column_name: 80 * 4bytes = 320bytes
foreign_key: 10 * 4bytes = 40bytes
locale: 80 * 4bytes = 320bytes

unique field: 320bytes + 320bytes + 40bytes + 320bytes = 1000bytes



This happens to me when I tried to install voyager. After few digging I found these two answers which working
try this on the /config/database.php

'mysql' => [
...,
...,
'engine' => null,
]

to
'mysql' => [
...,
...,
'engine' => 'InnoDB',
]

its work for me.



Because it's a database level issue, not necessarily a Voyager issue. Also, we just haven't put it there yet. Readme.io allows you to suggest changes, so feel free to do so

扩展阅读
相关阅读
© CopyRight 2010-2021, PREDREAM.ORG, Inc.All Rights Reserved. 京ICP备13045924号-1