PHP MySQL InnoDB Multiple Inserts to Different Tables Using Transactions Failed

Question

I have been studying transactions and from what I understand, my multiple insert statement should work.

I'm using InnoDB engine. Both insert statements below work and correctly enters the data into the respective tables, but only if I have one of them at a time in the transaction, not both together.

Can someone tell me what I need to do different?

It fails with Affected rows (INSERT): -1

code:

$con->begin_transaction();

$con->autocommit(FALSE);


$query = "

INSERT INTO `Order` (`PO`,`SellingManagerSalesRecordNumber`,`OrderID`,
  `AmountPaid`,`CreatedTime`,`Subtotal`,`Total`,`BuyerUserID`,
  `IsMultiLegShipping`,`Status_id`,`SellerUserID_id`)
   VALUES
   ('AF11514','11514','111809902234-1906167668001','55.98',
  '2019-01-07 17:42:46','55.98','55.98','hyccas3','false',
   (SELECT `id` FROM `Status` WHERE `Status` = 'Completed'),
   (SELECT `id` FROM `SellerUserID` WHERE `SellerUserID` = 'afiperformance'))
   ON DUPLICATE KEY UPDATE PO = VALUES(PO),
   SellingManagerSalesRecordNumber = VALUES(SellingManagerSalesRecordNumber),
   OrderID = VALUES(OrderID),
   AmountPaid = VALUES(AmountPaid),
   CreatedTime = VALUES(CreatedTime), Subtotal = VALUES(Subtotal),
   Total = VALUES(Total),
   BuyerUserID = VALUES(BuyerUserID),
   IsMultiLegShipping = VALUES(IsMultiLegShipping);

INSERT INTO `CheckoutStatus` (`PO`,`LastModifiedTime`, 
         `PaymentMethod_id`,`Status_id`)
   VALUES ('AF11514','2019-01-07 17:47:55',
  (SELECT `id` FROM `PaymentMethod` WHERE `PaymentMethod` = 'PayPal'),
   (SELECT `id` FROM `Status` WHERE `Status` = 'Complete'))
   ON DUPLICATE KEY UPDATE PO = VALUES(PO),
   LastModifiedTime = VALUES(LastModifiedTime);";

echo $query;

$con->query($query);
printf("<br><br>\n\nAffected rows (INSERT): %d ", $con->affected_rows) . "\n\n<br><br>";

if ($con->affected_rows == -1) {
    echo "<br><br>\n\n Failed " . "\n\n<br><br>";
    $con->rollback();
    $commit = '';
} else {
    $commit = $con->commit();
    echo " success " . "\n\n<br><br>";
}

/* commit transaction */
if ($commit == '') {
    print("Transaction commit failed\n");
} else {
    print("Transaction commit success\n");
}

$con->close();

Nick · Accepted Answer · 2019-01-10T07:17:36.793

2

mysqli_query doesn't support multiple queries in one call. You either need to use mysqli_multi_query or split the query into two separate calls to mysqli_query. Since you are using transactions, splitting into two calls would be better as it is hard to get error information for the second and subsequent queries in mysqli_multi_query.

edited Jan 10 '19 at 07:17

answered Jan 10 '19 at 06:37

Nick

138,499
22
57
95

Do I still need to use transactions then? Or is `mysqli_multi_query` sufficient? – Mike Jan 10 '19 at 06:46
No. Simply call `mysqli_query` one statement at a time. – Rick James Jan 10 '19 at 06:49
@Mike calling `mysqli_multi_query` is not equivalent to using transactions. You should still use them. – Nick Jan 10 '19 at 06:52
@RickJames you're saying put each SQL statement into its own `$con->query();` right? I was doing it this way and that worked, but I thought transactions send all data at once and if I send it one at a time in its own `$con->query();`, then it seems like I'm defeating the purpose of using transactions? – Mike Jan 10 '19 at 07:00
1

@Mike not at all, the point of using transactions is to allow you to rollback the effect of the transactions if any of them fail. – Nick Jan 10 '19 at 07:02
1

If you start a transaction, all subsequent statements will be part of that transaction, until you commit or rollback. You don't have to send the statements in one call. In fact, you may not be able to, if you need to run more SQL statements in one transaction than can fit in one packet. There's no upper limit to the number of statements in one transaction, but there is an upper limit to the size of one packet. – Bill Karwin Jan 10 '19 at 19:06
The former engineering director for MySQL told me: "There's literally no reason for multi-query to exist." – Bill Karwin Jan 10 '19 at 19:07
1

And the creator of PHP said, "don't use it". If you do allow for it, you are just giving a hacker one more (rather easy) way to get into your system. – Rick James Jan 10 '19 at 22:47

PHP MySQL InnoDB Multiple Inserts to Different Tables Using Transactions Failed

1 Answers1