php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #66621 PDO::commit() bug
Submitted: 2014-02-01 00:56 UTC Modified: 2014-12-30 10:42 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:1 (100.0%)
From: enrico_kaelert at kabelmail dot de Assigned: ab (profile)
Status: No Feedback Package: PDO MySQL
PHP Version: Irrelevant OS: Windows 7 64bit
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2014-02-01 00:56 UTC] enrico_kaelert at kabelmail dot de
Description:
------------
Trying to keep it short:

System:
Win7 64bit, xampp (MySQL Server Version: 5.6.14  PHP Version: 5.5.6)
Also tried that on a different system with (i think) PHP Version 5.8

The case:
using PDO to fire more than 1 INSERT INTO via PDO::beginTransaction() and PDO::commit()

The bug:
if i DO NOT set $stmt to "what ever", then the PDO::commit() has no effect.
But if i change $stmt to a string, a number or null - anything else but itself - then all INSERTs are fired up.

Weird to describe.
Just see the test script at "# 3. ..." (line 43) where the "magic" happens.
To switch between the "working version" and the "bug version" simple change the
if(1) to if(0) ...


"bug version"
$stmt is a prepared query which was just executed.
Because we did a $dbh->beginTransaction(); we do now fire a $dbh->commit();
But ... no action has been taken @db - no inserts made.
This only seem to happen if you fire MORE than 1 INSERT at 1 query.


"working version"
The ONLY different here is that we set $stmt to something else.
$stmt is of curse the current prepared and executed query.
Now BEFORE we fire the $dbh->commit() we do: $stmt = 'anything else';
And - its magic - the following $dbh->commit() works :: all INSERTs are done.


Weird:
If you look at the SELECT (# 4.) you can see that we get "fake"(?)-results back.
On every run the AUTO_INCREMENT counts up which is real - so those ids get lost.


Thanks for you time - coulndt do it shorter.
Was looking into that problem for hours today with a friend =)


Test script:
---------------
detailed test script: http://pastebin.com/1QEcnPhM

Expected result:
----------------
explained in the description

Actual result:
--------------
explained in the description

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-02-25 18:20 UTC] enrico_kaelert at kabelmail dot de
Forget about the part:
"
Weird:
If you look at the SELECT (# 4.) you can see that we get "fake"(?)-results back.
On every run the AUTO_INCREMENT counts up which is real - so those ids get lost.
"

Of curse the queries was sent to the sql server, auto_increment counted up (see docu dev.mysql.com).
The INSERTS/UPDATES/.. are not committed, so i guess we are getting the data out of temp tbls.
 [2014-04-23 14:37 UTC] ab@php.net
-Status: Open +Status: Feedback
 [2014-04-23 14:37 UTC] ab@php.net
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc. If the script requires a 
database to demonstrate the issue, please make sure it creates 
all necessary tables, stored procedures etc.

Please avoid embedding huge scripts into the report.


 [2014-04-23 15:27 UTC] enrico_kaelert at kabelmail dot de
I changed the test script so the database and the table will be created.
I cant make it easier for you ;)

here is the script: http://pastebin.com/LyK5FUbN
 [2014-04-24 06:25 UTC] ab@php.net
Hi,

I very appreciate your willingness to help, but still the script is not "10-20 lines long". Please reduce it as much as possible, it is really hard to understand now what is your issue and it would take to much time to debug such a big piece of code.

Many thanks.
 [2014-04-24 06:25 UTC] ab@php.net
-Assigned To: +Assigned To: ab
 [2014-04-24 12:25 UTC] enrico_kaelert at kabelmail dot de
Sorry, but i cant reduce that test script to 20 lines.
I dont wanna wast your or my time.
But i wrote it again as simple as possible: explanation and script at once.

http://pastebin.com/wt2DiNNB
If you dont understand what im doing there .. well, then sorry, i wont do more.
What you do if somebody wants to report a huge security bug? In 20 lines? 
Well, ... . Please dont get me wrong, but you need to spend a bit time to bug reports.
 [2014-04-24 14:05 UTC] ab@php.net
