Trigger for table optimization

Asked

Viewed 99 times

0

I’m trying to create a trigger which rotates the control OPTIMIZE mysql.

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

Trigger would

I tried the following code, but Mysql error and I can’t 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 database trigger

  • Which error Mysql accuses?

  • Good afternoon , Mysql returns this error: #1064 - You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'Qtd int = 0; DECLARE MAXDATE DATE; DECLARE CURRENT DATE; ' at line 3

2 answers

0

Your problem is on the line DECLARE @qtd int = 0; cannot set the value this way, you need to use the SET

Thus:

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

SET @qtd = 0;

In addition, it is using @quantidade in place of @qtd in his if

  • Good evening, as the variable was the broker who completed auto for hehe quantity. I removed the DECLARE , the error changed. 1064 - You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'optimize table lz_chat_archive; /* break Execution */ END IF; ' at line 10

  • See this link how to declare a condition

0

Good morning ,

I solved the problem by creating a precedent (Mysql does not accept commands optimize at Trigger). And I created a Trigger to call the Procedure every time you update the table.

It follows 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

Browser other questions tagged mysql database trigger

You are not signed in. Login or sign up in order to post.