Voyager: laravel 5.4 install error

Created on 10 Mar 2017 · 15Comments · Source: the-control-group/voyager

Laravel Version: 5.4.*
Voyager Version: 0.11.5
PHP Version: 7.0.1
Database Driver & Version: mysql 5.5

Description:

AppServiceProvider is add Schema::defaultStringLength(191);

Steps To Reproduce:

install error

λ php artisan voyager:install
Publishing the Voyager assets, database, and config files
Copied Directory [\vendor\tcg\voyager\publishable\assets] To [\public\vendor\tcg\voyager\assets]
Copied Directory [\vendor\tcg\voyager\publishable\database\migrations] To [\database\migrations]
Copied Directory [\vendor\tcg\voyager\publishable\database\seeds] To [\database\seeds]
Copied Directory [\vendor\tcg\voyager\publishable\demo_content] To [\storage\app\public]
Publishing complete.
Publishing complete.
Migrating the database tables into your application
Migration table created successfully.


  [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_fore
  ign_key_locale_unique`(`table_name`, `column_name`, `foreign_key`, `locale`))



  [Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length
  is 1000 bytes



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

duplicate question

Source

zanjs

👍1

Most helpful comment

If any body facing same problem, Please follow the below steps to fix the issue:

1. Update the "config/database.php" for 'mysql'

'engine' => null,

with

'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

++++++++++++++++++++++++++++++++++++++++++++

2. Update the "app/Providers/AppServiceProvider.php" with

<?php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        // Specified key was too long error, Laravel News post:
        Schema::defaultStringLength(191);
    }

    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

++++++++++++++++++++++++++++++++++++++++++++

3. Execute Command:

php artisan cache:clear
php artisan config:clear
php artisan voyager:install --with-dummy

Now, everything works! :)

neerajsinghsonu on 4 Apr 2017

👍24 🎉5 ❤3 😄3

All 15 comments

@zanjs did you try this? https://laravel-news.com/laravel-5-4-key-too-long-error

wzulfikar on 11 Mar 2017

This is an issue with Laravel 5.4, not with Voyager. Also, it has been asked and answered a few times already. The solution exists in the link @wzulfikar posted.

fletch3555 on 11 Mar 2017

@wzulfikar @fergthh

AppServiceProvider.php It has been modified

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        //
        Schema::defaultStringLength(191);
    }

    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

But still is error！

λ php artisan voyager:install --with-dummy
Publishing the Voyager assets, database, and config files
Copied Directory [\vendor\tcg\voyager\publishable\assets] To [\public\vendor\tcg\voyager\assets]
Copied Directory [\vendor\tcg\voyager\publishable\database\migrations] To [\database\migrations]
Copied Directory [\vendor\tcg\voyager\publishable\database\seeds] To [\database\seeds]
Copied Directory [\vendor\tcg\voyager\publishable\demo_content] To [\storage\app\public]
Publishing complete.
Publishing complete.
Migrating the database tables into your application
Migration table created successfully.


  [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 `tran
  slations` add unique `translations_table_name_column_name_foreign_key_locale_unique`(`table_name`, `column_name`, `foreign_key`, `locale
  `))



  [Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes



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

zanjs on 15 Mar 2017

@zanjs make sure /config/app.php has this:
https://github.com/laravel/laravel/blob/master/config/app.php#L174

abdgad on 15 Mar 2017

@abdgad Thinks！ Let me try.

zanjs on 15 Mar 2017

@zanjs I have same issues like you.

I resolved my problem, from @marktopper in Slack:

Can you please try modifying the migration and replace.

$table->increments('id');

$table->string('table_name', 64);
$table->string('column_name', 64);
$table->integer('foreign_key')->unsigned();
$table->string('locale', 2);

$table->text('value');

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

$table->timestamps();

Great explain from @marktopper :+1:

I will do some more calculations and figure out the best possible setup of that migrations. I just hate this kind of calculations.
The thing is, that the unique key has the max length of 1000 (in your case, this differs upon database driver and version).
The key is calculated by summing up the maximum possible key length of all the related columns.
In this case this is table_name, column_name, foreign_key and locale.
Before the column length of those was 255, 255, 11 and 255 (taken from the order above).
The key length of those are the column length multiplied by the amount of bits available per character.
For normal emoji support, it would be 3, to support all emojis it would be 4, this is defined by the charset and collation.
And that is how to calculate the length of a unique key :slightly_smiling_face:

vanloc0301 on 16 Mar 2017

👍3 ❤1

If any body facing same problem, Please follow the below steps to fix the issue:

1. Update the "config/database.php" for 'mysql'

'engine' => null,

with

'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

++++++++++++++++++++++++++++++++++++++++++++

2. Update the "app/Providers/AppServiceProvider.php" with

<?php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        // Specified key was too long error, Laravel News post:
        Schema::defaultStringLength(191);
    }

    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

++++++++++++++++++++++++++++++++++++++++++++

3. Execute Command:

php artisan cache:clear
php artisan config:clear
php artisan voyager:install --with-dummy

Now, everything works! :)

neerajsinghsonu on 4 Apr 2017

👍24 🎉5 ❤3 😄3

@neerajsinghsonu Can you explain why using 'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',?

Have only some people meet this issues?

Thanks.

vanloc0301 on 4 Apr 2017

👍2

@vanloc0301

DYNAMIC and COMPRESSED Row Formats:

When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page. InnoDB also encodes fixed-length fields greater than or equal to 768 bytes in length as variable-length fields. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

UTF8MB4:

Laravel 5.4 uses the utf8mb4 character set by default, which includes support for storing "emojis" in the database. If you are upgrading your application from Laravel 5.3, you are not required to switch to this character set.

More : MySQL reference

neerajsinghsonu on 4 Apr 2017

👍3 🎉1

@neerajsinghsonu your solution worked for me.

appl4e on 21 Apr 2017

@neerajsinghsonu your solution worked for me, too.

asker-hr on 17 Nov 2017

After some searching I found below solution and want to share it with you
1- set default string size in "app/Providers/AppServiceProvider.php" by adding _Schema::defaultStringLength(191);_ boot method
Schema::defaultStringLength(191);

2- change charset and engine in "config/database.php" by replacing
'mysql' => [ 'driver' => 'mysql', 'host' => env('DB_HOST', '127.0.0.1'), 'port' => env('DB_PORT', '3306'), 'database' => env('DB_DATABASE', 'forge'), 'username' => env('DB_USERNAME', 'forge'), 'password' => env('DB_PASSWORD', ''), 'unix_socket' => env('DB_SOCKET', ''), 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'strict' => true, 'engine' => null, ],
To
'mysql' => [ 'driver' => 'mysql', 'host' => env('DB_HOST', '127.0.0.1'), 'port' => env('DB_PORT', '3306'), 'database' => env('DB_DATABASE', 'forge'), 'username' => env('DB_USERNAME', 'forge'), 'password' => env('DB_PASSWORD', ''), 'unix_socket' => env('DB_SOCKET', ''), 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', 'strict' => true, 'engine' => 'InnoDB ROW_FORMAT=DYNAMIC', ],

vipmaa1 on 18 Nov 2017

👍1

I was using the latest laravel version, 5.5.32 and MySQL latest version V5.77. I did not expect to have this error, based on previous comment and installation instructions. This @ neerajsinghsonu solved it.