MySQL exception within a loop causes all records created within the loop to not be committed

Question

MySQL exception within a loop causes all records created within the loop to not be committed

lock-mc Dec '20

Created Dec '20	Last Reply Dec '20	Replies 1	Views 97	Votes 0

lock-mc 1.2k

Dec '20

I am using the latest version of Phalcon and have a foreach loop that is creating records in a database by creating a model and calling the save method. If an exception occurs when saving those models within the loop, every record that is part of the loop that occurs after the exception is not being committed.

Say, for example, there are 10 loops and the 3rd loop causes an exception when inserting the data (such as a character conversion issue), then the first and 2nd record is committed, the 3rd is not (as expected because it threw) and every other loop is not committed (can confirm that save method is being called).

I have a try..catch wrapped around each loop iteration.

Why is this and how do I get around this? It seems that Phalcon is creating a single transaction for the whole loop and then rolling it back when an exception happens. I turned on the query log but couldn't see a commit or even a rollback statement being called, so not sue what is happening.

talal424 · Answer 1 · 2020-12-24T16:16:18-07:00

what happens is when the exception is thrown inside the loop the code execution moves to the catch block and then continues from there

if you want the loop to continue in case of an exception you need to create a try catch block inside the loop and the iteration will continue

$someData = [
    [
        'someColumn' => 'someValue'
    ]
];

foreach ($someData as $dataSet) {
    try {

        $someModel = new SomeModel($dataSet);

        $someModel->create();

    } catch (\Throwable $th) {
        // manage your exception
    }
}