Mikhail Mikhail - 1 year ago 71
MySQL Question

Executing events in MySQL without SUPER privelege (?)

I have created events in my MySQL. They are working, but in order for them to execute, I need to

SET event_scheduler = ON;

In order to do that, I need to get SUPER priveleges (#1227 error)

I type this:

GRANT SUPER ON *.* TO user@'localhost' IDENTIFIED BY 'password';

But I get another error (#1045, another access denied).

I have contacted my wonderful host, they said that their host is shared, so no super privileges for you. Bye bye.

Can anyone help me to find another way, so the events would begin to run? Account doesn't belong to me, so I can't switch hosts.

Answer Source

Cron jobs are likely the best on shared hosts. If you have access to cPanel, it will make your life a lot easier.

First things, first: Create your script. This can access the database, send emails, pretty much do anything that needs to be done. Please note that $_GET, $_POST, $_REQUEST, and $_COOKIE will be unavailable for the duration of the script, as this isn't a web request. You wont be able to set cookies, or view the information about the remote user (As there is none). You can however, pass variables through command line though, using $argv. See this post for more information.

This sample script will send out a newsletter to all users in the to_be_mailed table:



$body = get_newsletter();

$result = $db->query("SELECT * FROM to_be_mailed");

while ( $row = $result->fetch_row() )
    mail($row['email'], 'Nightly news update!', $body);

echo 'Done sending emails!';

Debugging scripts can be difficult, as you usually don't have access to the output. Have a look at this answer and be sure to enable error_logs so that you can make sure your script executed sucessfully.

When using cPanel's Cron Jobs it makes your life easier by emailing the output to you at the end of execution. This makes it easier for you to track down bugs, and ensure your cron job executes successfully.

Next adding your cron job:

  • See cPanel's Cron Jobs for info on how to add a cron job for cPanel enabled hosts.
  • Non cpanel hosts: See this post. The gist is, you use crontab -e to add the task (This will open up an editor, and you would add in the following line):
    # crontab -e
    00 * * * * /usr/local/bin/php /home/USERNAME/myscript.php
  • If you're just getting started with cron syntax, this site is absolutely wonderful. It will give you examples, help with syntax, and help you figure out the schedule you want to run.