On Aug 11, 2011, at 2:04 PM, Daniel Robbins wrote:
> Hi there,
>
> I have been using ORM vertical partitioning for a while with MySQL 5.1.58 and
> MyISAM tables and SQLAlchemy 0.7.2.
>
> I have recently switched over to using InnoDB tables and my vertical
> partitioning table creation is failing part-way in with an "errno 105" code.
> I have confirmed that all the tables that *did* get created are using an
> InnoDB engine, so having stray MyISAM tables does not appear to be the
> problem.
>
> I have found a fix for the SQL that SQLAlchemy generates (and that MySQL
> barfs on) that allows the table creation to succeed, which involves simply
> prefixing SQLAlchemy's CREATE TABLE foreign key references with the database
> name. SQLAlchemy generates this line below, which also fails with errno 105
> when I paste it into MySQL monitor, just like when executed by SQLA directly:
>
> FOREIGN KEY(project_id) REFERENCES projects (id)
>
> When I change it to this, table creation succeeds in mySQL monitor:
>
> FOREIGN KEY(project_id) REFERENCES car_res.projects (id)
>
> Basically, "ForeignKey("projects.id") seems sufficient for SQLA to define the
> foreign key relationship, but MySQL seems to be wanting
> "car_res.projects(id)" to appear in the FOREIGN KEY SQL, instead of
> "projects(id)".
>
> So I'm a bit confused. Is SQLA to blame for not including this prefix in
> vertical partitioning table creation scenarios, or is there some bug in my
> code somewhere that is causing MySQL to barf or SQLA to generate incorrect
> SQL? I have some basic vertical partitioning test code that uses InnoDB with
> two tables, and for my basic test, it seems like the "database." prefix is
> *not* required by MySQL to successfully create tables.
>
> Anyone have any idea about what might be going on?
Vertical partitioning implies tables in separate databases or schemas. It
seems like you have it set up such that a table in partition A can refer to a
table in partition B using a schema qualifier. So if this is the case you'd
use the "schema" argument on each Table to establish these names, when you do
your CREATE, as well as schema-qualify the ForeignKey:
t1 = Table("table_a", metadata, Column('id', Integer, primary_key=True),
schema="schema_a")
t2 = Table("table_b", metadata, Column('table_a_id', Integer,
ForeignKey("schema_a.table_a.id")), schema="schema_b")
Another option would be to forego the usage of ForeignKey for table
relationships that span across two partitions, at least when CREATE TABLE is
emitted. This is effectively what you were doing when you were on MyISAM,
since REFERENCES is a no-op on MyISAM - it just gets thrown away hence you had
no error.
This is total conjecture since I don't know the details here nor do I know
MySQL's performance characteristics very deeply, but the existence of actual
cross-schema foreign key constraints in the MySQL database may be a performance
issue, if writing to table B means a disk operation on an entirely separate
schema for table A must occur in order to insert or update a row.
> ? I have some basic vertical partitioning test code that uses InnoDB with two
> tables, and for my basic test, it seems like the "database." prefix is *not*
> required by MySQL to successfully create tables.
this part is confusing since you said earlier that "REFERENCES projects (id)"
emits errno 105 when InnoDB is in use. What is the exact CREATE TABLE syntax
you would like to see ?
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
