[MDL-68860] LTI grade import on Oracle fails due to WHERE on CLOB column - Moodle Tracker

XML

Word

Printable

Details

Type: Bug
Status: Closed
Priority: Minor
Resolution: Fixed
Affects Version/s: 3.5.12, 3.8.4, 3.9.1, 3.10
Fix Version/s: 3.8.5, 3.9.2
Component/s: Other
Labels:
- ci
- triaged

Database:

Oracle

Testing Instructions:

Hide

Ensure your testing site has a public URL (e.g. Ngrok) and you are using Oracle
Navigate to Plugins > Activity modules > External tool > Manage tools in site administration
Press Configure a tool manually and enter the following:
- Tool name: Whatever
- Tool URL: http://ltiapps.net/test/tp.php
- Consumer key: consumerkey
- Shared secret: secret
Press Save changes
Create a new course
Add a new External tool activity and enter the following:
- Activity name: Test
- Preconfigured tool: Whatever
Press Save and display
Press Outcomes button
Press Read button

Confirm you receive back an XML response similar to the following:

<?xml version="1.0" encoding="UTF-8"?>

<imsx_POXEnvelopeResponse

	xmlns="http://www.imsglobal.org/services/ltiv1p1/xsd/imsoms_v1p0">

	<imsx_POXHeader>

		<imsx_POXResponseHeaderInfo>

			<imsx_version>V1.0</imsx_version>

			<imsx_messageIdentifier>1865684963</imsx_messageIdentifier>

			<imsx_statusInfo>

				<imsx_codeMajor>success</imsx_codeMajor>

				<imsx_severity>status</imsx_severity>

				<imsx_description>Result read</imsx_description>

				<imsx_messageRefIdentifier>5f17615003e05</imsx_messageRefIdentifier>

				<imsx_operationRefIdentifier>readResultRequest</imsx_operationRefIdentifier>

			</imsx_statusInfo>

		</imsx_POXResponseHeaderInfo>

	</imsx_POXHeader>

	<imsx_POXBody>

		<readResultResponse>

			<result>

				<resultScore>

					<language>en</language>

					<textString>1</textString>

				</resultScore>

			</result>

		</readResultResponse>

	</imsx_POXBody>

</imsx_POXEnvelopeResponse>

Confirm you don't receive back an XML response similar to the following:

<?xml version="1.0" encoding="UTF-8"?>

<imsx_POXEnvelopeResponse

	xmlns="http://www.imsglobal.org/services/ltiv1p1/xsd/imsoms_v1p0">

	<imsx_POXHeader>

		<imsx_POXResponseHeaderInfo>

			<imsx_version>V1.0</imsx_version>

			<imsx_messageIdentifier>1823082571</imsx_messageIdentifier>

			<imsx_statusInfo>

				<imsx_codeMajor>failure</imsx_codeMajor>

				<imsx_severity>status</imsx_severity>

				<imsx_description>Error reading from database (ORA-00932: inconsistent datatypes: expected - got CLOB

SELECT t2.value

                FROM m_lti_types_config t1

                JOIN m_lti_types_config t2 ON t1.typeid = t2.typeid

                JOIN m_lti_types type ON t2.typeid = type.id

              WHERE t1.name = 'resourcekey'

                AND t1.value = :o_key1

                AND t2.name = 'password'

                AND type.state = :o_configured1

                AND type.ltiversion <> :o_ltiversion

               UNION

              SELECT tp.secret AS value

                FROM m_lti_tool_proxies tp

                JOIN m_lti_types t ON tp.id = t.toolproxyid

              WHERE tp.guid = :o_key2

                AND t.state = :o_configured2

               UNION

              SELECT password AS value

               FROM m_lti

              WHERE resourcekey = :o_key3

[array (

  'o_key1' => 'consumerkey',

  'o_configured1' => 1,

  'o_ltiversion' => '1.3.0',

  'o_key2' => 'consumerkey',

  'o_configured2' => 1,

  'o_key3' => 'consumerkey',

)])

* line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown

* line 277 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()

* line 1154 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()

* line 2412 of /mod/lti/locallib.php: call to oci_native_moodle_database->get_records_sql()

