MySQL transaction rollback

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

I'm trying to use advantage of the transaction thing that I can do with MySQL... maybe I'm missing something or I simply don't understand something but the following code is telling me that everything is successful, yet it isn't.

PHP Code: [ Select ]
<?php
require_once "db.php";
$db = new db();
 
// Starting the transaction
if($db->transaction('BEGIN'))
{
   echo "Transaction started successfully!";
}
 
$sql = array(
    'test1' => 'val_4',
    'test3' => 'val_2'
);
 
$where = array(
    'test2' => '245'
);
 
// Generating the UPDATE statement
$update = $db->build_update('test', $sql, $where);
 
// Carrying out the UPDATE statement
if($db->resource($update))
{
   echo "<p>Table updated successfully!</p>";
}
 
// Undoing the last action (The UPDATE statement)
if($db->transaction('ROLLBACK'))
{
   echo "<p>Action has being set on queue to be undone.</p>";
}
 
// Ending the transaction
if($db->transaction('COMMIT'))
{
   echo "<p>Transaction has being undone successfully!</p>";
}
?>
  1. <?php
  2. require_once "db.php";
  3. $db = new db();
  4.  
  5. // Starting the transaction
  6. if($db->transaction('BEGIN'))
  7. {
  8.    echo "Transaction started successfully!";
  9. }
  10.  
  11. $sql = array(
  12.     'test1' => 'val_4',
  13.     'test3' => 'val_2'
  14. );
  15.  
  16. $where = array(
  17.     'test2' => '245'
  18. );
  19.  
  20. // Generating the UPDATE statement
  21. $update = $db->build_update('test', $sql, $where);
  22.  
  23. // Carrying out the UPDATE statement
  24. if($db->resource($update))
  25. {
  26.    echo "<p>Table updated successfully!</p>";
  27. }
  28.  
  29. // Undoing the last action (The UPDATE statement)
  30. if($db->transaction('ROLLBACK'))
  31. {
  32.    echo "<p>Action has being set on queue to be undone.</p>";
  33. }
  34.  
  35. // Ending the transaction
  36. if($db->transaction('COMMIT'))
  37. {
  38.    echo "<p>Transaction has being undone successfully!</p>";
  39. }
  40. ?>

Below is the result that that code is giving me:
Quote:
Transaction started successfully!

Table updated successfully!

Action has being undone successfully!

Transaction has being stopped successfully!

What it should be doing is start a transaction, carry out the update, undo the update back to the original and then end the transaction (or COMMIT in other words). The "transaction" function is:
PHP Code: [ Select ]
<?php
public function transaction($trans)
{
   // Going through the possibilities of a transaction
   switch($trans)
   {
      case 'BEGIN' || 'begin' || 'START' || 'start':
         // Starting a transaction
         if(!@mysql_query("BEGIN", $this->mysql_link))
         {
            return false;
         }
         return true;
         break;
      case 'ROLLBACK' || 'rollback' || 'UNDO' || 'undo':
         // Undo-ing a transaction
         if(!@mysql_query("ROLLBACK", $this->mysql_link))
         {
            return false;
         }
         return true;
         break;
      case 'COMMIT' || 'commit' || 'END' || 'end' || 'STOP' || 'stop':
         // Ending transaction
         if(!@mysql_query("COMMIT", $this->mysql_link))
         {
            return false;
         }
         return true;
         break;
      default:
   }
   
   // Returning true since the query succeeded
   return true;
}
?>
  1. <?php
  2. public function transaction($trans)
  3. {
  4.    // Going through the possibilities of a transaction
  5.    switch($trans)
  6.    {
  7.       case 'BEGIN' || 'begin' || 'START' || 'start':
  8.          // Starting a transaction
  9.          if(!@mysql_query("BEGIN", $this->mysql_link))
  10.          {
  11.             return false;
  12.          }
  13.          return true;
  14.          break;
  15.       case 'ROLLBACK' || 'rollback' || 'UNDO' || 'undo':
  16.          // Undo-ing a transaction
  17.          if(!@mysql_query("ROLLBACK", $this->mysql_link))
  18.          {
  19.             return false;
  20.          }
  21.          return true;
  22.          break;
  23.       case 'COMMIT' || 'commit' || 'END' || 'end' || 'STOP' || 'stop':
  24.          // Ending transaction
  25.          if(!@mysql_query("COMMIT", $this->mysql_link))
  26.          {
  27.             return false;
  28.          }
  29.          return true;
  30.          break;
  31.       default:
  32.    }
  33.    
  34.    // Returning true since the query succeeded
  35.    return true;
  36. }
  37. ?>

