Good afternoon everyone,
I need your advice about this function.
I activated on my MySQL server tye Global event scheduler using this command inside my.cnf
event_scheduler = ON;
I need to verify every 3 minutes if there is a new ticket with record new in the table ticket.
To do this I wrote this:
conn = mysqli_connect($servername, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
$sql = "CREATE EVENT popup ON SCHEDULE EVERY 3 MINUTES STARTS '2020-05-30 16:20:00' DO SELECT wid, state FROM ticket WHERE state= New";
if($result = mysqli_query($conn, $sql)){
if(mysqli_num_rows($result) > 0){
echo "<table>";
echo "<tr>";
echo "<th>wid</th>";
echo "<th>state</th>";
echo "</tr>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['wid'] . "</td>";
echo "<td>" . $row['state'] . "</td>";
echo "</tr>";
$wid = $row['wid'];
$state = $row['state'];
}
echo "</table>";
// Free result set
mysqli_free_result($result);
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
$conn->close();
Do you think that this can works for my purpose?
I tried to install it inside my hook directory ticket.php but it made me some problems like this:
ERROR: Could not able to execute "CREATE EVENT popup ON SCHEDULE EVERY 3 MINUTES STARTS '2020-05-30 16:20:00' DO SELECT wid, state FROM ticket WHERE state= New"
What do you think about it please?
Thank you very much indeed
Have a nice weekend
Davide
Cron on Mysql
Re: Cron on Mysql
Hi,
I dont think that you will achieve what you try to do with the event_scheduler.
The event scheduler is a database side event (=timebases) driven action. You can manipulate your database through that, but not trigger a server side php action. Also you cannot retrieve a result as you try to do in your script.
If you want to do such a check every 3 minutes you could do that via a cron job.
-----------------------------
For your script try:
can you test if the event scheduler is running via:
SELECT @@global.event_scheduler
on your DB. The result of the query should be "ON".
Next I think it should be "EVERY 3 MINUTE" not MINUTES
and state='New' instead of state= New
I dont think that you will achieve what you try to do with the event_scheduler.
The event scheduler is a database side event (=timebases) driven action. You can manipulate your database through that, but not trigger a server side php action. Also you cannot retrieve a result as you try to do in your script.
If you want to do such a check every 3 minutes you could do that via a cron job.
-----------------------------
For your script try:
can you test if the event scheduler is running via:
SELECT @@global.event_scheduler
on your DB. The result of the query should be "ON".
Next I think it should be "EVERY 3 MINUTE" not MINUTES
and state='New' instead of state= New
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Cron on Mysql
Thank you so much Pascal. I will try now