phpmyadminmariadbmariadb-10.1

phpmyadmin Event syntax error while it works on the sql command

DELETE FROM Ranking; ALTER TABLE Ranking AUTO_INCREMENT = 1; INSERT INTO Ranking (username) select username from Players order by rank desc LIMIT 100;

This is the command i want to execute every 10 Minutes, i have a table which should constantly store the top 100 players. And it works fine if i input the command into the sql command line. But as soon as i want to use the same command to be executed in an event this error shows up.

enter image description here

maybe i am not understanding the limitations of events can someone tell me what i am doing wrong? I am sadly using a Server which runs Maria Db. 10.1.48 so i cant use the new Windows functions like Rank()

Solution

  • It looks like you are defining an event with three SQL statements, but without using a BEGIN ... END block around them. You need to use BEGIN and END if your event has multiple statements.

    So your event definition should be:

    CREATE EVENT ...
DO BEGIN
  DELETE FROM Ranking; 
  ALTER TABLE Ranking AUTO_INCREMENT = 1; 
  INSERT INTO Ranking (username) select username from Players order by rank desc LIMIT 100;
END

    I suggest reading: https://mariadb.com/kb/en/begin-end

    I will also comment that you can replace the first two statements with TRUNCATE TABLE Ranking; which will delete all rows and reset the auto-increment more quickly than using DELETE. If you only have 100 rows in the table, maybe you won't notice the difference, but if the table becomes larger you probably will.