Nick2020-09-18 16:26:09
Nick, 2020-09-18 16:26:09

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

it was mysql 5.7 and everything was
updated to Server version: 8.0.2
and it turned out:
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 the question

In order to leave comments, you need to log in

2 answer(s)
Rsa97, 2020-09-18

Version 8.0.2 added the reserved word RANK.
If this word is used as the name of a table or field, then it must be enclosed in back quotes `rank`.

Dmitry Gamzin, 2020-09-18

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???

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question