php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #49803 OCI statement cache causes ORA-01007
Submitted: 2009-10-07 16:29 UTC Modified: 2012-10-26 05:37 UTC
Votes:13
Avg. Score:4.2 ± 0.9
Reproduced:12 of 12 (100.0%)
Same Version:3 (25.0%)
Same OS:0 (0.0%)
From: stronk7 at moodle dot org Assigned:
Status: Suspended Package: OCI8 related
PHP Version: 5.2.11 OS: MacOS X (any)
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2009-10-07 16:29 UTC] stronk7 at moodle dot org
Description:
------------
Under certain circumstances (multiple DDL creation) OCI client statement 
cache causes ORA-01007: variable not in select list error in simple 
queries against those tables. Only disabling the cache ( 
oci8.statement_cache_size = 0 in php.ini, from default 20) alleviates 
the problem. Running with cache disabled has a big impact in oci 
performance.

It should be some explicit way to clean the cache from php oci or the 
driver itself should be "clever enough" to clean it when DDL statements 
are executed.

Reproduce code:
---------------
http://tracker.moodle.org/secure/attachment/18556/testing_oci_stmt_cache_pureoci.php

Expected result:
----------------
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
Dropped table unit_table (id, course). Ok

Actual result:
--------------
TESTING MOODLE 2.0 OCI DRIVER WITH oci8.statement_cache_size = 20 (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
Error selecting records from table!!
ORA-01007: variable not in select list
Dropped table unit_table (id, course). Ok

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2010-01-06 19:40 UTC] sixd@php.net
What's the testcase?
 [2010-01-06 19:42 UTC] sixd@php.net
Doh.  I've got it.
 [2011-11-09 22:28 UTC] sixd@php.net
I've logged an Oracle enhancement (#13366134) so this request can be better 
tracked by Oracle's normal enhancement process.
 [2011-11-09 22:28 UTC] sixd@php.net
-Status: Assigned +Status: Suspended
 [2012-10-26 05:37 UTC] sixd@php.net
-Assigned To: sixd +Assigned To:
 [2013-04-25 14:03 UTC] stronk7 at moodle dot org
Just to add that exactly the same problem continues happening both under latest 
php 5.3.x and 5.4.x, with he module compiled with the new x64 11.2.0.3.0 version 
for mac (previously using x32 10.2.0.4.0 with php 5.2).
 [2018-02-07 01:12 UTC] andrewn at moodle dot com
I've just confirmed that this is still an issue today with the following configuration:

root@f7d715e97f26:/var/www/html# php -v
PHP 7.1.9 (cli) (built: Sep  1 2017 20:31:12) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2017 Zend Technologies
    with Zend OPcache v7.1.9, Copyright (c) 1999-2017, by Zend Technologies


Relevant output of php -i:
oci8

OCI8 Support => enabled
OCI8 DTrace Support => disabled
OCI8 Version => 2.1.7
Revision => $Id: 93e7bce2a38c2b7ca3f595e60d92cba198cba14f $
Oracle Run-time Client Library Version => 12.1.0.2.0
Oracle Compile-time Instant Client Version => 12.1

Directive => Local Value => Master Value
oci8.connection_class => no value => no value
oci8.default_prefetch => 100 => 100
oci8.events => Off => Off
oci8.max_persistent => -1 => -1
oci8.old_oci_close_semantics => Off => Off
oci8.persistent_timeout => -1 => -1
oci8.ping_interval => 60 => 60
oci8.privileged_connect => Off => Off
oci8.statement_cache_size => 20 => 20


As before, setting the statement_cache_size to 0 fixes this issue but at a huge detriment to performance.
 [2018-08-29 12:49 UTC] s dot stuecher at mediadialog dot de
Reproducible with PHP 7.2.8 with statement_cache_size > 0
 [2020-05-11 15:38 UTC] stronk7 at moodle dot org
More than 10 years, still impossible to use statement caching with many DDL operations. Completely unbeilable, I'm afraid.

PHP could at very least allow the setting to be configured dynamically (maybe it's impossible, I don't know). Or warn whenever it's used under intense DDL ops.

And Oracle, well, I imagine enhancement #13366134 will be somewhere.

If you had to paid me for the processing time I've spent along these 10 years with the setting disabled... really.

I won't comment here again. Pathetic.

Ciao :-/
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Sat Oct 24 21:01:24 2020 UTC