Propel database:reverse generates empty foreign key attributes in schema

Question

I have a table which was created using the following syntax:

CREATE TABLE `Agreements` (
  `Agreement_ID` int(30) NOT NULL AUTO_INCREMENT,
  `Case_ID` int(11) NOT NULL,
  KEY `fk_case_id` (`Case_ID`),
  CONSTRAINT `fk_case_id` FOREIGN KEY (`Case_ID`) REFERENCES `cases` (`case_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When running propel database:reverse the generated schema looks like this:

<?xml version="1.0" encoding="utf-8"?>
<database name="xxx" defaultIdMethod="native" defaultPhpNamingMethod="underscore">
  <table name="Agreements" idMethod="native" phpName="Agreements">
    <column name="Agreement_ID" phpName="AgreementId" type="INTEGER" size="30" autoIncrement="true" required="true"/>
    <column name="Case_ID" phpName="CaseId" type="INTEGER" required="true"/>
    <foreign-key foreignTable="Cases" name="fk_case_id">
      <reference local="Case_ID" foreign=""/>
    </foreign-key>
    <index name="fk_case_id">
      <index-column name="Case_ID"/>
    </index>
    <vendor type="mysql">
      <parameter name="Engine" value="InnoDB"/>
    </vendor>
  </table>
</database>

As you can see, the foreign attribute of the foreign-key element is empty, which in turn prevents propel from generating the models based on this schema.

I'm a bit stuck here - any help would be appreciated

Answer 1

I found out what the problem is. For context I'm on Mac OS X and have lower_case_table_names set to 2.

Mixed case in the DDL

As you can see in the DDL I posted, case_id is lower case but is defined on the table as Case_ID. MySQL doesn't bother with this difference but this causes the Propel reverse engineering & model generation code to fail as it can't find the foreign key in the table definition.

What got me stuck here is that even if I manually changed the DDL to match the casing, no matter what, MySQL would still generate the DDL with the lower case version if I used the command SHOW CREATE TABLE Cases;.

MySQL versions

It seems MySQL 8.x - the version I was using in development - presents this odd behaviour. At this point this seems like a bug to me.

What I ended up doing is downgrade to 5.7 and re-create my database. MySQL 5.7 preserves the casing of the DDL you provide. This in turn allows Propel to do its thing and successfully update the schema as well as generate the models.

If anybody know why the two MySQL versions behave differently, I'd love to hear why.

This issue is now solved on my end. (though I'll need to look into it again if and when we upgrade to 8.x)