php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

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.3 ± 0.8
Reproduced:9 of 9 (100.0%)
Same Version:2 (22.2%)
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
View Developer Edit
 [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

Pull Requests

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 :-/
 [2021-02-04 01:15 UTC] andrewn at moodle dot com 
I'm just wondering why this bug is marked as "Suspended" when, 11 yeaers after it was raised, it is still a bug.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Sat Dec 21 13:01:31 2024 UTC