Trigger for table optimization

Question

Trigger for table optimization

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I'm trying to create a trigger that runs the OPTIMIZE command of MySQL.

In short, I have a table A and I created a table B that will write a datetime where this value will be updated every time the table A receives the OPTIMIZE command.

Trigger would go

I have tried the following code, but MySQL has an error and I do not find the solution.

DELIMITER $$
CREATE TRIGGER optimizechattable AFTER UPDATE ON lz_chat_archive FOR EACH ROW
BEGIN
    DECLARE @qtd int = 0; 
    DECLARE @MAXDATE DATE; 
    DECLARE @DATAATUAL DATE;

    SET @qtd = ( SELECT count(c.time) as qtd FROM lz_chats_table_optimize as c );

    SET @MAXDATE = ( SELECT MAX(c.time) FROM lz_chats_table_optimize as c );
    SET @DATAATUAL = (SELECT GETDATE());

    IF(@quantidade <= 0)
        optimize table lz_chat_archive;
        /* break execution */
    END IF;

     /* adaptar para  , se diferença for maior que x horas, executar if*/
    IF (@MAXDATE > @DATAATUAL)

        UPDATE lz_chats_table_optimize as c SET c.time = @DATAATUAL where id = 1
        optimize table lz_chat_archive;

    END IF;
END $$
DELIMITER;

mysql trigger

asked by anonymous 19.05.2017 / 20:01

2 answers

Upload JPEG via JSON Error adding column with dynamical table in Jquery, with dblclick?

score 0 · Answer 1

Your problem is in line DECLARE @qtd int = 0; can not set value this way, need to use SET

Thus:

DECLARE @qtd int; 
DECLARE @MAXDATE DATE; 
DECLARE @DATAATUAL DATE;

SET @qtd = 0;

In addition, you are using @quantidade instead of @qtd on your if

score 0 · Answer 2

Good morning,

I solved the problem by creating a procedure (MySql does not accept the optimize commands in the trigger). And I created a trigger to call the procedure every time you update the table.

Next complete code:

BEGIN

    DECLARE qtd int(11);
    DECLARE lastatt DATETIME;
    DECLARE diffhour INT(11);

    SET qtd = (SELECT count(id) FROM lz_chats_table_optimize where id = 1 );

    if qtd <= 0 THEN

        SELECT "Inserindo dados na tabela de controle de atualização";
        INSERT INTO lz_chats_table_optimize ('time' ) VALUES ( NOW() );
        SELECT "Otimizando tabela alvo";
        OPTIMIZE TABLE lz_chat_archive;
        OPTIMIZE TABLE lz_chat_posts;
        OPTIMIZE TABLE lz_chat_requests;
        OPTIMIZE TABLE lz_chat_forwards;
        OPTIMIZE TABLE lz_visitor_browser_urls;
        OPTIMIZE TABLE lz_visitor_browsers;
        OPTIMIZE TABLE lz_event_triggers;
        OPTIMIZE TABLE lz_visitors;
        OPTIMIZE TABLE lz_visitor_chats;
    ELSE

        SET lastatt = (SELECT c.time FROM lz_chats_table_optimize as c where id = 1 );
        SET diffhour = ( SELECT HOUR(TIMEDIFF(NOW() , lastatt)) );  

        IF diffhour >= 1 THEN

            SELECT "Atualizando dados na tabela de controle de atualização";
            UPDATE lz_chats_table_optimize set 'time' = now() where id = 1;
            SELECT "Otimizando tabela alvo";
            OPTIMIZE TABLE lz_chat_archive;
            OPTIMIZE TABLE lz_chat_posts;
            OPTIMIZE TABLE lz_chat_requests;
            OPTIMIZE TABLE lz_chat_forwards;
            OPTIMIZE TABLE lz_visitor_browser_urls;
            OPTIMIZE TABLE lz_visitor_browsers;
            OPTIMIZE TABLE lz_event_triggers;
            OPTIMIZE TABLE lz_visitors;
            OPTIMIZE TABLE lz_visitor_chats;

        ELSE

            SELECT "Não há necessidade de otimizar a tabela";

        END IF;

    END IF;

END