Level 73
Why are you using two databases?
1 like
Aug 10, 2022
9
Level 1
multiple database rollback
i try with below code. it rollback from first database if any error come but not from second database. it just ingnore the second database error
DB::beginTransaction();
DB::connection('mysql2')->beginTransaction();
try {
//creating company
$companyResult = $this->CreateCompanyService->add($input);
//creating company user
$userResult = $this->CreateCompnayUserService->add($input);
//store company user details
$userDetailResult = $this->CreateUserDetialService->add($companyResult, $userResult);
//creating company resources
$resources = $this->CreateCompanyResources->add($companyResult, $input);
DB::commit();
DB::connection('mysql2')->commit();
} catch (\Exception $ex) {
DB::rollBack();
DB::connection('mysql2')->rollBack();
return $this->returnResponse(500, false, $ex->getMessage());
// something went wrong
}
Level 1
@Tray2 i am extending the old project that is in cake PHP. now creating the new version in laravel. the user details are in the old database and we are creating some new modules in laravel it's totally independent. we just sharing user listing
Level 73
@krishantalwar Then I would export the users from the old database and import them in the new one. If that isn't an option, take a look at federated tables
https://dev.mysql.com/doc/refman/5.7/en/federated-create.html
Level 1
@Tray2 thanks for the suggestion. I not only need user listing. there is so many tables i need like this. if follow you suggestion. I have to create so many tables.
all things working fine. but the problem is if something went wrong it rollback query from the primary database, not the second one.
DB::connection('mysql2')->rollBack();
this statement needs to rollback insert operation but it does not work
Level 73
@krishantalwar Are you sure the database transaction is started on the second db, and isn't closed when you roll back the first? So maybe trying to specify the first database just like you do the second make fix your issue.
DB::connection('mysql')->beginTransaction();
DB::connection('mysql2')->beginTransaction();
//Do the stuff
//Exception
DB::connection('mysql')->rollBack();
DB::connection('mysql2')->rollBack();
Level 1
@Tray2 i try this but not work
Level 73
@krishantalwar Is there really a need to use transactions?
I would consider moving to a rdbms that supports database links if I were you.
This is the closest you can get in MySQL
Level 102
@krishantalwar Are you sure both tables is innodb? If its myisam, it does not support transactions.
Level 122
it seems you don't update one of the databases so not sure why there needs to be a transaction on that one?
Please or to participate in this conversation.