Foreign Key missing on One to One relationship (Mysql)

[tacone]

Given this schema:

<table name="submission">
        <column name="id" required="true" primaryKey="true"
            autoIncrement="true" type="INTEGER" />
        <column name="first_name" type="varchar" size="255" required="true"
            description="Nome" />
        <column name="last_name" type="varchar" size="255" required="true"
            description="Cognome" />
        <column name="user_code" type="varchar" size="255" required="true"
            description="Matricola" />
        <column name="filename" type="varchar" size="255" required="true"
            description="file scansione" />
                <column name="hash" type="varchar" size="255" required="true"
            description="verifica unicità del file" />

                <column name="survey_id" type="integer" required="true" />
                <foreign-key foreignTable="survey" onDelete="cascade" onUpdate="cascade">
                        <reference local="survey_id" foreign="id" />
                </foreign-key>
    </table>

        <table name="correction">
        <column name="id" required="true" primaryKey="true"
             type="INTEGER" />

        <column name="choices" type="longvarchar"  required="true"
            description="Risposte scelte dall'utente. 
                                     Array Json in formato: 
                                    q_{FrozenQuestion->ID} : FrozenAnswerId" 
                />

                <foreign-key foreignTable="submission" onDelete="cascade" onUpdate="cascade">
                        <reference local="id" foreign="id" />
                </foreign-key>
    </table>

I would expect the table correction to have a foreign-key set, bound to submission.id, but apparently propel (1.6.7) doesn't think so. Foreign keys for many-to-many and one-to-many relationships are created as expected.

Am I missing something ?

[tacone]

I found out the bug cause.

Eliminating the single-quote (') from the description attribute of the choices field everything works as expected and propel creates the foreign key.

Please note there's not warning or error during the building process nor during the sql insertion.

[tacone]

The generated sql seems good, it has to be a bug with mysql:

CREATE TABLE `correction`
(
    `id` INTEGER NOT NULL,
    `choices` TEXT NOT NULL COMMENT 'Risposte scelte dal\' utente. Array Json in formato: {FrozenQuestion->ID}:{FrozenAnswer->ID}',
    PRIMARY KEY (`id`),
    CONSTRAINT `correction_FK_1`
        FOREIGN KEY (`id`)
        REFERENCES `submission` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB;

Note that using COMMENT "Risposte scelte dal\" utente" doesn't work either. Executing the query with phpMyAdmin directly doesn't show any error.

My Mysql version is mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (i686) using readline 6.2

As a workaround I found that escaping ala Microsoft works (doubling the character to be escaped):

`choices TEXT NOT NULL COMMENT 'Risposte scelte dal'' utente.` works perfectly (note that "dal" is followed by two single-quotes)

Edit: According to mysql documentation, quoting by doubling is a supported feature.

[willdurand]

Well, that's a weird bug for something that isn't a new feature...

[tacone]

I don't understand the meaning of your statement.

[willdurand]

yeah sorry, I was speaking and writing at the same time... updated.

[tacone]

Yes, very weird. But still it's reproducible outside propel (I used phpmyadmin). Could be a mysql bug or a pdo one (I I think it should be fixed/workarounded (at the least in Propel2), and luckily the workaround is there.

I just reproduced the bug on my home computer now, using the mysql console from the command line mysql version: mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2

So yes, it's a mysql bug.

[willdurand]

So yes, sounds like an issue not tied to Propel (which is great from my point of view). Can we close this ticket then?

[tacone]

I reported it because I thought it was a misbehaviour important enough to deserve a patch.

Closing the bug, feel free to reopen if you change your mind.

[willdurand]

Is your "patch", the use of escaping à la Microsoft?

[tacone]

Yes, it's officially supported by mysql: http://dev.mysql.com/doc/refman/5.0/en/string-literals.html (search the page for '').

[willdurand]

Could you write a patch?

[tacone]

I may try to do it tomorrow.

[tacone]

Apparently, I'm not able to setup the testsuite right. I followed the docs, but I end up with this output after running reset_tests.sh

/var/www/propel.loc/httpdocs/test$ ./reset_tests.sh 
[ bookstore ]
[ bookstore-packaged ]
[ generator ]
Execution of target "check-buildprops-for-propel-gen" failed for the following reason: /var/www/propel.loc/httpdocs/generator/build.xml:95:15: No project directory specified.

BUILD FAILED
/var/www/propel.loc/httpdocs/generator/build.xml:95:15: No project directory specified.
Total time: 0.1008 seconds

Execution of target "check-buildprops-for-propel-gen" failed for the following reason: /var/www/propel.loc/httpdocs/generator/build.xml:95:15: No project directory specified.

BUILD FAILED
/var/www/propel.loc/httpdocs/generator/build.xml:95:15: No project directory specified.
Total time: 0.1019 seconds

[ namespaced ]
[ nestedset ]
[ reverse ]
Execution of target "check-buildprops-for-propel-gen" failed for the following reason: /var/www/propel.loc/httpdocs/generator/build.xml:95:15: No project directory specified.

BUILD FAILED
/var/www/propel.loc/httpdocs/generator/build.xml:95:15: No project directory specified.
Total time: 0.1003 seconds

Execution of target "check-buildprops-for-propel-gen" failed for the following reason: /var/www/propel.loc/httpdocs/generator/build.xml:95:15: No project directory specified.

BUILD FAILED
/var/www/propel.loc/httpdocs/generator/build.xml:95:15: No project directory specified.
Total time: 0.1006 seconds

[ schemas ]
[ treetest ]
[ mysql ]

After then the tests run successfully, but some are skipped. And I guess some of those are needed, because adding a field into one or two schema.xml files, running reset_tests.sh and then phpunit has all the test pass successfully, even though if the generated database is wrong (no FKs).

Either I get to get the testsuite running, or I'm not able to help.

[marcj]

I think these messages are OK, because they appears in the travis-CI build log, too.

[willdurand]

Yes, they are ok.

[jaugustin]

@kirankotari could you please stop posting message in issues that or not related to your problem, The github issue system is not a customer support system, it is only dedicated report Propel bug.

you can find all the documentation you need on http://www.propelorm.org/