Moodle

OCI breaks badly with statement_cache enabled under certain situations

Details

  • Type: Sub-task Sub-task
  • Status: Closed Closed
  • Priority: Critical Critical
  • Resolution: Fixed
  • Affects Version/s: 2.0
  • Fix Version/s: 2.0
  • Component/s: Database SQL/XMLDB
  • Labels:
    None
  • Database:
    Oracle
  • Difficulty:
    Difficult
  • Affected Branches:
    MOODLE_20_STABLE
  • Fixed Branches:
    MOODLE_20_STABLE

Description

While developing the Moodle 2.0 oracle native driver both Petr and me have ended with some "random" bugs happening when executing 100% trivial queries in DB functional tests.

After some research, it seems that the creation/dropping/creation of one table multiple times with some difference in columns between both creations cause any "SELECT *" operation in the table to break badly with one not-self-explaining at all error ORA-01007: variable not in select list

After searching for that error, at all levels, I found some comments in one thread about OCI-C, commenting about cached metadata being the responsible of those errors, so... I set:

oci8.statement_cache_size = 0

in my php.ini file and then, all the problems magically disappeared. So it seems that the PHP driver is caching some statements and, if any change happens in the underlying tables (add/drop on field, or recreate the table with same name and other specs), the cache doesn't become invalidated and next cached query fails with the silly "ORA-01007: variable not in select list" error. At least the driver should give us some option to invalidate the cache (or do that automatically).

And I've reproduced it under PHP 5.2.10 running the latest OCI 1.2.5 driver

So I'm going to:

1) Create one PHP script, for use in Moodle 2.0 with oci_native_driver configured able to reproduce the problem.
2) Create one PHP script, for standalone use, using the low level oci driver able to reproduce the problem.
3) Send the standalone script to the PHP bugs.
4) Try to use a newer OCI driver (1.3.5 is current one) from PECL to see if the problem continues happening.
5) If no solution arrives, we'll need to check for the value of that setting (oci8.statement_cache_size), recommending 0 as the value to set. After all, in our non-persistent conections, that cache isn't supposed to help in a noticeable way, afaik.

Attachments

  1. File
    testing_oci_stmt_cache_moodle.php
    24/Sep/09 7:07 AM
    2 kB
    Eloy Lafuente (stronk7)
  2. File
    testing_oci_stmt_cache_pureoci.php
    08/Oct/09 12:17 AM
    3 kB
    Eloy Lafuente (stronk7)

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

I've attached one simple script (testing_oci_stmt_cache_moodle.php) able to reproduce the problem by forcing one table to be created, dropped and recreated with slightly different columns.

Please, if you've one Moodle 2.0 test server running against oracle with the native driver... execute the test and post results here.

Here there are my results:

With oci8.statement_cache_size = 20

  • Operating System: Darwin
  • PHP Version: 5.2.10
  • Database: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production, 10.2.0.1.0
  • OCI Version: (you can get it from the phpinfo page, OCI8 section)
  • oci8.statement_cache_size: 20
  • Test Result: ERROR! ORA-01007: variable not in select list - SELECT * FROM m_unit_table

With oci8.statement_cache_size = 0

  • Operating System: Darwin
  • PHP Version: 5.2.10
  • Database: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production, 10.2.0.1.0
  • OCI Version: (you can get it from the phpinfo page, OCI8 section)
  • oci8.statement_cache_size: 0
  • Test Result: OK!

TIA!

Show
Eloy Lafuente (stronk7) added a comment - I've attached one simple script (testing_oci_stmt_cache_moodle.php) able to reproduce the problem by forcing one table to be created, dropped and recreated with slightly different columns. Please, if you've one Moodle 2.0 test server running against oracle with the native driver... execute the test and post results here. Here there are my results: With oci8.statement_cache_size = 20
  • Operating System: Darwin
  • PHP Version: 5.2.10
  • Database: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production, 10.2.0.1.0
  • OCI Version: (you can get it from the phpinfo page, OCI8 section)
  • oci8.statement_cache_size: 20
  • Test Result: ERROR! ORA-01007: variable not in select list - SELECT * FROM m_unit_table
With oci8.statement_cache_size = 0
  • Operating System: Darwin
  • PHP Version: 5.2.10
  • Database: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production, 10.2.0.1.0
  • OCI Version: (you can get it from the phpinfo page, OCI8 section)
  • oci8.statement_cache_size: 0
  • Test Result: OK!
TIA!
Hide
Permalink
Martin Dougiamas added a comment -

I added Ashley, perhaps he can help organise some of Netspot's clients who are using Oracle to help in testing 2.0

Show
Martin Dougiamas added a comment - I added Ashley, perhaps he can help organise some of Netspot's clients who are using Oracle to help in testing 2.0
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Attaching pure oci php script able to reproduce the problem.

Show
Eloy Lafuente (stronk7) added a comment - Attaching pure oci php script able to reproduce the problem.
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

The bug has been reported to PHP at: http://bugs.php.net/49803

Show
Eloy Lafuente (stronk7) added a comment - The bug has been reported to PHP at: http://bugs.php.net/49803
Hide
Permalink
Christian Mueller-Boehm added a comment -

I have the same problem in our test environment (Redhat Enterprise 5.5, Oracle11G Cluster)
I tried to set the oci8.statement_cache_size = 0 but I get still reading errors from the database.
The errors are not random, I get "Error from reading database" when I try for example to open Site Administration Language settings or something else.
Every other reading of the database is very slow. I know there is a bug in the adodb, which makes moodle with oracle very slow, I don't know if this is the reason for the other problems.

