Queries broken when using mysql 8.0 database - reserved word 'groups'

[londi-afk]

Application version: 1.2.22 PHP version: 7.4.3 PHP SAPI: apache2handler HTTP Client: socket OS version: Linux 5.4.0-66-generic Database driver: mysql Database version: 8.0.28-0ubuntu0.20.04.3

If you use Kanboard with mysql version 8.x or higher and Group_assign plugin all queriers related to groups are broken since 'groups' is reserved word.

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

[londi-afk]

Adding `` to SELECT with .GroupMemberModel::TABLE. seems to resolve this issue.

[londi-afk]

Created #70

[creecros]

sounds good. I've been out of the game for awhile and no way to test, so excuse my ignorance, backwards compatible?

[londi-afk]

Cant test extensively on all versions, but works on 5.5.x with test table and using the quotes (although backticks is the correct expression it seems) in select:

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.5.58-0ubuntu0.14.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> describe groups;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select id from groups;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select id from `groups`;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

So Id assume this should work on all versions from 5.5.x up .

[creecros]

good enough for me. I'll pull next chance I get

[bmscmoreira]

I've changed the ".../plugins/Group_assign/Filter/TaskAllAssigneeFilter.php" file according to the above but still can't get the groups to pop up. Is it enough to change this file?

My SQL version is a bit higher though:

Versão da aplicação: 1.2.22 Versão PHP: 7.4.28 SAPI PHP: litespeed Cliente HTTP: cURL Versão SO: Linux 3.10.0-962.3.2.lve1.5.24.10.el7.x86_64 Driver da base de dados: mysql Versão base de dados: 10.3.34-MariaDB

[creecros]

is your server caching php? try a server restart.

[bmscmoreira]

is your server caching php? try a server restart.

Unfortunately can't do that since it's on shared hosting; but I gave it some time and heavily changed the file / deleted it / etc to fight the cache, but still seems not to be working... asignees work fine though. Will try and find some logs.

[bmscmoreira]

I only changed line 105 so I guess that may be the problem - will need to change all relevant lines of code if I understood correctly.

[creecros]

pretty sure I merged the PR. just use the master.

[bmscmoreira]

pretty sure I merged the PR. just use the master.

Thanks. Yes, the PR was merged, but still not working. It may be specific to my sql version (10.3.34-MariaDB). Will try to investigate.

[MrDaves24]

I had this issue too !

With MySQL 8.0.29 PHP 8.0.18

The commit adding the quote fixes the issue for me, but Kanboard only uses the last release, so a fresh install of the plugin trough their panel doesn't have the "patch" with the quotes.

[creecros]

latest version should be 1.7.12, and I just checked, panel will use latest version of plugin.

https://github.com/kanboard/website/blob/15d23ed362874f1e71a371e2f13f5efe69e8e7b1/plugins.json#L170