1

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()

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Samuel Rogers
  • 35
  • 3

1 Answers1

0

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks a lot, yeah i am aware of Truncate beeing better but apparently its only better when the Table is big enough and this Table will constantly stay at 100. But i am not sure if thats true , just what i read – Samuel Rogers Jul 20 '21 at 17:16