The results of the scripts:

With oci8.statement_cache_size = 20:

  • Operating System: Linux RedHat enterprise 5.5
  • PHP Version: 5.2.12
  • Database: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    With the Partitioning, Real Application Clusters, OLAP, Data Mining
    and Real Application Testing options, 11.1.0.7.0
  • OCI Version: 1.4.1 Revision: 293235
  • oci8.statement_cache_size: 20
  • Test Result: ERROR! ORA-01007: variable not in select list - SELECT * FROM m_unit_table

With oci8.statement_cache_size = 0

TESTING MOODLE 2.0 OCI DRIVER WITH oci8.statement_cache_size = 0 (from php.ini)

Created table unit_table (id, course, name). Ok

Selected 0 records from table. Ok

Dropped table unit_table (id, course, name). Ok

Created table unit_table (id, course). Ok

Selected 0 records from table. Ok

TEST FINISHED OK!

Dropped table unit_table (id, course). Ok

Please report this information:

  • Operating System: Linux
  • PHP Version: 5.2.12
  • Database: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    With the Partitioning, Real Application Clusters, OLAP, Data Mining
    and Real Application Testing options, 11.1.0.7.0
  • OCI Version: 1.4.1 Revision: 293235
  • oci8.statement_cache_size: 0
  • Test Result: OK!
Show
Christian Mueller-Boehm added a comment - I have the same problem in our test environment (Redhat Enterprise 5.5, Oracle11G Cluster) I tried to set the oci8.statement_cache_size = 0 but I get still reading errors from the database. The errors are not random, I get "Error from reading database" when I try for example to open Site Administration Language settings or something else. Every other reading of the database is very slow. I know there is a bug in the adodb, which makes moodle with oracle very slow, I don't know if this is the reason for the other problems. The results of the scripts: With oci8.statement_cache_size = 20:
  • Operating System: Linux RedHat enterprise 5.5
  • PHP Version: 5.2.12
  • Database: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options, 11.1.0.7.0
  • OCI Version: 1.4.1 Revision: 293235
  • oci8.statement_cache_size: 20
  • Test Result: ERROR! ORA-01007: variable not in select list - SELECT * FROM m_unit_table
With oci8.statement_cache_size = 0 TESTING MOODLE 2.0 OCI DRIVER WITH oci8.statement_cache_size = 0 (from php.ini) Created table unit_table (id, course, name). Ok Selected 0 records from table. Ok Dropped table unit_table (id, course, name). Ok Created table unit_table (id, course). Ok Selected 0 records from table. Ok TEST FINISHED OK! Dropped table unit_table (id, course). Ok Please report this information:
  • Operating System: Linux
  • PHP Version: 5.2.12
  • Database: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options, 11.1.0.7.0
  • OCI Version: 1.4.1 Revision: 293235
  • oci8.statement_cache_size: 0
  • Test Result: OK!
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Hi Christian,

the problem (bug) with oci8.statement_cache_size enable has been already notified to PHP (http://bugs.php.net/49803).

In any case, it shouldn't affect normal Moodle operations, in fact I detected it only when running DB unit tests, where we use to create and drop many times the same table. And that only happens in unit tests.

So I'd say that it is 99.9% safe to run Moodle with the statement cache enabled. In fact that will speed-up your site, as far as there are a lot of queries that are repeated on each request.

Also, note that this bug always produce the error: "ORA-01007: variable not in select list", so if you are getting any other error, please report it in a new issue in the Tracker.

I've tried the Admin->Language settings page here running Oracle and cannot reproduce any problem. Can you, please, enable DEBUG_DEVELOPER in Admin->Development->Debugging, then try the page again and post complete details of the error in new bug? TIA!

Ciao

Show
Eloy Lafuente (stronk7) added a comment - Hi Christian, the problem (bug) with oci8.statement_cache_size enable has been already notified to PHP (http://bugs.php.net/49803). In any case, it shouldn't affect normal Moodle operations, in fact I detected it only when running DB unit tests, where we use to create and drop many times the same table. And that only happens in unit tests. So I'd say that it is 99.9% safe to run Moodle with the statement cache enabled. In fact that will speed-up your site, as far as there are a lot of queries that are repeated on each request. Also, note that this bug always produce the error: "ORA-01007: variable not in select list", so if you are getting any other error, please report it in a new issue in the Tracker. I've tried the Admin->Language settings page here running Oracle and cannot reproduce any problem. Can you, please, enable DEBUG_DEVELOPER in Admin->Development->Debugging, then try the page again and post complete details of the error in new bug? TIA! Ciao
Hide
Permalink
Christian Mueller-Boehm added a comment -

Hi Eloy,

ok, it was an error of the installation. With a new moodle_latest it works.

Thank you

Christian

Show
Christian Mueller-Boehm added a comment - Hi Eloy, ok, it was an error of the installation. With a new moodle_latest it works. Thank you Christian
Hide
Permalink
Martin Dougiamas added a comment -

I'm assuming this is fixed then. Please reopen if not.

Show
Martin Dougiamas added a comment - I'm assuming this is fixed then. Please reopen if not.

People

Vote (1)
Watch (6)

Dates

  • Created:
    Updated:
    Resolved:
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.