Export not working with MySQL8

[Dianne Tennent]

This problem happens with MySQL8 on Firefox, Chromium and Chrome

Tested with postgresql - not a problem.

Tested under both admin account and masquarading as a regular user.

When you select "All my data", then 'Generate export' it just hangs and doesn't do anything

But if you select 'Just some of my pages' it will export fine

Importing zip file working fine

[Kristina Hoeppner (kris-hoeppner)]

Haven't tested because I don't have MySQL, but someone should try to replicate.

[ma (mafen1)]

I can replicate this bug. We are using Mahara version 22.04.1.
The error message is:

[WAR] b7 (lib/dml.php:922) Failed to get a recordset: mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?UNION, referer: https://mahara.abc.xyz/export/index.php
[WAR] b7 (lib/dml.php:922) SELECT artefact, referer: https://mahara.abc.xyz/export/index.php
[WAR] b7 (lib/dml.php:922) FROM "view_art...' at line 1] in EXECUTE("SELECT id FROM "artefact" WHERE "owner" = ?UNION, referer: https://mahara.abc.xyz/export/index.php
[WAR] b7 (lib/dml.php:922) SELECT artefact, referer: https://mahara.abc.xyz/export/index.php
[WAR] b7 (lib/dml.php:922) FROM "view_artefact", referer: https://mahara.abc.xyz/export/index.php
[WAR] b7 (lib/dml.php:922) WHERE "view" IN (SELECT id FROM "view" WHERE "owner" = ?), referer: https://mahara.abc.xyz/export/index.php
[WAR] b7 (lib/dml.php:922) ORDER BY id")Command was: SELECT id FROM "artefact" WHERE "owner" = ?UNION, referer: https://mahara.abc.xyz/export/index.php
[WAR] b7 (lib/dml.php:922) SELECT artefact, referer: https://mahara.abc.xyz/export/index.php
[WAR] b7 (lib/dml.php:922) FROM "view_artefact", referer: https://mahara.abc.xyz/export/index.php
[WAR] b7 (lib/dml.php:922) WHERE "view" IN (SELECT id FROM "view" WHERE "owner" = ?), referer: https://mahara.abc.xyz/export/index.php
[WAR] b7 (lib/dml.php:922) ORDER BY id and values was (0:1135,1:1135), referer: https://mahara.abc.xyz/export/index.php
Call stack (most recent first):, referer: https://mahara.abc.xyz/export/index.php
  * get_column_sql() at /var/www/html/mahara.abc.xyz/export/lib.php:306, referer: https://mahara.abc.xyz/export/index.php
  * PluginExport->__construct() at /var/www/html/mahara.abc.xyz/export/html/lib.php:83, referer: https://mahara.abc.xyz/export/index.php
  * PluginExportHtml->__construct() at /var/www/html/mahara.abc.xyz/export/lib.php:1063, referer: https://mahara.abc.xyz/export/index.php
  * PluginExportAll->__construct() at /var/www/html/mahara.abc.xyz/export/download.php:82, referer: https://mahara.abc.xyz/export/index.php
, referer: https://mahara.abc.xyz/export/index.php

[Nitin Mishra (nitinm)]

I am also using MySQL 5.7 same problem generates while exporting Mahara portfolios. Error logs are below:

(lib/errors.php:853) 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 '?UNION
(lib/errors.php:853) SELECT artefact
(lib/errors.php:853) FROM "view_artefa' at line 1] in EXECUTE("SELECT id FROM "artefact" WHERE "owner" = ?UNION
(lib/errors.php:853) SELECT artefact
(lib/errors.php:853) FROM "view_artefact"
(lib/errors.php:853) WHERE "view" IN (SELECT id FROM "view" WHERE "owner" = ?)
(lib/errors.php:853) ORDER BY id")Command was: SELECT id FROM "artefact" WHERE "owner" = ?UNION
(lib/errors.php:853) SELECT artefact
(lib/errors.php:853) FROM "view_artefact"
(lib/errors.php:853) WHERE "view" IN (SELECT id FROM "view" WHERE "owner" = ?)
(lib/errors.php:853) ORDER BY id and values was (0:1,1:1)
Call stack (most recent first):
  * log_message(string(size 790), integer, true, true) at /home/xxxxxx/public_html/lib/errors.php:89
  * log_warn(string(size 790)) at /home/xxxxxx/public_html/lib/errors.php:853
  * SQLException->__construct(string(size 790)) at /home/xxxxxx/public_html/lib/dml.php:922
  * get_column_sql(string(size 231), array(size 2)) at /home/xxxxxx/public_html/export/lib.php:306
  * PluginExport->__construct(object(User), integer, integer, string(size 30), integer, integer, integer) at /home/xxxxxx/public_html/export/html/lib.php:83
  * PluginExportHtml->__construct(object(User), integer, integer, string(size 30), integer, integer, integer) at /home/xxxxxx/public_html/export/lib.php:1063
  * PluginExportAll->__construct(object(User), integer, integer, string(size 30)) at /home/xxxxxx/public_html/export/download.php:82