Maybe I misunderstood the functionality of "ROLLBACK" or the implementation... I don't know, but it doesn't work like I think it should.

The database:
SQL Code: [ Select ]
CREATE TABLE IF NOT EXISTS `test` (
  `test1` varchar(2354) NOT NULL,
  `test2` int(234) NOT NULL,
  `test3` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
INSERT INTO `test` (`test1`, `test2`, `test3`) VALUES
('sdft56', 4536, 'dfrset56 3w45e tg destg '),
('sdfg', 245, 'sdfg  dsfgds fgdg sfdsgf'),
('srftgh', 5642, 'dsfg435w 354 6 5ty rew twyre534 6'),
('weqrt', 2354, 're ta34t 5w534 345 tre wret w 345w');
  1. CREATE TABLE IF NOT EXISTS `test` (
  2.   `test1` varchar(2354) NOT NULL,
  3.   `test2` int(234) NOT NULL,
  4.   `test3` text NOT NULL
  5. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  6.  
  7. INSERT INTO `test` (`test1`, `test2`, `test3`) VALUES
  8. ('sdft56', 4536, 'dfrset56 3w45e tg destg '),
  9. ('sdfg', 245, 'sdfg  dsfgds fgdg sfdsgf'),
  10. ('srftgh', 5642, 'dsfg435w 354 6 5ty rew twyre534 6'),
  11. ('weqrt', 2354, 're ta34t 5w534 345 tre wret w 345w');

Any help would be awesome!
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

Here is an update on how things are going here. I figured out that I was using them wrong, but now that (I think) that I'm using it right, it's still not working properly, and since it's not working properly, it's telling me I'm probably using it wrong somewhere. :lol:

Below is my current PHP code:

PHP Code: [ Select ]
<?php
require_once "db.php";
$db = new db();
 
// We do NOT want to cache results when using transactions
$db->set_opts(array('cache_results' => false));
 
// Starting the transaction
$db->transaction('BEGIN');
 
$sql = array(
    'test1' => 'val_erw',
    'test3' => 'val_24'
);
 
$where = array(
    'test2' => '242'
);
 
// Generating the UPDATE statement
$update = $db->build_update('test', $sql, $where);
 
// Printing the query used
echo "<p>" . $update . "</p>";
 
// Carrying out the UPDATE statement
if($db->resource($update))
{
   $db->transaction('COMMIT');
   echo "<p>Table updated successfully!</p>";
}
else
{
   $db->transaction('ROLLBACK');
   echo "<p>There was an error updating the database!</p>";
}
?>
  1. <?php
  2. require_once "db.php";
  3. $db = new db();
  4.  
  5. // We do NOT want to cache results when using transactions
  6. $db->set_opts(array('cache_results' => false));
  7.  
  8. // Starting the transaction
  9. $db->transaction('BEGIN');
  10.  
  11. $sql = array(
  12.     'test1' => 'val_erw',
  13.     'test3' => 'val_24'
  14. );
  15.  
  16. $where = array(
  17.     'test2' => '242'
  18. );
  19.  
  20. // Generating the UPDATE statement
  21. $update = $db->build_update('test', $sql, $where);
  22.  
  23. // Printing the query used
  24. echo "<p>" . $update . "</p>";
  25.  
  26. // Carrying out the UPDATE statement
  27. if($db->resource($update))
  28. {
  29.    $db->transaction('COMMIT');
  30.    echo "<p>Table updated successfully!</p>";
  31. }
  32. else
  33. {
  34.    $db->transaction('ROLLBACK');
  35.    echo "<p>There was an error updating the database!</p>";
  36. }
  37. ?>

The transaction function:
PHP Code: [ Select ]
<?php
public function transaction($trans)
{
   // Going through the possibilities of a transaction
   switch($trans)
   {
      case 'BEGIN' || 'begin' || 'START' || 'start':
         // Starting a transaction
         if(!mysql_query("BEGIN", $this->mysql_link))
         {
            return false;
         }
         return true;
         break;
      case 'ROLLBACK' || 'rollback' || 'UNDO' || 'undo':
         // Undo-ing a transaction
         if(!mysql_query("ROLLBACK", $this->mysql_link))
         {
            return false;
         }
         return true;
         break;
      case 'COMMIT' || 'commit':
         // Ending transaction
         if(!mysql_query("COMMIT", $this->mysql_link))
         {
            return false;
         }
         return true;
         break;
      default:
   }
   
   // Returning true since the query succeeded
   return true;
}
?>
  1. <?php
  2. public function transaction($trans)
  3. {
  4.    // Going through the possibilities of a transaction
  5.    switch($trans)
  6.    {
  7.       case 'BEGIN' || 'begin' || 'START' || 'start':
  8.          // Starting a transaction
  9.          if(!mysql_query("BEGIN", $this->mysql_link))
  10.          {
  11.             return false;
  12.          }
  13.          return true;
  14.          break;
  15.       case 'ROLLBACK' || 'rollback' || 'UNDO' || 'undo':
  16.          // Undo-ing a transaction
  17.          if(!mysql_query("ROLLBACK", $this->mysql_link))
  18.          {
  19.             return false;
  20.          }
  21.          return true;
  22.          break;
  23.       case 'COMMIT' || 'commit':
  24.          // Ending transaction
  25.          if(!mysql_query("COMMIT", $this->mysql_link))
  26.          {
  27.             return false;
  28.          }
  29.          return true;
  30.          break;
  31.       default:
  32.    }
  33.    
  34.    // Returning true since the query succeeded
  35.    return true;
  36. }
  37. ?>

The resource function:
PHP Code: [ Select ]
<?php
public function resource($sql = null, $return = false)
{
   // Checking if we are connected to MySQL
   if(!is_resource($this->mysql_link))
   {
      $this->connect();
   }
   
   // Checking if the SQL is empty
   if(is_null($sql))
   {
      $sql = $this->get_last_sql();
   }
   
   // Getting the resource into a variable
   $resource = mysql_query($sql);
   
   // Setting the last result variable
   $this->last_result = $resource;
   
   // Checking if the resource was created properly
   if(is_resource($resource) || $resource == true)
   {
      // Checking if we are returning the result
      if($return == true)
      {
         return $resource;
      }
     
      // Otherwise we are returning true
      return true;
   }
   else
   {
      // Logging the error if need be
      if($this->log_errors === true)
      {
         $this->log_error("Error setting MySQL Resource. SQL used: '{$sql}' MySQL Error: " . mysql_error());
      }
     
      // Checking if we need to kill the script
      if($this->silent == false)
      {
         echo $sql . "<br /><br />";
         die(mysql_error());
      }
     
      // It was a failure... return false
      return false;
   }
}
?>
  1. <?php
  2. public function resource($sql = null, $return = false)
  3. {
  4.    // Checking if we are connected to MySQL
  5.    if(!is_resource($this->mysql_link))
  6.    {
  7.       $this->connect();
  8.    }
  9.    
  10.    // Checking if the SQL is empty
  11.    if(is_null($sql))
  12.    {
  13.       $sql = $this->get_last_sql();
  14.    }
  15.    
  16.    // Getting the resource into a variable
  17.    $resource = mysql_query($sql);
  18.    
  19.    // Setting the last result variable
  20.    $this->last_result = $resource;
  21.    
  22.    // Checking if the resource was created properly
  23.    if(is_resource($resource) || $resource == true)
  24.    {
  25.       // Checking if we are returning the result
  26.       if($return == true)
  27.       {
  28.          return $resource;
  29.       }
  30.      
  31.       // Otherwise we are returning true
  32.       return true;
  33.    }
  34.    else
  35.    {
  36.       // Logging the error if need be
  37.       if($this->log_errors === true)
  38.       {
  39.          $this->log_error("Error setting MySQL Resource. SQL used: '{$sql}' MySQL Error: " . mysql_error());
  40.       }
  41.      
  42.       // Checking if we need to kill the script
  43.       if($this->silent == false)
  44.       {
  45.          echo $sql . "<br /><br />";
  46.          die(mysql_error());
  47.       }
  48.      
  49.       // It was a failure... return false
  50.       return false;
  51.    }
  52. }
  53. ?>

The database is pretty much the same. I'm making the SQL to update a column that doesn't exist and it still spitting out that the transaction has being successful.
Quote:
UPDATE `test` SET `test1` = 'val_erw', `test3` = 'val_24' WHERE test2 = '242'

Table updated successfully!

I'm confused as to how this function works now...
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9089
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

Got a few questions for you. In your last post you are no longer testing the return values for when you do this:

PHP Code: [ Select ]
$db->transaction('COMMIT')


Was that intentional?

The 2nd question is with your transaction function, did you write that function or did you grab it from somewhere? I did notice that in the first post you were using @mysql_query('blah blah') which the @ symbol would hide any errors. In the 2nd post it looks like you removed the @ symbol which would allow the errors to be returned, but now you no longer test for them on the returned result.
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

Yes, it was intentional to remove the testing of the return values of the transactions. That was because I learned how to actually use them :lol: (I know, weird, huh?).

Anyway, I think the problem here is that I don't know how to turn off auto-commit (I think that's my problem... I have that setup but auto-commit is still enabled).

PHP Code: [ Select ]
mysql_query('SET AUTOCOMMIT = 0');


I just found this while Googling even more right now... does that turn off autocommit like I think it does? If so, I think I may have found my problem.


I wrote the transaction function myself.

I'm not exactly sure why I removed the '@' from the code... thought it may be bad practice and since the SQL query is not affected by the user (unless, of course, their MySQL server doesn't support transactions) it doesn't really need an error choke (silencer, whatever the '@' is called in this context).

