Jump to content

Problem Creating MySQL Event using PHP


chrisrulez001
Go to solution Solved by mac_gyver,

Recommended Posts

Hi there,

 

I'm trying to create a MySQL event using the built-in MySQL event scheduler. This is so that in an hour the users account can be automatically unlocked. The following is the query I'm trying to run:

CREATE EVENT update_locked
ON SCHEDULE
 AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE `check_locked` SET `is_locked` = :locked WHERE `check_locked`.`username` = :username;

Now if I take out the :username and replace it with a valid user from the database, example 'admin' and also replacing the :locked with 0, the query is run fine from PHPMyAdmin and the event is created. But when I run the query from PHP I get no errors and the query supposedly runs but when I check the events table in the MySQL database.

 

Here is the code I'm trying to run in a function:

protected function Lock_Account($username)
                {
                                //Reset the login attempts to 0
                                $this->Reset_Login_Attempts($username);
                               
                                //Lock the users account
                                //Use prepared query
                                $Lock = $this->db->prepare("UPDATE check_locked SET is_locked=:locked WHERE username=:username");
                                //Bind values to prepared query
                                //Execute the lock user prepared query
                                $Lock->execute(array(":locked" => 1, ":username" => $username));
                               
                                //Create event to unlock the users account after an hour
                                //Use prepared query
                                $Lock_Event = $this->db->prepare("CREATE EVENT update_locked
                                                                  ON SCHEDULE
                                                                  AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
                                                                  DO
                                                                  UPDATE `check_locked` SET `is_locked` = :locked WHERE `check_locked`.`username` = :username;");
                                //Bind values to prepared query and execute the set the lock event prepared query
                                $Lock_Event->execute(array(":locked" => 0, ":username" => $username));
                } 

I've tried just running the query from PHP with the :locked replaced with 0 and :username replaced with 'admin', that didn't create the event.

 

Thanks in advance.

 

Link to comment
Share on other sites

the database user you are using for the php connection probably doesn't have permission to create events.

 

have you set the error mode to exceptions on your pdo connection so that any errors will throw exceptions, and are you either catching the exceptions/providing your own exception handler and doing something with the error information OR you have php's error_reporting set to E_ALL and display_errors set to ON so that any uncaught exceptions would be reported and displayed?

Link to comment
Share on other sites

Thanks for your reply,

 

I'm connecting to the database at the moment through the root account, although that probably makes sense why it isn't creating events.

 

The PDO connection is set to throw any exceptions but I'm not catching any exceptions for this query through a try catch, I'll try that. PHP's error reporting is set to E_ALL.

 

Edit: I tried creating a new user with global privileges and re-ran the query, unfortunately this hasn't worked.

 

I also tried a try catch on the query, no exceptions are thrown, PHP doesn't report any errors either.

Edited by chrisrulez001
Link to comment
Share on other sites

  • Solution

just checked the mysql documentation and CREATE EVENT queries cannot be prepared. see the list of statements that can be used - http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

 

just confirmed this by getting your 'prepared' query to work with PDO's prepared query set to 'emulated' and also by using a normal ->query() method with your hard-coded query.

 

using a real prepared query, it silently fails with nothing in the mysql query or error log, so the lack of any pdo error is likely due to the php driver not doing anything in this case.

Edited by mac_gyver
Link to comment
Share on other sites

Thank you very much for helping me out with this.

 

I'll probably use the ->query() method to run this query.

 

EDIT: As I need to pass values to the query, I would probably be best setting PDO prepared query to emulated as suggested

 

EDIT 2: Just tried this with what was suggested above and it works.

 

Thanks again

Edited by chrisrulez001
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.