Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-14679 META: DB layer 2.0
  3. MDL-20339

OCI breaks badly with statement_cache enabled under certain situations



    • Sub-task
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • 2.0
    • 2.0
    • Database SQL/XMLDB
    • None
    • Oracle
    • Difficult


      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.




            stronk7 Eloy Lafuente (stronk7)
            stronk7 Eloy Lafuente (stronk7)
            Nobody Nobody
            Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Stevani Andolo
            1 Vote for this issue
            6 Start watching this issue