3

When I try php artisan migrate with Laravel, I get an error like this.

1071 - Specified key was too long; max key length is 767 bytes

I can use this as a solution.

However, in MariaDB I use, the default database engine is InnoDB, so setting this engine option should be unnecessary.

That's because my use of MariaDB 10.2.2 should have MySQL 5.7.7 equivalent functionality.

15.1 Setting the Storage Engine

If you omit the ENGINE option, the default storage engine is used. The default engine is InnoDB as of MySQL 5.5.5 (MyISAM before 5.5.5). You can specify the default engine by using the --default-storage-engine server startup option, or by setting the default-storage-engine option in the my.cnf configuration file.

General Purpose¶

InnoDB is a good general transaction storage engine. It is the default storage engine from MariaDB 10.2 (as well as MySQL). For earlier releases, XtraDB is a performance enhanced fork of InnoDB and is usually preferred.

Index Lengths & MySQL / MariaDB

Laravel uses the utf8mb4 character set by default, which includes support for storing "emojis" in the database. If you are running a version of MySQL older than the 5.7.7 release or MariaDB older than the 10.2.2 release (My MariaDB is 10.2.10 newer than 10.2.2), you may need to manually configure the default string length generated by migrations in order for MySQL to create indexes for them. You may configure this by calling the Schema::defaultStringLength method within your AppServiceProvider:

If the setting of Laravel's engine option is null, InnoDB will be used for the database engine, and I think that the error of index key length does not occur.

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            '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' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

Why should I specify InnoDB in Laravel's engine?

Here are the versions of Laravel, PHP and MariaDB that I use.

  • Laravel v5.8.16
  • mysql Ver 15.1 Distrib 10.2.10-MariaDB, for Win64 (AMD64)
  • PHP 7.3.5 (cli) (built: May 1 2019 13:17:17) ( ZTS MSVC15 (Visual C++ 2017) x64 )
  • Windows
Community
  • 1
  • 1
  • If you set it to InnoDB as referenced in the question you linked, does it solve your problem? – gview Jul 03 '19 at 23:16
  • @gview : Yes it solves. However, according to the cited MariaDB manual, I think that the operation should not be necessary. –  Jul 03 '19 at 23:35
  • Actually I think that you are making assumptions about the code the ORM creates. It's not interesting enough to me to explore it, but my guess is that it explicitly is setting an engine parameter, and only by explicitly setting it to InnoDB can you override the default that Laravel is setting. – gview Jul 05 '19 at 00:28

1 Answers1

0

There are 5 solutions:

http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

You pick the one that best suits your situation.

The first bullet item mentions that 5.7.7 has a bigger limit. It's not InnoDB, but the version of MySQL (or MariaDB) that has this solution built-in. Having InnoDB as a default does not affect any table that already exists.

Rick James
  • 106,233
  • 9
  • 103
  • 171
  • As I said in the question, I am not asking for a solution, but a question as to why the question solution works. –  Jul 04 '19 at 04:30
  • @syhulimn - Because you are at 5.7 (or equivalent)? (I added to my answer.) – Rick James Jul 04 '19 at 05:33