Auto Increment ID Roll back in Laravel

Question

I want to Rollback or Reset Auto Increment ID from multiple mysql tables after Transaction Fail in Laravel 5.6.

DB::beginTransaction();
 try {

  //My Database Queries goes here

  DB::commit();
 }

catch (\Exception $e) {

       DB::rollback();
     }

Simply what I want is, I want to write some Query to Rollback Auto Increment IDs here

catch (\Exception $e) {

   DB::rollback();
 }

after DB::rollback() executed.

Can any body suggest me a way to do that..?

koalaok · Answer 1 · 2018-08-07T10:53:46.867

To strictly answer your question, I think you should first find your max for the AI column (e.g. named id)

SQL:

  SELECT MAX(id) FROM yourTable;

in Laravel

  $maxId = DB::table('yourTable')->max('id');

Then reset Auto Increment value:

SQL:

  ALTER TABLE yourTable AUTO_INCREMENT=$maxId;

in Laravel

 DB::statement("ALTER TABLE yourTable AUTO_INCREMENT=$maxId");

In any case I would be careful in what you're doing. Just in case you think to rely on your AI id column....Have a look at this: MySQL AUTO_INCREMENT does not ROLLBACK

Otherwise if it's just a worry to not grow big with id values because you've got thousands of failing Transactions... it might be an optimisation.But in this case I would get rid of id column at all...

Auto Increment ID Roll back in Laravel

1 Answers1