* line 64 of /mod/lti/service.php: call to lti_get_shared_secrets_by_key()

				</imsx_description>

				<imsx_messageRefIdentifier/>

				<imsx_operationRefIdentifier>unknownRequest</imsx_operationRefIdentifier>

			</imsx_statusInfo>

		</imsx_POXResponseHeaderInfo>

	</imsx_POXHeader>

	<imsx_POXBody>

		<unknownResponse/>

	</imsx_POXBody>

</imsx_POXEnvelopeResponse>

Show

Ensure your testing site has a public URL (e.g. Ngrok) and you are using Oracle Navigate to Plugins > Activity modules > External tool > Manage tools in site administration Press Configure a tool manually and enter the following: Tool name: Whatever Tool URL: http://ltiapps.net/test/tp.php Consumer key: consumerkey Shared secret: secret Press Save changes Create a new course Add a new External tool activity and enter the following: Activity name: Test Preconfigured tool: Whatever Press Save and display Press Outcomes button Press Read button Confirm you receive back an XML response similar to the following: <?xml version="1.0" encoding="UTF-8"?> <imsx_POXEnvelopeResponse xmlns="http://www.imsglobal.org/services/ltiv1p1/xsd/imsoms_v1p0"> <imsx_POXHeader> <imsx_POXResponseHeaderInfo> <imsx_version>V1.0</imsx_version> <imsx_messageIdentifier>1865684963</imsx_messageIdentifier> <imsx_statusInfo> <imsx_codeMajor>success</imsx_codeMajor> <imsx_severity>status</imsx_severity> <imsx_description>Result read</imsx_description> <imsx_messageRefIdentifier>5f17615003e05</imsx_messageRefIdentifier> <imsx_operationRefIdentifier>readResultRequest</imsx_operationRefIdentifier> </imsx_statusInfo> </imsx_POXResponseHeaderInfo> </imsx_POXHeader> <imsx_POXBody> <readResultResponse> <result> <resultScore> <language>en</language> <textString>1</textString> </resultScore> </result> </readResultResponse> </imsx_POXBody> </imsx_POXEnvelopeResponse> Confirm you don't receive back an XML response similar to the following: <?xml version="1.0" encoding="UTF-8"?> <imsx_POXEnvelopeResponse xmlns="http://www.imsglobal.org/services/ltiv1p1/xsd/imsoms_v1p0"> <imsx_POXHeader> <imsx_POXResponseHeaderInfo> <imsx_version>V1.0</imsx_version> <imsx_messageIdentifier>1823082571</imsx_messageIdentifier> <imsx_statusInfo> <imsx_codeMajor>failure</imsx_codeMajor> <imsx_severity>status</imsx_severity> <imsx_description>Error reading from database (ORA-00932: inconsistent datatypes: expected - got CLOB SELECT t2.value FROM m_lti_types_config t1 JOIN m_lti_types_config t2 ON t1.typeid = t2.typeid JOIN m_lti_types type ON t2.typeid = type.id WHERE t1.name = 'resourcekey' AND t1.value = :o_key1 AND t2.name = 'password' AND type.state = :o_configured1 AND type.ltiversion <> :o_ltiversion UNION SELECT tp.secret AS value FROM m_lti_tool_proxies tp JOIN m_lti_types t ON tp.id = t.toolproxyid WHERE tp.guid = :o_key2 AND t.state = :o_configured2 UNION SELECT password AS value FROM m_lti WHERE resourcekey = :o_key3 [array ( 'o_key1' => 'consumerkey', 'o_configured1' => 1, 'o_ltiversion' => '1.3.0', 'o_key2' => 'consumerkey', 'o_configured2' => 1, 'o_key3' => 'consumerkey', )]) * line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown * line 277 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end() * line 1154 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end() * line 2412 of /mod/lti/locallib.php: call to oci_native_moodle_database->get_records_sql() * line 64 of /mod/lti/service.php: call to lti_get_shared_secrets_by_key() </imsx_description> <imsx_messageRefIdentifier/> <imsx_operationRefIdentifier>unknownRequest</imsx_operationRefIdentifier> </imsx_statusInfo> </imsx_POXResponseHeaderInfo> </imsx_POXHeader> <imsx_POXBody> <unknownResponse/> </imsx_POXBody> </imsx_POXEnvelopeResponse>

