SQL syntax when using MariaDB server near 'read...

[nadavkav]

Only 1 out of 30+ students in some course got this error: (Last year we migrated from MySQL to MariaDB + Galera cluster. Moodle version 2.8 + ForumNG version 2.8 r1)

Debug info: 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:

'read FROM mdl_forumng_posts fp INNER JOIN mdl_user u ON fp.userid = u.id' 

SELECT
    fp.*,
    u.id as u_id,u.username as u_username,u.picture as u_picture,u.url as u_url,u.imagealt as u_imagealt,u.email as u_email,u.maildisplay as u_maildisplay,u.mailformat as u_mailformat,u.maildigest as u_maildigest,u.emailstop as u_emailstop,u.deleted as u_deleted,u.auth as u_auth,u.timezone as u_timezone,u.lang as u_lang,u.idnumber as u_idnumber,u.firstnamephonetic as u_firstnamephonetic,u.lastnamephonetic as u_lastnamephonetic,u.middlename as u_middlename,u.alternatename as u_alternatename,u.firstname as u_firstname,u.lastname as u_lastname,
    eu.id as eu_id,eu.username as eu_username,eu.picture as eu_picture,eu.url as eu_url,eu.imagealt as eu_imagealt,eu.idnumber as eu_idnumber,eu.email as eu_email,eu.firstnamephonetic as eu_firstnamephonetic,eu.lastnamephonetic as eu_lastnamephonetic,eu.middlename as eu_middlename,eu.alternatename as eu_alternatename,eu.firstname as eu_firstname,eu.lastname as eu_lastname,
    du.id as du_id,du.username as du_username,du.picture as du_picture,du.url as du_url,du.imagealt as du_imagealt,du.idnumber as du_idnumber,du.email as du_email,du.firstnamephonetic as du_firstnamephonetic,du.lastnamephonetic as du_lastnamephonetic,du.middlename as du_middlename,du.alternatename as du_alternatename,du.firstname as du_firstname,du.lastname as du_lastname
    , ff.flagged
    , fr.time AS read
FROM
    mdl_forumng_posts fp
    INNER JOIN mdl_user u ON fp.userid = u.id
    LEFT JOIN mdl_user eu ON fp.edituserid = eu.id
    LEFT JOIN mdl_user du ON fp.deleteuserid = du.id
    LEFT JOIN mdl_forumng_flags ff ON ff.postid = fp.id AND ff.userid = ?
    LEFT JOIN mdl_forumng_read_posts fr ON fr.postid = fp.id AND fr.userid = ?
WHERE
    fp.discussionid=? AND fp.oldversion=0
ORDER BY
    fp.created

[array (
  0 => '22365',
  1 => '22365',
  2 => '6479',
)]
Error code: dmlreadexception
Stack trace: 
line 443 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 1080 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 1036 of /mod/forumng/mod_forumng_post.php: call to mysqli_native_moodle_database->get_records_sql()
line 661 of /mod/forumng/mod_forumng_discussion.php: call to mod_forumng_post::query_posts()
line 2548 of /mod/forumng/mod_forumng_discussion.php: call to mod_forumng_discussion->get_root_post()
line 273 of /mod/forumng/type/general/forumngtype_general.php: call to mod_forumng_discussion->display_unread_skip_link()
line 104 of /mod/forumng/discuss.php: call to forumngtype_general->print_discussion_page()

[nadavkav]

Fix: https://github.com/moodleou/moodle-mod_forumng/blob/MOODLE_28_STABLE/mod_forumng_post.php#L1002 Change:

$readquery = ', fr.time AS read';

To:

$readquery = ', fr.time AS "read"';

[nadavkav]

Ok. just saw your master branch is already fixed: https://github.com/moodleou/moodle-mod_forumng/blob/master/mod_forumng_post.php#L1002

[aspark21]

@nadavkav looks like the same issue as #44 & #47 the fix was released 1st September

[nadavkav]

Yes. thanks. I remembered I bumped into this previously, but could not remember where. Funny how I solved it a little differently this time ;-)