1--TEST--
2MySQL PDO:query() vs. PDO::prepare() and MySQL error 2050
3--EXTENSIONS--
4pdo_mysql
5--SKIPIF--
6<?php
7require_once __DIR__ . '/inc/mysql_pdo_test.inc';
8MySQLPDOTest::skip();
9if (MYSQLPDOTest::isPDOMySQLnd())
10    die("skip libmysql only test");
11?>
12--FILE--
13<?php
14    require_once __DIR__ . '/inc/mysql_pdo_test.inc';
15    $db = MySQLPDOTest::factory();
16
17    $table = 'pdo_mysql_stmt_unbuffered_2050';
18
19    try {
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($table, $db);
27        $db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
28        $stmt = $db->query("SELECT id, label FROM {$table} WHERE id = 1");
29        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
30        $stmt = $db->query("SELECT id, label FROM {$table} WHERE id = 1");
31        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
32
33        printf("Unbuffered...\n");
34        MySQLPDOTest::createTestTable($table, $db);
35        $db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
36        $stmt = $db->query("SELECT id, label FROM {$table} 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 {$table} WHERE id = 1");
89        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
90
91        $stmt = $db->prepare("SELECT id, label FROM {$table} WHERE id = 1");
92        $stmt->execute();
93        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
94
95        $stmt = $db->prepare("SELECT id, label FROM {$table} 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 {$table} WHERE id = 1");
101        var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
102
103        unset($stmt);
104        $stmt = $db->query("SELECT id, label FROM {$table} 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_once __DIR__ . '/inc/mysql_pdo_test.inc';
117$db = MySQLPDOTest::factory();
118$db->exec('DROP TABLE IF EXISTS pdo_mysql_stmt_unbuffered_2050');
119?>
120--EXPECTF--
121Native PS...
122Buffered...
123array(1) {
124  [0]=>
125  array(2) {
126    ["id"]=>
127    string(1) "1"
128    ["label"]=>
129    string(1) "a"
130  }
131}
132array(1) {
133  [0]=>
134  array(2) {
135    ["id"]=>
136    string(1) "1"
137    ["label"]=>
138    string(1) "a"
139  }
140}
141Unbuffered...
142
143Warning: PDOStatement::fetchAll(): SQLSTATE[HY000]: General error: 2050 Row retrieval was canceled by mysql_stmt_close() call in %s on line %d
144array(0) {
145}
146
147Warning: PDOStatement::fetchAll(): SQLSTATE[HY000]: General error: 2050 Row retrieval was canceled by mysql_stmt_close() call in %s on line %d
148array(0) {
149}
150array(1) {
151  [0]=>
152  array(2) {
153    ["id"]=>
154    string(1) "1"
155    ["label"]=>
156    string(1) "a"
157  }
158}
159array(1) {
160  [0]=>
161  array(2) {
162    ["id"]=>
163    string(1) "1"
164    ["label"]=>
165    string(1) "a"
166  }
167}
168array(1) {
169  [0]=>
170  array(2) {
171    ["id"]=>
172    string(1) "1"
173    ["label"]=>
174    string(1) "a"
175  }
176}
177array(1) {
178  [0]=>
179  array(2) {
180    ["id"]=>
181    string(1) "1"
182    ["label"]=>
183    string(1) "a"
184  }
185}
186done!
187