I decided to dig into the gem code and change the call to user_tab_cols to
see if it was the root of our problem conclusively. (This was in
lib/sequel/adapters/oracle.rb:276.) The results were perplexing, and we
decided to check again if this was actually a db-wide problem by fixing the
primary keys one-by-one. After I set the primary key manually on three
models, our app was working regardless of what tab_cols was used. In more
detail:
*metadata_dataset.from(:user_tab_cols) *
Every time that defaults is set in schema_parse_table, it is an empty hash
(because our Oracle user doesn't own anything)
App fails to start without force-setting primary keys, appears to function
after setting them.
DB.tables returns an empty array.
DB.schema.(:table_name) works.
Running queries on models works.
*metadata_dataset.from(:all_tab_cols) *(A table our resident Oracle expert
suggested)
App functions the same as with user_tab_cols, except that defaults hashes
contain data. (ex: {"NAME"=>nil, "ID"=>nil})
*metadata_dataset.from(:dba_tab_cols)*
App launches regardless of primary keys.
DB.tables returns an empty array.
DB.schema(:table_name) errs: Sequel::DatabaseError: OCIError: ORA-00942:
table or view does not exist
Running queries on models works.
When setting defaults, an exception is thrown: Sequel::DatabaseError:
OCIError: ORA-00942: table or view does not exist. It is not rescued, and
the rest of schema_parse_table is not run.
In other words, what I thought was a systemic problem was only a few
models, and we didn't notice it with the older configuration because
dba_tab_cols would generate exceptions that stop schema parsing before the
problem was actually found. Somehow, the app still functioned in spite of
this. Like the rest of the world, we cannot read dba_tab_cols, and trying
to do so makes Sequel fail in an unexpected, less visible way. I'd like to
recommend that user_tab_cols be replaced with all_tab_cols. According to this
document
<http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn20276.htm#REFRN20276>,
user_tab_cols only gives back tables the user owns, and all_tab_cols gives
back all tables accessible to the user, so the users that are happy with
user_tab_cols should still get all of the tables they're used to.
It is still unclear why the primary keys of three tables are being misread,
but we can force-set them in the meantime.
On Wednesday, July 9, 2014 5:11:55 PM UTC-7, Jeremy Evans wrote:
>
> On Wednesday, July 9, 2014 12:03:14 PM UTC-7, Chris Fincher wrote:
>>
>> Thanks for the quick response! Deal.primary_key is nil, but when I force
>> it to be :id, I get a different error where :merchant_id takes the place of
>> :deal_id. So I think rather than deals being the root of the problem, it's
>> just the first part being loaded and the first to break.
>>
>> Our Oracle expert has told me that she would indeed expect switching from
>> dba_tab_cols to user_tab_cols to break everything. dba_tab_cols has all of
>> the columns in the database, and user_tab_cols only has columns from the
>> tables that the user owns. This app connects to Oracle as a user that owns
>> no tables, so user_tab_cols is probably empty. Is there an option to go
>> back to the old behavior on this? Failing that, do you think it would be
>> especially dangerous to try to go in and override this?
>>
>
> OK. The reason behind the change is supposedly that dba_tab_cols requires
> superuser privileges to access. Not being an Oracle expert, I don't know
> if that is true or not, though from some googling, it does appear to be an
> issue at least in some configurations.
>
> I don't have an issue with making the table to use configurable, but I'm
> not sure why it would be causing the primary key issue, unless schema
> parsing is failing completely. The switch from dba_tab_cols to
> user_tab_cols only effects default parsing, not primary key parsing (which
> uses all_constraints and all_cons_columns).
>
> What does DB.schema(:table_name) return in your setup? If it doesn't
> raise an error, I'm not sure what the issue is, so you may have to do some
> manually troubleshooting (use an SQL logger, log all queries, issue each
> query manually and post the results).
>
> Note that if you want a quick fix until this issue is resolved, you can
> just call set_primary_key manually for all of your models.
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
