PDO SQL-state "00000" but still error?

Question

Can anybody explain why

$sql->execute($params);

returns FALSE, whereas

print $pdo->errorCode();
print_r($pdo->errorInfo());

both return SQLSTATE 00000, which means according to the documentation success? It is an INSERT and nothing is actually being inserted into the database... so, why do I get a success message from SQLSTATE?

In case it helps, this is the code...

$sql = $pdo->prepare("
        INSERT INTO user (
            username, fname, pass, salt, email,
            loc_id_home, country_id_home, region_id_home,
            cont_id_home, timestamp_reg, timestamp_upd, timestamp_lastonline, 
            online_status, gender, birthdate
            )
        VALUES (
            :username,:fname,:pass,:random_salt,:email,
            :loc_id_home,:country_id_home,:region_id_home,
            :cont_id_home,'".time()."','".time()."','".time()."',
            1,:gender,:birthdate)
        ");

$params=array(
    ':username'=>$username,
    ':fname'=>$fname,
    ':pass'=>$pass,
    ':random_salt'=>$random_salt,
    ':email'=>$email,
    ':loc_id_home'=>$loc_id_home,
    ':country_id_home'=>$country,
    ':region_id_home'=>$region,
    ':cont_id_home'=>$continent,
    ':gender'=>$gender,
    ':birthdate'=>$birthdate
);  

$sql->execute($params);

print $pdo->errorCode();
print_r($pdo->errorInfo());

There's a [bug](http://bugs.mysql.com/bug.php?id=8759). Maybe this is a reason. — Leri, Jul 17 '12 at 09:51
You also need to check `PDOStatement::errorCode` meaning `$sql->errorCode()` after the creation of the prepared statement. — Mihai Stancu, Jul 17 '12 at 10:01
I know it's old, but I would advise to use $sql->errorInfo(); instead of $pdo->errorInfo(); — Seb, May 21 '15 at 16:05
Gotta wonder about the logic behind marking this question as a duplicate of a question asked more than 3 YEARS AFTER this one. This is the better question, with the better title, and more answers. Any idea how that happened @YourCommonSense ? — Ragdata, Jul 24 '19 at 01:48

score 20 · Accepted Answer · edited Apr 09 '18 at 16:11

20

It is because $pdo->errorInfo() refers to the last statement that was successfully executed. Since $sql->execute() returns false, then it cannot refer to that statement (either to nothing or to the query before).

As to why $sql->execute() returns false, I don't know... either there is a problem with your $params array or with your database connection.

PDO::errorCode — Fetch the SQLSTATE associated with the last operation on the database handle

Note: The PHP manual (http://php.net/manual/en/pdo.errorinfo.php) does not define exactly what "last operation on the database handle" means, but if there was an issue with binding parameters, that error would have occurred inside PDO and without any interaction with the database. It is safe to say that if $pdo->execute() returns true, that $pdo->errorInfo() is valid. If $pdo->execute() returns false, the behavior of $pdo->errorInfo() is not explicitly clear from the documentation. If I recall correctly from my experience, execute returns true, even if MySQL returned an error, returns false if no operation was done. Since the documentation is not specific, it might be db driver specific.

This answer reflects practical experience as of when it was written in September 2012. As a user has pointed out, the documentation does not explicitly reaffirm this interpretation. It also may only reflect the particular database driver implementation, but it should always be true that if $pdo->execute() returns true, that $pdo->errorInfo() is valid.

You might also want to set PDO::ERRMODE_EXCEPTION in your connect sequence. Exception handling makes it unnecessary to check and query the error.

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

edited Apr 09 '18 at 16:11

Sᴀᴍ Onᴇᴌᴀ

8,218
8
36
58

answered Sep 15 '12 at 15:32

user1122069

1,767
1
24
52

3

Your first sentence doesn't make sense. $pdo->errorInfo() does not refer to the last SUCCESSFULLY executed statment. Simple because it is an error info. It does refer to the _last operation on the database handle_ as given on the php documentation. – Seb May 21 '15 at 15:57
"last operation performed by this database handle" == "last statement that was successfully executed". Performed means completed and a failed statement was not preformed. Try it yourself and note the check mark on the answer. – user1122069 May 22 '15 at 16:35
The documentation does not say it is a "performed operation". It is just the "last operation on the database" - even if it does not perform and execute() returns false - you can still use errorInfo(). You can try it yourself. Misspell a column name, execute the query (it will return false) and call errorInfo(). Very important is what you check. If you check the overall connection($pdo) it might return no error, but your used query connection($sql) will do. If you use $sql->errorInfo() you get the error. – Seb May 27 '15 at 10:11
@Seb The documentation does say operation. If the parameter array is invalid no operation will be run on the database handle, and no error generated. That edge case should rightly be mentioned in the documentation, or proactively cleared. It is also entirely possible that this behavior has changed since '12, or will change someday. – user1122069 Sep 13 '17 at 15:43
Not for this particular query listed, but sometimes the issue arises from the query itself. I had this error occur when I forgot to close a parenthesis in an Insert Query after listing the values. ex. "INSERT INTO TABLE (Column1, Column2, Column3) VALUES (...,...,..." <----Missing ")" caused SQL error – Dfranc3373 Dec 29 '17 at 22:54

Renjith K N · Answer 2 · 2012-10-24T06:28:01.393

1

I Faced the similar problem ,

This occurs manly due to error in query, try to run your query in php-myadmin or any other query runner and confirm that your query is working fine.
Even if our query syntax is correct other simple errors like leaving null or not mentioan a column that set as not null in table structure will cause this error.(This was the errror made by me)

As user1122069 explained the reason for $pdo->errorInfo() says nothing is wrong may be due to

$pdo->errorInfo() refers to the last statement that was successfully executed.
Since $sql->execute() returns false, then it cannot refer to that statement (either to nothing or to the query before)

Hopes this helps :)

edited Oct 24 '12 at 06:28

answered Sep 27 '12 at 16:34

Renjith K N

2,613
2
31
53

3

Doesn't make much sense to have no error code when execute returns false as people would like to know why it returns false. That this behavior is by design only makes it worse. $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); might give you an idea what's going on as it will throw somthing – HMR Dec 11 '12 at 06:58

score 1 · Answer 3 · answered Oct 24 '12 at 23:45

From the php manual:

PDO::ERR_NONE (string) Corresponds to SQLSTATE '00000', meaning that the SQL statement was successfully issued with no errors or warnings. This constant is for your convenience when checking PDO::errorCode() or PDOStatement::errorCode() to determine if an error occurred. You will usually know if this is the case by examining the return code from the method that raised the error condition anyway.

So it sounds like it did insert the record. Check the last record id in your table... maybe you just missed it?

score 0 · Answer 4 · answered Oct 04 '12 at 04:01

I was getting this error at one time. I only got it on one server for all failures. A different server would report the error correctly for the same errors. That led me to believe it was a MySQL client configuration error. I never solved the specific error, but check your configurations.

score -1 · Answer 5 · answered Oct 18 '12 at 10:14

-1

Try to check $sql by print_r() and copy your query then try resultant query in phpMyadmin. Hope will get the reason. There would be chance of irrelevant value.

answered Oct 18 '12 at 10:14

Farhan

1,453
2
15
20

PDO SQL-state "00000" but still error?

5 Answers5

Linked

Related