Moodle

Cannot create questions using Oracle 10 in Moodle 2.1 and onwards

Details

  • Database:
    Oracle
  • Testing Instructions:
    Hide

    Install a Moodle 2.1 or 2.2 version
    Go to the question bank (general or in a course)
    Create a new question multichoice, with penalty (decimal)

    Show
    Install a Moodle 2.1 or 2.2 version Go to the question bank (general or in a course) Create a new question multichoice, with penalty (decimal)
  • Workaround:
    Hide

    I think the problem is in the base form:
    https://github.com/moodle/moodle/blob/MOODLE_21_STABLE/question/type/edit_question_form.php#L382

    The penalty is not "typed" as number

    In previous versions (2.0), the penalty field is "typed" as number
    https://github.com/moodle/moodle/blob/MOODLE_20_STABLE/question/type/edit_question_form.php#L149
    $mform->setType('penalty', PARAM_NUMBER);

    Show
    I think the problem is in the base form: https://github.com/moodle/moodle/blob/MOODLE_21_STABLE/question/type/edit_question_form.php#L382 The penalty is not "typed" as number In previous versions (2.0), the penalty field is "typed" as number https://github.com/moodle/moodle/blob/MOODLE_20_STABLE/question/type/edit_question_form.php#L149 $mform->setType('penalty', PARAM_NUMBER);
  • Affected Branches:
    MOODLE_21_STABLE, MOODLE_22_STABLE

Description

When trying to create a new question (multichoice) we got this error:

[Wed Jan 18 18:26:04 2012] [error] [client 10.100.100.21] Default exception handler: Error al escribir a la base de datos Debug: ORA-01722: n\xc3\xbamero no v\xc3\xa1lido\n
INSERT INTO m_question (category,qtype,name,parent,length,penalty,questiontext,questiontextformat,generalfeedback,generalfeedbackformat,defaultmark,stamp,createdby,timecreated) VALUES (:category,:qtype,:name,:parent,:length,:penalty,:questiontext,:questiontextformat,:generalfeedback,:generalfeedbackformat,:defaultmark,:stamp,:createdby,:timecreated) RETURNING id INTO :oracle_id\n[array (\n 'category' => '2',\n 'qtype' => 'multichoice',\n 'name' => '1',\n 'parent' => 0,\n 'length' => 1,\n 'penalty' => '0.3333333',\n 'questiontext' => '<p>asfsadf</p>',\n 'questiontextformat' => '1',\n 'generalfeedback' => '',\n 'generalfeedbackformat' => '1',\n 'defaultmark' => 1,\n 'stamp' => 'mydomain.es+120118172604+bxcrL1',\n 'createdby' => '2',\n 'timecreated' => 1326907564,\n)]\n* line 397 of /lib/dml/moodle_database.php: dml_write_exception thrown\n* line 273 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()\n* line 1205 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()\n* line 1248 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->insert_record_raw()\n* line 393 of /question/type/questiontypebase.php: call to oci_native_moodle_database->insert_record()\n* line 264 of /question/question.php: call to question_type->save_question()\n, referer: http://mydomain.es/question/question.php?returnurl=%2Fquestion%2Fedit.php%3Fcmid%3D82&cmid=82&category=2&qtype=multichoice

According to: http://www.orafaq.com/wiki/ORA-01722
An ORA-01722 error occurs when an attempt is made to convert a character string into a number

Issue Links

has been marked as being related by

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-11629 Oracle can't store multiple choice fractional value questions.

  • Major - Major loss of function, incorrect output
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-17491 Implement native Oracle DML driver

  • Minor - Minor loss of function where workaround is possible
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed should not be reopened.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Juan Leyva added a comment -

Please, note that seems that the incorrect field is:

'penalty' => '0.3333333',

See the workaround, I cannot test it because I haven't access to the server where the problem is

Show
Juan Leyva added a comment - Please, note that seems that the incorrect field is: 'penalty' => '0.3333333', See the workaround, I cannot test it because I haven't access to the server where the problem is
Hide
Permalink
Juan Leyva added a comment -

I was suggested in the Developers chat to do the following:

Set this enviroment option in Oracle:

NLS_NUMERIC_CHARACTERS='.,'

before the webserver starts

Show
Juan Leyva added a comment - I was suggested in the Developers chat to do the following: Set this enviroment option in Oracle: NLS_NUMERIC_CHARACTERS='.,' before the webserver starts
Hide
Permalink
Tim Hunt added a comment -

Any news about whether that suggested fix solves the problem?

Show
Tim Hunt added a comment - Any news about whether that suggested fix solves the problem?
Hide
Permalink
Juan Leyva added a comment -

Our client switch to MySQL and since Moodle is faster they are not going back to Oracle

Sorry for not providing any useful feedback

Show
Juan Leyva added a comment - Our client switch to MySQL and since Moodle is faster they are not going back to Oracle Sorry for not providing any useful feedback
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

From HQ:

12:59:27] <Cindereloy> please, allow me to pass here. I did not delete that line from the driver nor agreed with that measure. So I'm not going to decide about that. The guy that did that (Petr) seemed to have some good reasons that I don't know.

Show
Eloy Lafuente (stronk7) added a comment - From HQ: 12:59:27] <Cindereloy> please, allow me to pass here. I did not delete that line from the driver nor agreed with that measure. So I'm not going to decide about that. The guy that did that (Petr) seemed to have some good reasons that I don't know.
Hide
Permalink
Tim Hunt added a comment -

To explain Eloy's last comment:

