Solved

php SQL insert still throwing exception after catch

Posted on 2014-01-25
17
524 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
17 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39809536
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
ID: 39809681
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 110

Expert Comment

by:Ray Paseur
ID: 39809950
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Surrano
ID: 39811326
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
ID: 39812069
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
ID: 39812208
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 110

Expert Comment

by:Ray Paseur
ID: 39813193
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
ID: 39813235
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39813367
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
ID: 39814036
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 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39814999
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
ID: 39815016
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 110

Expert Comment

by:Ray Paseur
ID: 39815064
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
ID: 39815072
i posted it above. in the previous message (below the modified version of your test code), or you asking for something else?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39815247
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
ID: 39815272
something has changed. it is now behaving as desired.
I need to go back and see what i have changed
0
 
LVL 110

Expert Comment

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

Featured Post

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

615 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