oracle-enhanced: Strange error: OCIError: ORA-01008: not all variables bound
Steps to reproduce
first request after application start or restart.
Expected behavior
After migrating the application from Rails 6.1 to 7 I get this error
Actual behavior
The application use a legacy DB, so Arel is used most time to do complex SQL queries. If the first query after start is a query with UNION ALL, this query fail. If before that you run a simpler one, all is working as expected.
example of query (use ArelHelpers::QueryBuilder):
def cdpcomponents(neturef = nil, nveretu = nil, start_datetime = nil, include_transferred = nil, nhut = nil)
left_query = cdp_components1(neturef, nveretu, start_datetime, include_transferred, nhut)
right_query = cdp_components2(neturef, nveretu, start_datetime, include_transferred, nhut)
# crete a new relation
::Cmdmary::Etudprix.all.select(Arel.star)
.from("(#{left_query.query.to_sql} UNION ALL #{right_query.query.to_sql})")
.order('nhut, cmat')
end
it fail with:
OCIError: ORA-01008: not all variables bound
Array (163 elements)
stmt.c:265:in oci8lib_300.so
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/ruby-oci8-2.2.11-x64-mingw32/lib/oci8/cursor.rb:131:in `exec'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:146:in `exec'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:41:in `block (2 levels) in exec_query'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:276:in `block in with_retry'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:433:in `with_retry'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:101:in `with_retry'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:275:in `with_retry'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:26:in `block in exec_query'
During debuging, the ‘left_query.query.to_sql’ generate this error and the SQL string in the error is:
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.2') */ cols.column_name AS name,
cols.data_type AS sql_type,
cols.data_default,
cols.nullable,
cols.virtual_column,
cols.hidden_column,
cols.data_type_owner AS sql_type_owner,
DECODE(cols.data_type, 'NUMBER', data_precision, 'FLOAT', data_precision, 'VARCHAR2', DECODE(char_used, 'C', char_length, data_length), 'RAW', DECODE(char_used, 'C', char_length, data_length), 'CHAR', DECODE(char_used, 'C', char_length, data_length), NULL) AS limit,
DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale,
comments.comments as column_comment
FROM all_tab_cols cols,
all_col_comments comments
WHERE cols.owner = :owner
AND cols.table_name = :table_name
AND cols.hidden_column = 'NO'
AND cols.owner = comments.owner
AND cols.table_name = comments.table_name
AND cols.column_name = comments.column_name
ORDER BY cols.column_id
and the binds Array is correctly populated.
The log contain
Cmdmary::Etudprix Load (2.4ms) SELECT "ETUDPRIX".* FROM "ETUDPRIX" WHERE ROWNUM <= :a1 /* loading for inspect */ [["LIMIT", 11]]
↳ (eval):5:in `block in print_variable'
repeated 4 or more time.
The strangest thing: With rubymine if I put a breakpoint on the ::Cmdmary::Etudprix.all.select(Arel.star) … line,
- left_query.query.to_sql fail with the error but
- left_query.query don’t fail and after that
- left_query.query.to_sql return the correct sql! and the application work correctly until restart
If the first DB request is a more simpler SQL it also work and the fulll application is working as expected.
** the request use a connection to a shard (implemented wit a around_action block)
If i revert back to
rails (6.1.4.4)
activerecord-oracle_enhanced-adapter (6.1.5)
composite_primary_keys (13.0.3)
ruby-oci8 (2.2.10)
it’s working.
I don’t known how to continue to try to resolve this, so I request some help about this problem.
System configuration
Rails version: rails 7.0.2.3
Oracle enhanced adapter version: 7.0.2
Ruby version: ruby 3.0.3
Oracle Database version: oracle 10.2.0.1.0, instanclient 10.2.0.1 ** and arel-helpers (2.14.0), composite_primary_keys (14.0.4)
About this issue
- Original URL
- State: open
- Created 5 years ago
- Reactions: 1
- Comments: 18 (5 by maintainers)
Done. Thank you akostadinov.
A friend has my same versions of the related gems but oracle instant client 21.7. I may try downgrading instant client instead of the gems.
We fixed it using this monkey patch:
arelwould have been called anyways later on and is cached in@arelso there is no performance loss.I was surprised to find that there already exists a cache busting method in the schema loading code:
select_values_forcing_binds(arel, name, binds).A fix for this (in our case) was to just add one for select_all as well: