0

I used to have this sql statement working with mysql 5 (I think ...):

SQL:

SELECT users.id, users.account_id, 
        ( SELECT GROUP_CONCAT( 
                    DISTINCT users_groups.group_id 
                    ORDER BY users_groups.group_id ASC SEPARATOR ',' 
                ) AS groups 
        FROM users_groups 
        WHERE users_groups.user_id = ? 
        ), 
        ( SELECT GROUP_CONCAT( 
                    DISTINCT users_limits.limit_id 
                    ORDER BY users_limits.limit_id ASC 
                    SEPARATOR ',' 
                    ) AS limits 
        FROM users_limits 
        WHERE users_limits.user_id = ? 
        ) 
FROM users 
    LEFT JOIN users_groups ON users_groups.user_id = users.id 
WHERE users_groups.user_id = ? 
AND users_groups.group_id = ?;

I upgraded my server to mysql 8 now but I'm getting this error:

code: 'ER_PARSE_ERROR',
errno: 1064,
sqlState: '42000',
sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups FROM users_groups WHERE users_groups.user_id = 1 ), ( SELECT GROUP_CONCAT' at line 1",

Mysql-Version:

$ mysql -V
mysql  Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))

What am I missing and what do I need to change?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Philipp M
  • 3,306
  • 5
  • 36
  • 90
  • 2
    `groups` was added as a new reserved keyword in MySQL 8.0.2. Either change the column alias, or delimit the keyword in back-ticks. – Bill Karwin Apr 15 '23 at 15:51
  • You should always review the list of [new reserved keywords](https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-new-in-current-series) when you upgrade. – Bill Karwin Apr 15 '23 at 15:53
  • @BillKarwin Great thanks! With back-ticks its already working again! And thanks for the hint, I'll keep that in mind! – Philipp M Apr 15 '23 at 16:09

0 Answers0