MySQL8 syntax error - reserved word 'lock'
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Mahara |
Fix Released
|
High
|
Doris Tam |
Bug Description
Release testing 21.10:
master
MySQL 8
Firefox
Maroon theme
PHP 7.4
To test:
1. Create an institution template collection (set to 'Current auto-copied template') with portfolio completion.
2. Add 1 review block, type a review statement and set 'Lock portfolio' to 'Yes'.
3. Create a new account and run the cron so that the portfolio is created.
4. Share the portfolio with a person who can do a review.
5. Log in as the reviewer and approve the review statement.
Expected: box should be able to be checked and portfolio locked
Actual: Does not allow you to check the box. On page reload, sql syntax error appears.
On investigation, the problem was found to be the name of the column in the collection table 'lock' - this is a reserved word in MySQL8. When I changed the column name to 'locked' this fixed the problem.
Carrying on through the test scenario:
6. As portfolio author, check that the portfolio is locked. You can't delete the portfolio.
7. Wait for 6 months ;-) or rather manipulate the locking date.
8. Manually run the locking cron job with `UPDATE cron SET nextrun = null WHERE callfunction = 'unlock_
Expected result: portfolio is unlocked
Actual result: sql error (probably also to do with the 'lock' column name.
Extra: helpful query to manipulate the rolloverdate so we don't have to wait 6 months
-- move the rolloverdate forward 10 months back so that today, more than 6 months have past
UPDATE collection_template
SET rolloverdate = DATE_ADD(
WHERE collection=
description: | updated |
description: | updated |
Changed in mahara: | |
status: | New → Confirmed |
importance: | Undecided → High |
milestone: | none → 21.10rc2 |
assignee: | nobody → Doris Tam (doristam) |
Changed in mahara: | |
status: | Confirmed → In Progress |
description: | updated |
Changed in mahara: | |
milestone: | 21.10rc2 → 21.10.0 |
status: | In Progress → Fix Committed |
Changed in mahara: | |
status: | Fix Committed → Fix Released |
I'm getting the same error when I am testing this bug: https:/ /bugs.launchpad .net/mahara/ +bug/1928920
It uses the same queries using the reserved word 'lock' when generating a report for 'portfolios with reviewers'