After updating mysql, the query became incorrect, how to fix it?

Question

MySQL

it was mysql 5.7 and everything was
updated to Server version: 8.0.2
and it turned out:
SELECT rank FROM (
SELECT s.*, @rank := @rank + 1 rank FROM (
SELECT l.user_id, l.meta_value AS Balance FROM wp_usermeta l

WHERE l.meta_key = 'mycred_default'
AND l.meta_value != 0
) s, (SELECT @rank := 0) init
ORDER BY Balance+0 DESC, s.user_id ASC
) r
WHERE user_id = 1

You have an error in your query. Check the documentation for your version of MySQL for the correct syntax around 'FROM (
SELECT s.*, @rank := @rank + 1 rank FROM (
SELECT l.user' on line 1

How can I fix or rewrite the query?

Answer 1

Version 8.0.2 added the reserved word RANK.
https://dev.mysql.com/doc/refman/8.0/en/keywords.html
If this word is used as the name of a table or field, then it must be enclosed in back quotes `rank`.

Answer 2

I would completely rewrite the selection structure through JOIN and keys.
And generally, where structure of the table? What error does MySql show in the output or what is the difference between the answers???