CODEWITHSUNDEEP
phpmysqlpdomysqlnd
Tom Raganowicz
Tom Raganowicz

Reputation: 2470

MySQL PDO empty result set when ATTR_EMULATE_PREPARES set to false and query is killed

When PDO::ATTR_EMULATE_PREPARES is set to false and your query get killed (e.g. MySQL Workbench -> Client Connections -> Right click -> Kill query) during state: "Sending data", PDO "query" or "execute" methods, returns empty PDOStatement. PDO should throw an EXCEPTION (only if PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION) or return false instead of PDOStatement.

As a result your application receives empty result set, despite that query should return some data. There is no way of handling that situation. When you run errorCode() on received PDOStatement you get: '00000', errorInfo() returns an array:

['00000', null, null]

I am running: MySQL 5.6.22, PHP 5.6.4, pdo_mysql - mysqlnd 5.0.11-dev - 20120503

This leads me to assumption that there is bug either in PDO or MySQL.

It is explained here: PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not? that for MySQL >=5.1.17 it is recommended to rely on MySQL and have the emulation turned off.

Is it unrecommended to use the PDO emulation?

What might be the impact on my application when I leave it default (enabled)?

Personally I think it's quite critical issue, because when problem occurs (query execution fail etc.) your application thinks there is no data when actually there is some. Imagine that your application didn't trigger a reminder to buy your girlfriend birthday flowers? Pure evil.

Note

When query is interrupted in state: 

 "Copying to tmp table"

you get PDOException with message: 

 "SQLSTATE[70100]: <<Unknown error>>: 1317 Query execution was interrupted"

You get exactly the same exception when you kill that query execution even if in "Sending data" state, but PDO::ATTR_EMULATE_PREPARES must be se to true.

Upvotes: 1

Views: 452

Answers (1)

Thanh T&#249;ng B&#249;i
Thanh T&#249;ng B&#249;i

Reputation: 13

I faced the same issue. Luckily in my case I only need to check for existence of data, so my workaround is to wrap a count statement around the original select query:

SELECT COUNT(*) FROM (<original query>);

In this case, I will receive an empty response only on timeout.

Upvotes: 0

Related Questions