Cron on Mysql

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
dgasparin
Veteran Member
Posts: 31
Joined: 2020-03-26 13:03

Cron on Mysql

Post by dgasparin » 2020-05-30 15:43

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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Cron on Mysql

Post by pbottcher » 2020-05-31 10:04

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
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.

dgasparin
Veteran Member
Posts: 31
Joined: 2020-03-26 13:03

Re: Cron on Mysql

Post by dgasparin » 2020-06-01 08:10

Thank you so much Pascal. I will try now

Post Reply