No active transaction

Question

I'm trying to use transactions with PDO, with the following code

try
{
    $bdd = new PDO('mysql:host=HOSTNAME;dbname=DATABASENAME', 'USERNAME', 'PASSWORD');
}
catch(Exception $e)
{
    die('Error : '.$e->getMessage());
}    

$qry = 'UPDATE table SET field = CASE';
foreach($elements as $el){
  $qry .= ' WHEN id = '. $el['id'] .' THEN '. $el['value'];
}
$qry .= ' ELSE field END, SET update_date = CASE';
foreach($elements as $el){
  $qry .= ' WHEN id = '. $el['id'] .' THEN NOW()';
}
$qry .= ' ELSE update_date END';

$update = $bdd->prepare($qry);

$bdd->beginTransaction();
try {
    $update->execute();  
    $bdd->commit();
}
catch(Exception $e) {
    $bdd->rollback();
    echo 'Error : '.$e->getMessage().'<br />';
    echo 'N° : '.$e->getCode();
    exit();
}

But I get the following error (and the table is not updated) :

Fatal error: Uncaught exception 'PDOException' with message 'There is no active transaction' in script.php:106 
Stack trace: 
#0 script.php(106): PDO->rollBack() #1 {main} thrown in script.php on line 106

(Line 106 is $bdd->rollback();)

I understand that it's telling me there is no transaction going on, but... there should be, with $bdd->rollback();...

Thanks for any help you can give !

---

EDIT : The database engine is MyISAM.

I have additional info after further testing :

I simplified the $qry in my question because I thought it was irrelevant, but it seems it isn't. The query I'm actually using is a much longer one, updating multiple elements with the following code (I also modified the code up there ^ in the original question) :

$qry = 'UPDATE table SET field = CASE';
foreach($elements as $el){
  $qry .= ' WHEN id = '. $el['id'] .' THEN '. $el['value'];
}
$qry .= ' ELSE field END, SET update_date = CASE';
foreach($elements as $el){
  $qry .= ' WHEN id = '. $el['id'] .' THEN NOW()';
}
$qry .= ' ELSE update_date END';

It seems that the length of the query and/or the number of updated lines is causing my issue :

• when updating 72 lines at most (the query is 3463 characters long, all spaces included), the table is updated, and vardump($bdd->beginTransaction();) returns bool(true) (regardless of wether it's before or after the $update = $bdd->prepare($qry) line.
• when updating 73 lines or more (the query is 3509 characters long, all spaces included, when it updates 73 lines), the table is not updated, and vardump($bdd->beginTransaction();) returns bool(false) (once again, regardless of wether it is before or after the $update = $bdd->prepare($qry) line.

Hints on why, and how I can fix it are appreciated.

Thanks again

---

EDIT 2 : I switched the engine to InnoDB. The issue still stands.

Answer 1

Move

$bdd->beginTransaction();

to above your query:

$qry = 'UPDATE table SET field = value'
$update = $bdd->prepare($qry);

EDIT:

You are using MyISAM engine which does not support transactions. Update your tables to use InnoDB.