Running a MySQL Scheduled Event via PHP PDO

I’ve just started looking into mySQL Scheduled Events and I want to set/run one via PHP PDO. I have an update query that sets a number of rows in a table to 3. Once this is done I then want to schedule an event to set these values to NULL after ‘X’ minutes. I’ve tested this in mySQL and it performs as expected.

SET GLOBAL event_scheduler = ON;
	CREATE EVENT resetMIS
	ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 minute
	DO
	UPDATE misTable 
	SET mis = NULL
	WHERE mis >2
	AND misID IN(SELECT 
			   misID 
			   FROM misIDTable
			   WHERE misGroupID = 4
		    );

I’d like to be able to utilise this is a prepared statement and set the misGroupID via a parameter. However, when I try to run the above via PHP I get the following:

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

I don’t really understand this error as my UPDATE query does not return any results per se just $stmt->rowcount

Looking forward to receiving guidance and pearls of wisdom…

which PDO method do you use to execute the query?

maybe it helps to rewrite the SQL to not use a sub-select

UPDATE 
    misTable mt
INNER JOIN
    misIDTable mit
    ON
        mt.misID = mit.misID -- a bit of a guess, consult your FK when in doubt
SET 
    mt.mis = NULL
WHERE 
        mt.mis > 2
    AND mit.misGroupID = 4

@Dormilich Hey thanks for that…It was late for me last night and my head was completely fried… I think I got to the bottom of my issue re: error (after a good nights sleep) I had one of those doh! moments. Its a long, long transactional query and it dawned on me this morning (no pun intended) that I was trying to initiate this scheduled Event query inside the transaction… so of course there was already an active query. So I wrote a function and called it separately after the ‘commit’ of the first big query… Worked a treat…
There are days when mySQL and PHP make some sense and other where the simplest of queries bounces majestically off my forehead as it heads skyward… I think last night was one of those moments… but thanks for the cleaner and much more efficient update query. I’m hoping that I’d have got there in he end.

The above statement is not a single query, but a set of queries. And when you are running several queries at once, naturally you have to get the result of each. Hence the error which is telling you that.

To solve this problem you can either get all the results one by one, or simply run each query in separate call, I.e.

$pdo->query("SET GLOBAL event_scheduler = ON");

And then another one, using prepare like you want.

@colshrapnel Thanks for that … I’ve pretty much done what you suggested. Whether that was by design or shear luck I’m not entirely sure. Thanks again for your clarification.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.