Moodle

insert_record fails on Oracle Database when sequence reaches double figures

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: 2.0
  • Fix Version/s: None
  • Component/s: Database SQL/XMLDB
  • Labels:
    None
  • Environment:
    Moodle 2.0 running on Linix Redhat ES 3.0 connected to Oracle 10.2 (oci native)
  • Database:
    Oracle
  • Difficulty:
    Moderate
  • Affected Branches:
    MOODLE_20_STABLE

Description

The hang is due to a database INSERT into the SCORM table returning the wrong record id.
The code is line 45 of mod/scorm/lib.php - if (!$id = $DB->insert_record('scorm', $scorm)) {

The problem only appears when the sequence MBL_SCOR_ID_SEQ (MBL_ default prefix) next_val reaches double figures, then only the first digit of the sequence number is returned from the insert_record at line 45 of lib.php.

I've traced the problem down to the php oracle oci plugin and have logged a bug with www.php.net. I'd still like to know if there is a work around for this problem.
B.T.W. The php I'm running is 5.2.9 compiled again oracle 10.2 client.

Attachments

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Roger Robins added a comment -

I've attached the file I was trying to import to help reproduce the problem

Show
Roger Robins added a comment - I've attached the file I was trying to import to help reproduce the problem
Hide
Permalink
Petr Škoda (skodak) added a comment -

assigning to our db guru

Show
Petr Škoda (skodak) added a comment - assigning to our db guru
Hide
Permalink
Roger Robins added a comment -

This is the feedback from www.php.net

http://bugs.php.net/?id=48324&edit=2

ID: 48324
Updated by: sixd@php.net
Reported By: roger dot robins at det dot nsw dot edu dot au
-Status: Open
+Status: Bogus
Bug Type: OCI8 related
Operating System: Linux Redhat ES 3.0
PHP Version: 5.2.9
New Comment:

Passing a -1 length to the oci_bind_by_name call allocates only enough storage for the current size of $id at the time of the call. A size of 1 is used for a null.

Instead of -1, pass a length big enough to hold your largest expected return value.

Previous Comments:
------------------------------------------------------------------------

[2009-05-19 01:48:11] roger dot robins at det dot nsw dot edu dot au

Description:
------------
When INSERTing a record into a table that has a database sequence for calculating the table id (unique identifier) and that sequence's next_val has reached double figures; and you use oci_bind_by_name to return the id of the inserted row; then only the first digit of the two digit row id is returned.

Code:
$sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES($values) $returning"; $id = null;

$this->query_start($sql, $params, SQL_QUERY_INSERT); $stmt = $this->parse_query($sql); $descriptors = $this->bind_params($stmt, $params, $table); if ($returning) { oci_bind_by_name($stmt, ":oracle_id", $id, -1, SQLT_LNG); } $result = oci_execute($stmt); $this->free_descriptors($descriptors);
$this->query_end($result, $stmt);
oci_free_statement($stmt);

Show
Roger Robins added a comment - This is the feedback from www.php.net http://bugs.php.net/?id=48324&edit=2 ID: 48324 Updated by: sixd@php.net Reported By: roger dot robins at det dot nsw dot edu dot au -Status: Open +Status: Bogus Bug Type: OCI8 related Operating System: Linux Redhat ES 3.0 PHP Version: 5.2.9 New Comment: Passing a -1 length to the oci_bind_by_name call allocates only enough storage for the current size of $id at the time of the call. A size of 1 is used for a null. Instead of -1, pass a length big enough to hold your largest expected return value. Previous Comments: ------------------------------------------------------------------------ [2009-05-19 01:48:11] roger dot robins at det dot nsw dot edu dot au Description: ------------ When INSERTing a record into a table that has a database sequence for calculating the table id (unique identifier) and that sequence's next_val has reached double figures; and you use oci_bind_by_name to return the id of the inserted row; then only the first digit of the two digit row id is returned. Code: $sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES($values) $returning"; $id = null; $this->query_start($sql, $params, SQL_QUERY_INSERT); $stmt = $this->parse_query($sql); $descriptors = $this->bind_params($stmt, $params, $table); if ($returning) { oci_bind_by_name($stmt, ":oracle_id", $id, -1, SQLT_LNG); } $result = oci_execute($stmt); $this->free_descriptors($descriptors); $this->query_end($result, $stmt); oci_free_statement($stmt);
Hide
Permalink
Dan Marsden added a comment -

updating component - this isn't a bug with SCORM - it's a problem with db functions.

Show
Dan Marsden added a comment - updating component - this isn't a bug with SCORM - it's a problem with db functions.
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Uhm... this is really curious, as fa as we already have some tests, checking for big ids on insert and they are passing perfectly here under Oracle.

Can you, plz, go to Settings->Development->Functional DB tests and run them in your Oracle DB?

Here I get two fails - one related with test_ilike() and another with test_sql_concat(), but the one related with inserted ids, named test_reset_sequence(), runs ok, returning one id = 667 without problems.

What do you get? TIA and ciao

Show
Eloy Lafuente (stronk7) added a comment - Uhm... this is really curious, as fa as we already have some tests, checking for big ids on insert and they are passing perfectly here under Oracle. Can you, plz, go to Settings->Development->Functional DB tests and run them in your Oracle DB? Here I get two fails - one related with test_ilike() and another with test_sql_concat(), but the one related with inserted ids, named test_reset_sequence(), runs ok, returning one id = 667 without problems. What do you get? TIA and ciao
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Once you run the tests above, please, edit lib/dml/oci_native_moodle_database.php and change this line:

oci_bind_by_name($stmt, ":oracle_id", $id, -1, SQLT_INT);

by this:

oci_bind_by_name($stmt, ":oracle_id", $id, 10, SQLT_INT);

And run tests again... what do you get now?

Here I get exactly the same results, but seems that the maxsize (10) is the correct way. Just waiting for confirmation of results in your server before applying the change.

Ciao

Show
Eloy Lafuente (stronk7) added a comment - Once you run the tests above, please, edit lib/dml/oci_native_moodle_database.php and change this line: 
oci_bind_by_name($stmt, ":oracle_id", $id, -1, SQLT_INT);
by this: 
oci_bind_by_name($stmt, ":oracle_id", $id, 10, SQLT_INT);
And run tests again... what do you get now? Here I get exactly the same results, but seems that the maxsize (10) is the correct way. Just waiting for confirmation of results in your server before applying the change. Ciao

People

Vote (0)
Watch (2)

Dates

  • Created:
    Updated:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Moodle. Try JIRA - bug tracking software for your team.