Solved

php SQL insert still throwing exception after catch

Posted on 2014-01-25
17
509 Views
Last Modified: 2014-01-31
I have this pretty simple try catch block that works fine on an old server running php 5.3.20 and it does not work on a brand new server running. PHP 5.3.3. I am thinking it must be a php.ini setting???

The problem is that if my insert gets a 23000 (dup record), I catch it and continue because thats ok. This used to work, but now when that exception is thrown, I catch the 23000 but it does not continue it throws that exception up to the calling method

the code looks like

try{
    $sql = "INSERT INTO mobile_favorites (userID,siteUserID, lastVisited)".
           " values (:userid, :siteuserid, now() )";

    $istmt = $link->prepare($sql);
    $istmt->execute(array(':userid'=>$row['id'], ':siteuserid'=>$row['id'] ));

}catch(Exception $ex){
    if ($ex->getCode() != 23000 ){
        debugLog("login] Exception INSERT INTO mobile_favorites NOT OK-->"
                 .$ex->getMessage()."\n");
    }else{
        debugLog("[login 23000] Exception OK-->\n");
    }
}

Open in new window

0
Comment
Question by:paries
  • 8
  • 7
  • 2
17 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
It appears that the script catches the exception in this code, but what is going on in the debuglog() function?

I might try it something like this just to see what is going on:

try{
    $sql = "INSERT INTO mobile_favorites (userID,siteUserID, lastVisited)".
           " values (:userid, :siteuserid, now() )";

    $istmt = $link->prepare($sql);
    $istmt->execute(array(':userid'=>$row['id'], ':siteuserid'=>$row['id'] ));
}
catch(Exception $ex){
    var_dump($ex);
    trigger_error('Oops', E_USER_ERROR);
}

Open in new window

0
 

Author Comment

by:paries
Comment Utility
All the debugLog is

 error_log("[".date("m.d.y G:i:s")."]".$outString , 3, PHPERRLOG_FILE );

the problem is that the catch is catching the exception , but still throwing it to the calling method
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Did you use the var_dump() recommendation?  If not, please use it.  I think you may find that you're not catching the exception there.  

What may be happening is that you're using PDO and the exception that is being thrown is PDOException, which is not the same as Exception.  But the script is set up to catch Exception, not PDOException.  We do not have enough of the code in this question to know, but the var_dump() will tell you this.

Man page refs that may be helpful:
http://php.net/manual/en/language.exceptions.php
http://php.net/manual/en/language.exceptions.extending.php
http://php.net/manual/en/class.pdoexception.php
0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
PDOException extends RuntimeException
RuntimeException extends Exception

How can PDOException *not* be an Exception?

But generally speaking, maybe this is no Exception indeed. What is it that is finally thrown (and caught in the calling method if I understood it right)?
0
 

Author Comment

by:paries
Comment Utility
I will post the dump later tonight, but the strange part is that the exception is caught. ( i should have put that in the description)
The debug line debugLog("[login 23000] Exception OK-->\n"); gets printed in the logs, but it does not finished the code in that method. I behaves as if after the
 debugLog("[login 23000] Exception OK-->\n");  i do a throw( which i do not)

and in the calling function i am catching Exception and it prints out
php_errors.log-20140126:[01.25.14 19:03:36][login 23000] code=[23000] Exception INSERT INTO mobile_favorites-->SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '8663-8663' for key 'PRIMARY'
0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
1. Is there a finally block in the calling or in the called function?

2. I don't think it should be an issue here, but... Is the variable name $ex used somewhere else?  Especially: is it a global variable? What if you rename the variable to something really unique?

3. Can you quote the (simplified version of) try-catch(-finally) block of the calling party which produced th log entry you quoted?
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I think "finally" is only PHP 5.5+

This script works correctly, inasmuch as it causes PDO to throw a PDOException, and it catches the PDOException.  If you pull out the PDOException catch block, it still works correctly, going into the Exception catch block.

I think we may need to understand the calling method.  Is there any nesting of try{} blocks anywhere?

