Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 3.5.12, 3.8.4, 3.9.1, 3.10
-
Component/s: Other
-
Database:Oracle
-
Testing Instructions:
-
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:
-
Pull 3.8 Branch:
MDL-68860-38 -
Pull 3.9 Branch:
MDL-68860-39 -
Pull Master Branch:
-
Pull Master Diff URL:
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()