(lib/dml.php:922) 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 '?UNION
(lib/dml.php:922) SELECT artefact
(lib/dml.php:922) FROM "view_artefa' at line 1] in EXECUTE("SELECT id FROM "artefact" WHERE "owner" = ?UNION
(lib/dml.php:922) SELECT artefact
(lib/dml.php:922) FROM "view_artefact"
(lib/dml.php:922) WHERE "view" IN (SELECT id FROM "view" WHERE "owner" = ?)
(lib/dml.php:922) ORDER BY id")Command was: SELECT id FROM "artefact" WHERE "owner" = ?UNION
(lib/dml.php:922) SELECT artefact
(lib/dml.php:922) FROM "view_artefact"
(lib/dml.php:922) WHERE "view" IN (SELECT id FROM "view" WHERE "owner" = ?)
(lib/dml.php:922) ORDER BY id and values was (0:1,1:1)
Call stack (most recent first):
  * get_column_sql(string(size 231), array(size 2)) at /home/xxxxxx/public_html/export/lib.php:306
  * PluginExport->__construct(object(User), integer, integer, string(size 30), integer, integer, integer) at /home/xxxxxx/public_html/export/html/lib.php:83
  * PluginExportHtml->__construct(object(User), integer, integer, string(size 30), integer, integer, integer) at /home/xxxxxx/public_html/export/lib.php:1063
  * PluginExportAll->__construct(object(User), integer, integer, string(size 30)) at /home/xxxxxx/public_html/export/download.php:82

My Mahara version is 22.04.1, also...

[Kristina Hoeppner (kris-hoeppner)]

It looks like in MySQL it says "near '?UNION," when there should be a space between ? and 'UNION'.

[Mahara Bot (dev-mahara)]

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

[Nitin Mishra (nitinm)]

Thanks, it works for me.

[Mahara Bot (dev-mahara)]

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

commit 01e58608ccb9646636aa1f2081c8a770e3a11fb7
Author: veronicavarsha <email address hidden>
Date: Wed Aug 24 10:22:49 2022 +1200

Bug 1969548: Export not working with MySQL8

Signed-off-by: veronicavarsha <email address hidden>
Change-Id: I9499e3fd5b17a1876ac6a7811a4032dc82e62d9a

[Mahara Bot (dev-mahara)]

Patch for "22.04_DEV" branch: https://reviews.mahara.org/c/mahara/+/13083

[Mahara Bot (dev-mahara)]

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

commit 4b27326a0d7d303a958f2cd9c2b2db899c973a2e
Author: veronicavarsha <email address hidden>
Date: Wed Aug 24 10:22:49 2022 +1200

Bug 1969548: Export not working with MySQL8

Signed-off-by: veronicavarsha <email address hidden>
Change-Id: I9499e3fd5b17a1876ac6a7811a4032dc82e62d9a
(cherry picked from commit 01e58608ccb9646636aa1f2081c8a770e3a11fb7)

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

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

commit fc76609fb374a0e2be2579a5f62a7c97f22ca0bb
Author: veronicavarsha <email address hidden>
Date: Wed Aug 24 10:22:49 2022 +1200

Bug 1969548: Export not working with MySQL8

Signed-off-by: veronicavarsha <email address hidden>
Change-Id: I9499e3fd5b17a1876ac6a7811a4032dc82e62d9a
(cherry picked from commit 01e58608ccb9646636aa1f2081c8a770e3a11fb7)
(cherry picked from commit 4b27326a0d7d303a958f2cd9c2b2db899c973a2e)

[Mahara Bot (dev-mahara)]

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

commit b40251ae86325f8bb622daa8dea070c33377c521
Author: veronicavarsha <email address hidden>
Date: Wed Aug 24 10:22:49 2022 +1200

Bug 1969548: Export not working with MySQL8

Signed-off-by: veronicavarsha <email address hidden>
Change-Id: I9499e3fd5b17a1876ac6a7811a4032dc82e62d9a
(cherry picked from commit 01e58608ccb9646636aa1f2081c8a770e3a11fb7)
(cherry picked from commit 4b27326a0d7d303a958f2cd9c2b2db899c973a2e)