Failed to throw a "PDOException" with the message "No active transaction"?

Question

This is the code I'm using to insert a record. Whenever an insert error occurs, does the autostart number of the subscriber table increase anyway, even when I roll back? What is the problem? I just want the auto increment number not to be added when an error occurs . Many thanks for the help.

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $conn->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE); $conn->beginTransaction(); try { $email = $_POST['Email']; $FirstName = $_POST['FirstName']; $LastName = $_POST['LastName']; $query="INSERT INTO subscriber (Email,FirstName,LastName,CreateDate) VALUES (?,?,?,CURDATE())"; $stmt = $conn->prepare($query); $stmt->bindParam(1, $email , PDO::PARAM_STR); $stmt->bindParam(2, $FirstName, PDO::PARAM_STR); $stmt->bindParam(3, $LastName, PDO::PARAM_STR); $stmt->execute(); $conn->commit(); } catch(PDOException $e) { $conn->rollBack(); die ($e->getMessage()."<a href='addSub.php'>Back</a>"); } $conn->beginTransaction(); try { $userID = $_SESSION['username']; $query="INSERT INTO list_sub (SubID,ListID) VALUES ('',$_SESSION[ListID])"; $stmt = $conn->prepare($query); $stmt->execute(); $conn->commit(); } catch(PDOException $e) { $conn->rollBack(); die ($e->getMessage()."<a href='addSub.php'>Back</a>"); } $conn = null;} 

Answer 1

Without knowing the line numbers in the code, it is difficult to find out, but you make a transaction at the end of the first try-catch block, and then continue without starting a new transaction in the second try-catch block.

Add $conn->beginTransaction(); to the beginning of the second try-catch block.

EDIT - You say: "I just want the auto-increment number not to be added when an error occurs." You do not have to rely on the auto-increment function to generate a “gapless” sequence of numbers.

Answer 2

PDO auto-commit is probably enabled and causes problems when you try to roll back because it has already been completed. You can use PDO :: ATTR_AUTOCOMMIT to disable this behavior:

 $conn->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE);