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

时间：2021-07-23 15:53:17

Laravel5.5 配置后台管理包Voyager问题：SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes

听说voyager不错，新建立一个文件尝试了一下，

D:\wamp64\www\laravel\myproject
λ> 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

问题让人头大，根据laravel官方的文档，更改laravel5.5的文件（ ../app/providers/appServiceProvider.php）内容 , 如下

<?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

λ> 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 `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

数据库显示，users和migration成功导入，其他还是失败了。最后到github找到这个解决方案

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

这里也有类似的问题：https://github.com/the-control-group/voyager/issues/1333

根据上述做了些更改，然后安装

D:\wamp64\www\laravel\myproject

λ> 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

成功！

分析：关键是这个：

'engine' => 'InnoDB ROW_FORMAT=DYNAMIC'

上述这名话的意思是，采用MySQL存储引擎采用InnoDB(而不是MyISAM)，动态文本格式。当然InnoDB不是没有缺点，这个大家可以自己进一步深入研究。

标签：

相关文章