Evento Mysql / insert SELECT

Muy Buenas a todos!


Os comento mi problema.

Me gustaría crear un evento diario que utilizaria para hacer un "insert into summarized_table () SELECT [...]". El evento se ejecutaría todos los dias a las 10:00am.

- Esta es la tabla sumarizada donde insertaria los datos agrupados:

create table attemptsub_carrierday_sum (
id int(11) not null AUTO_INCREMENT,
time date not null,
country int(11) not null,
id_carrier int(11) not null,
attempts int(11) not null,
optins int(11) not null,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 


- Y este es el insert que utilizaría para el evento:

INSERT INTO attemptsub_carrierday_sum (time, country, id_carrier, attempts, optins) select DATE(att.created_at) as time , co.id as country , ca.id_carrier , count(att.id) as attempts,
sum(case when att.api_code = 100 AND att.api_wasactive = 1 AND api_sending_short_code > 0 then 1 else 0 end) as optins
from attemptsub as att
LEFT JOIN carrier as ca ON att.id_carrier= ca.id_carrier
LEFT JOIN country as co ON ca.country_id = co.id
where created_at between curdate() - interval 2 day and curdate() - interval 1 day
GROUP BY country, id_carrier, DATE(att.created_at)
ORDER BY time desc, country, id_carrier


My problema es que nunca he hecho un evento en MySQL ( he hecho realizado algun job en oracle pero mysql es diferente!) y apreciaria mucho mucho vuestra ayuda.

Si necesitais mas información estare encantado de ayudar.

Muchas gracias de antemano

 

Hola Mario, te copio debajo el tutorial que Alfonso Blanco Criado ha publicado en la web adictosaltrabajo.com, está muy bien explicado y no creo que tengas ningún problema para implementarlo.

Además, en el blog 'Otro DBA MySQL', en el post Uso del planificador de eventos de MySQL lo explican en detalle con muchos ejemplos, y utilizando procedimientos almacenados para probar los eventos, que seguramente es como terminarás haciéndolo si implementas eventos. 

Saludos,


Eventos en MySQL

1. Introducción

A partir de la versión 5.1, MySQL añade un nuevo concepto, los eventos. Estos no son más que la ejecución planificada de ciertas sentencias que pueden ser llamadas a procedimientos almacenados o simples conjuntos de sentencias SQL. La ventaja de estos eventos es que se pueden configurar para planificarlos, es decir, determinar cuando se tienen que ejecutar, que intervalo de repetición tienen, etc.

Esto aporta una gran funcionalidad a las bases de datos MySQL ya que con la aparición de estos eventos se van a poder "simular" vistas materializadas. Estas vistas materializadas solo están disponibles en las bases de datos Oracle y en ese aspecto MySQL se estaba quedando atrás porque hasta la aparición de los eventos no se podía usar el concepto de "resetear una tabla según una condición temporal". Lo que se hacía antes en las base de datos MySQL era usar un cron (Unix) para lanzar ciertas ejecuciones que simulaban las vistas materializadas pero era un poco "chapuza". Ahora con la llegada de los eventos es mucho más fácil ya que para simular una vista materializada solo tenemos que crear un evento, establecerle cuando se quiere que se ejecute e implementar un procedimiento almacenado que se encarge de borrar y rellenar una tabla (transaccionalmente).

En este tutorial vamos a explicar como se puede crear eventos en MySQL y explicaremos como planificarlos temporalmente con un sencillo ejemplo práctico.

2. Entorno

  • Hardware: Portátil MacBook Pro 15' (2.2 GHz Intel Core i7, 8GB DDR3 SDRAM, 500GB HDD).

  • Sistema Operativo: Mac OS X Lion 10.7.5Software: 5.5.25a MySQL Community Server

3. Planificador / Scheduler

Antes de empezar a crear eventos, necesitamos saber si el planificador o scheduler de MySQL está arrancado. Para ello tendremos que ver el fichero de configuración de MySQL (my.cnf en Unix o my.ini en Window) y ver el valor de la variable event_scheduler. Estos valores pueden ser:

  • 0 (por defecto): estado del planificador parado (OFF).

  • 1: estado del planificador arrancado (ON).

  • DISABLED: estado del planificador deshabilitado.

Para poder modificar el estado del planificador/scheduler podemos o bien modificar el fichero de configuración de MySQL y rearrancar la base de datos o bien modificar el valor de la variable event_scheduler ejecutando lo siguiente:


SET GLOBAL event_scheduler=ON;

Una vez que tengamos arrancado el planificador/scheduler podemos comprobarlo ejecuando


SHOW PROCESSLIST;

Entonces veremos como hay un nuevo proceso llamado evento_scheduler.


4. Creación de un evento

Lo primero que vamos a ver es la sintaxis para la creación de un evento y como se puede ver se puede dividir en cuatro bloques: configuración, planificación, configuración del intervalo o frecuencia de ejecución y sentencias SQL a ejecutar.

CREATE

    [DEFINER = { user | CURRENT_USER }]

    EVENT

    [IF NOT EXISTS]

    event_name

    ON SCHEDULE schedule

    [ON COMPLETION [NOT] PRESERVE]

    [ENABLE | DISABLE | DISABLE ON SLAVE]

    [COMMENT 'comment']

    DO event_body;

 

schedule:

    AT timestamp [+ INTERVAL interval] ...

  | EVERY interval

    [STARTS timestamp [+ INTERVAL interval] ...]

    [ENDS timestamp [+ INTERVAL interval] ...]

 

interval:

    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Algunas peculiaridades a la hora de la creación del evento es que su nombre no puede tener más de 64 caracteres y que no distinge mayúsculas de minúsculas (non case sensitive). Además comentar que el evento se vincula con un determinado esquema (por ejemplo: schema_name.event_name), asi que si no se especifica ninguno en la creación del evento se tomará el esquema actual.

Dentro de la planificación de ejecución del evento, AT se usará cuando se quiere ejecutar solo una vez.

Para esteblecer un intervalo de ejecución de un determinado evento se deberá utilizar EVERY.

Ahora vamos a crear un evento para que ejecute una sencilla sentencia en un determinado momento y que se repita cada hora.


CREATE EVENT

    eventosMysqlTutorial

    ON SCHEDULE EVERY 1 HOUR STARTS '2012-10-04 23:59:00'

    COMMENT 'Ha sido facil, ¿verdad?'

    DO SELECT NOW() FROM DUAL;


Una vez creado el evento podemos verlo ejecutanto lo siguiente:

1

SHOW CREATE EVENT eventosMysqlTutorial\G;

5. Conclusiones

Como se ha podido ver el concepto de evento es muy intuitivo y su manejo es realmente sencillo. A veces en este mundo de la informática no es cuestión de saber o no hacer una cosa, sino de tener una visión más amplia e invertir tiempo en I+D para encontrar las mejores soluciones para un determinado problema.

Espero que este tutorial os haya sido de ayuda. Un saludo.

Alfonso Blanco Criado
ablanco@autentia.com