SOLVED: Very urgent problem with joined table deleting data!

[juuser]

Hello!

After upgrading to fabrik 3.2 very strange behavior started with several tables with joins. Most of my site is halted for that reason

I have a main table "Praagitabel - survevalu" (id 116). This table has join to table Toodete ja komponentide nimekiri" (id 39). Joined group showing "Praagitabel - survevalu- [plastone_tooted_nimekiri]" is set to "Yes, but hidden".

Now when i add record to main table, data in joined table record is cleared (data of elements which are published).

This behavior seems to be also some other tables with similar setup after 3.2 upgrade.

Thank you in advance!

 

[troester]

are you running a recent GitHub version?

gesendet mit Tapatalk

 

[juuser]

Hi Troester,

I have Github from 31th of January.

 

[cheesegrits]

I know this doesn't help after the fact, but it is kind of critical that when doing any kind of Fabrik upgrade, you do it on a test site first, so you don't wind up in this situation. We have always stressed this, and recommend Akeeba as the way to clone you live site to a sandbox, and test any upgrades there first.

Anyway ... I'm looking at it now, logged on to your site to get the exact setup, and seeing if I can duplicate that issue here to test it.

-- hugh

 

[cheesegrits]

Still working on it.

-- hugh

 

[cheesegrits]

What version did you upgrade from, btw?

-- hugh

 

[cheesegrits]

I have this fixed locally, testing, will commit the fix to github as soon as I've run some more tests.

-- hugh

 

[juuser]

Thanks Hugh,

I was upgrading from Joomla 2.5.8 and Fabrik 3.0 (some latest Github). I know that its nessecary to make proper testing and i thought i did. Everything seemed to work fine, i just didn't notice the empty rows as these were moreat the end of the list and database table.

Thanks for a fast response on this, really appreaciate this!

Martin

 

[cheesegrits]

No problem. The issue is that your one-to-one joins are "FK (Foreign Key) on parent", rather than "FK on child", i.e. ...

parent.child_id (FK) => child.id (PK)

... rather than ...

parent.id (PK) <= child.parent_id (FK)

... which require very different handling when writing the joined data out. I could have sworn we had this working correctly in 3.1/2, but apparently not.

Apologies for the loss of data. I've tested my fix locally, and it seems to be working OK in all the scenarios I can think of to test it in. I'm trying to commit the fix, but Github is down atm. The web page is working, but it's not asnwering to upstream push requests. I'll keep trying to push the fixes up to github, and update this thread as soon as I get it done.

-- hugh

 

[juuser]

Thanks one more time. Fortunately i noticed this quite early and the data loss wasn't big. Recovered just some rows from the earlier backup.

 

[cheesegrits]

Cool.

I still can't get the changes pushed up to github, and it's getting late. I'll try again as soon as I wake up tomorrow.

-- hugh

 

[juuser]

OK, thanks!

 

[cheesegrits]

Did you see the commit from earlier today?

https://github.com/Fabrik/fabrik/commit/b3eec08f6a7a967b2720b9a63e762851e2650466

-- hugh

 

[juuser]

Thanks, just noticed it and updated. This data deleting problem seems to be gone now But i just noticed a problem with similar setup, where it adds new half-empty record to joined table (with only elements data that is published).

I have a main table "Tootemuudatuste register" (id 95) which has a join to table "Toodete ja komponentide nimekiri" (id 39). Now when i add or edit a record in main table, it adds new record to joined table with partial data from published elements.

Problem nr.2 (appeared now after last Github update)
This same main table has also join to another table "Tootemuudatuste register - tooted" (id 125). When adding new record or editing existing one, i get the following error:
Unknown column 'tooted' in 'field list' SQL=UPDATE `plastone_tooted_muudatused` SET `tooted` = '1997' WHERE `plastone_tooted_muudatused`.`id` = '25'

BR,
Martin

 

[cheesegrits]

I have a main table "Tootemuudatuste register" (id 95) which has a join to table "Toodete ja komponentide nimekiri" (id 39). Now when i add or edit a record in main table, it adds new record to joined table with partial data from published elements.

Well, we won't write out data for elements which aren't published, because ... well, they aren't published! So as far as the code is concerned, they don't exist. So I'm not sure what you are expecting to happen?

I'll take a look at the setup, see what I can see.

-- hugh

 

[cheesegrits]

Oh, jeez, so you have a repeat join from one table to another, then a one to one from the repeated joined table to another table, with the FK on the repeated join?

I don't even know if I can fix that, either way it's going to take a day or two.

If you have an archive of your 3.0 site, you may want to punt back to that while I see if this can be fixed in 3.2. Basicallhy we totally re-wrote the join code between 3.0 and 3.1, where we took any thousands of lines of horribly confusing code and boiled it down to about 100 lines using a very different approach ... but it appears we weren't really thinking about "FK on parent" joins, as nobody really uses them, the usual way to join is with the FK on the child, pointing back to the master.

-- hugh

 

[juuser]

OK, I understand. Unfortunately i can't go back to the old site as there is already too many changes on new one.
Actually i don't want that new row to created on joined table at all as the group is also set to show "yes, but hidden"

 

[cheesegrits]

Hmm, I could probably throw that in.

So in 3.0, how did the hidden one-to-one group actually render? Did it output X hidden groups, one for each of the repeated instances of the join it relates to? Or did it just output one?

-- hugh

 

[juuser]

I'm not 100% sure what you mean, but it rendered the same way as it does now in the test site.

BR,
martin

 

[cheesegrits]

We already have some logic in 3.1/2 which should prevent writing out a group if it cannot be viewed or edited:

            if ($groupModel->canView() && $groupModel->canEdit())
            {
                $groupModel->process($parentId);
            }

Can you try setting the ACL on that group so it's not viewable?

One of the changes we made between 3.0 and 3.1 was removing the "visible" requirement for writing out join data, as that really isn't a reliable indication of whether data should be written. Quite often, people want "hidden" data to be written out, they just don't want it visible on the form. So in 3.1/2, we only use actual ACL's on the group to decide whether to write it out. So joined data wil only get processed if the ACL on it allows viewing and editing. The latter is more of an internal concept, but you can set the 'view' acces sin the group settings.

-- hugh