Opened 18 years ago

Closed 16 years ago

#3642 closed (wontfix)

[multi-db] Filters with nested foreign keys across databases fail

Reported by: Carole Zieler <carole.zieler@…> Owned by: nobody
Component: Database layer (models, ORM) Version: other branch
Severity: Keywords: multi-db
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

If you have two or more databases:

DATABASE_ENGINE = 'mysql' # 'postgresql', 'mysql', 'sqlite3' or 'ado_mssql'.
DATABASE_NAME = 'djangodb' # Or path to database file if using sqlite3.
DATABASE_USER = 'root' # Not used with sqlite3.
DATABASE_PASSWORD = # Not used with sqlite3.
DATABASE_HOST = 'somehost' # Set to empty string for localhost. Not used with sqlite3.
DATABASE_PORT = # Set to empty string for default. Not used with sqlite3.

OTHER_DATABASES = {

'mortgagedb': {

'DATABASE_NAME': 'mortgagedb',
'DATABASE_USER': 'root',
'DATABASE_PASSWORD': ,
'MODELS': mortgagedb

},
'userdb': {

'DATABASE_NAME': 'userdb',
'DATABASE_USER': 'root',
'DATABASE_PASSWORD': ,
'MODELS': userdb

},

.
.
.

Then you have models that look like this:

Users Model:
class Company(models.Model):

company_id = models.AutoField(primary_key=True)
company_name = models.CharField(blank=True, maxlength=150)
....other properties

class UserInfo(models.Model):

user_id = models.AutoField(primary_key=True)
login = models.CharField(blank=True, maxlength=765)
first_name = models.CharField(blank=True, maxlength=60)
middle_name = models.CharField(blank=True, maxlength=60)
last_name = models.CharField(blank=True, maxlength=60)
company = models.ForeignKey(Company,related_name="company_users")

Loans Model:
(import users model up top)
class Mortgage(models.Model):

mortgage_id = models.AutoField(primary_key=True)
property = models.ForeignKey(Property)
obligor_user = models.ForeignKey(UserInfo, null=True)

Then in the views if you try to do this:

paramsnew = {}
paramsnewobligor_user__company__company_name= request.POSTcompany_name # istartswith fails too, as well as trying to do company ids with in
mortgages = Mortgage.objects.filter(params).distinct() #Mortgage.objects.filter(params) fails too

You get the following error:
Exception Value: (1146, "Table 'mortgagedb.user_info' doesn't exist")
Exception Location: /usr/lib/python2.4/site-packages/MySQLdb/connections.py in defaulterrorhandler, line 33
Traceback (innermost last)
Switch to copy-and-paste view

  • /usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/core/handlers/base.py in get_response
    1. # Apply view middleware
    2. for middleware_method in self._view_middleware:
    3. response = middleware_method(request, callback, callback_args, callback_kwargs)
    4. if response:
    5. return response 72.
    6. try:
    7. response = callback(request, *callback_args, callback_kwargs) ...
    8. except Exception, e:
    9. # If the view raised an exception, run it through exception
    10. # middleware, and if the exception middleware returns a
    11. # response, use that. Otherwise, reraise the exception.
    12. for middleware_method in self._exception_middleware:
    13. response = middleware_method(request, e)
    ▶ Local vars Variable Value callback <function rendermortgageadmin at 0xb6d5c3e4> callback_args () callback_kwargs {} e <_mysql_exceptions.ProgrammingError instance at 0xb6c85f4c> exceptions <module 'django.core.exceptions' from '/usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/core/exceptions.pyc'> mail_admins <function mail_admins at 0xb7196fb4> middleware_method <bound method TransactionMiddleware.process_exception of <django.middleware.transaction.TransactionMiddleware object at 0xb705c82c>> path '/rsswww/mortgageadmin/' request <ModPythonRequest path:/rsswww/mortgageadmin/, GET:<MultiValueDict: {}>, POST:<MultiValueDict: {'city': [''], 'zip': [''], 'filter': ['1'], 'state': [''], 'company_name': ['Trinity'], 'endmortgageamount': [''], 'startmortgageamount': ['']}>, COOKIES:{'sessionid': '61fc326c056ad6757f419441d9ac599f'}, META:{'AUTH_TYPE': None, 'CONTENT_LENGTH': 0L, 'CONTENT_TYPE': None, 'GATEWAY_INTERFACE': 'CGI/1.1', 'HTTP_ACCEPT': 'text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5', 'HTTP_ACCEPT_CHARSET': 'ISO-8859-1,utf-8;q=0.7,*;q=0.7', 'HTTP_ACCEPT_LANGUAGE': 'en-us,en;q=0.5', 'HTTP_CONNECTION': 'close', 'HTTP_CONTENT_LENGTH': '87', 'HTTP_CONTENT_TYPE': 'application/x-www-form-urlencoded', 'HTTP_COOKIE': 'sessionid=61fc326c056ad6757f419441d9ac599f', 'HTTP_HOST': '192.168.1.71', 'HTTP_USER_AGENT': 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.9) Gecko/20050711 Firefox/1.0.5', 'HTTP_X_FORWARDED_FOR': '66.177.134.144', 'HTTP_X_FORWARDED_HOST': 'dev.realsecuritizationsystems.com', 'HTTP_X_FORWARDED_SERVER': 'dev.realsecuritizationsystems.com', 'HTTP_~': ':~', 'HTTP_~': '', 'PATH_INFO': '/mortgageadmin/', 'PATH_TRANSLATED': None, 'QUERY_STRING': None, 'REMOTE_ADDR': '192.168.1.111', 'REMOTE_HOST': None, 'REMOTE_IDENT': None, 'REMOTE_USER': None, 'REQUEST_METHOD': 'POST', 'SCRIPT_NAME': None, 'SERVER_NAME': 'rssws1d.realdatacenter.com', 'SERVER_PORT': 0, 'SERVER_PROTOCOL': 'HTTP/1.1', 'SERVER_SOFTWARE': 'mod_python'}> resolver <django.core.urlresolvers.RegexURLResolver object at 0xb705c7ec> response None self <django.core.handlers.modpython.ModPythonHandler object at 0xb724dfcc> settings <django.conf.LazySettings object at 0xb7202acc> urlresolvers <module 'django.core.urlresolvers' from '/usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/core/urlresolvers.pyc'>
  • /home/carole/rssproject/rsswww/views/mortgages.py in rendermortgageadmin
    1. paramsappraiser_user__user_id = current_user.user_id 130.
    2. # do they need to filter the list further?
    3. count = 0
    4. if request.session.get("SortMortgageListBy"):
    5. count = Mortgage.objects.filter(params).order_by(request.session.get("SortMortgageListBy")).distinct().count()
    6. else:
    7. count = Mortgage.objects.filter(params).distinct().count() ... 137.
    8. if count > 300:
    9. specialtitle="View A Filtered List Of Mortgages:"
    10. # we want to have them filter the list down some..this number is too much to look at anyway
    11. return render_to_response('mortgageadmin.html',{'mortgagecount':count,'displayfilter':"yes",'specialtitle':specialtitle,'current_role':current_role,'company_name':company_name,'city':city,'state':state,'zip':zip,'menu':menu},context_instance=RequestContext(request))
    12. else:
    ▶ Local vars Variable Value add 1 city company_name count 0 current_role 'ORIGINATOR' current_user <UserInfo: UserInfo object> delete 1 edit 1 menu '<ul id="primary-nav"> <li style="width:110px;"><a href="/rsswww/">Home</a></li> <li class="menuparent"><a href="#" >My Account</a><ul><li><a href="/rsswww/changemypassword">Change Password</a></li><li><a href="/rsswww/myprofile">My Profile</a></li><li><a href="/rsswww/choosereport">My Reports</a></li></ul></li><li class="menuparent" style="width:130px;"><a href="#">Companies/People</a><ul><li><a href="/rsswww/companyadmin">Companies</a></li><li><a href="/rsswww/useradmin">People</a></li></ul></li><li class="menuparent" style="width:120px;"><a href="#">Securitization</a><ul><li><a href="/rsswww/mortgageadmin">Admin Mortgages</a></li></ul></li><li><a href="/rsswww/logout">Logout</a></li></ul>' obligor_company <Company: Trinity Property Investments, Inc.> params {'obligor_usercompanycompany_name': 'Trinity', 'originator_useruser_id': 50001L} paramsnew {'obligor_usercompanycompany_name': 'Trinity', 'originator_useruser_id': 50001L} privilegelist ['ADD MORTGAGE', 'EDIT MORTGAGE', 'UPLOAD MORTGAGE DOCUMENTS', 'CREATE PROPERTY BUDGET', 'APPROVE/REJECT PROPERTY BUDGET', 'CREATE CONSTRUCTION DRAW', 'APPROVE/REJECT DRAW', 'VIEW USER NOTES', 'ADD/EDIT USER NOTES', 'VIEW USER DOCUMENTS', 'ADD/EDIT/REMOVE USER DOCUMENTS', 'VIEW COMPANY NOTES', 'ADD/EDIT COMPANY NOTES', 'VIEW COMPANY DOCUMENTS', 'ADD/EDIT/REMOVE COMPANY DOCUME', 'APPROVE MORTGAGE DOCUMENT', 'RESTRICTED COMPANY/PEOPLE ADMI'] request <ModPythonRequest path:/rsswww/mortgageadmin/, GET:<MultiValueDict: {}>, POST:<MultiValueDict: {'city': [''], 'zip': [''], 'filter': ['1'], 'state': [''], 'company_name': ['Trinity'], 'endmortgageamount': [''], 'startmortgageamount': ['']}>, COOKIES:{'sessionid': '61fc326c056ad6757f419441d9ac599f'}, META:{'AUTH_TYPE': None, 'CONTENT_LENGTH': 0L, 'CONTENT_TYPE': None, 'GATEWAY_INTERFACE': 'CGI/1.1', 'HTTP_ACCEPT': 'text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5', 'HTTP_ACCEPT_CHARSET': 'ISO-8859-1,utf-8;q=0.7,*;q=0.7', 'HTTP_ACCEPT_LANGUAGE': 'en-us,en;q=0.5', 'HTTP_CONNECTION': 'close', 'HTTP_CONTENT_LENGTH': '87', 'HTTP_CONTENT_TYPE': 'application/x-www-form-urlencoded', 'HTTP_COOKIE': 'sessionid=61fc326c056ad6757f419441d9ac599f', 'HTTP_HOST': '192.168.1.71', 'HTTP_USER_AGENT': 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.9) Gecko/20050711 Firefox/1.0.5', 'HTTP_X_FORWARDED_FOR': '66.177.134.144', 'HTTP_X_FORWARDED_HOST': 'dev.realsecuritizationsystems.com', 'HTTP_X_FORWARDED_SERVER': 'dev.realsecuritizationsystems.com', 'HTTP_~': ':~', 'HTTP_~': '', 'PATH_INFO': '/mortgageadmin/', 'PATH_TRANSLATED': None, 'QUERY_STRING': None, 'REMOTE_ADDR': '192.168.1.111', 'REMOTE_HOST': None, 'REMOTE_IDENT': None, 'REMOTE_USER': None, 'REQUEST_METHOD': 'POST', 'SCRIPT_NAME': None, 'SERVER_NAME': 'rssws1d.realdatacenter.com', 'SERVER_PORT': 0, 'SERVER_PROTOCOL': 'HTTP/1.1', 'SERVER_SOFTWARE': 'mod_python'}> sort specialtitle 'Your Current Mortgages' state zip
  • /usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/db/models/query.py in count
    1. counter._limit = None
    2. counter._select_related = False
    3. select, sql, params = counter._get_sql_clause()
    4. cursor = connection.cursor()
    5. if self._distinct:
    6. id_col = "%s.%s" % (backend.quote_name(self.model._meta.db_table),
    7. backend.quote_name(self.model._meta.pk.column))
    8. cursor.execute("SELECT COUNT(DISTINCT(%s))" % id_col + sql, params) ...
    9. else:
    10. cursor.execute("SELECT COUNT(*)" + sql, params)
    11. return cursor.fetchone()[0] 206.
    12. def get(self, *args, kwargs):
    13. "Performs the SELECT and returns a single object matching the given keyword arguments."
    ▶ Local vars Variable Value backend <module 'django.db.backends.mysql.base' from '/usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/db/backends/mysql/base.pyc'> connection <django.db.backends.mysql.base.DatabaseWrapper object at 0xb6c859cc> counter Error in formatting:(1146, "Table 'mortgagedb.user_info' doesn't exist") cursor <django.db.backends.util.CursorDebugWrapper object at 0xb6c85cac> db Connection: <django.db.backends.mysql.base.DatabaseWrapper object at 0xb6c859cc> (ENGINE=mysql NAME=mortgagedb) id_col 'mortgage.mortgage_id' params ['Trinity', 50001L] select ['mortgage.mortgage_id', 'mortgage.property_id', 'mortgage.user_id', 'mortgage.credit_score', 'mortgage.ltarv', 'mortgage.ltv_current', 'mortgage.ltv_after', 'mortgage.mortgage_amount', 'mortgage.estimated_months', 'mortgage.obligor_user_id', 'mortgage.obligor_company_id', 'mortgage.receiver_company_id', 'mortgage.closing_agent_user_id', 'mortgage.closing_agent_company_id', 'mortgage.mortgage_broker_user_id', 'mortgage.mortgage_broker_company_id', 'mortgage.title_company_id', 'mortgage.title_company_user_id', 'mortgage.assignment_date', 'mortgage.letter_date', 'mortgage.origination_date', 'mortgage.cancellation_date', 'mortgage.fail_to_close_date', 'mortgage.mature_date', 'mortgage.payoff_date', 'mortgage.completion_date', 'mortgage.life_of_loan', 'mortgage.originator_user_id', 'mortgage.originator_company_id', 'mortgage.escrow', 'mortgage.loan_amount', 'mortgage.payoff_amount', 'mortgage.appraisal', 'mortgage.loan_to_value', 'mortgage.points', 'mortgage.points_dollars', 'mortgage.interest_rate', 'mortgage.interest_amount', 'mortgage.cost_of_credit_yearly_rate', 'mortgage.finance_charge', 'mortgage.number_of_payments', 'mortgage.total_payments', 'mortgage.daily_interest', 'mortgage.days_of_interest', 'mortgage.deferred_interest', 'mortgage.total_interest', 'mortgage.due_date', 'mortgage.first_payment_month_year', 'mortgage.last_payment_month_year', 'mortgage.update_user_id', 'mortgage.address_id', 'mortgage.time_stamp'] self Error in formatting:(1146, "Table 'mortgagedb.user_info' doesn't exist") sql ' FROM mortgage INNER JOIN user_info AS mortgage__obligor_user ON mortgage.obligor_user_id = mortgage__obligor_user.user_id INNER JOIN company AS mortgage__obligor_user__company ON mortgage__obligor_user.company_id = mortgage__obligor_user__company.company_id WHERE (mortgage__obligor_user__company.company_name = %s AND mortgage.originator_user_id = %s)'
  • /usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/db/backends/util.py in execute
    1. def init(self, cursor, db):
    2. self.cursor = cursor
    3. self.db = db 8.
    4. def execute(self, sql, params=()):
  1. start = time()
  2. try:
  3. return self.cursor.execute(sql, params) ...
  4. finally:
  5. stop = time()
  6. # If params was a list, convert it to a tuple, because string
  7. # formatting with '%' only works with tuples or dicts.
  8. if not isinstance(params, (tuple, dict)):
  9. params = tuple(params)

▶ Local vars
Variable Value
params
('Trinity', 50001L)
self
<django.db.backends.util.CursorDebugWrapper object at 0xb6c85cac>
sql
'SELECT COUNT(DISTINCT(mortgage.mortgage_id)) FROM mortgage INNER JOIN user_info AS mortgage__obligor_user ON mortgage.obligor_user_id = mortgage__obligor_user.user_id INNER JOIN company AS mortgage__obligor_user__company ON mortgage__obligor_user.company_id = mortgage__obligor_user__company.company_id WHERE (mortgage__obligor_user__company.company_name = %s AND mortgage.originator_user_id = %s)'
start
1172897283.3177619
stop
1172897283.3196101

  • /usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/db/backends/mysql/base.py in execute
    1. # It's only used when DEBUG=True.
    2. class MysqlDebugWrapper:
    3. def init(self, cursor):
    4. self.cursor = cursor 32.
    5. def execute(self, sql, params=()):
    6. try:
    7. return self.cursor.execute(sql, params) ...
    8. except Database.Warning, w:
    9. self.cursor.execute("SHOW WARNINGS")
    10. raise Database.Warning, "%s: %s" % (w, self.cursor.fetchall()) 39.
    11. def executemany(self, sql, param_list):
    12. try:
    ▶ Local vars Variable Value params ['Trinity', 50001L] self <MySQLdb.cursors.Cursor object at 0xb6c85a2c> sql 'SELECT COUNT(DISTINCT(mortgage.mortgage_id)) FROM mortgage INNER JOIN user_info AS mortgage__obligor_user ON mortgage.obligor_user_id = mortgage__obligor_user.user_id INNER JOIN company AS mortgage__obligor_user__company ON mortgage__obligor_user.company_id = mortgage__obligor_user__company.company_id WHERE (mortgage__obligor_user__company.company_name = %s AND mortgage.originator_user_id = %s)'
  • /usr/lib/python2.4/site-packages/MySQLdb/cursors.py in execute
    1. else:
    2. self.messages.append((TypeError, m))
    3. self.errorhandler(self, TypeError, m)
    4. except:
    5. exc, value, tb = exc_info()
    6. del tb
    7. self.messages.append((exc, value))
    8. self.errorhandler(self, exc, value) ...
    9. self._executed = query
    10. self._warning_check()
    11. return r 141.
    12. def executemany(self, query, args):

143.

▶ Local vars
Variable Value
ListType
<type 'list'>
TupleType
<type 'tuple'>
args
['Trinity', 50001L]
exc
<class _mysql_exceptions.ProgrammingError at 0xb6d3cbfc>
exc_info
<built-in function exc_info>
query
'SELECT COUNT(DISTINCT(mortgage.mortgage_id)) FROM mortgage INNER JOIN user_info AS mortgage__obligor_user ON mortgage.obligor_user_id = mortgage__obligor_user.user_id INNER JOIN company AS mortgage__obligor_user__company ON mortgage__obligor_user.company_id = mortgage__obligor_user__company.company_id WHERE (mortgage__obligor_user__company.company_name = %s AND mortgage.originator_user_id = %s)'
self
<MySQLdb.cursors.Cursor object at 0xb6c85a2c>
value
<_mysql_exceptions.ProgrammingError instance at 0xb6c85f4c>

  • /usr/lib/python2.4/site-packages/MySQLdb/connections.py in defaulterrorhandler

26.

  1. """
  2. error = errorclass, errorvalue
  3. if cursor:
  4. cursor.messages.append(error)
  5. else:
  6. connection.messages.append(error)
  7. raise errorclass, errorvalue ... 34. 35.
  8. class Connection(_mysql.connection): 37.
  9. """MySQL Database Connection Object"""

39.

▶ Local vars

Change History (5)

comment:1 by Carole Zieler <carole.zieler@…>, 18 years ago

Summary: [multip-db] Filters with nested foreign keys across databases fail → [multi-db] Filters with nested foreign keys across databases fail

comment:2 by Carole Zieler <carole.zieler@…>, 18 years ago

Sorry... I haven't pasted a code block in here before...let me do that the correct way...I'll preview from now on ;)

Settings file:

DATABASE_ENGINE = 'mysql'           # 'postgresql', 'mysql', 'sqlite3' or 'ado_mssql'.
DATABASE_NAME = 'djangodb'             # Or path to database file if using sqlite3.
DATABASE_USER = 'root'             # Not used with sqlite3.
DATABASE_PASSWORD = ''         # Not used with sqlite3.
DATABASE_HOST = 'somehost'             # Set to empty string for localhost. Not used with sqlite3.
DATABASE_PORT = ''             # Set to empty string for default. Not used with sqlite3.


OTHER_DATABASES = {
    'mortgagedb': {
           'DATABASE_NAME': 'mortgagedb',
           'DATABASE_USER': 'root',
           'DATABASE_PASSWORD': '',
           'MODELS': ['mortgagedb']
    },
    'userdb': {
           'DATABASE_NAME': 'userdb',
           'DATABASE_USER': 'root',
           'DATABASE_PASSWORD': '',
           'MODELS': ['userdb']
    },
.
.
.

Models Code:

Users Model:
class Company(models.Model):
    company_id = models.AutoField(primary_key=True)
    company_name = models.CharField(blank=True, maxlength=150)
    ....other properties

class UserInfo(models.Model):
    user_id = models.AutoField(primary_key=True)
    login = models.CharField(blank=True, maxlength=765)
    first_name = models.CharField(blank=True, maxlength=60)
    middle_name = models.CharField(blank=True, maxlength=60)
    last_name = models.CharField(blank=True, maxlength=60)
    company = models.ForeignKey(Company,related_name="company_users")


Loans Model:
(import users model up top)
class Mortgage(models.Model):
    mortgage_id = models.AutoField(primary_key=True)
    property = models.ForeignKey(Property)
    obligor_user = models.ForeignKey(UserInfo,  null=True)

View Code

paramsnew = {}
paramsnew['obligor_user__company__company_name']= request.POST['company_name'] # __istartswith fails too, as well as trying to do company ids with __in
mortgages = Mortgage.objects.filter(**params).distinct() #Mortgage.objects.filter(**params) fails too

comment:3 by Simon G. <dev@…>, 18 years ago

Keywords: multi-db added
Triage Stage: Unreviewed → Accepted
Version: SVN → other branch

comment:4 by ben, 17 years ago

This sounds exactly like the issue I had, it's to do with the select clause being in one SQL dialect and some other stuff being in another (sorry for being vague I don't have the details to hand). ​Ticket 3885 has some more detail with some preliminary findings into causes.

comment:5 by Malcolm Tredinnick, 16 years ago

Resolution: → wontfix
Status: new → closed

The multi-db is no longer active. Alternative approaches are being considered for trunk.

Note: See TracTickets for help on using tickets.
Back to Top