Help to MySQL Gurus...

#1 · August 24, 2022, 7:30 pm

Hello everyone

I need to do a cron job to check the value of a mysql table recordset at a regular time interval.

Doing tests, I try the following statement:

CREATE EVENT bb
ON SCHEDULE EVERY 10 seconds
DO
SELECT count(*) FROM `jobs`

and the answer is:

MySQL has returned an empty set of values (ie: zero columns). (The query took 0.0026 seconds.)

when I am totally sure that the table has 40 records

Can someone tell me why it doesn't return the value 40?

If I run directly the statement:

Select count(*) from `jobs`

Returns 40.

Any ideas?

Thanks in advance,
David from Argentina

#2 · August 25, 2022, 9:32 am

Hello David,

I don't have the possibility to test on the moment, but looking at your statement I'm almost sure the mistake is in the "seconds".

In My opinion it should be:

ON SCHEDULE EVERY 10 SECOND

seconds isn't a defined interval quantity.

Hopefully this helps.

Best regards,

Eric

#3 · August 25, 2022, 7:29 pm

Thanks @ebear,

ON SCHEDULE EVERY 10 SECOND

is the correct syntax.

(Google was changed words when i use the translate service...)

The statement works fine under PHPMyAdmin webpage ... but does not returns the correct value....

very odd....

#4 · August 26, 2022, 8:56 am

Hello David,

You are correct, I tried on one of my databases and have the same issue. Don't have time on the moment to have a deeper look into this.

If you find out what causes this issue quicker then I please let me know. Interesting.

Best regards,

Eric

#5 · August 26, 2022, 6:59 pm

Hi @daviddeargentina , @ebear

Mysql EVENTS limitations:

Events cannot return a result set. The output is directed to dev/null, and the event either fails or succeeds without notifying the user.

Un saludo

#6 · August 26, 2022, 10:49 pm

@emmanuel-fernandez,

Bingo!

This is the explanation for the why...

While I have made a PHP/javascript process that reads every few seconds a mysql table to see if there is anything new in the table and sends the new data to another process... it works.

It's very inelegant and unprofessional... but it works.

The problem is that every time I do a read from the MySQL table, I have to do a huge amount of submissions to the MySQL server, consuming server resources.

I can't find the way around it to do something more consistent.

If someone comes up with something better, I read all the ideas...

Infinite thanks!

------*********--------

Si bien tengo hecho un proceso PHP / javascript que lee cada pocos segundosuna tabla mysql para ver si hay novedades en la tabla y envía los nuevos datos a otro proceso... funciona.

Es muy poco elegante y profesional... pero funciona.

El problema es que cada vez que hago una lectura de la tabla MySQL, tengo que hacer una enorme cantidad de presentaciones en el servidor MySQL, con el consiguiente consumo de recursos del servidor.

No le encuentro la vuelta para hacer algo mas consistente.

Si a alguien se le ocurre algo mejor, leo todas las ideas...

Infinitas gracias!

David de Argentina

#7 · August 26, 2022, 11:50 pm

@daviddeargentina

While I have made a PHP/javascript process that reads every few seconds a mysql table to see if there is anything new in the table and sends the new data to another process... it works.
It's very inelegant and unprofessional... but it works.

Can you tell us what the 'other process' does with the (COUNT) information ?

The problem is that every time I do a read from the MySQL table, I have to do a huge amount of submissions to the MySQL server, consuming server resources.
I can't find the way around it to do something more consistent.
If someone comes up with something better, I read all the ideas...

You might consider having a separate Table (with a timestamp and a count field) where the scheduled event updates the count (so just one row of data) ... and have the other process read the information from there.

This page has some good info on Events handling in MySQL ... https://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

#8 · August 27, 2022, 1:44 am

Quote from DaviddeArgentina on August 27, 2022, 1:44 am
thanks @gaev, it was very useful for me.

I tell you the scenario of the project:

1) There are several users who activate a program (in practice, through an APP for cell phones) that sends a series of data to a MySQL table.

2) I do this through a PHP program that saves the data of interest for each user (user id, date/time, message, etc)

3) There is a program made in PHP/Javascript that reads the current value of the record ID from the same table mentioned in 1)

4) This PHP/Javascript program is executed continuously every 5 to 10 seconds, to check if there are news from users who have entered some type of message in that time interval, forcing itself to "reread" itself every fraction of time .

5) Knowing the current value of the ID, I can know if this last value coincides with the last reported reading of the new user registrations. If this is different from the old ID, then I have to retrieve the data from the new records, which is done with a separate php.

6) Once I already have the new data updated, another php updates the screen view of the main program, which only has access to be seen by some system operators.

In practice, all this is working.

When I bring up the system, I get a message from the server saying that I am using too many resources accessing the MySQL database.

as @emmanuel-fernandez says, the events do not return a recordset, that is, you have to make some trap to execute the SELECT process to obtain the last registered id

