I am using laravel and laravel migration mechanism. I created tables and seted up foreign keys. But the tables are MyISSAM so no foreign keys are created. Where do I enable / configure this? (to change it to InnoDB and not in the mysql server).
Add a comment
|
9 Answers
You can edit your /config/database.php file, search for mysql entry and change:
'engine' => null,
to
'engine' => 'InnoDB',
This saves you from adding $table->engine = "InnoDB"; for each of your Schemas ;)
2 Comments
Vincent Decaux
The question is now, why Laravel allows to create foreign key in the migrations files using MyISAM. Using MySQL 5.7 (so with InnoDB as default), the
migrate command still creates table with MyISAM, and my foreign keys are only indexes. Weird.
Mr.Singh
@VincentDecaux, Laravel has given all the developers the functionality to create the foreign keys via migration code. Suppose if Laravel doesn't allow this functionality and someone, who is using the InnoDB engine, needed it then obviously he'll have to write the query(ies) separately to add foreign key constraints. Which in result will double his/her work and time as well.
Define engine like this
Schema::create("models", function(Blueprint $table) {
$table->engine = "InnoDB";
}
Comments
2 Comments
jlbang
Sadly, that link seems to be dead now.
Thomas LAURENT
Now the way to do this is here in Connection & Storage Engine
I found @ThomasLAURENT is the best solution but what about the existing tables I have in my database.
Working around.
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class ConvertTablesIntoInnoDB extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
$tables = [
'users',
'products',
];
foreach ($tables as $table) {
DB::statement('ALTER TABLE ' . $table . ' ENGINE = InnoDB');
}
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
$tables = [
'users',
'products',
];
foreach ($tables as $table) {
DB::statement('ALTER TABLE ' . $table . ' ENGINE = MyISAM');
}
}
}
This will allow us to convert all the tables and roll-back them when I need.
1 Comment
Adolfo Augusto Rodrigues
already helped on how to do it in an application
Another approach (for whose that don't uses database.php) is to include on .env file:
DB_ENGINE=InnoDB
Remember to check if you have 'engine' => env('DB_ENGINE', null), on your database.php
Comments
I would recommend to update your Mysql to 5.5 or higher. The default storage engine for Mysql now is InoDB
Before MySQL 5.5.5, MyISAM is the default storage engine. (The default was changed to InnoDB in MySQL 5.5.5.) MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions.
http://dev.mysql.com/doc/refman/5.5/en/myisam-storage-engine.html
Once done, you can easily map relationships within the entity classes via Laravel
1 Comment
Rick James
If the tables exist, changing the version to get a different default will not cause the existing tables to change. You need
ALTER TABLE t ENGINE=InnoDB; (for each t).Schema::create('users', function($table)
{
$table->engine = 'InnoDB';
$table->string('email');
});
Like document Laravel: https://laravel.com/docs/4.2/schema#storage-engines
P/s: Thanks @Nico Haase for reminding me to provide the link.
3 Comments
Nico Haase
Is there any way for a global definition, such that it works for all tables? What's that
email column used for?Mr.Singh
@NicoHaase, that email column is just an example & to answer your question
Yes you can define it globally. To do that go to config/database.php and under mysql key change 'engine' => null, to 'engine' => 'InnoDB',.Nico Haase
This looks like copy&paste from laravel.com/docs/4.2/schema#storage-engines - if you already do this, you should at least provide a link
Use InnoDb tables on the server side it's best way to success. Use MySQL Workbench. It easy in Workbench. And, if you want read the native manual
Comments
The best approach for those that don't use database.php) is to include on the .env file: DB_ENGINE=InnoDB. Remember to check if you have 'engine' => env('DB_ENGINE', null) on your database.php
