SAWarning: Got 'None' querying 'table_name' from all_cons_columns - does the user have proper rights to the table?

[Sven A. Schmidt]

Getting this out of the way first, because I always forget ;) :
SQLAlchemy-0.5.5, Python 2.6

I'm getting the above error when trying to connect to an Oracle schema
'TEST' and read from another schema 'SAS' where tables are exposed via
synonyms (permissions have been granted) and I'm hoping that someone
on this list may be able to cast some light on what's going on. I've
googled for the error but only found the source where this exception
is being raised.

I've tried connecting with echo=True and running the SQL by hand from
the TEST account and the strange thing is I get no error but a list of
fields as I would expect (see below). First, here's the echo output of
my script:

2010-08-10 14:56:00,221 INFO sqlalchemy.engine.base.Engine.0x...4b90
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
DATA_SCALE, NULLABLE, DATA_DEFAULT from ALL_TAB_COLUMNS where
TABLE_NAME = :table_name and OWNER = :owner
INFO select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
DATA_SCALE, NULLABLE, DATA_DEFAULT from ALL_TAB_COLUMNS where
TABLE_NAME = :table_name and OWNER = :owner
2010-08-10 14:56:00,221 INFO sqlalchemy.engine.base.Engine.0x...4b90
{'owner': 'SAS', 'table_name': 'FORECASTS'}
INFO {'owner': 'SAS', 'table_name': 'FORECASTS'}
2010-08-10 14:56:00,230 INFO sqlalchemy.engine.base.Engine.0x...4b90
SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
-- order multiple primary keys correctly
ORDER BY ac.constraint_name, loc.position, rem.position
INFO SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
-- order multiple primary keys correctly
ORDER BY ac.constraint_name, loc.position, rem.position
2010-08-10 14:56:00,230 INFO sqlalchemy.engine.base.Engine.0x...4b90
{'owner': 'SAS', 'table_name': 'FORECASTS'}
INFO {'owner': 'SAS', 'table_name': 'FORECASTS'}
/Library/Python/2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/
sqlalchemy/engine/base.py:1265: SAWarning: Got 'None' querying
'table_name' from all_cons_columns - does the user have proper rights
to the table?
self.dialect.reflecttable(conn, table, include_columns)
2010-08-10 14:56:00,251 INFO sqlalchemy.engine.base.Engine.0x...4b90
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
DATA_SCALE, NULLABLE, DATA_DEFAULT from ALL_TAB_COLUMNS where
TABLE_NAME = :table_name and OWNER = :owner
INFO select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
DATA_SCALE, NULLABLE, DATA_DEFAULT from ALL_TAB_COLUMNS where
TABLE_NAME = :table_name and OWNER = :owner
2010-08-10 14:56:00,251 INFO sqlalchemy.engine.base.Engine.0x...4b90
{'owner': 'SAS', 'table_name': 'GROUPS'}

I guess the error message refers to the query with the parmeters
{'owner': 'SAS', 'table_name': 'FORECASTS'} (but I've also tried the
one after, in case the error precedes the query -- it looks the same).
So I ran the query:

SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = 'FORECASTS'
AND ac.constraint_type IN ('R','P')
AND ac.owner = 'SAS'
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
-- order multiple primary keys correctly
ORDER BY ac.constraint_name, loc.position, rem.position;

and got

"CONSTRAINT_NAME" "CONSTRAINT_TYPE" "LOCAL_COLUMN" "REMOTE_TABLE" "REMOTE_COLUMN" "REMOTE_OWNER"
"FK5E6775D8575C2425" "R" "FORECAST_STATUS_ID" "" "" ""
"FK5E6775D871226E5" "R" "SUB_GROUP_ID" "GROUPS" "ID" "SAS"
"FK5E6775D87C3474A6" "R" "PROGRAMME_ID" "PROGRAMME_CFG" "ID" "SAS"
"FK5E6775D88E98545F" "R" "APPROVAL_CYCLE_ID" "APPROVAL_CYCLES" "ID" "SAS"
"FK5E6775D896C4452F" "R" "BUSINESS_IMPACT_ID" "" "" ""
"FK5E6775D89A1530AE" "R" "DIVISION_ID" "DIVISIONS_CFG" "ID" "SAS"
"FK5E6775D89F6A3DA5" "R" "BUSINESS_JUSTIFICATION_ID" "" "" ""
"FK5E6775D8B85D0B4E" "R" "INITIATIVE_ID" "INITIATIVE_CFG" "ID" "SAS"
"FK5E6775D8FC3CABC6" "R" "GROUP_ID" "GROUPS" "ID" "SAS"
"SYS_C0057558" "P" "ID" "" "" ""

(I hope this is readable, here's the same in Google docs:
https://spreadsheets.google.com/ccc?key=0AgdbG5HyoweVdGZiN2RnM1JMY3hDSGpuZ09CVVdsbkE&hl=en&authkey=CKqIrcsL)

I'm connecting to the database with the schema='SAS' parameter on all
Table objects. I've done the same thing in the past (connecting to
another schema I read from) in SQLAlchemy (same version, 0.5.5) but
unfortunately the different scripts are hard to compare as such. They
do look very much alike from how they connect and obtain schema
information (but you're often blind to differences in your own code).
I'll probably have to strip them down further in the end to track this
down but this error looks like something more fundamental may be
wrong.

Does anyone on this list have an idea what I could try to investigate
further? I hope I've included all the relevant infos in this (quite
long, sorry!) mail. Let me know if I can provide anything else!

Cheers,
Sven

[Michael Bayer]

On Aug 10, 2010, at 9:28 AM, Sven A. Schmidt wrote:

> Getting this out of the way first, because I always forget ;) :
> SQLAlchemy-0.5.5, Python 2.6

I'll take a look at this later but you should probably be tracking down the issue in 0.6.3, assuming its still present - that's where we'd fix any issues.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Sven A. Schmidt]

Thanks, Michael. I wish I could update to 0.6.3 but unfortunately I'll
have to stick with the deployed version of 0.5.5 for now. But in any
case the good news is that this used to work once. It's just that
quite a few parameters are at work here so it may be difficult to
track down why it's failing now. Maybe your looking into it will give
the angle to see what really made this break.

I'm currently trying to avoid autoload=True (and thereby the failing
schema queries, I assume) by manually specifying all columns (it's
just 4-5 tables, fortunately). Maybe that'll help me work around the
issue. I'll report back once I know more.

Cheers,
Sven

> >https://spreadsheets.google.com/ccc?key=0AgdbG5HyoweVdGZiN2RnM1JMY3hD...)

[Sven A. Schmidt]

Michael,

I've found the problem when I stepped back and started from scratch.
It turns out that the error message is actually just a warning (and
I'm still getting it) but the schema reflection works despite this
message. I wrongly assumed this is why my script is failing but it's
actually due to this part of my schema declaration:

forecasts_line_items = Table('forecasts_line_items', metadata,
...
Column('line_item_id', Integer, ForeignKey('line_items.id')),
...
autoload=True, useexisting=True, schema=conf.schema
)

Due a problem with the schema I'm accessing -- it does not always have
FKs set up properly -- I needed to declared the FKs manually for sqla
to pick up the relations. It turns out that I need to write
'SAS.line_items.id' in this case (not sure if the upper case is really
needed but there was a problem with lower case schema prefixes in the
past). This didn't occur to me, because the schema prefix is
automatically applied in other cases, like table and sequence names.
Only when I went back to the bare minimum did I notice it starting to
fail when the ForeignKey declaration came in.

This is also the one difference to the old working code where all FKs
were in place and I never needed to specify ForeignKey(...)

Sorry for the noise,
Sven