CAST type 'int' is not supported by MySQL

[Son Nguyen]

Version: 15.04
Platform: Apache 2.2, PHP 5.3, MySQL 5.5
Browser: any

This is a regression caused by https://reviews.mahara.org/4154.

I got the following error when accessing the Dashboard page

 [WAR] 83 (lib/errors.php:747) 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 'int), a.ctime, 'artefact_multirecipient_notification' AS msgtable, subject
    ' at line 8] in EXECUTE("SELECT COUNT(*) FROM (
        (
        SELECT a.id, a.read, a.ctime, 'notification_internal_activity' AS msgtable, subject
        FROM "mh01_notification_internal_activity" AS a
        INNER JOIN "mh01_activity_type" AS at ON a.type = at.id
        WHERE a.usr = '1'
        AND at.name IN ('feedback','groupmessage','institutionmessage','maharamessage','usermessage','viewaccess','watchlist')
        )
        UNION
        (
        SELECT a.id, CAST(b.read AS int), a.ctime, 'artefact_multirecipient_notification' AS msgtable, subject
        FROM "mh01_artefact_multirecipient_notification" AS a
        INNER JOIN "mh01_artefact_multirecipient_userrelation" AS b
            ON a.id = b.notification
        INNER JOIN "mh01_activity_type" AS at ON a.type = at.id
        WHERE b.usr = '1'
        AND b.deleted = '0'
        AND b.role = 'recipient'
        AND at.name IN ('feedback','groupmessage','institutionmessage','maharamessage','usermessage','viewaccess','watchlist')
        )) AS dummytable")
Command was: SELECT COUNT(*) FROM (
        (
        SELECT a.id, a.read, a.ctime, 'notification_internal_activity' AS msgtable, subject
        FROM "mh01_notification_internal_activity" AS a
        INNER JOIN "mh01_activity_type" AS at ON a.type = at.id
        WHERE a.usr = ?
        AND at.name IN ('feedback','groupmessage','institutionmessage','maharamessage','usermessage','viewaccess','watchlist')
        )
        UNION
        (
        SELECT a.id, CAST(b.read AS int), a.ctime, 'artefact_multirecipient_notification' AS msgtable, subject
        FROM "mh01_artefact_multirecipient_notification" AS a
        INNER JOIN "mh01_artefact_multirecipient_userrelation" AS b
            ON a.id = b.notification
        INNER JOIN "mh01_activity_type" AS at ON a.type = at.id
        WHERE b.usr = ?
        AND b.deleted = '0'
        AND b.role = 'recipient'
        AND at.name IN ('feedback','groupmessage','institutionmessage','maharamessage','usermessage','viewaccess','watchlist')
        )) AS dummytable and values was (1,1)
Call stack (most recent first):
log_message("Failed to get a recordset: mysqli error: [1064: Yo...", 8, true, true) at /home/sonn/code/mahara/master/htdocs/lib/errors.php:97
log_warn("Failed to get a recordset: mysqli error: [1064: Yo...") at /home/sonn/code/mahara/master/htdocs/lib/errors.php:747
SQLException->__construct("Failed to get a recordset: mysqli error: [1064: Yo...") at /home/sonn/code/mahara/master/htdocs/lib/dml.php:477
get_recordset_sql("SELECT COUNT(*) FROM ( ( SELECT a....", array(size 2)) at /home/sonn/code/mahara/master/htdocs/lib/dml.php:266
count_records_sql("SELECT COUNT(*) FROM ( ( SELECT a....", array(size 2)) at /home/sonn/code/mahara/master/htdocs/artefact/multirecipientnotification/lib/activityextend.php:69
activitylistin("feedback,groupmessage,institutionmessage,maharames...", "5") at /home/sonn/code/mahara/master/htdocs/blocktype/inbox/lib.php:56
PluginBlocktypeInbox::render_instance(object(BlockInstance)) at Unknown:0
call_user_func_array(array(size 2), array(size 1)) at /home/sonn/code/mahara/master/htdocs/lib/mahara.php:1578
call_static_method("PluginBlocktypeInbox", "render_instance", object(BlockInstance)) at /home/sonn/code/mahara/master/htdocs/blocktype/lib.php:782
BlockInstance->render_viewing() at /home/sonn/code/mahara/master/htdocs/lib/view.php:2059
View->build_column(1, 2, false) at /home/sonn/code/mahara/master/htdocs/lib/view.php:2016
View->build_columns(1, false) at /home/sonn/code/mahara/master/htdocs/lib/view.php:2001
View->build_rows() at /home/sonn/code/mahara/master/htdocs/index.php:49

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/4201
Committed: http://gitorious.org/mahara/mahara/commit/a98f6d9748acadffb5434d4475537ae2bc35f2d0
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit a98f6d9748acadffb5434d4475537ae2bc35f2d0
Author: Son Nguyen <email address hidden>
Date: Tue Jan 20 13:48:24 2015 +1300

Fix SQL cast type for postgres and mysql db. Bug 1412606

Change-Id: I58635b1a878c18f6626acadcc1a5974f6fd3d40f
Signed-off-by: Son Nguyen <email address hidden>