Issue: Cross-database foreign keys do not work
Author: conqp
State: closed
Created: 2014-10-20 12:55:23

Using the _ForeignKeyField_ inside a model with a _rel_model_ that's located inside another database than the aforementioned model peewee won't resolve the related model to its correct database.
As a result, tables containing foreign keys with related models of other databases cannot be created:

```
Traceback (most recent call last):
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 2406, in execute_sql
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/cursors.py", line 132, in execute
    result = self._query(query)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/cursors.py", line 271, in _query
    conn.query(q)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 744, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 879, in _read_query_result
    result.read()
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 1082, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 844, in _read_packet
    packet.check_error()
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 370, in check_error
    raise_mysql_exception(self._data)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/err.py", line 116, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/err.py", line 112, in _check_mysql_exception
    raise InternalError(errno, errorvalue)
pymysql.err.InternalError: (1005, "Can't create table 'realestatedb.realestate' (errno: 150)")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "./setup.py", line 22, in <module>
    table.create_table(fail_silently=True)
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 3078, in create_table
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 2471, in create_table
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 2414, in execute_sql
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 2283, in __exit__
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 105, in reraise
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 2406, in execute_sql
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/cursors.py", line 132, in execute
    result = self._query(query)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/cursors.py", line 271, in _query
    conn.query(q)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 744, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 879, in _read_query_result
    result.read()
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 1082, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 844, in _read_packet
    packet.check_error()
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 370, in check_error
    raise_mysql_exception(self._data)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/err.py", line 116, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/err.py", line 112, in _check_mysql_exception
    raise InternalError(errno, errorvalue)
peewee.InternalError: (1005, "Can't create table 'realestatedb.realestate' (errno: 150)")
```

And, if the databases were then manually created, it cannot assign the correct foreign key fields:

```
    Traceback (most recent call last):
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 2406, in execute_sql
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/cursors.py", line 132, in execute
    result = self._query(query)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/cursors.py", line 271, in _query
    conn.query(q)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 744, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 879, in _read_query_result
    result.read()
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 1082, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 844, in _read_packet
    packet.check_error()
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 370, in check_error
    raise_mysql_exception(self._data)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/err.py", line 116, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/err.py", line 112, in _check_mysql_exception
    raise InternalError(errno, errorvalue)
pymysql.err.InternalError: (1054, "Unknown column 't1.customer_id' in 'field list'")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 2139, in __iter__
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 2132, in execute
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 1838, in _execute
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 2414, in execute_sql
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 2283, in __exit__
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 105, in reraise
  File "/usr/lib/python3.4/site-packages/peewee-2.2.4-py3.4.egg/peewee.py", line 2406, in execute_sql
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/cursors.py", line 132, in execute
    result = self._query(query)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/cursors.py", line 271, in _query
    conn.query(q)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 744, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 879, in _read_query_result
    result.read()
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 1082, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 844, in _read_packet
    packet.check_error()
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/connections.py", line 370, in check_error
    raise_mysql_exception(self._data)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/err.py", line 116, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "/usr/lib/python3.4/site-packages/PyMySQL-0.6.2-py3.4.egg/pymysql/err.py", line 112, in _check_mysql_exception
    raise InternalError(errno, errorvalue)
peewee.InternalError: (1054, "Unknown column 't1.customer_id' in 'field list'")
```


What is the correct syntax for using cross-database relationships? I'm not familiar with them. Can you share some example SQL queries that work, so I can compare against the ones that don't?


On MySQL-level it works like that:

``` MySQL
create database db1;

use db1;

CREATE TABLE `historial` (
`codh` smallint(5) unsigned NOT NULL auto_increment,
PRIMARY KEY (`codh`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create database db2;

use db2;

CREATE TABLE t1 (
id tinyint(3) unsigned NOT NULL auto_increment,
micod smallint(5) unsigned default NULL,
PRIMARY KEY (id),
KEY micod (micod),
CONSTRAINT t1_ibfk_1 FOREIGN KEY (micod) REFERENCES db1.historial (codh) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE t2 (
id tinyint(3) unsigned NOT NULL auto_increment,
micod2 smallint(5) unsigned default NULL,
PRIMARY KEY (id),
KEY micod2 (micod2)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

alter table t2 add foreign key FK_t2(micod2) references db1.historial(codh) on delete cascade on update cascade; 
```

Taken from: http://forums.mysql.com/read.php?22,150829,200251#msg-200251


Sorry, I should have clarified my question -- what are some example SQL queries you want to execute but are currently not able to?


Oh, sorry. I obviously misunserstood you then.
I am trying to map real estates from our real estate database to our customers in our CRM database.
1) CRM database
DB definition: _homeinfodb.config_

``` python
DB = 'homeinfodb'
HOST = 'myusername'
USER = 'myuser'
PASSWD = 'mypasswd'

database = MySQLDatabase(DB, host=HOST, user=USER, passwd=PASSWD)
```

Base model: _homeinfodb.models.abc_

``` python
from peewee import Model
from ..config import database

class CRMModel(Model):
    """
    Generic HOMEINFO-DB Model
    """
    class Meta:
        database = database
```

The actual customer model: _homeinfodb.models.customer_

``` python
from .abc import CRMModel
from .company import Company
from peewee import ForeignKeyField, IntegerField

class Customer(CRMModel):
    """
    CRM's customer(s)
    """
    cid = IntegerField()
    """A unique customer ID"""
    company = ForeignKeyField(Company, related_name='customers')
    """A related company"""
    # TODO: Add other stuff like merchants etc.
```

(The foreign key field in customer is not the issue here!) 

2) The real estate database
Definition: _realestatedb.config_

``` python
from peewee import MySQLDatabase

DB = 'realestatedb'
HOST = 'myusername'
USER = 'myuser'
PASSWD = 'mypasswd'

database = MySQLDatabase(DB, host=HOST, user=USER, passwd=PASSWD)
```

Base model: _realestatedb.models.abc_

``` python
from ..config import database
from peewee import Model

class REDBModel(Model):
    class Meta:
        database = database
```

Realestate definition: _realestatedb.models.realestate_

``` python
from .abc import OIDBModel
from .log import ImportLog
from homeinfodb.models import Customer
from peewee import ForeignKeyField, TextField
# XXX: Rename import to avoid collision with property
import openimmo as openimmo_classbinding

class RealEstate(OIDBModel):
    """
    A table for real estate lists
    """
    customer = ForeignKeyField(Customer, related_name='realestates')
    """Foreign key mapping to the owning customer"""
    xml = TextField()
    """The OpenImmo XML text
    XXX: Must be of type 'VOLL'!"""
    last_import = ForeignKeyField(ImportLog)
    """Link to the last import"""

    @property
    def openimmo(self):
        """Returns an OpenImmo class binding"""
        return openimmo_classbinding.CreateFromDocument(self.xml)
```

Only the foreign key to the Customer (of the _homeinfodb_ package and in the other database) triggers an error here, when I try to create the latter database tables or try any type of selection after I created them manually. The stack traces are those of my initial post. If I don't use foreign keys on models within other databases, there is no problem whatsoever.

If you need more information, just let me know.


You might try using the `schema` model option. This is typically reserved for referencing tables that belong to a schema, but it might work for your use-case as well.

``` python
class CRMModel(Model):
    """
    Generic HOMEINFO-DB Model
    """
    class Meta:
        database = database
        schema = 'homeinfodb'

# ...


class REDBModel(Model):
    class Meta:
        database = database
        schema = 'realestatedb'
```


Please re-open if using `schema` does not resolve the issue.