Error 1005 (errno 150) trying to create related tables using InnoDB

[Gerald Thibault]

I have a User class, and a Registration class with a FK to User.id.

When I try to create these on a db using InnoDB as default, I get this error:

sqlalchemy.exc.OperationalError: (OperationalError) (1005, "Can't create table 'test2.registrations' (errno: 150)") '\nCREATE TABLE test2.registrations (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tuser_id INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(user_id) REFERENCES user_vars (id)\n)\n\n' ()

If i copy this query and try it manually via MySQL Workbench, it still fails. 

If I prepend the default schema to the table (user_vars -> 'test.user_vars'), the query succeeds.

I'm not entirely sure what is happening here. In the absence of an explicit schema, I thought mysql used the active schema to handle table lookups, but it looks like this isn't the case. Is it 'switching' the active schema to the one hosting the new table? Also, this works perfectly with MyISAM tables, so I have even less to go on.

Any ideas? Do I need explicit schema declarations for every fk declared?

[Michael Bayer]

you might try asking this as a generic MySQL question on stackoverflow, I don't really know how MySQL does cross-schema work.  my rough understanding was "not much".

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
<innodb.py>

[Gerald Thibault]

To make it sqlalchemy specific, how do i cause generated CREATE statements to use absolute schema.table names for foreign key references?

I realized the reason MyISAM had no issue with it was because it ignores all those lines, so even if they were wrong (which they seem to be), it wouldn't care.

Here is the relevant output from SHOW ENGINE STATUS INNODB:

------------------------

LATEST FOREIGN KEY ERROR

------------------------

130821 13:22:18 Error in foreign key constraint of table test2/registrations:

FOREIGN KEY(user_id) REFERENCES user_vars (id)

)ENGINE=InnoDB:

Cannot resolve table name close to:

 (id)

)ENGINE=InnoDB

It seems the schema is mandatory when operating cross-schema.

Is there a way to get this behavior from sqlalchemy?

If MyISAM ignores the FK declarations, and InnoDB requires a schema in order to have cross-schema fk references, it seems like using schema.table format would fix this.

[Michael Bayer]

On Aug 21, 2013, at 5:37 PM, Gerald Thibault <diesel...@gmail.com> wrote:

To make it sqlalchemy specific, how do i cause generated CREATE statements to use absolute schema.table names for foreign key references?

you either need to specify "schema" in your referenced Table def, or you'd otherwise have to intercept the AddConstraint construct using @compiles to inject the schema name that you'd want there.

[Gerald Thibault]

I looked into the AddConstraint class, and added some debugging info and found it wasn't being hit, because the fk was part of a CREATE. So I'd need to use @compiles to override both of those to ensure both of them render the references correctly.

The single point where the magic happens is in compiler.py, in a function called 'define_constraint_remote_table'. Can I use @compiles to override that function?

Right now I have manually added the following to dialects.mysql.base.MySQLDDLCompiler:

    def define_constraint_remote_table(self, constraint, table, preparer):

        """Format the remote table clause of a CREATE CONSTRAINT clause.

           If using InnoDB, tables without manually-provided schemas will

           be formatted using the default connection schema when rendered

           in foreign key statements. This is because InnoDB (or perhaps 

           mysql?) will interpret references without schemas as being in the

           same schema as the table being created/altered """

           

        engine_key = '%s_engine' % self.dialect.name

        is_innodb = engine_key in table.kwargs and \

                    table.kwargs[engine_key].lower() == 'innodb'

        if is_innodb and table.schema is None:

            default_schema = table.bind.url.database

            constraint_schema = constraint.columns[0].table.schema

            if constraint_schema not in (default_schema, None):

                """ if the constraint schema is not the default, we need to 

                    add a schema before formatting the table """

                table.schema = default_schema

                value = preparer.format_table(table)

                table.schema = None

                return value

        return preparer.format_table(table)

This does exactly what I want it to, and ensures that in any cases where the referenced keys are in a different schema than the active table, the keys are prefixed by the schema.

Is there a way to 'inject' this into the MySQLDDLCompiler?

Would this version of the formatting function not be more appropriate than the current, as the current is unable to handle a simple 2 model relation when they are in different schemas?

[Michael Bayer]

if you specify a "name" and "use_alter=True" on your ForeignKey it should use the AddConstraint feature, which is easier to override.

>
> Would this version of the formatting function not be more appropriate than the current, as the current is unable to handle a simple 2 model relation when they are in different schemas?

first I'd want to fully understand and verify exactly what's going on with this, if you have some definitive documentation that refers to this it would be helpful. from there I'd probably still prefer to make this a table-level flag (I considered a dialect-level flag, but this is really part of "mysql_engine"'s behavior), particularly because we prefer CREATE TABLE statements to be correct for the target database even in the absence of a database connection.