<?php // RAY_temp_paries.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28348305.html#a39812208
// REF http://www.php.net/manual/en/language.exceptions.php


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

$dsn = "mysql:host=$db_host;dbname=$db_name";
try
{
    $link = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $e)
{
    var_dump($e);
    trigger_error('NO PDO Connection', E_USER_ERROR);
}

// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$link->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$link->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE mobile_favorites
( userID      INT        NOT NULL AUTO_INCREMENT PRIMARY KEY
, siteUserID  INT UNIQUE NOT NULL DEFAULT 0
, lastVisited DATETIME   NOT NULL DEFAULT 0
)
"
;
// RUN QUERY TO CREATE THE TABLE
try
{
    $pdos = $link->query($sql);
}
catch(PDOException $e)
{
    var_dump($e);
}



// THE TEST DATA
$row['id'] = 3;

// THE FIRST TRY SHOULD WORK
try{
    echo PHP_EOL . "FIRST TRY";
    $sql = "INSERT INTO mobile_favorites (userID,siteUserID, lastVisited)".
           " values (:userid, :siteuserid, now() )";

    $istmt = $link->prepare($sql);
    $istmt->execute(array(':userid'=>$row['id'], ':siteuserid'=>$row['id'] ));

}catch(Exception $ex){
    var_dump($ex);
}

// THE SECOND TRY SHOULD FAIL
try{
    echo PHP_EOL . "SECOND TRY";
    $sql = "INSERT INTO mobile_favorites (userID,siteUserID, lastVisited)".
           " values (:userid, :siteuserid, now() )";

    $istmt = $link->prepare($sql);
    $istmt->execute(array(':userid'=>$row['id'], ':siteuserid'=>$row['id'] ));

}

catch(GooseballException $ex){
    echo PHP_EOL . 'GooseballException! ';
    var_dump($ex);
}

catch(PDOException $exc){
    echo PHP_EOL . 'PDOException! ';
    var_dump($exc->getCode());
}

catch (Exception $xyz){
    echo PHP_EOL . 'Exception! ';
    var_dump($xyz);
}

echo PHP_EOL . 'ALL DONE';

Open in new window

0
 

Author Comment

by:paries
Comment Utility
Ray what version of PHP was this script ran with?
My script works find with php 5.3.20 but  it does not work on a brand new server running. PHP 5.3.3.  I will work up a simple test case tonight with the calling function.
Thanks for the help
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
PHP on my server is 5.4.24

PHP 5.3.3 was a big release.  Here is the changelog:
http://www.php.net/ChangeLog-5.php#5.3.3
0
 

Author Comment

by:paries
Comment Utility
Ray,
So i know a little more now.

I changed your code a little to look a little more like what i am doing and found something interesting::
1) if i left it just like you had, but put it a function and ran it , the program did what was expected. i got the PDOException and then ALL DONE IN FUNCTION and then ALL DONE

2) if I commented out GooseballException $ex and PDOException $exc, i got Exception and then calling function did a var dump (see below). So even though PDOException extends Exception(or whatever it is called in PHP) , it behaves differently



<?php // RAY_temp_paries.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "local.millhouse"; // PROBABLY THIS IS OK
$db_name = "";
$db_user = "";
$db_word = "";

$dsn = "mysql:host=$db_host;dbname=$db_name";
try
{
    $link = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $e)
{
    var_dump($e);
    trigger_error('NO PDO Connection', E_USER_ERROR);
}

// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$link->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$link->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE mobile_favorites_test
( userID      INT        NOT NULL AUTO_INCREMENT PRIMARY KEY
, siteUserID  INT UNIQUE NOT NULL DEFAULT 0
, lastVisited DATETIME   NOT NULL DEFAULT 0
)
"
;
// RUN QUERY TO CREATE THE TABLE
try
{
    $pdos = $link->query($sql);
}
catch(PDOException $e)
{
    var_dump($e);
}

