Tag Archives: SQLSTATE[42000] Syntax error

Laravel5.5 Error: SQLSTATE[42000] Syntax error or access violation 1071 Specified key was too long; max k

Laravel5.5 Configuration Background management Package Voyager problem: SQLSTATE[42000]: Syntax Error or Access Restriction: 1071 Specified key was too long; max key length is 1000 bytes
I heard voyager is good. I’ve tried to create a new file.
D:\wamp64\WWW\laravel\myproject
lambda > PHP artisan voyager: install – with – dummy

Setting up the hooks

Hooks are now ready to use! Go ahead and try to "php artisan hook:install test-hook"
Publishing the Voyager assets, database, language, 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]
Copied Directory [\vendor\tcg\voyager\publishable\lang] To [\resources\lang]
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 users add unique users_email_unique(email))

  [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

The problem is too big, according to the official laravel document, change the file of Laravel 5.5 (../app/will/appServiceProvider. PHP) content, below

<?php

namespace App\Providers;

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


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

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

D: \wamp64\www\laravel\myproject


Setting up the hooks
Hooks are now ready to use! Go ahead and try to "php artisan hook:install test-hook"
Publishing the Voyager assets, database, language, 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]
Copied Directory [\vendor\tcg\voyager\publishable\lang] To [\resources\lang]
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_nam
  e_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

The database shows that users and Migration imported successfully, while others failed. Finally, I went to Github to find the solution
https://github.com/the-control-group/voyager/issues/901#issuecomment-291470960

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! :)

Has a similar problem here: https://github.com/the-control-group/voyager/issues/1333
Make some changes as described above, and then install
D:\wamp64\www\laravel\myproject
Lambda & gt; PHP artisan voyager: install – with – dummy


Setting up the hooks
Hooks are now ready to use! Go ahead and try to "php artisan hook:install test-hook"
Publishing the Voyager assets, database, language, 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]
Copied Directory [\vendor\tcg\voyager\publishable\lang] To [\resources\lang]
Publishing complete.
Publishing complete.
Migrating the database tables into your application
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table
Migrating: 2016_01_01_000000_add_voyager_user_fields
Migrated:  2016_01_01_000000_add_voyager_user_fields
Migrating: 2016_01_01_000000_create_data_types_table
Migrated:  2016_01_01_000000_create_data_types_table
Migrating: 2016_01_01_000000_create_pages_table
Migrated:  2016_01_01_000000_create_pages_table
Migrating: 2016_01_01_000000_create_posts_table
Migrated:  2016_01_01_000000_create_posts_table
Migrating: 2016_02_15_204651_create_categories_table
Migrated:  2016_02_15_204651_create_categories_table
Migrating: 2016_05_19_173453_create_menu_table
Migrated:  2016_05_19_173453_create_menu_table
Migrating: 2016_10_21_190000_create_roles_table
Migrated:  2016_10_21_190000_create_roles_table
Migrating: 2016_10_21_190000_create_settings_table
Migrated:  2016_10_21_190000_create_settings_table
Migrating: 2016_11_30_135954_create_permission_table
Migrated:  2016_11_30_135954_create_permission_table
Migrating: 2016_11_30_141208_create_permission_role_table
Migrated:  2016_11_30_141208_create_permission_role_table
Migrating: 2016_12_26_201236_data_types__add__server_side
Migrated:  2016_12_26_201236_data_types__add__server_side
Migrating: 2017_01_13_000000_add_route_to_menu_items_table
Migrated:  2017_01_13_000000_add_route_to_menu_items_table
Migrating: 2017_01_14_005015_create_translations_table
Migrated:  2017_01_14_005015_create_translations_table
Migrating: 2017_01_15_000000_add_permission_group_id_to_permissions_table
Migrated:  2017_01_15_000000_add_permission_group_id_to_permissions_table
Migrating: 2017_01_15_000000_create_permission_groups_table
Migrated:  2017_01_15_000000_create_permission_groups_table
Migrating: 2017_01_15_000000_make_table_name_nullable_in_permissions_table
Migrated:  2017_01_15_000000_make_table_name_nullable_in_permissions_table
Migrating: 2017_03_06_000000_add_controller_to_data_types_table
Migrated:  2017_03_06_000000_add_controller_to_data_types_table
Migrating: 2017_04_11_000000_alter_post_nullable_fields_table
Migrated:  2017_04_11_000000_alter_post_nullable_fields_table
Migrating: 2017_04_21_000000_add_order_to_data_rows_table
Migrated:  2017_04_21_000000_add_order_to_data_rows_table
Migrating: 2017_07_05_210000_add_policyname_to_data_types_table
Migrated:  2017_07_05_210000_add_policyname_to_data_types_table
Migrating: 2017_08_05_000000_add_group_to_settings_table
Migrated:  2017_08_05_000000_add_group_to_settings_table
Attempting to set Voyager User model as parent to App\User
Dumping the autoloaded files and reloading all new files
Adding Voyager routes to routes/web.php
Seeding data into the database
Adding the storage symlink to your public folder
The [public/storage] directory has been linked.
Successfully installed Voyager! Enjoy

Success!
Analysis: The key is this:

'engine' => 'InnoDB ROW_FORMAT=DYNAMIC'

This means that the MySQL storage engine USES InnoDB(instead of MyISAM), dynamic text format. Of course InnoDB is not without its disadvantages, you can further study this.