How to execute all queries in full or none at all

[hadoob024]

Like in my PHP script suppose I have to perform a SELECT, then an INSERT, then a DELETE, then maybe another SELECT or DELETE. Now suppose this script somehow fails on the DELETE or INSERT or the last SELECT. How do I get it to disregard the other queries unless everything goes through? Do I need to use something like transactions, commits, and rollbacks?

[shoz]

[quote]
Do I need to use something like transactions, commits, and rollbacks?
[/quote]
You should.

[a href=\"http://www.innodb.com/ibman.php\" target=\"_blank\"]http://www.innodb.com/ibman.php[/a]

[hadoob024]

cool. so maybe something like:

[code]
$connresult = @mysql_connect("myDatabase","myUser","myPassword");
if (!$connresult)
{
    $problemtext = 'Error opening a db connection.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR);
}

$db_selected = @mysql_select_db('ownersfinancingrealestate_com_-_listingsdb', $connresult);
if (!db_selected)
{
    $problemtext = 'Error selecting MySQL db.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR);
}

//For MySQL 4.0.11 and higher
$start_transaction = "START TRANSACTION";
$start_transaction_result = mysql_query($start_transaction);
if (!$start_transaction_result)
{
    $problemtext = 'Error Starting MySQL Transaction.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR);
}


$insert_query = "INSERT INTO `ofrearchive`
     SELECT * FROM `ofrelistings` LEFT JOIN `ofrearchive` USING(uniqueid)
     WHERE `ofrearchive.uniqueid` IS NULL AND DATEDIFF(`ofrelistings.dateadded`, now())>30";
$insert_result = mysql_query($insert_query);
if (!insert_result)
{
     $rollback_transaction = "ROLLBACK";
     $rollback_result = mysql_query($rollback_transaction);
     if (!$rollback_result)
     {
         $problemtext = 'Error with Rollback.\r\n'.mysql_error($connresult);
         trigger_error($problemtext, E_USER_ERROR);
     }
    $problemtext = 'Error INSERT-ing information into archive table.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR);
}

$delete_query = "DELETE `ofrelistings.*` FROM `ofrelistings` INNER JOIN `ofrearchive` USING(uniqueid)";
$delete_result = mysql_query($delete_query);
if (!$delete_result)
{
     $rollback_transaction = "ROLLBACK";
     $rollback_result = mysql_query($rollback_transaction);
     if (!$rollback_result)
     {
         $problemtext = 'Error with Rollback.\r\n'.mysql_error($connresult);
         trigger_error($problemtext, E_USER_ERROR);
     }
    $problemtext = 'Error DELETE-ing information from main table.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR);
}

$select_query = "SELECT * FROM `ofrelistings` INNER JOIN `ofrearchive` USING(uniqueid)";
$select_result = mysql_query($select_query);
if (!select_result)
{
        echo 'Backup worked successfully';
}
else
{
     $rollback_transaction = "ROLLBACK";
     $rollback_result = mysql_query($rollback_transaction);
     if (!$rollback_result)
     {
         $problemtext = 'Error with Rollback.\r\n'.mysql_error($connresult);
         trigger_error($problemtext, E_USER_ERROR);
     }
    $problemtext = 'Error completing the backing up of information from main table to archive table.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR);
}

$searchstring = "SELECT * FROM `ofrelistings` WHERE blahblahblahblah";
$searchresults = mysql_query($searchstring);
if (!$searchresults)
{
     $rollback_transaction = "ROLLBACK";
     $rollback_result = mysql_query($rollback_transaction);
     if (!$rollback_result)
     {
         $problemtext = 'Error with Rollback.\r\n'.mysql_error($connresult);
         trigger_error($problemtext, E_USER_ERROR);
     }
    $problemtext = 'Error searching OFRE listings db.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR);
}


//If no errors occurred, then commit transactions
$commit_transaction = "COMMIT";
$commit_result = mysql_query($commit_transaction);
if (!$commit_result)
{
    $problemtext = 'Error with Commit.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR);
}


$numrows = mysql_num_rows($searchresults);
if ($numrows == 0)
{
    echo "No results matched your search criteria";
    require('/home/virtual/ofre.com/var/www/includes/copyright.php');
    require('/home/virtual/ofre.com/var/www/includes/closetables.php');
    exit;
}


//Retrieve, check, and print results
while ($row = mysql_fetch_assoc($searchresults))
{
    //echo out results in a table including handling pics
}


//free up result memory
mysql_free_result($searchresults);
[/code]

[shoz]

What you posted should work. Note that if you don't issue a COMMIT the transaction should be rolled back when you end your session. Although I would explicitly rollback the transaction as you've done.

Note also that you can use a [a href=\"http://www.php.net/set_error_handler\" target=\"_blank\"]custom error handler[/a] to handle the printing of the error,rolling back the transaction and the exiting for this script. Remember that because you're not explicitly calling for an exit and currently rely on the PHP error handler to exit the script on an E_USER_ERROR, you'll have to manually call an exit in the error handler.

[hadoob024]

Oh right. Good call. So change:
[code]
//If no errors occurred, then commit transactions
$commit_transaction = "COMMIT";
$commit_result = mysql_query($commit_transaction);
if (!$commit_result)
{
    $problemtext = 'Error with Commit.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR);
} [/code]


to this instead:
[code]
//If no errors occurred, then commit transactions
$commit_transaction = "COMMIT";
$commit_result = mysql_query($commit_transaction);
if (!$commit_result)
{
     $rollback_transaction = "ROLLBACK";
     $rollback_result = mysql_query($rollback_transaction);
     if (!$rollback_result)
     {
         $problemtext = 'Error with Rollback.\r\n'.mysql_error($connresult);
         trigger_error($problemtext, E_USER_ERROR);
     }

    $problemtext = 'Error with Commit.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR);
}
[/code]


And this is what I have in my customer error handler:

[code]
function my_error_handler($error_type, $error_string, $errFile, $errLine)
{
    $userip = $_SERVER['REMOTE_ADDR'];
    $userhostname = $_SERVER['REMOTE_HOST'];
    $subject = 'Unexpected OFRE.com Error: '.date("F j, Y, g:i a");
    $body = "Error Message: $error_string\r\n"."Error Code: $error_type\r\n"."ErrFile: $errFile\r\n"."ErrLine: $errLine\r\n"."User IP Address: $userip\r\n"."User Host Name: $userhostname\r\n"."========================================================\r\n";

    //Notify site administrator by email.
    mail("admin@mysite.com", $subject, $body);
    //Write same subject line and body of the email to the error log
    error_log("$subject \r\n $body \r\n");

    echo '<div align="center"><B><font size="+2">System down for maintenance.</font><BR />Please try again later.  Thank you and sorry for the inconvenience.</B></div><BR /><BR />';
    require('/home/virtual/ofre.com/var/www/includes/copyright.php');
    require('/home/virtual/ofre.com/var/www/includes/closetables.php');
    echo '</body></html>';
    exit;
}
[/code]


Is that what you were talking about? Does it look thorough enough? Thanks!

[shoz]

[quote]
Note that if you don't issue a COMMIT the transaction should be rolled back when you end your session. Although I would explicitly rollback the transaction as you've done.
[/quote]
What I meant by the above was that the transaction would be rolled back automatically if you didn't issue a COMMIT before closing the connection to the MYSQL SERVER(ending your session). I was just letting you know that you have the option of not doing a ROLLBACK manually when anything fails and could just exit the script (As long as you're not using a persistent connection mysql_pconnect() that is). As I said previously however, I would do the rollbacks explicitly as you've done.

I mentioned using the error handler because you were duplicating the ROLLBACK code whenever something failed . Using the error handler to do the ROLLBACK etc, would allow you to do all of the rollbacks in one place. You could also use a function, but I thought that because the script is relatively small and that you triggered an E_USER_ERROR on all failures it would be a good place for it. I'd recommend you just use a function to do the rollbacks for the time being.
[code]
if (!$result)
{
   my_rollback();
}
[/code]

[hadoob024]

Cool. Thanks. I get now what you were saying. And my tables have to be something like InnoDB and not MyISAM if I want to use transactions, right? And in is:

[code]
//If no errors occurred, then commit transactions
$commit_transaction = "COMMIT";
$commit_result = mysql_query($commit_transaction);
if (!$commit_result)
{
    $problemtext = 'Error with Commit.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR);
}
[/code]


Is this overkill to call a ROLLBACK if the COMMIT fails?

[shoz]

[quote]
my tables have to be something like InnoDB and not MyISAM if I want to use transactions, right?
[/quote]
That's correct.
[quote]
is this overkill to call a ROLLBACK if the COMMIT fails?
[/quote]
I don't see any problem with it.

[hadoob024]

Cool! Thanks a bunch. You answered all my questions.

[hadoob024]

Here's a follow-up. Do I also need to use LOCK TABLES and UNLOCK TABLES?

[shoz]

[quote]
For InnoDB and BDB tables, MySQL uses only table locking if you explicitly lock the table with LOCK TABLES. For these storage engines, we recommend that you not use LOCK TABLES at all, because InnoDB uses automatic row-level locking and BDB uses page-level locking to ensure transaction isolation.
[/quote]
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/table-locking.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/table-locking.html[/a]

Innodb has it's own locking system
[a href=\"http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/5.0/en/inn...tion-model.html[/a]

Depending on what other scripts you have that modify the database and in what way, you may or may not need to worry about that as the default handles a number of things automatically.

Read through the page linked to above to get a better understanding of how the innodb transaction model works. Use some test innodb tables and multiple connections to try things out. You can also use the queries in this script as a test. ie Open up two connections start a transaction in each. Run the first INSERT SELECT query in the first connection and then do the same query in the other transaction etc etc.

Btw. when looking through your code one thing that seems incorrect is
[code]
$select_query = "SELECT * FROM `ofrelistings` INNER JOIN `ofrearchive` USING(uniqueid)";
$select_result = mysql_query($select_query);

if (!select_result)
{
        echo 'Backup worked successfully';
}
[/code]
I think what you want is
[code]
if (!mysql_num_rows($select_result))
[/code]
You should be able to trust the previous queries that did the backup however.

[shoz]

From the look of this script you're using a script that's accessed for displaying some results to do the backup when it's accessed yes?

I'd recommend you make the backup/archive portion of this script into a cronjob and have it do the backup periodically, to avoid "problems" (possible deadlocks etc) with the backing up affecting the display of search results. Although, you can code the script so that archiving problems don't affect the display of search results.

[hadoob024]

Hmm. That's an interesting point. Yeah, you're right. Basically, the first 3 transactions backup the main table to a backup table, then delete from the main table, and then verify (using the SELECT statement) that this was done successfully (yeah, I know I should be able to trust that the first 2 go through ok, but I'm just trying to be overly cautious here).

But yeah, what you're saying makes sense. Instead of having these transactions called everytime someone uses our "search listings" page, I'll just create a cron job to run once a day. Hmm... This being the case, I guess I don't need to use the InnoDB engine and can just stick with MyISAM, right? Because I really won't have to use transactions/rollbacks/commits and such, right? Or should I still use InnoDB, so that I can use transactions with my cron job? Thanks!

[shoz]

[quote]
But yeah, what you're saying makes sense. Instead of having these transactions called everytime someone uses our "search listings" page, I'll just create a cron job to run once a day. Hmm... This being the case, I guess I don't need to use the InnoDB engine and can just stick with MyISAM, right? Because I really won't have to use transactions/rollbacks/commits and such, right? Or should I still use InnoDB, so that I can use transactions with my cron job? Thanks!
[/quote]
The queries to do the archive are made in such a way that even if the script/query fails along the way, the database wouldn't/shouldn't be left in an inconsistent state. Subsequent executions of the script should resolve any "errors" in the event something goes wrong(eg: listings that weren't removed from the listings table or inserted into the archive table).

I'd think that the advantages of keeping the table as MYISAM, especially since you're using the table for searching ([a href=\"http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html\" target=\"_blank\"]FULLTEXT INDEXES[/a]) outways the advantages of using transactions(ie INNODB tables) in this case.

My only suggestion would be that if you're going to use MYISAM tables, you should get a WRITE LOCK on both tables before doing the archiving.
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html[/a]
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]LOCK TABLES ofrearchive WRITE, ofrelistings WRITE [!--sql2--][/div][!--sql3--]

[hadoob024]

Oh yeah. You're right. So I guess I don't really need transactions at all. That's good to know. And it seems that once this script runs to completion, the tables get unlocked, right? Or should I explicitly add an UNLOCK TABLES after all my archiving code?

[shoz]

[quote]
And it seems that once this script runs to completion, the tables get unlocked, right? Or should I explicitly add an UNLOCK TABLES after all my archiving code?
[/quote]
The locks should be released once you close your connection to the server, but I'd recommend that you explicitly do the unlock.

[hadoob024]

Cool. That's what I thought. It's like you know stuff "should" happen, but it doesn't mean that it will. I'll go ahead and add the code to unlock the tables. Thanks again for all your help!