try{
    echo PHP_EOL . "Just before test";
    runTest($link);
    echo PHP_EOL . 'ALL DONE';
}catch(Exception $ex){
    var_dump($ex);
}

function runTest($link){

    // THE TEST DATA
    $row['id'] = 3;

    // THE FIRST TRY SHOULD WORK
    try{
        echo PHP_EOL . "FIRST TRY";
        $sql = "INSERT INTO mobile_favorites_test (userID,siteUserID, lastVisited)".
               " values (:userid, :siteuserid, now() )";

        $istmt = $link->prepare($sql);
        $istmt->execute(array(':userid'=>$row['id'], ':siteuserid'=>$row['id'] ));

    }catch(Exception $ex){
        var_dump($ex);
    }

    // THE SECOND TRY SHOULD FAIL
    try{
        echo PHP_EOL . "SECOND TRY";
        $sql = "INSERT INTO mobile_favorites_test (userID,siteUserID, lastVisited)".
               " values (:userid, :siteuserid, now() )";

        $istmt = $link->prepare($sql);
        $istmt->execute(array(':userid'=>$row['id'], ':siteuserid'=>$row['id'] ));

        
    }
    /*
    catch(GooseballException $ex){
        echo PHP_EOL . 'GooseballException! ';
        var_dump($ex);
    }

    catch(PDOException $exc){
        echo PHP_EOL . 'PDOException! ';
        var_dump($exc->getCode());
    }
    */
    
    catch (Exception $xyz){
        echo PHP_EOL . 'Exception! ';
        var_dump($xyz);
    }

    echo PHP_EOL . 'ALL DONE IN FUNCTION';

}

Open in new window






Just before test
FIRST TRY
SECOND TRY
Exception! object(PDOException)#3 (8) {
  ["message":protected]=>
  string(91) "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '3' for key 'PRIMARY'"
  ["string":"Exception":private]=>
  string(0) ""
  ["code":protected]=>
  string(5) "23000"
  ["file":protected]=>
  string(37) "/home/mobile/html/test2300.php"
  ["line":protected]=>
  int(81)
  ["trace":"Exception":private]=>
  array(2) {
    [0]=>
    array(6) {
      ["file"]=>
      string(37) "/home/mobile/html/test2300.php"
      ["line"]=>
      int(81)
      ["function"]=>
      string(7) "execute"
      ["class"]=>
      string(12) "PDOStatement"
      ["type"]=>
      string(2) "->"
      ["args"]=>
      array(1) {
        [0]=>
        array(2) {
          [":userid"]=>
          int(3)
          [":siteuserid"]=>
          int(3)
        }
      }
    }
    [1]=>
    array(4) {
      ["file"]=>
      string(37) "/home/mobile/html/test2300.php"
      ["line"]=>
      int(50)
      ["function"]=>
      string(7) "runTest"
      ["args"]=>
      array(1) {
        [0]=>
        object(PDO)#1 (0) {
        }
      }
    }
  }
  ["previous":"Exception":private]=>
  NULL
  ["errorInfo"]=>
  array(3) {
    [0]=>
    string(5) "23000"
    [1]=>
    int(1062)
    [2]=>
    string(37) "Duplicate entry '3' for key 'PRIMARY'"
  }
}

ALL DONE IN FUNCTION
ALL DONE
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
I'm not seeing any unexpected behavior.  The logic flow creates this output:

Just before test
FIRST TRY
SECOND TRY
Exception!
ALL DONE IN FUNCTION
ALL DONE

The object that is thrown by PDO is the PDOException object.  It's just a question of where the catch{} occurs and what it does with the object.  At the end of catch{} everything is "normal" again.

<?php // RAY_temp_paries.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "local.millhouse"; 
$db_name = "";
$db_user = "";
$db_word = "";

$dsn = "mysql:host=$db_host;dbname=$db_name";
try
{
    $link = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $e)
{
    var_dump($e);
    trigger_error('NO PDO Connection', E_USER_ERROR);
}

// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$link->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$link->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE mobile_favorites_test
( userID      INT        NOT NULL AUTO_INCREMENT PRIMARY KEY
, siteUserID  INT UNIQUE NOT NULL DEFAULT 0
, lastVisited DATETIME   NOT NULL DEFAULT 0
)
"
;
// RUN QUERY TO CREATE THE TABLE
try
{
    $pdos = $link->query($sql);
}
catch(PDOException $e)
{
    var_dump($e);
}

// TRY / CATCH BLOCK INVOKES A FUNCTION
try{
    echo PHP_EOL . "Just before test";
    runTest($link);
    echo PHP_EOL . 'ALL DONE';
}

// THIS DOES NOT GET INVOKED BECAUSE THE EXCEPTION WAS HANDLED IN THE FUNCTION
catch(Exception $ex){
    echo PHP_EOL . 'EXCEPTION CAUGHT OUTSIDE OF RUNTEST()';
    var_dump($ex);
}

function runTest($link){

    // THE TEST DATA
    $row['id'] = 3;

    // THE FIRST TRY SHOULD WORK
    try{
        echo PHP_EOL . "FIRST TRY";
        $sql = "INSERT INTO mobile_favorites_test (userID,siteUserID, lastVisited)".
               " values (:userid, :siteuserid, now() )";

        $istmt = $link->prepare($sql);
        $istmt->execute(array(':userid'=>$row['id'], ':siteuserid'=>$row['id'] ));

    }catch(Exception $ex){
        var_dump($ex);
    }

    // THE SECOND TRY SHOULD FAIL
    try{
        echo PHP_EOL . "SECOND TRY";
        $sql = "INSERT INTO mobile_favorites_test (userID,siteUserID, lastVisited)".
               " values (:userid, :siteuserid, now() )";

        $istmt = $link->prepare($sql);
        $istmt->execute(array(':userid'=>$row['id'], ':siteuserid'=>$row['id'] ));


    }
    /*
    catch(GooseballException $ex){
        echo PHP_EOL . 'GooseballException! ';
        var_dump($ex);
    }

    catch(PDOException $exc){
        echo PHP_EOL . 'PDOException! ';
        var_dump($exc->getCode());
    }
    */

    catch (Exception $xyz){
        echo PHP_EOL . 'Exception! ';
        /*
        var_dump($xyz);
        */
    }

    echo PHP_EOL . 'ALL DONE IN FUNCTION';
}

Open in new window

0
 

Author Comment

by:paries
Comment Utility
that is so weird.
So it has to be my version of php.
when i run it without catching the PDO Exception i get

Just before test
FIRST TRY
SECOND TRY
Exception!
Big stack dump from calling function with no "ALL DONE IN FUNCTION" ever called
ALL DONE

thanks for all your help. At this point it has to be the version of PHP i am using
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
If you got the message "Exception!" the script caught the exception (see line 103 in the most recent post).  I commented out the var_dump() to simplify the messages and make the logic easier to follow.  

I am highly suspicious that this is something else - not related to the PHP version.  PHP 5.3.3 is quite old and if PHP exception handling had gone off the rails in that release we would have heard about it by now.  While it's possible that there is an installation error, or something like that, I don't know of any way that the PHP version could be in play.

Can you please post the big stack dump in the code snippet?  Thanks.
0
 

Author Comment

by:paries
Comment Utility
i posted it above. in the previous message (below the modified version of your test code), or you asking for something else?
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
That's not a stack dump; it's the output from var_dump() called on the Exception Object.  I was wondering if there really was a stack trace, which would indicate something different.

That is the expected output from executing the catch block in this code:
    catch (Exception $xyz){
        echo PHP_EOL . 'Exception! ';
        var_dump($xyz);
    }

Open in new window

It's easy to get confused by the large amount of content in the Exception object and its extensions!
0
 

Author Comment

by:paries
Comment Utility
something has changed. it is now behaving as desired.
I need to go back and see what i have changed
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
If you're using Version Control, that answer is at your fingertips :-)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now