Just curious to know why

[I-AM-OBODO]

Hello all.

I am playing with transaction and i noticed something. I deliberately omitted txn in my second mysql statement. the first statement went through i.e the data were stored regardless of the error thrown by transaction: why was it so? why did the query execute? was thinking no entry is supposed to pass through should there be any error with transaction?

below my code

try {
    
    $pdo->beginTransaction();

    $sql = "INSERT INTO tbl_cont_asess_records (
    txn,
    subj_id,
    test_score,
    exam_score

    ) VALUES (
    ?,
    ?,
    ?,
    ?
    )";

    $stmt = $pdo->prepare($sql);

    foreach(array_column($subject_data,'id') as $key)
    {
      $stmt->execute([
        $txn,
        $key,
        $post['test_score'][$key],
        $post['exam_score'][$key]
        
      ]);
      
    }

    $sql2 = "INSERT INTO tbl_cont_asess (
    txn,
    sch_session,
    sch_term,
    ward_id, 
    class_id, 
    staff_id

    ) VALUES (
    ?,
    ?,
    ?,
    ?,
    ?,
    ?
    )";
    $stmt2 = $pdo->prepare($sql2);

      $stmt2->execute([
        
        $current_session,
        $current_term,
        $_GET['id'],
        $class_id,
        $staff_id
        
      ]);

    $pdo->commit();

    echo '<div class="alert alert-success text-center text-dark">Scores Saved SUCCESSFULLY!</div>';

  }catch (PDOException $e) {
  
    $pdo->rollBack();

    echo '<div class="alert alert-danger text-center">Score Not Saved. Problem Occurred!</div>';

}

 

[requinix]

Maybe a silly question, but are you sure the data from that first query is being entered? It's not possible that you had identical data in the table and so it seems like it worked?

[I-AM-OBODO]

44 minutes ago, requinix said:

Maybe a silly question, but are you sure the data from that first query is being entered? It's not possible that you had identical data in the table and so it seems like it worked?

I am very very sure the data are not identical and the query from the first query is entered. I did that several times. I am baffled myself. 

[kicken]

Assuming mysql, Your tables may be using the MyISAM engine which does not support transactions.  You should be using InnoDB unless you have a specific reason not to.

[I-AM-OBODO]

1 hour ago, kicken said:

Assuming mysql, Your tables may be using the MyISAM engine which does not support transactions.  You should be using InnoDB unless you have a specific reason not to.

I Guest that's why! Funny though! Was using InnoDB before now. Had to switch cos a functionality I needed will only be possible with MyISAM and now another function can only work in InnoDB. 😆 

Guess that's life! Nothing and no one has everything. Just gotta do with what's paramount. 

Thanks 😊 

[gizmola]

On 12/16/2023 at 3:38 AM, I-AM-OBODO said:

I Guest that's why! Funny though! Was using InnoDB before now. Had to switch cos a functionality I needed will only be possible with MyISAM and now another function can only work in InnoDB. 😆 

Guess that's life! Nothing and no one has everything. Just gotta do with what's paramount. 

Thanks 😊 

 

I don't know what functionality that is only in the MyISAM engine at this point.  That is why Innodb is the default engine.  With that said, the engine gets set for each table, and you can even alter the engine of an existing table, so it's possible to intermix them, but things like declarative referential integrity, transactions and row level locking only work with InnoDB tables. 

[Barand]

The only MyIsam-only  functionality that I can think of is the ability to have a compound primary key EG

PRIMARY KEY (year, number)

where the 2nd part auto_increments within the first part, so if you have

CREATE TABLE `test1` (
  `year` int(11) NOT NULL,
  `number` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`year`,`number`)
) ENGINE=MyISAM ;

mysql> select * from test1;
+------+--------+
| year | number |
+------+--------+
| 2022 |      1 |
| 2022 |      2 |
+------+--------+


mysql> insert into test1 (year) values (2022), (2022), (2023), (2023), (2024);

mysql> select * from test1;
+------+--------+
| year | number |
+------+--------+
| 2022 |      1 |
| 2022 |      2 |
| 2022 |      3 |
| 2022 |      4 |
| 2023 |      1 |
| 2023 |      2 |
| 2024 |      1 |
+------+--------+

 

 

[gizmola]

That is interesting to note. 

The main reason people used to use MyISAM tables intermixed in a database with InnoDB was to support fulltext indexing.  Innodb didn't support fulltext search until version 5.6.4 which was released late in 2011.