adam/Piwigo
1
0 Fork 0
mirror of https://github.com/Piwigo/Piwigo.git synced 2026-11-03 07:22:22 +01:00
Code Issues 657 Packages Projects Releases Wiki Activity

mysql 8 groups is a reserved keyword #954

New Issue
Closed
opened 2026-10-05 00:25:22 +02:00 by adam · 10 comments
adam commented 2026-10-05 00:25:22 +02:00
Owner
Copy link
Copy source

When accessing User config page in admin i get a sql error

I tried to create a user, then looked at XHR request, it dumps a SQL statement, this is a issue also because it reveals SQL structure. Debug options should be hidden in production.

SELECT
user_id,
GROUP_CONCAT(name ORDER BY name SEPARATOR ", ") AS groups
  FROM piwigo_user_group
JOIN `piwigo_groups` ON id = group_id
  WHERE user_id IN (1,2,3)
  GROUP BY user_id

AS groups => groups is a reserved keyword in mysql 8 query fails

https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-G

GROUPS (R); added in 8.0.2 (reserved)

image

EDIT: updated the SQL query forgot to correct as it is in piwigo files (group => groups)

When accessing User config page in admin i get a sql error I tried to create a user, then looked at XHR request, it dumps a SQL statement, this is a issue also because it reveals SQL structure. Debug options should be hidden in production. SELECT user_id, GROUP_CONCAT(name ORDER BY name SEPARATOR ", ") AS groups FROM piwigo_user_group JOIN `piwigo_groups` ON id = group_id WHERE user_id IN (1,2,3) GROUP BY user_id ```AS groups``` => groups is a reserved keyword in mysql 8 query fails https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-G GROUPS (R); added in 8.0.2 (reserved) ![image](https://user-images.githubusercontent.com/6019313/66714553-39bd8d80-edb8-11e9-9f04-96969c210764.png) EDIT: updated the SQL query forgot to correct as it is in piwigo files (group => groups) <!-- github-sync-id:506307090 -->
adam closed this issue 2026-10-05 00:25:22 +02:00
adam commented 2026-10-05 00:25:23 +02:00
Author
Owner
Copy link
Copy source

simillar issue here https://github.com/Piwigo/Piwigo/issues/919

simillar issue here https://github.com/Piwigo/Piwigo/issues/919 <!-- github-comment-id:541409459 -->
adam commented 2026-10-05 00:25:23 +02:00
Author
Owner
Copy link
Copy source

Hi @Kwaadpepper

How can you have AS group ?!? It's AS groups in admin/user_list_backend.php (not changed in the last 6 years).

Hi @Kwaadpepper How can you have `AS group` ?!? It's `AS groups` in `admin/user_list_backend.php` (not changed in the last 6 years). <!-- github-comment-id:541652624 -->
adam commented 2026-10-05 00:25:24 +02:00
Author
Owner
Copy link
Copy source

Sorry i played with the query but still changing AS group to AS groups there is the same issue
changing to something random like AS groupss and it works

GROUPS (R); added in 8.0.2 (reserved)

image
image

Sorry i played with the query but still changing ```AS group``` to ```AS groups``` there is the same issue changing to something random like ```AS groupss``` and it works GROUPS (R); added in 8.0.2 (reserved) ![image](https://user-images.githubusercontent.com/6019313/66775187-bc7f3f00-eec3-11e9-9f85-c43a3d044cba.png) ![image](https://user-images.githubusercontent.com/6019313/66775269-e8022980-eec3-11e9-9aa1-61b42301867e.png) <!-- github-comment-id:541853288 -->
adam commented 2026-10-05 00:25:25 +02:00
Author
Owner
Copy link
Copy source

Sorry i played with the query but still changing AS group to AS groups there is the same issue
changing to something random like AS groupss and it works

GROUPS (R); added in 8.0.2 (reserved)

image
image

I do agree with Kwaadpepper. I have the same behaviour on my environment. I changed the line 204 from "AS groups" to "AS groupss" of file "admin/user_list_backend.php" and also the line 213 from :

$groups_of_user[ $row['user_id'] ] = $row['groups'];

To :

$groups_of_user[ $row['user_id'] ] = $row['groupss'];

And it now works as a charm :) .

> Sorry i played with the query but still changing `AS group` to `AS groups` there is the same issue > changing to something random like `AS groupss` and it works > > ``` > GROUPS (R); added in 8.0.2 (reserved) > ``` > > ![image](https://user-images.githubusercontent.com/6019313/66775187-bc7f3f00-eec3-11e9-9f85-c43a3d044cba.png) > ![image](https://user-images.githubusercontent.com/6019313/66775269-e8022980-eec3-11e9-9aa1-61b42301867e.png) I do agree with Kwaadpepper. I have the same behaviour on my environment. I changed the line 204 from "AS groups" to "AS groupss" of file "admin/user_list_backend.php" and also the line 213 from : `$groups_of_user[ $row['user_id'] ] = $row['groups'];` To : `$groups_of_user[ $row['user_id'] ] = $row['groupss'];` And it now works as a charm :) . <!-- github-comment-id:542695444 -->
adam commented 2026-10-05 00:25:26 +02:00
Author
Owner
Copy link
Copy source

I think as it is said on the linked issue, adding quotes may be the solution, or just change alias as you say.

I think as it is said on the linked issue, adding quotes may be the solution, or just change alias as you say. <!-- github-comment-id:542829313 -->
adam commented 2026-10-05 00:25:26 +02:00
Author
Owner
Copy link
Copy source

I had that issue as well after updating to Ubuntu 19.10/MySQL 8.

In my local version, I just added backticks to quote the reserved keyword,that's enough.
Now the query looks like that:

 $query = '
SELECT
    user_id,
    GROUP_CONCAT(name ORDER BY name SEPARATOR ", ") AS `groups`
  FROM '.USER_GROUP_TABLE.'
    JOIN `'.GROUPS_TABLE.'` ON id = group_id
  WHERE user_id IN ('.implode(',', $user_ids).')
  GROUP BY user_id
;';
I had that issue as well after updating to Ubuntu 19.10/MySQL 8. In my local version, I just added backticks to quote the reserved keyword,that's enough. Now the query looks like that: ``` $query = ' SELECT user_id, GROUP_CONCAT(name ORDER BY name SEPARATOR ", ") AS `groups` FROM '.USER_GROUP_TABLE.' JOIN `'.GROUPS_TABLE.'` ON id = group_id WHERE user_id IN ('.implode(',', $user_ids).') GROUP BY user_id ;'; ``` <!-- github-comment-id:549267094 -->
adam commented 2026-10-05 00:25:27 +02:00
Author
Owner
Copy link
Copy source

Just added a pull request with my proposed fix, hope that helps...

Just added a pull request with my proposed fix, hope that helps... <!-- github-comment-id:550288231 -->
adam commented 2026-10-05 00:25:28 +02:00
Author
Owner
Copy link
Copy source

I fixed it in my environment like described in https://github.com/Piwigo/AMenuManager/issues/5#issuecomment-551291566

I fixed it in my environment like described in https://github.com/Piwigo/AMenuManager/issues/5#issuecomment-551291566 <!-- github-comment-id:551298629 -->
adam commented 2026-10-05 00:25:29 +02:00
Author
Owner
Copy link
Copy source

As for me as a workaround, I installed a lower mysql version to use with docker and using another port (docker is doing the forwarding) on a debian server.

As for me as a workaround, I installed a lower mysql version to use with docker and using another port (docker is doing the forwarding) on a debian server. <!-- github-comment-id:551396660 -->
adam commented 2026-10-05 00:25:30 +02:00
Author
Owner
Copy link
Copy source

@plegall Did you find some time to review my pull request #1117 ?

A quite simple and straight forward solution to improve compatibility with newer MySQL/MariaDB versions.

@plegall Did you find some time to review my pull request #1117 ? A quite simple and straight forward solution to improve compatibility with newer MySQL/MariaDB versions. <!-- github-comment-id:555115545 -->
Sign in to join this conversation.
No Branch/Tag Specified
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: adam/Piwigo#954