Mahara

SQL vs MySQL on group table update

Bug #1967811 reported by Gold
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
Fix Released
High
Unassigned
Mahara 22.04.0
20.10
Fix Released
High
Unassigned
Mahara 20.10.5
21.04
Fix Released
High
Unassigned
Mahara 21.04.4
21.10
Fix Released
High
Unassigned
Mahara 21.10.2
22.04
Fix Released
High
Unassigned
Mahara 22.04.0

Bug Description

I was switching DBs for ES7 testing on a smaller dataset. The smaller db is on MySQL. During the site upgrade I got this:
```
[DBG] 66 (lib/dml.php:180) mysqli error: [1093: You can't specify target table 'group' for update in FROM clause] in EXECUTE("
            UPDATE "group" SET submittableto = 1 WHERE id IN (
                SELECT g1.id FROM "lti_assessment" l
                JOIN "group" g1 ON g1.id = l.group
                WHERE g1.submittableto = 0
            )")Command was:
            UPDATE "group" SET submittableto = 1 WHERE id IN (
                SELECT g1.id FROM "lti_assessment" l
                JOIN "group" g1 ON g1.id = l.group
                WHERE g1.submittableto = 0
            )
```

This is related to this review: https://reviews.mahara.org/c/mahara/+/12492

Revision history for this message
Gold (gold.catalyst) wrote :

Bob spotted that MySQL has some weird behaviour when it comes to reading and writing to the same table. This method gets around that though;

        execute_sql("
            UPDATE {group}
            SET submittableto = 1
            WHERE id IN ((
                SELECT foo.id FROM (
                    SELECT g1.id
                    FROM {lti_assessment} l
                    JOIN {group} g1 ON g1.id = l.group
                    WHERE g1.submittableto = 0
                ) AS foo
            ));");

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

Patch for "main" branch: https://reviews.mahara.org/c/mahara/+/12592

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

Reviewed: https://reviews.mahara.org/c/mahara/+/12592
Committed: https://git.mahara.org/mahara/mahara/commit/2505ba8b8cf5c3d606c1263570d5e947ade19875
Submitter: "Robert Lyon <email address hidden>"
Branch: main

commit 2505ba8b8cf5c3d606c1263570d5e947ade19875
Author: Gold <email address hidden>
Date: Tue Apr 12 13:23:46 2022 +1200

Bug 1967811: Set submittableto = 1 in a MySQL friendly manner.

This SQL is also compatible with how MySQL does things.

Change-Id: I9630fb137eae0791ffac2d5e9e3ea04e3bed29ef
Signed-off-by: Gold <email address hidden>

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

Patch for "21.10_DEV" branch: https://reviews.mahara.org/c/mahara/+/12564

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

Patch for "21.04_DEV" branch: https://reviews.mahara.org/c/mahara/+/12565

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

Patch for "20.10_DEV" branch: https://reviews.mahara.org/c/mahara/+/12566

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

Reviewed: https://reviews.mahara.org/c/mahara/+/12566
Committed: https://git.mahara.org/mahara/mahara/commit/4d1c3fc4d3a7b38205970e8496bec7f035eeb433
Submitter: "Robert Lyon <email address hidden>"
Branch: 20.10_DEV

commit 4d1c3fc4d3a7b38205970e8496bec7f035eeb433
Author: Gold <email address hidden>
Date: Tue Apr 12 13:23:46 2022 +1200

Bug 1967811: Set submittableto = 1 in a MySQL friendly manner.

This SQL is also compatible with how MySQL does things.

Change-Id: I9630fb137eae0791ffac2d5e9e3ea04e3bed29ef
Signed-off-by: Gold <email address hidden>
(cherry picked from commit 2505ba8b8cf5c3d606c1263570d5e947ade19875)

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

Reviewed: https://reviews.mahara.org/c/mahara/+/12565
Committed: https://git.mahara.org/mahara/mahara/commit/d51a822fb27e3b2d751ae8a02cd5c5ddff703cb2
Submitter: "Robert Lyon <email address hidden>"
Branch: 21.04_DEV

commit d51a822fb27e3b2d751ae8a02cd5c5ddff703cb2
Author: Gold <email address hidden>
Date: Tue Apr 12 13:23:46 2022 +1200

Bug 1967811: Set submittableto = 1 in a MySQL friendly manner.

This SQL is also compatible with how MySQL does things.

Change-Id: I9630fb137eae0791ffac2d5e9e3ea04e3bed29ef
Signed-off-by: Gold <email address hidden>
(cherry picked from commit 2505ba8b8cf5c3d606c1263570d5e947ade19875)

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

Reviewed: https://reviews.mahara.org/c/mahara/+/12564
Committed: https://git.mahara.org/mahara/mahara/commit/1011616212010789c77a9f698554b3c69a75ef6c
Submitter: "Robert Lyon <email address hidden>"
Branch: 21.10_DEV

commit 1011616212010789c77a9f698554b3c69a75ef6c
Author: Gold <email address hidden>
Date: Tue Apr 12 13:23:46 2022 +1200

Bug 1967811: Set submittableto = 1 in a MySQL friendly manner.

This SQL is also compatible with how MySQL does things.

Change-Id: I9630fb137eae0791ffac2d5e9e3ea04e3bed29ef
Signed-off-by: Gold <email address hidden>
(cherry picked from commit 2505ba8b8cf5c3d606c1263570d5e947ade19875)

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.