Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Currently we have been using pure innodb. The issue is that we have architecture where we have multiple outlet each have its own local db and a copy of it in the hq outlet. The issue is that we involve distributed transaction which we know is not supported in mysql so what we do we build 3 transaction one for local db and one for the remote db and one for the hq db. So it works most of the time but there are times it failed too. So we are looking how could federated engine help in this issue?

For e.g this table in hq outlet is updated according to the branch outlet columns TotalStock and Total Amount. There many other tables involved too.

CREATE TABLE `tblproduct` (
  `productID` int(5) NOT NULL AUTO_INCREMENT,
  `productCategoryID` int(10) NOT NULL,
  `productSubCategoryID` int(10) NOT NULL,
  `productBrandID` int(10) NOT NULL,
  `productGroupID` int(10) NOT NULL,
  `productStatusID` int(10) NOT NULL,
  `productCode` varchar(100) NOT NULL,
  `productName` varchar(100) NOT NULL,
  `productPrice` double(10,2) NOT NULL DEFAULT '0.00',
  `productType` enum('Accessory','Coupon','Imei') NOT NULL,
  `productTotalStock` int(10) NOT NULL DEFAULT '0',
  `productTotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `productBooked` int(5) NOT NULL DEFAULT '0',
  `productStatus` enum('y','n') NOT NULL DEFAULT 'y',
  `rrpMarkUpType` enum('Fixed','Amount','Percentage') NOT NULL,
  `rrpMarkUpFixed` double(6,2) NOT NULL DEFAULT '0.00',
  `rrpMarkUpAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `rrpMarkUpPercentage` double(5,2) NOT NULL DEFAULT '0.00',
  `branch1TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch1TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch2TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch2TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch3TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch3TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch4TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch4TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch5TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch5TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch6TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch6TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch7TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch7TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch8TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch8TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch9TotalStock` int(10) DEFAULT '0',
  `branch9TotalAmount` double(10,2) DEFAULT '0.00',
  `branch11TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch11TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch12TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch12TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  `branch13TotalStock` int(10) NOT NULL DEFAULT '0',
  `branch13TotalAmount` double(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`productID`),
  UNIQUE KEY `productName` (`productName`),
  UNIQUE KEY `productCategoryCode` (`productCategoryID`,`productSubCategoryID`,`productBrandID`,`productGroupID`,`productStatusID`),
  UNIQUE KEY `NewIndex1` (`productCode`)
) ENGINE=InnoDB AUTO_INCREMENT=8210 DEFAULT CHARSET=latin1
share|improve this question
    
Your question is unclear. You say there are "times it failed." What failed? How and in what way did "it" fail? How are these databases connected? Or is the application updating all of these databases separately? Finally, I should ask why you believe MySQL doesn't support distributed transactions? dev.mysql.com/doc/refman/5.6/en/xa.html –  Michael - sqlbot Jun 18 '13 at 4:06
    
@When I say at times it fail is that the distributed transaction which is updating the hq database fail to update. There database are all sitting in different machine in different location. Yes I have been to this link which you gave me but I can find any C# implementation for it? –  biz14 Jun 18 '13 at 15:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.