Reason I have the testing thing removed in my second post because from all tutorials that I've read about transactions, the functionality of it doesn't really require it, but then again, I've being wrong before.

I'll test this auto-commit thing and post back the results.
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

No, that did not make it work. Here is what I tried...

PHP Code: [ Select ]
<?php
require_once "db.php";
$db = new db();
 
// We do NOT want to cache results when using transactions
$db->set_opts(array('cache_results' => false));
 
// Building the query
$sql = array(
    'test1' => 'val_erw',
    'test3' => 'val_24'
);
 
$where = array(
    'test2' => '242'
);
 
// Generating the UPDATE statement
$update = $db->build_update('test', $sql, $where);
 
// Printing the query used
echo $update;
 
// Turning AUTOCOMMIT off
mysql_query('SET AUTOCOMMIT = 0');
 
// Starting the transaction
$db->transaction('BEGIN');
 
// Carrying out the UPDATE statement
$result = $db->resource($update);
$result = false;
 
if($result === true)
{
   $db->transaction('COMMIT');
   echo "<p>Table updated successfully!</p>";
}
else
{
   $db->transaction('ROLLBACK');
   echo "<p>There was an error updating the database!</p>";
}
 
$result = $db->fetch_rowset('SELECT * FROM test ORDER BY test2 ASC');
 
