Migrations and table options (Row format...)

Hi,

I've been needing to use utf8 on mysql databases. Therefore, I've had this error

[PDOException] SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.

with the unique index on the email field. I figured out why and therefore needed to add ROW_FORMAT = DYNAMIC but I couldn't find a nice way to do it. I had to tweak it this way :

DB::statement('CREATE TABLE IF NOT EXISTS users (temp BOOL) ROW_FORMAT = DYNAMIC');

Schema::table('users', function (Blueprint $table) {
    //ADD FIELDS
    $table->dropColumn('temp');
});

Do anyone know a better way to do this ?

Thanks in advance

Defrag

9 years ago

Best Answer

Level 18

One way to do this is to put the following in the config/database.php file:

'connections' => [
    ...
    'mysql' => [
        ...
        'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',
    ]
    ...

You also might consider adjusting your column to be a maximum of 191 characters, either by specifying the column like:

$table->string('temp', 191)

or adding the following to app/Providers/AppServiceProvider.php:

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

7 likes

AlexVV

9 years ago

Level 6

Thanks for the swift reply, exactly what I needed !

Please or to participate in this conversation.