How to make MySQL 8 compatible with statements containing multiple FROM tables

Question

I am migrating from MySQL 5.7 to 8.0 and one of my applications is throwing errors on existing SQL queries like this:

SELECT
       g.*,
       gp.file_name AS group_picture_fn
FROM groups g,
     group_picture gp
WHERE
      g.status = 3
  AND g.ID = gp.group_id
ORDER BY
    timestamp desc LIMIT 4

Error:

[42000][1064] 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 g, group_picture gp WHERE g.status = 3 AND g.ID = gp.group_i' at line 2

The query runs OK on the old system. Is there anything I can change on the MySQL config / SQL Mode to make it compatible or do I have to rewrite the queries in a JOIN like syntax?

`groups` is a [reserved word](https://dev.mysql.com/doc/refman/8.0/en/keywords.html) you have to use backticks to escape it or better rename the table — Jens, May 24 '21 at 16:32

Ralph Ritoch · Accepted Answer · 2021-05-24T16:39:39.753

The keyword GROUPS was added in version 8.0.2

See: https://dev.mysql.com/doc/refman/8.0/en/keywords.html

The problem is that you need to escape your table names and aliases so they won't be treated as keywords.

SELECT
       `g`.*,
       `gp`.`file_name` AS `group_picture_fn`
FROM `groups` `g`,
     `group_picture` `gp`
WHERE
      `g`.`status` = 3
  AND `g`.`ID` = `gp`.`group_id`
ORDER BY
    `timestamp` desc LIMIT 4

How to make MySQL 8 compatible with statements containing multiple FROM tables

1 Answers1