Invalid PDO query does not return an error

Question

The second SQL statement below returns an error in phpMyAdmin:

SET @num=2000040;
INSERT INTO artikel( artikel_nr, lieferant_nr, bezeichnung_1, bezeichnung_1 )
SELECT @num := @num +1 AS anum, 70338, f2, f3
FROM import
WHERE id >1

MySQL says:

#1110 - Column 'bezeichnung_1' specified twice

All correct. But when I run the queries in Symfony 1.4 with this function:

// run sql query
// http://erisds.co.uk/symfony/snippet-creating-debugging-complex-sql-queries-in-symfony
// http://stackoverflow.com/questions/5434702/php-quick-refactoring
// param $sql:    the query to run
// param $silent: bool if errors should be ignored
// throws:        pdo error info if statement failed and $silent=false
// returns:       pdo-statement (use for looping over result rows and error messages)
public static function runQuery($sql, $silent=false)
{
  $conn = Propel::getConnection();
  $pdo_statement = $conn->prepare($sql);

  $error = null;
  try
  {
    $pdo_statement->execute();
  }
  catch (Exception $e)
  {
    $error = $e->getMessage();
  }

  if ( !$error )
  {
    $pdo_error = $pdo_statement->errorInfo();
    $error = $pdo_error[2];
  }
  if ( !$silent && $error ) throw new Exception($error);

  return $pdo_statement;
}

no error is thrown. The two SQL statements must be submitted at the same time since they depend on each other. The faulty query is constructed from user input. I need to get that error back, otherwise I can't tell if the database was changed, and I can't tell the user about it.

Do you know why PDO doesn't complain about the invalid statement, and if it can't be made to do so, how to get the success/failure information?

BTW the query does update the database if there are no duplicate columns.

Here's the link to the PDOStatement class: http://www.php.net/manual/en/class.pdostatement.php

No, definitely not. I didn't pass the $silent parameter to the function. — Timm, Jun 01 '11 at 15:25

score 11 · Accepted Answer · edited Jun 09 '16 at 10:51

11

This is expected behavior. Since there are two statements and the first one is valid, you have to use nextRowset()

try
  {
    $pdo_statement->execute();
    while ($pdo_statement->nextRowset()) {/* https://bugs.php.net/bug.php?id=61613 */};
  }

Source: bugs.php.net/bug.php?id=61613

edited Jun 09 '16 at 10:51

Motin

4,853
4
44
51

answered Aug 30 '12 at 20:23

Timm

2,488
2
22
25

The bug status over on bugs.php.net is currently Assigned. People are actively asking for a resolution, but it has not been addressed. – Timm Mar 24 '15 at 02:07
This is not a bug. You will notice the status has changed to `Not a bug`. Read this: https://phpdelusions.net/pdo#multiquery – Luke Apr 06 '16 at 09:58
@Motin - I'm not so happy about your edit. You swapped out my whole answer for your answer, taking over my points and acceptance. What you should have done is comment on my answer and then write your own. See http://stackoverflow.com/help/editing. I will check out the `nextRowset` solution and post the results. – Timm Jun 22 '16 at 15:36
@Timm I re-edited the post to include your original post. This makes the correct answer harder to find than if the original post was not there (the original post is not correct any longer and confuses visitors looking for the correct answer) but hopefully the edit now conforms to the guidelines – Motin Jul 18 '16 at 09:27

score 4 · Answer 2 · edited Oct 26 '12 at 12:55

4

By default PDOStatement::execute() doesn't throw any exception, it simply returns false on error. You have to set error handling to PDO::ERRMODE_EXCEPTION through db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION).

edited Oct 26 '12 at 12:55

deadly

1,194
14
24

answered Mar 30 '12 at 11:41

Mav

57
3

2

I tried your suggestion; it makes no difference. The problem really seems to lie in the first statement being valid - PDO doesn't look at the second statement for errors and happily executes the second without complaint. When I just have one invalid statement an exception is thrown whether `PDO::ATTR_ERRMODE` is set or not. I will submit a bug report to php.net and post the response here. – Timm Apr 03 '12 at 22:27
Follow-up of the bug report: This is not a bug. Since there are two statements and the first one is valid, you have to use nextRowset() and this is expected behavior. – Motin Jun 09 '16 at 08:55

score 0 · Answer 3 · answered Oct 13 '13 at 18:37

If you have the option to use mysqli instead of PDO for the multi query, you can use mysqli_multi_query. As error handling is a little complex, here is my function:

/**
 * Executes multiple queries at once (separated by semicolons) and discards the results
 *
 * @param string $sql
 * @throws RuntimeException if a query failed
 */
function multi_query($sql) {
    $mysqli = new mysqli('host', 'user', 'password', 'database');


    //Execute all queries
    $index = 1;
    if ($mysqli->multi_query($sql)) {
        do {
            // next_result() fails if store_result() is not called
            if ($result = $mysqli->store_result()) {
                $result->free();
            }
            $has_next = $mysqli->more_results();
            if (!$has_next)
                return; // all queries successfully executed
            $index++;
        } while ($mysqli->next_result());
    }
    // At this point, either the multi_query() has returned false - which is
    // when the first query failed - or more_results() was true, while next_result()
    // returned false - which is when a different query failed.

    $error = $mysqli->error;
    if (!$error)
        $error = $mysqli->errno ? "errno $mysqli->errno" : '(unknown error)';
    throw new RuntimeException("mysqli query $index failed: $error");
}

Invalid PDO query does not return an error

3 Answers3

Linked