Enrico,

don't get me wrong, but brevity is wit. The script is still too excessive, but the explanation in your latest pastebin is much better, even for such a dull person like me. Lets see.

Thanks
 [2014-04-29 09:38 UTC] johannes@php.net
With the ode from 2014-04-24 12:25 UTC I get this result:

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => a
        )

    [1] => Array
        (
            [id] => 2
            [name] => b
        )

    [2] => Array
        (
            [id] => 3
            [name] => c
        )

)

This seems right to me. I tried a handful combinations of MySQL servers & PHP versions. Not on windows, though (while I can't imagine something Windows specific to be the case)
 [2014-04-29 14:51 UTC] enrico_kaelert at kabelmail dot de
@johannes
Which $type you used?
And did you looked into the db to make sure the entries really exists?

@all
btw: something new:
I found out that:
- if you use only 1 INSERT INTO in the query
- and if you add comments to the query 
then the same effect happen: no INSERT made.

So change the query in the test script to: 
# 3. multi insert via beginTransaction() and commit() / rollBack()
(...)
$sql = "
    INSERT INTO `testdb`.`tbl` SET `name` = 'a'; -- this is my comment
    -- this is a new line comment
";
(...)

Delete the test table if exists and use the $type "regular".
This is only 1 INSERT INTO and should work.
But it doesnt because of the comments(?).

BUT it will work if we destroy the $stmt (by using the $type "alternative").
 [2014-04-29 15:07 UTC] enrico_kaelert at kabelmail dot de
edit:
try this:

change the query in the test script to: 
# 3. multi insert via beginTransaction() and commit() / rollBack()
(...)
$sql = "
    -- this is my pretty commented query ...
    -- above comments works
    INSERT INTO
        `testdb`.`tbl`
    SET
        `name` = 'a' -- middle comment works
        -- `name` = 'a' middle new line comments works
    ; -- but this comment finally f* it up
    -- so simple nothing is allowed after the ;
    -- except if we destroy the stmt
";
 [2014-04-30 11:47 UTC] ab@php.net
Johannes, thanks for looking in. As far as I could debug by now, the behaviour is reproduceable. But, only if mysqld runs on windows. When I point the snippet from April 24th to a DB running under Linux, the error disappears. Strange enough, but seems to have to do with mysqld+platform itself or its configs.

Additionally, as far I could debug, the error is caused by something in mysqlnd, namely mysqlnd.c:320 in simple_command method. There, when asked for the connection status, it appears to be CONN_NEXT_RESULT_PENDING, so produces an error and no COMMIT. In the backtrace, simple_method is called from the pdo commit method doing COMMIT query internally. That's all I have at the moment, so digging deeper into it. Interestingly, when adding CONN_NEXT_RESULT_PENDING to CONN_READY case, it seems to pass. But I'm entirely not sure it should be so.

Enrico, could you please test whether it works if you use mysqld not on windows? Thanks.
 [2014-04-30 14:06 UTC] enrico_kaelert at kabelmail dot de
Debian 
MySQL Server Version: 5.5.31-1~dotdeb.0 
PHP Version: 5.4.25-1~dotdeb.0

Same result as on windows: 
1st $type : no inserts 
2nd $type : inserts made

So i cant confirm that this happen only on windows
 [2014-04-30 14:28 UTC] ab@php.net
Enrico,

I meant you stay on windows with PHP, but use mysqld on linux. Not both of them on Linux. But after what you report it actually smells on some config issue, as on my side it was all fine on linux (with your last http://pastebin.com/wt2DiNNB).

Thanks.
 [2014-07-14 13:02 UTC] enrico_kaelert at kabelmail dot de
Any news on this one?
I just tried the same using the msqli object (http://php.net/manual/de/mysqli.quickstart.prepared-statements.php)
and i found out that its "forbidden" to use multi-queries (getting always an e-code).
And that good! if we use something wrong, slap us with a e-msgs =)

But when you add that to the PDO:: ?
 [2014-12-30 10:42 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Mon Aug 02 10:01:24 2021 UTC