mysqllaravelinnodbmyisam

Model::Create() method not inserting with InnoDB storage engine

I am trying to insert a new record customer into MySQL database as following:

$customer = Customer::create(['name'=>$request->customerName,
'email'=>$request->customerEmail,
'phone'=>$request->customerPhone,
'area_id'=>$request->customerArea,
'gender'=>$request->customerGender]);
dd($customer->id);

It works well with myISAM storage engine and a new record is there, but not working with InnoDB, the post request returns 200 with no record in the database. While the dump dd() returns the new ID as it's auto incremented. The ID is taken already, as when I insert a new record it gives me a newer ID than previous one, with no record in the database also.

200 Response

New ID

Here's Customer model structure:

namespace App\Models\User;

use Illuminate\Database\Eloquent\Model;

class Customer extends Model
{
    protected $fillable = [
        'name', 'phone', 'email', 'area_id', 'gender'
    ];

    protected $table = 'customers';
}

And here's customers table structure:

Customers table structure

Solution

  • When using InnoDB, you need to know at least a little about "transactions".

    If you have autocommit=ON, each statement (such as INSERT) will be a transaction unto itself and will be automatically COMMITTed.

    If you have autocommit=OFF, you must eventually issue COMMIT. This is because the statements (INSERTs, etc) are being collected in a "transaction".

    The latter case fits the symptoms.