MySQL / MariaDB working only sometimes
MariaDB 5.2 is really old.  Upgrade.
MariaDB 10.2 (and MySQL 8.0) have RANK and other &quot;windowing functions&quot; to assist with your query.
Setting @variables inside SELECTs should not be trusted.  Rewrite the query to avoid them.
Updating everyone's rank all the time will be a performance drag when you have a lot of users.