Affected Branches:
MOODLE_310_STABLE, MOODLE_35_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
Fixed Branches:
MOODLE_38_STABLE, MOODLE_39_STABLE
Pull from Repository:
https://github.com/paulholden/moodle.git
Pull 3.8 Branch:
~~MDL-68860~~-38
Pull 3.8 Diff URL:
https://github.com/moodle/moodle/compare/MOODLE_38_STABLE...paulholden:MDL-68860-38
Pull 3.9 Branch:
~~MDL-68860~~-39
Pull 3.9 Diff URL:
https://github.com/moodle/moodle/compare/MOODLE_39_STABLE...paulholden:MDL-68860-39
Pull Master Branch:
~~MDL-68860~~
Pull Master Diff URL:
https://github.com/moodle/moodle/compare/master...paulholden:MDL-68860

Description

LTI grade import on Oracle fails due to WHERE on CLOB column

Error displayed
Unknown error: Error while exporting lms_id: 130078. Error reading from database
* line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
* line 277 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
* line 1179 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
* line 1727 of /mod/lti/locallib.php: call to oci_native_moodle_database->get_records_sql()
* line 63 of /mod/lti/service.php: call to lti_get_shared_secrets_by_key() ..

Adding some try-catch debug lines, I found the actual error is

exception=dml_read_exception::__set_state(array(

   'error' => 'ORA-00932: inconsistent datatypes: expected - got CLOB',

   'sql' => 'SELECT t2.value

                FROM m_lti_types_config t1

                JOIN m_lti_types_config t2 ON t1.typeid = t2.typeid

                JOIN m_lti_types type ON t2.typeid = type.id

              WHERE t1.name = \'resourcekey\'

                AND t1.value = :o_key1

                AND t2.name = \'password\'

                AND type.state = :o_configured1

               UNION

              SELECT tp.secret AS value

                FROM m_lti_tool_proxies tp

                JOIN m_lti_types t ON tp.id = t.toolproxyid

              WHERE tp.guid = :o_key2

                AND t.state = :o_configured2

              UNION

             SELECT password AS value

               FROM m_lti

              WHERE resourcekey = :o_key3',

   'params' =>

  array (

    'o_key1' => 'snipped',

    'o_configured1' => 1,

    'o_key2' => 'snipped',

    'o_configured2' => 1,

    'o_key3' => 'snipped',

),

The problem in the code is you can't put a CLOB in the WHERE clause. From the documentation:

Large objects (LOBs) are not supported in comparison conditions. However, you can use PL/SQL programs for comparisons on CLOB data.

mod/lti/locallib.php
function lti_get_shared_secrets_by_key($key) {
global $DB;

// Look up the shared secret for the specified key in both the types_config table (for configured tools)
// And in the lti resource table for ad-hoc tools.
$query = "SELECT t2.value
FROM {lti_types_config} t1
JOIN {lti_types_config} t2 ON t1.typeid = t2.typeid
JOIN {lti_types} type ON t2.typeid = type.id
WHERE t1.name = 'resourcekey'
AND t1.value = :key1
AND t2.name = 'password'
AND type.state = :configured1
UNION
SELECT tp.secret AS value
FROM {lti_tool_proxies} tp
JOIN {lti_types} t ON tp.id = t.toolproxyid
WHERE tp.guid = :key2
AND t.state = :configured2
UNION
SELECT password AS value
FROM {lti}
WHERE resourcekey = :key3";

$sharedsecrets = $DB->get_records_sql($query, array('configured1' => LTI_TOOL_STATE_CONFIGURED,
'configured2' => LTI_TOOL_STATE_CONFIGURED, 'key1' => $key, 'key2' => $key, 'key3' => $key));

The fix is to use dbms_lob.substr()

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

image-2020-08-13-10-48-32-544.png
112 kB
13/Aug/20 10:48 AM
LTI_10_error.png
110 kB
23/Jul/20 10:44 AM

Activity

People

Assignee:: Paul Holden

Reporter:: Mark van Hoek

Peer reviewer:: Jake Dallimore

Integrator:: Eloy Lafuente (stronk7)

Tester:: Janelle Barcega

Participants:: CiBoT, Eloy Lafuente (stronk7), Jake Dallimore, Janelle Barcega, Mark van Hoek, Paul Holden

Component watchers:: Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 28/May/20 11:54 PM

Updated:: 14/Aug/20 5:32 PM

Resolved:: 14/Aug/20 5:32 PM

Fix Release Date:: 14/Sep/20

Time Tracking

Estimated:

Not Specified

Remaining:

Logged:

1h 31m