1--TEST--
2MySQL PDO:query() vs. PDO::prepare() and MySQL error 2050
3--EXTENSIONS--
4pdo_mysql
5--SKIPIF--
6<?php
7require_once(__DIR__ . DIRECTORY_SEPARATOR . 'mysql_pdo_test.inc');
8MySQLPDOTest::skip();
9$db = MySQLPDOTest::factory();
10if (MYSQLPDOTest::isPDOMySQLnd())
11    die("skip libmysql only test");
12?>
13--FILE--
14<?php
15    require_once(__DIR__ . DIRECTORY_SEPARATOR . 'mysql_pdo_test.inc');
16    $db = MySQLPDOTest::factory();
17
18    try {
19
20        printf("Native PS...\n");
21        $db->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, 0);
22        if (0 != $db->getAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY))
23            printf("[004] Unable to turn off emulated prepared statements\n");
24
25        printf("Buffered...\n");
26        MySQLPDOTest::createTestTable($db);
27        $db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
28        $stmt = $db->query('SELECT id, label FROM test WHERE id = 1');
29        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
30        $stmt = $db->query('SELECT id, label FROM test WHERE id = 1');
31        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
32
33        printf("Unbuffered...\n");
34        MySQLPDOTest::createTestTable($db);
35        $db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
36        $stmt = $db->query('SELECT id, label FROM test WHERE id = 1');
37        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
38        /*
39        NOTE - this will cause an error and it OK
40        When using unbuffered prepared statements MySQL expects you to
41        fetch all data from the row before sending new data to the server.
42        PDO::query() will prepare and execute a statement in one step.
43        After the execution of PDO::query(), MySQL expects you to fetch
44        the results from the line before sending new commands. However,
45        PHP/PDO will send a CLOSE message as part of the PDO::query() call.
46
47        The following happens:
48
49            $stmt = PDO::query(<some query>)
50                mysql_stmt_prepare()
51                mysql_stmt_execute()
52
53            $stmt->fetchAll()
54                mysql_stmt_fetch()
55
56            And now the right side of the expression will be executed first:
57                $stmt = PDO::query(<some query>)
58                    PDO::query(<some query>)
59                        mysql_stmt_prepare
60                        mysql_stmt_execute
61
62            PHP continues at the left side of the expression:
63
64                $stmt = PDO::query(<some query>)
65
66                    What happens is that $stmt gets overwritten. The reference counter of the
67                    zval representing the current value of $stmt. PDO gets a callback that
68                    it has to free the resources associated with the zval representing the
69                    current value of stmt:
70                        mysql_stmt_close
71                            ---> ERROR
72                            ---> execute() has been send on the line, you are supposed to fetch
73                            ---> you must not try to send a CLOSE after execute()
74                            ---> Error: 2050 (CR_FETCH_CANCELED)
75                            ---> Message: Row retrieval was canceled by mysql_stmt_close() call
76                            ---> MySQL does its best to recover the line and cancels the retrieval
77
78                    PHP proceeds and assigns the new statement object/zval obtained from
79                    PDO to $stmt.
80
81        Solutions:
82                - use mysqlnd
83                - use prepare() + execute() instead of query()
84                - as there is no explicit close() in PDO, try unset($stmt) before the new assignment
85                - fix PDO::query() [not the driver, fix PDO itself]
86        */
87
88        $stmt = $db->query('SELECT id, label FROM test WHERE id = 1');
89        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
90
91        $stmt = $db->prepare('SELECT id, label FROM test WHERE id = 1');
92        $stmt->execute();
93        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
94
95        $stmt = $db->prepare('SELECT id, label FROM test WHERE id = 1');
96        $stmt->execute();
97        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
98
99        unset($stmt);
100        $stmt = $db->query('SELECT id, label FROM test WHERE id = 1');
101        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
102
103        unset($stmt);
104        $stmt = $db->query('SELECT id, label FROM test WHERE id = 1');
105        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
106
107    } catch (PDOException $e) {
108        printf("[001] %s [%s] %s\n",
109            $e->getMessage(), $db->errorCode(), implode(' ', $db->errorInfo()));
110    }
111
112    print "done!";
113?>
114--CLEAN--
115<?php
116require __DIR__ . '/mysql_pdo_test.inc';
117MySQLPDOTest::dropTestTable();
118?>
119--EXPECTF--
120Native PS...
121Buffered...
122array(1) {
123  [0]=>
124  array(2) {
125    ["id"]=>
126    string(1) "1"
127    ["label"]=>
128    string(1) "a"
129  }
130}
131array(1) {
132  [0]=>
133  array(2) {
134    ["id"]=>
135    string(1) "1"
136    ["label"]=>
137    string(1) "a"
138  }
139}
140Unbuffered...
141
142Warning: PDOStatement::fetchAll(): SQLSTATE[HY000]: General error: 2050 Row retrieval was canceled by mysql_stmt_close() call in %s on line %d
143array(0) {
144}
145
146Warning: PDOStatement::fetchAll(): SQLSTATE[HY000]: General error: 2050 Row retrieval was canceled by mysql_stmt_close() call in %s on line %d
147array(0) {
148}
149array(1) {
150  [0]=>
151  array(2) {
152    ["id"]=>
153    string(1) "1"
154    ["label"]=>
155    string(1) "a"
156  }
157}
158array(1) {
159  [0]=>
160  array(2) {
161    ["id"]=>
162    string(1) "1"
163    ["label"]=>
164    string(1) "a"
165  }
166}
167array(1) {
168  [0]=>
169  array(2) {
170    ["id"]=>
171    string(1) "1"
172    ["label"]=>
173    string(1) "a"
174  }
175}
176array(1) {
177  [0]=>
178  array(2) {
179    ["id"]=>
180    string(1) "1"
181    ["label"]=>
182    string(1) "a"
183  }
184}
185done!
186