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