Ticket #1351 (reopened defect)

Opened 11 months ago

Last modified 5 months ago

Fixtures for Searchable on I18n with MySQL InnoDB cause MySQL error

Reported by: eXtreme Owned by: jwage
Priority: blocker Milestone: 1.2.0
Component: Data Fixtures Version: 1.0.0
Severity: Keywords: searchable, i18n, fixtures
Cc: Has Test: no
Status: Pending Core Response Has Patch: no

Description (last modified by jwage) (diff)

Using fixtures for InnoDB tables acting as I18n and Searchable as a child causes MySQL error. Schema: 

---
Table:
    actAs:
        I18n:
            fields: [name]
            actAs:
                Searchable:
                    fields: [name]
    columns:
        id:
            primary: true
            autoincrement: true
            type: integer(10)
        name:
            type: string(100)

Fixture: 

Table:
    Record:
        Translation:
            pl:
                name: foobar
            en:
                name: foobar_en

Importing this fixture generates mysql error: 

SQLSTATE[HY000]: 
General error: 1452 Cannot add or update a child row: 
a foreign key constraint fails (`test_doctrine/table_translation`, 
CONSTRAINT `table3_translation_ibfk_1` FOREIGN KEY (`id`, `lang`) 
REFERENCES `table3_translation_index` (`id`, `lang`))

It seems to be a problem with innodb foreign keys. Everything works when we switch to other mysql engine like myisam.

I don't know how to make a failing testcase for this.

Change History

Changed 11 months ago by eXtreme

  • summary changed from Fixtures form Searchable on I18n with MySQL InnoDB to Fixtures for Searchable on I18n with MySQL InnoDB

Changed 11 months ago by eXtreme

  • summary changed from Fixtures for Searchable on I18n with MySQL InnoDB to Fixtures for Searchable on I18n with MySQL InnoDB cause MySQL error

Changed 10 months ago by eXtreme

  • priority changed from critical to blocker

Changed 10 months ago by jwage

  • milestone changed from Unknown to 1.0.0-RC2

Changed 10 months ago by jwage

What version are you testing this in? The issue is that since the parent object to the translations doesn't have any columns set on it, it is not saved and the child translations fail because the parent object doesn't exist. This should be working in 1.0 branch. The code sets all records state to TDIRTY so they will save even if no values are set on them.

Changed 10 months ago by eXtreme

I have this problem in stable 0.11, 1.0 betas and SVN 1.0 branch.

"The issue is that since the parent object to the translations doesn't have any columns set on it, it is not saved and the child translations fail because the parent object doesn't exist."

In this example - propably yes, but I have the same problem with larger parent table, with fields which are not I18nzed, with nestedset enabled etc.

"This should be working in 1.0 branch"

The same code as presented in ticket and the newest SVN version - problem still exists. 

F:downloadsscriptsdoctrinedev>php test.php build-all-reload
build-all-reload - Are you sure you wish to drop your databases? (y/n)
y
build-all-reload - Successfully dropped database for connection "0" named "test_doctrine"
build-all-reload - Generated models successfully from YAML schema
build-all-reload - Successfully created database for connection "0" named "test_doctrine"
build-all-reload - Created tables successfully
SQLSTATE[HY000]: General error: 1452 Cannot add or update a child row: a foreign key constraint fails (`test_doctrine/table3_translation`, CONSTRAINT `table3_translation_ibfk_1` FOREIGN KEY (`id`, `lang`) REFERENCES  `table3_translation_index` (`id`, `lang`))

Changed 10 months ago by anonymous

  • milestone New deleted

Milestone New deleted

Changed 10 months ago by romanb

  • milestone set to 1.0.1

Changed 10 months ago by jwage

This is related to #1384

Changed 10 months ago by jwage

  • milestone changed from 1.0.1 to 1.0.2

Changed 10 months ago by jwage

  • milestone changed from 1.0.3 to 2.0.0

Changed 7 months ago by jwage

  • description modified (diff)
  • milestone changed from 2.0.0 to 1.0.6

Changed 7 months ago by jwage

  • milestone changed from 1.0.6 to 1.0.7

Changed 5 months ago by guilhermeblanco

  • status changed from new to closed
  • resolution set to worksforme

Is this issue still active?

We have a ticket #1619 that covers this and is working as expected in test suite.

So I'm marking this ticket as worksforme.

If now the case, reopen it and I'll try to dig it more.

Regards,

Changed 5 months ago by eXtreme

  • status changed from closed to reopened
  • resolution worksforme deleted

The same code as in the description of this ticket, doctrine 1.1 svn and 1.0 svn, the same error on loading fixtures, only name of the constraint is a lil bit different.

Changed 5 months ago by jwage

  • milestone changed from 1.0.7 to 1.2.0

Changed 5 months ago by eXtreme

Guilherme will you try to find out what is wrong? Cuz Jonathan want to fix it in 1.2...

Changed 5 months ago by guilhermeblanco

I can surely help, but I'll need some assistance.

I'll need you to provide a test case for that. I can debug it and fix. Since this is a MySQL specific issue, I'll suggest you to use parts of this test:  http://trac.doctrine-project.org/browser/branches/1.0/tests/Ticket/1868TestCase.php

To help in MySQL connection.

Can you wrap your issue in a test, I can look into it... I'm delaying the 1.1 RC2 due to a limit-subquery bug, but as long as I fix that, I can look into yours.

Regards,

Changed 5 months ago by eXtreme

Well, as I can see, there is a TestCase? prepared by Jonathan about 4 months ago: 1351TestCase.php, but it does not run on MySQL (and InnoDB). I tried to make it work like in 1868TestCase but i got [Unknown method Ticket_1351_Article::setTranslation] exception ...

Changed 5 months ago by jwage

I know what the problem is. I spent many hours on the issue and in order to fix it we will have to break some backwards compatibility. The fix does not offer any direct upgrade path.

It is actually a problem with how behaviors and generators that generate foreign keys. The problem does not exist if you turn off foreign key checks or your dbms doesn't support foreign key constraints.

Changed 5 months ago by eXtreme

Is it possible to turn off this foreign key checks for specific table (I mean index table)?

Changed 5 months ago by jwage

You can turn off foreign key exporting using the ATTR_EXPORT attribute. You can check the manual for some information on it.

Note: See TracTickets for help on using tickets.