(11:55:29 AM) jleyva@jabber.org: the problem is that ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,' was present in configure_dbconnection (Moodle 1.9) but now has been removed

...

(11:59:27 AM) Cindereloy: please, allow me to pass here. I did not delete that line from the driver nor agreed with that measure. So I'm not going to decide about that. The guy that did that (Petr) seemed to have some good reasons that I don't know.

Petr, can you help us here?

  • Do I need to add a cast to float to my code, or
  • Should the oracle DB class handle this, or
  • Should I just close this won'tfix and rely on Oracle admins reading the docs and setting NLS_NUMERIC_CHARACTERS manually?

Thanks.

Show
Tim Hunt added a comment - To explain Eloy's last comment: (11:55:29 AM) jleyva@jabber.org: the problem is that ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,' was present in configure_dbconnection (Moodle 1.9) but now has been removed ... (11:59:27 AM) Cindereloy: please, allow me to pass here. I did not delete that line from the driver nor agreed with that measure. So I'm not going to decide about that. The guy that did that (Petr) seemed to have some good reasons that I don't know. Petr, can you help us here? Do I need to add a cast to float to my code, or Should the oracle DB class handle this, or Should I just close this won'tfix and rely on Oracle admins reading the docs and setting NLS_NUMERIC_CHARACTERS manually? Thanks.
Hide
Permalink
Juan Leyva added a comment -

I've added a note also in the WIKI page for the Oracle installation (PHP)

http://docs.moodle.org/en/Installing_Oracle_for_PHP#Configure_Apache

See also this related ticket: http://tracker.moodle.org/browse/MDL-11629

Show
Juan Leyva added a comment - I've added a note also in the WIKI page for the Oracle installation (PHP) http://docs.moodle.org/en/Installing_Oracle_for_PHP#Configure_Apache See also this related ticket: http://tracker.moodle.org/browse/MDL-11629
Hide
Permalink
Petr Škoda added a comment -

Hi,

I do not have Oracle test install and necessary knowledge, sorry. I never maintained the oracle driver, feel free to do any changes inside it that do not affect other drivers.

We should imo:

  • convert all user input (strings with , or . based on current lang) to PHP floats
  • us PHP floats when inserting/updating database
Show
Petr Škoda added a comment - Hi, I do not have Oracle test install and necessary knowledge, sorry. I never maintained the oracle driver, feel free to do any changes inside it that do not affect other drivers. We should imo: convert all user input (strings with , or . based on current lang) to PHP floats us PHP floats when inserting/updating database
Hide
Permalink
Tim Hunt added a comment -

This is not user input. This is a value from a select element in a form.

Show
Tim Hunt added a comment - This is not user input. This is a value from a select element in a form.
Hide
Permalink
Petr Škoda added a comment - - edited

Anything coming from POST is a user input here - labels have to be localised for the user, the values shoudl be casted to proper PHP type.

Show
Petr Škoda added a comment - - edited Anything coming from POST is a user input here - labels have to be localised for the user, the values shoudl be casted to proper PHP type.
Hide
Permalink
Petr Škoda added a comment -

I guess the DML unittests should be improved first and then the driver + coding style and code should be updated. My vote goes to explicit casting of string decimals to PHP floats before storage to database. Fetching from database is yet another problem, I guess it is broken in Oracle driver too when some locale settings used there.

I think that HQ (or anybody else) should either provide test servers for each DB supported (all possible versions and configurations) or we have to stop pretending we are supporting Oracle and MSSQL...

Show
Petr Škoda added a comment - I guess the DML unittests should be improved first and then the driver + coding style and code should be updated. My vote goes to explicit casting of string decimals to PHP floats before storage to database. Fetching from database is yet another problem, I guess it is broken in Oracle driver too when some locale settings used there. I think that HQ (or anybody else) should either provide test servers for each DB supported (all possible versions and configurations) or we have to stop pretending we are supporting Oracle and MSSQL...
Hide
Permalink
Tim Hunt added a comment -

OK, reassigning to HQ. This is clearly a database/policy issue.

Show
Tim Hunt added a comment - OK, reassigning to HQ. This is clearly a database/policy issue.
Hide
Permalink
Matthias Then added a comment -

I had a similar Problem (Moodle 2.2.1, Oracle 11) and added the following lines of code in oci_native_moodle_database.php after line 214:
...
$sql1 = "ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'";
$this->query_start($sql1, null, SQL_QUERY_UPDATE);
$stmt1 = $this->parse_query($sql1);
$result = oci_execute($stmt1, $this->commit_status);
$this->query_end($result, $stmt1);
oci_free_statement($stmt1);
...
After I restarted my server it worked.
I think the problem was, that our database is configured for German language and so the NLS_NUMERIC_CHARACTERS is ',.' by default.

Show
Matthias Then added a comment - I had a similar Problem (Moodle 2.2.1, Oracle 11) and added the following lines of code in oci_native_moodle_database.php after line 214: ... $sql1 = "ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'"; $this->query_start($sql1, null, SQL_QUERY_UPDATE); $stmt1 = $this->parse_query($sql1); $result = oci_execute($stmt1, $this->commit_status); $this->query_end($result, $stmt1); oci_free_statement($stmt1); ... After I restarted my server it worked. I think the problem was, that our database is configured for German language and so the NLS_NUMERIC_CHARACTERS is ',.' by default.
Hide
Permalink
Michael de Raadt added a comment -

This looks like it has been hanging around for a bit.

Show
Michael de Raadt added a comment - This looks like it has been hanging around for a bit.

People

Vote (4)
Watch (5)

Dates

  • Created:
    Updated: