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