read_schema may add non-existent columns or foreign keys for MySQL

[sammakkoinen]

In Yancy::Backend::Role::Relational, line 410, the query my $sth = $db->dbh->foreign_key_info( undef, undef, $foreign_table, undef, undef, $table ); does not specify schema names. The resulting sql looks like:

SELECT NULL AS PKTABLE_CAT,
       A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM,
       A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,
       A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,
       A.TABLE_CATALOG AS FKTABLE_CAT,
       A.TABLE_SCHEMA AS FKTABLE_SCHEM,
       A.TABLE_NAME AS FKTABLE_NAME,
       A.COLUMN_NAME AS FKCOLUMN_NAME,
       A.ORDINAL_POSITION AS KEY_SEQ,
       NULL AS UPDATE_RULE,
       NULL AS DELETE_RULE,
       A.CONSTRAINT_NAME AS FK_NAME,
       NULL AS PK_NAME,
       NULL AS DEFERABILITY,
       NULL AS UNIQUE_OR_PRIMARY
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A,
       INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
 WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL
 AND A.REFERENCED_TABLE_NAME = ? AND A.TABLE_NAME = ? ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION

so it can select items from another database, if it has tables with the same names as REFERENCED_TABLE_NAME and TABLE_NAME. Then, in line 433: $schema{ $table }{ properties }{ $column }{ 'x-foreign-key' } = $foreign_table; if $column does not exist, it gets added to $schema{ $table }{ properties } by autovivification, or non-existing foreign key may be added to the existing column. Imagine that we have two databases, both of which have tables named 'comment' and 'user', whose structure is different. We connect Yancy to database A, where these tables do not have common foreign keys. However in database B the table 'comment' has column 'author' that references user.id. This will result in adding 'author' key to the database A's schema and later DBD::mysql::st execute failed: Unknown column 'author' in 'field list' and internal server error when trying to open the table in the UI.

[preaction]

Agh. I should've been paying attention: The column_info and primary_key_info calls above have the correct catalog and schema arguments. I'll fix this right away, thanks for the report!