Mahara

New reserved keywords in mysql8 cause SQL errors

Bug #1797276 reported by Rebecca Blundell
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
Fix Released
High
Cecilia Vela Gurovic
Mahara 18.10.0

Bug Description

I'm running PHP7.2 on Ubuntu 16.04 with MYSQL8 in Firefox.

There's an SQL error for the groups report that occurs when running mysql. If you go to http://mahara/admin/users/statistics.php while running postgres, you see the reports page as expected. In mysql you get an error (Reports page in mysql.png - attached):

[WAR] a6 (lib/dml.php:517) Failed to get a recordset: mysqli error: [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
            FROM "group"
            WHERE deleted = 0
            GROUP ' at line 1] in EXECUTE("
            SELECT grouptype, COUNT(id) AS groups
            FROM "group"
            WHERE deleted = 0
            GROUP BY grouptype
            ORDER BY groups DESC")Command was:
            SELECT grouptype, COUNT(id) AS groups
            FROM "group"
            WHERE deleted = 0
            GROUP BY grouptype
            ORDER BY groups DESC

Revision history for this message
Rebecca Blundell (rjb-dev) wrote :
Cecilia Vela Gurovic (ceciliavg)
Changed in mahara:
status: New → In Progress
importance: Undecided → High
assignee: nobody → Cecilia Vela Gurovic (ceciliavg)
milestone: none → 18.10.0
Revision history for this message
Cecilia Vela Gurovic (ceciliavg) wrote :

we are using the word 'groups' as a column name for a sql query but it is a keyword since MySQL 8.0.2

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

it should be wrapped in ""

Revision history for this message
Mahara Bot (dev-mahara) wrote : A patch has been submitted for review

Patch for "master" branch: https://reviews.mahara.org/9215

Robert Lyon (robertl-9)
Changed in mahara:
milestone: 18.10.0 → 18.10rc2
Robert Lyon (robertl-9)
summary: - 18.10 RC2: Reports page causes error in mysql8
+ New reserved keywords in mysql8 cause SQL errors
Revision history for this message
Mahara Bot (dev-mahara) wrote :

Patch for "master" branch: https://reviews.mahara.org/9236

Revision history for this message
Mahara Bot (dev-mahara) wrote :

Reviewed: https://reviews.mahara.org/9215
Committed: https://git.mahara.org/mahara/mahara/commit/cd68ce01a2e6192dd4b25f02121e6947520a8561
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit cd68ce01a2e6192dd4b25f02121e6947520a8561
Author: Cecilia Vela Gurovic <email address hidden>
Date: Thu Oct 11 18:13:44 2018 +1300

Bug 1797276: keyword 'groups' needs changed in sql queries

groups is a keyword since MySQL 8.0.2

behatnotneeded

Change-Id: Ia63763f16f3c64dade7d5aff3ab1daf08092fc0c

Revision history for this message
Mahara Bot (dev-mahara) wrote :

Reviewed: https://reviews.mahara.org/9236
Committed: https://git.mahara.org/mahara/mahara/commit/06e3f6b88871668c4f1e79e26d19f2ed69bfe9fa
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit 06e3f6b88871668c4f1e79e26d19f2ed69bfe9fa
Author: Cecilia Vela Gurovic <email address hidden>
Date: Thu Oct 18 10:43:09 2018 +1300

Bug 1797276: escape admin reserved word in Mysql8

reserved keyword found needing to be fixed:
admin
was set to reserved in mysql 8.0.2 and then changed
back to unreserved in 8.0.12
we need this patch for the version in between them

behatnotneeded
Change-Id: Ife693b3d490ca5fd1112d0a55ff1ae74a9579aa5

Robert Lyon (robertl-9)
Changed in mahara:
status: In Progress → Fix Committed
milestone: 18.10rc2 → 18.10.0
Revision history for this message
Mahara Bot (dev-mahara) wrote :

Patch for "18.10_STABLE" branch: https://reviews.mahara.org/9249

Revision history for this message
Mahara Bot (dev-mahara) wrote : A change has been merged

Reviewed: https://reviews.mahara.org/9249
Committed: https://git.mahara.org/mahara/mahara/commit/16dec02dd92ba93efea77b81a9cf8610e559c94d
Submitter: Robert Lyon (<email address hidden>)
Branch: 18.10_STABLE

commit 16dec02dd92ba93efea77b81a9cf8610e559c94d
Author: Cecilia Vela Gurovic <email address hidden>
Date: Thu Oct 11 18:13:44 2018 +1300

Bug 1797276: keyword 'groups' needs changed in sql queries

groups is a keyword since MySQL 8.0.2

behatnotneeded

Change-Id: Ia63763f16f3c64dade7d5aff3ab1daf08092fc0c
(cherry picked from commit cd68ce01a2e6192dd4b25f02121e6947520a8561)

Revision history for this message
Mahara Bot (dev-mahara) wrote : A patch has been submitted for review

Patch for "18.10_STABLE" branch: https://reviews.mahara.org/9250

Revision history for this message
Mahara Bot (dev-mahara) wrote : A change has been merged

Reviewed: https://reviews.mahara.org/9250
Committed: https://git.mahara.org/mahara/mahara/commit/de470bec99813807fafefe389f97d6c9e0744e6b
Submitter: Robert Lyon (<email address hidden>)
Branch: 18.10_STABLE

commit de470bec99813807fafefe389f97d6c9e0744e6b
Author: Cecilia Vela Gurovic <email address hidden>
Date: Thu Oct 18 10:43:09 2018 +1300

Bug 1797276: escape admin reserved word in Mysql8

reserved keyword found needing to be fixed:
admin
was set to reserved in mysql 8.0.2 and then changed
back to unreserved in 8.0.12
we need this patch for the version in between them

behatnotneeded
Change-Id: Ife693b3d490ca5fd1112d0a55ff1ae74a9579aa5
(cherry picked from commit 06e3f6b88871668c4f1e79e26d19f2ed69bfe9fa)

Cecilia Vela Gurovic (ceciliavg)
Changed in mahara:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.