"php artisan migrate" error ... access violation: 1071 Specified key was too long

[Djas2017]

Trying to install a new instance on CentOS 7.2

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table
subscribers add unique subscribers_email_unique(email))

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

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

The only tables created in Mysql are:

show tables; +--------------------+ | Tables_in_cachet | +--------------------+ | component_groups | | components | | incident_templates | | incidents | | metric_points | | metrics | | migrations | | settings | | subscribers | +--------------------+

And seems that some tables are missing (users, ...)

[jbrooksuk]

What version of MySQL are you using?

[Djas2017]

DB server : Server version: 5.5.52-MariaDB MariaDB Server PHP version: PHP 5.6.30 (cli) (built: Jan 19 2017 07:57:06) OS: CentOS Linux release 7.3.1611 (Core)

[jbrooksuk]

MariaDB isn't officially supported. Can you try MySQL? We've had no issues with the indexes there.

[Djas2017]

I will try to install it using Mysql server and provide the details. Thank you

[Djas2017]

Tested on: MySQL server: Server version: 5.6.36 MySQL Community Server (GPL) CentOS Linux release 7.3.1611 (Core) PHP 5.6.30 (cli) (built: Jan 19 2017 07:57:06)

php artisan migrate Migration table created successfully.

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table subscribers ad
d unique subscribers_email_unique(email))

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

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

mysql> show tables; +--------------------+ | Tables_in_cachet | +--------------------+ | component_groups | | components | | incident_templates | | incidents | | metric_points | | metrics | | migrations | | settings | | subscribers | +--------------------+ 9 rows in set (0.00 sec)

[Djas2017]

Changed the AppServiceProvider.php as described in https://github.com/rydurham/Sentinel/issues/158 this fixed the database TABLES creation issue. All the tables were created. But generated other issues (Cannot update components statuses from the dashboard, error 500 in some dashboard sections, ...)

Hints??

In the meantime, I will try installing a new instance on Ubuntu server!

[jbrooksuk]

@GrahamCampbell @joecohens any ideas on this? I've never had an issue running the migrations.

[GrahamCampbell]

Is a limitation of InnoDB on MySQL. MYISAM has a higher limit.

[Djas2017]

Tried on different servers (ubuntu, centos) and finally this works fine on CentOS 7.3

What I did (may be this is not the best solution, but worked for me!): If you have a better way, please advise!

1- Changing the public function boot() in AppServiceProvider.php as described in https://github.com/rydurham/Sentinel/issues/158 2- running php artisan migrate 3- reverting the changes to the original AppServiceProvider.php .

Everything seems to be working now! need to perform a full test of the different sections of the application!

[GrahamCampbell]

The fix in that PR looks missinformed. You should instead edit the database.php config file.

[GrahamCampbell]

Ping @jbrooksuk. Some defaults to change perhapse? Is the config in sync with latest laravel?

[Djas2017]

> You should instead edit the database.php config file What changes should I apply to that file and I will give it a try to see if that fixes the issue

[jbrooksuk]

Should be but I'll double check tomorrow.

[joemoore13]

I noticed when using commits up to and including b108f33de482fc44b710758bede18d92c05fcd85, I don't have this issue, so I believe the following commit which made some dependency changes with laravel may have started the issue, at least pinning to linked commit fixes it for me.

[joecohens]

I think it's this same issue posted here https://laravel-news.com/laravel-5-4-key-too-long-error, can you confirm?

[Djas2017]

@jbrooksuk Are the suggested changes to the database.php documented somewhere? I want to test them to see if this solves the problem!

@joecohens for info: the link you have provided gives similar solution as what was mentioned above 1- Changing the public function boot() in AppServiceProvider.php as described in https://github.com/rydurham/Sentinel/issues/158

[SantinoVega]

Hi there. I hope can understandme. I solved to follow this steps. in laravel-project/databases/migrations edit both files (create_users_table, create_password_reset_table) in function up(). like this: `public function up() { Schema::create('users', function (Blueprint $table) { $table->increments('id'); $table->string('name'); $table->string('email'); $table->string('password'); $table->rememberToken(); $table->timestamps();

        $table->index([DB::raw('email(191)')]);
    });
}`

Tested on: Ubuntu 16.04 10.1.13-MariaDB PHP 7.2.7-1

[VinothArockiaraj]

Hi there, Try this

Locate config/database.php from the root folder

If you have something like this, 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci',

then change to 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci',

save and try " php artisan migrate " I think this could solve the problem

Thanks

[ryanpaulfyfe]

Hi there, Try this

Locate config/database.php from the root folder

If you have something like this, 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci',

then change to 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci',

save and try " php artisan migrate " I think this could solve the problem

Thanks

I was running into this issue when trying to deploy on AWS RDS Serverless which has mysql 5.6 compatability, even after setting:

Schema::defaultStringLength(191);

I was still getting the errror. This solution to change the charset to utf8 finally resolved it.

Many thanks!