Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-68860

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

    XMLWordPrintable

    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:
    • Database:
      Oracle
    • Testing Instructions:
      Hide
      1. Ensure your testing site has a public URL (e.g. Ngrok) and you are using Oracle
      2. Navigate to Plugins > Activity modules > External tool > Manage tools in site administration
      3. Press Configure a tool manually and enter the following:
      4. Press Save changes
      5. Create a new course
      6. Add a new External tool activity and enter the following:
        • Activity name: Test
        • Preconfigured tool: Whatever
      7. Press Save and display
      8. Press Outcomes button
      9. Press Read button
      10. 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>
        

      11. 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 3.8 Branch:
    • Pull 3.9 Branch:
    • Pull Master Branch:

      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

          Activity

            People

            Assignee:
            pholden Paul Holden
            Reporter:
            mwebster Mark van Hoek
            Peer reviewer:
            Jake Dallimore
            Integrator:
            Eloy Lafuente (stronk7)
            Tester:
            Janelle Barcega
            Participants:
            Component watchers:
            Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              14/Sep/20

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 hour, 31 minutes
                1h 31m