print_r($result);
?>
  1. <?php
  2. require_once "db.php";
  3. $db = new db();
  4.  
  5. // We do NOT want to cache results when using transactions
  6. $db->set_opts(array('cache_results' => false));
  7.  
  8. // Building the query
  9. $sql = array(
  10.     'test1' => 'val_erw',
  11.     'test3' => 'val_24'
  12. );
  13.  
  14. $where = array(
  15.     'test2' => '242'
  16. );
  17.  
  18. // Generating the UPDATE statement
  19. $update = $db->build_update('test', $sql, $where);
  20.  
  21. // Printing the query used
  22. echo $update;
  23.  
  24. // Turning AUTOCOMMIT off
  25. mysql_query('SET AUTOCOMMIT = 0');
  26.  
  27. // Starting the transaction
  28. $db->transaction('BEGIN');
  29.  
  30. // Carrying out the UPDATE statement
  31. $result = $db->resource($update);
  32. $result = false;
  33.  
  34. if($result === true)
  35. {
  36.    $db->transaction('COMMIT');
  37.    echo "<p>Table updated successfully!</p>";
  38. }
  39. else
  40. {
  41.    $db->transaction('ROLLBACK');
  42.    echo "<p>There was an error updating the database!</p>";
  43. }
  44.  
  45. $result = $db->fetch_rowset('SELECT * FROM test ORDER BY test2 ASC');
  46.  
  47. print_r($result);
  48. ?>


I set the AUTOCOMMIT to 0 and set the $result to false, and yet it still updates the database and doesn't rollback.

Any ideas?

Quote:
UPDATE `test` SET `test1` = 'val_erw', `test3` = 'val_24' WHERE test2 = '242'

There was an error updating the database!
Array
(
[0] => Array
(
[test1] => val_erw
[test2] => 242
[test3] => val_24
)

[1] => Array
(
[test1] => weqrt
[test2] => 2354
[test3] => re ta34t 5w534 345 tre wret w 345w
)

[2] => Array
(
[test1] => sdft56
[test2] => 4536
[test3] => dfrset56 3w45e tg destg
)

[3] => Array
(
[test1] => srftgh
[test2] => 5642
[test3] => dsfg435w 354 6 5ty rew twyre534 6
)

)

It tells me that there was an error in the query but it still updates the database...

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 65 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.