----------- /// -----------------------

gracias @gaev, me resultó de mucha utilidad.

Les cuento el escenario del proyecto:

1) Hay varios usuarios que activan un programa (en la práctica, a través de una APP para celulares) que envía una serie de datos a una tabla MySQL.

2) Esto lo hago a través de un programa PHP que guarda los datos de interés de cada usuario (id de usuario, fecha/hora, mensaje, etc)

3) Hay un programa hecho en PHP/Javascript que lee el valor actual del ID de registro de la misma tabla mencionada en 1)

4) Este programa PHP/Javascript se ejecuta de manera continua cada 5 a 10 segundos, para verificar si hay novedades de usuarios que hayan ingresado algún tipo de mensaje en ese intervalo de tiempo, obligando a "releerse" a si mismo cada fraccion de tiempo.

5) Sabiendo el valor actual del ID, puedo saber si este ultimo valor coincide con la última lectura informada de los nuevos registros de los usuarios. Si esto es distinto al ID anterior, entonces, tengo que recuperar los datos de los registros nuevos, que esto se hace con un php independiente.

6) Una vez que ya tengo los nuevos datos actualizados, otro php actualiza la vista de pantalla del programa principal, que solo tiene acceso a ser visto por algunos operadores del sistema.

En la práctica, todo esto está funcionando.

Cuando pongo en actividad el sistema, recibo un mensaje del servidor diciendo que estoy usando demasiado recursos accediendo a la base de datos MySQL.

como @emmanuel-fernandez dice, los eventos no devuelven un recordset, es decir, hay que fabricar alguna trampa para ejecutar el proceso de SELECT para obtener el último id registrado

thanks @gaev, it was very useful for me.

I tell you the scenario of the project:

1) There are several users who activate a program (in practice, through an APP for cell phones) that sends a series of data to a MySQL table.

2) I do this through a PHP program that saves the data of interest for each user (user id, date/time, message, etc)

3) There is a program made in PHP/Javascript that reads the current value of the record ID from the same table mentioned in 1)

4) This PHP/Javascript program is executed continuously every 5 to 10 seconds, to check if there are news from users who have entered some type of message in that time interval, forcing itself to "reread" itself every fraction of time .

5) Knowing the current value of the ID, I can know if this last value coincides with the last reported reading of the new user registrations. If this is different from the old ID, then I have to retrieve the data from the new records, which is done with a separate php.

6) Once I already have the new data updated, another php updates the screen view of the main program, which only has access to be seen by some system operators.

In practice, all this is working.

When I bring up the system, I get a message from the server saying that I am using too many resources accessing the MySQL database.

as @emmanuel-fernandez says, the events do not return a recordset, that is, you have to make some trap to execute the SELECT process to obtain the last registered id

----------- /// -----------------------

gracias @gaev, me resultó de mucha utilidad.

Les cuento el escenario del proyecto:

1) Hay varios usuarios que activan un programa (en la práctica, a través de una APP para celulares) que envía una serie de datos a una tabla MySQL.

2) Esto lo hago a través de un programa PHP que guarda los datos de interés de cada usuario (id de usuario, fecha/hora, mensaje, etc)

3) Hay un programa hecho en PHP/Javascript que lee el valor actual del ID de registro de la misma tabla mencionada en 1)

4) Este programa PHP/Javascript se ejecuta de manera continua cada 5 a 10 segundos, para verificar si hay novedades de usuarios que hayan ingresado algún tipo de mensaje en ese intervalo de tiempo, obligando a "releerse" a si mismo cada fraccion de tiempo.

5) Sabiendo el valor actual del ID, puedo saber si este ultimo valor coincide con la última lectura informada de los nuevos registros de los usuarios. Si esto es distinto al ID anterior, entonces, tengo que recuperar los datos de los registros nuevos, que esto se hace con un php independiente.

6) Una vez que ya tengo los nuevos datos actualizados, otro php actualiza la vista de pantalla del programa principal, que solo tiene acceso a ser visto por algunos operadores del sistema.

En la práctica, todo esto está funcionando.

Cuando pongo en actividad el sistema, recibo un mensaje del servidor diciendo que estoy usando demasiado recursos accediendo a la base de datos MySQL.

como @emmanuel-fernandez dice, los eventos no devuelven un recordset, es decir, hay que fabricar alguna trampa para ejecutar el proceso de SELECT para obtener el último id registrado

#9 · August 27, 2022, 8:42 am

Hi @daviddeargentina ,

Prueba a guardar el resultado en una variable local

CREATE EVENT mievento

ON SCHEDULE EVERY 10 second

DO

set @cuenta=0

SELECT count(*) INTO @cuenta FROM `jobs`

Select @cuenta

danito has reacted to this post.

#10 · August 27, 2022, 3:50 pm

@emmanuel-fernandez;

Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful ! Beautiful !

Help to MySQL Gurus... - Forum

Help to MySQL Gurus...