FK Problem with Migrated/Converted Parent/Child Form

[ckcoj]

I have run into a problem after converting from Joomla 3.10.12/Fabrik 3.10 to Joomla 4.4.2/Fabrik 4.0Zeta/PHP 8.1.18. The problem does NOT occur in J3/F3 - everything works fine.

The problem occurs when saving a new form that contains a child. It results in the child's FK to the parent being set to 0.

This only happens when the parent and child are first created/new. In the database, both the parent and child records are inserted with all the correct values except the child's foreign key to the parent is set to 0.

If I edit an existing parent record and create a new child, it is saved in the database with the child's foreign key populated correctly.

I tried creating new testing lists/forms/groups/elements the similar to the original ones (with different names) and they work correctly.

I cannot see any difference between the original and testing lists/forms/groups/elements.

I have tried unpublishing all of the original form's plugins and all but a few simple elements, but that did not fix the problem.

I have turned on debugging and logging, but there are no front-end error messages or warnings or any logged messages.

Any advice on how to better troubleshoot this problem?

Thanks much,
Marty Phelan
Capital Kennel Club of Juneau

 

[troester]

Hard to say if it's not easy to replicate.
Which element type has your element holding the FK?
Any access settings for this element?
What is shown in your list's Data/Joins?

 

[kouwearie]

Hard to say if it's not easy to replicate.
Which element type has your element holding the FK?
Any access settings for this element?
What is shown in your list's Data/Joins?

Its the same problem as mine... labels point to the non cloned first element...Maybe something small, but it is not easy to troubleshoot this...

 

[kouwearie]

Hard to say if it's not easy to replicate.
Which element type has your element holding the FK?
Any access settings for this element?
What is shown in your list's Data/Joins?

It is very easy to replicate, with a simple list like i made.... One table with families and one with a joined table family members, make this a repeated group and there you go...

Maybe if made from scratch in fabrik the error doesnt occur, but with existing tables it is there, always..

 

[ckcoj]

Element type holding FK is int(11) same as parent ID. Child's FK element is databasejoin plugin.
Access settings are all PUBLIC.

Data Joins appear correct: Left Join from member to member_dog from column id to column member_id repeatable yes.

Is there debug/trace code I could add somewhere to assist?

 

[troester]

@kouwearie : No, this issue is not replicatable with new lists. Yours (with radio elements) is an other one. The issue with incrementing of repeat group classes or labels of radio elements while cloning is not relevant in this case, the ids are incremented correctly.

@ckcoj : Any list plugins? Is your site accessible?
What are the column definitions (defaults) of your FK element in the database?

 

[ckcoj]

Good Morning,

Any list plugins?
No plugins for either list (Member or Member Dog).
There ARE plugins for the Member form, but I have unpublished all of them while troubleshooting.

Is your site accessible?
Yes. The testing/development site is at https://www.ckcoj.org/dev2. The form with trouble is Members->CKCOJ Membership->New Member Applicaiton.
Direct link is https://www.ckcoj.org/dev2/index.php/member-registration/form/2
Do you need admin access? Or a database export of the fabrik tables?

What are the column definitions (defaults) of your FK element in the database?

Table: member

Columns:
id int(11) AI PK
date_time datetime
userid int(11)
first_name varchar(255)
last_name varchar(255)
... other fields omitted

Table: member_dog

Columns:
id int(11) AI PK
date_time datetime
member_id int(11)
dog_name varchar(255)
... other fields omitted


FK Relationship is member_dog.member_id -> member.id

Thanks for you help,
Marty Phelan
Capital Kennel Club of Juneau

 

[troester]

What are the exact column settings for member_id? Null=yes/ no, default value none or set?
What is your database version?

 

[ckcoj]

The database and tables are identical for PROD and DEV2. PROD is without problem using J3/F3.

Database Version:
Connection Details
Server
Product: MariaDB Server
Version: 10.6.14-MariaDB-cll-lve
Connector
Version: C++ 8.0.32

DDL for the 2 tables:
CREATE TABLE `member_dog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_time` datetime DEFAULT NULL,
`member_id` int(11) DEFAULT NULL,
`dog_name` varchar(255) DEFAULT NULL,
`breed` varchar(255) DEFAULT NULL,
`sex` text DEFAULT NULL,
`date_of_birth` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fb_join_fk_member_id_INDEX` (`member_id`)
) ENGINE=MyISAM AUTO_INCREMENT=241 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

CREATE TABLE `member` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_time` datetime DEFAULT NULL,
`userid` int(11) DEFAULT NULL,
`first_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`last_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`full_name` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`email_confirm` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`captcha` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`block` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`user_groups` text CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`password_reset` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`address_1` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`address_2` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`city` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`state` varchar(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`zip_code` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`phone_1` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`phone_1_type` text CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`phone_2` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`phone_2_type` text CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`dog_related_activities` text CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`other_dog_related_activities` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`volunteer_activities` text CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`other_volunteer_activities` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`customer_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=179 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

 

[troester]

You can send me backend creditals in a conversation.

Is this registration form set up with "Row id"=-1 and "Key name" in the menu item?
Did you try to add a record in the backend via the standard list "Add"?

 

[ckcoj]

You can send me backend creditals in a conversation.
Will do shortly.

Is this registration form set up with "Row id"=-1 and "Key name" in the menu item?
Yes. The form is invoked by a button on the page with a url to a menu item on the utility menu. That menu item has Row ID=-1 and Keyname = userid.

Did you try to add a record in the backend via the standard list "Add"?
Yes, It works OK on the back end using Add.

I have created a couple more entities to test with and compare with member/member_dog. I created member_cat as a new child of member. It has the same problem. I have also created another parent/child entity pet_owner/pet. That one works. I am going to visually compare all related fabrik data elements to see if I can spot a difference.

 

[troester]

Ok, so the "culprit" is the useKey feature. I think this is not the only issue with it, it's ringing a bell...

 

[ckcoj]

I added a new field joomla_user_id to pet_owner testing entity. Then modified the menu item for that form to row_id=-1 and Keyname=joomla_user_id.

Then when adding a new pet owner and pet, the pet was added with its pet_owner_id = 0.

So that caused the test form to exhibit the same problem.

The culprit seems to be cornered....

 

[ckcoj]

I am thinking, for a temporary work around, I could create 2 menu items: one for adding a new entity with row_id=blank, Keyname=blank, and one for editing and existing entity with those two values to -1,user_id. I can try that in another environment DEV1.

Maybe that will work. Will let you know.....

 

[ckcoj]

Creating 2 menu items, one for new and one for edit, seemed to fix the problem.

 

[troester]

Yes, but this is only a workaround. You will need something like an "isunique" validation on the "new" form.
I'll have a look.

 

[ckcoj]

It should work when I re-enable all the other fields and plugins (disabled while trying to isolate the problem). It includes the juser form plugin and is creating a new Joomla user with a unique userid.

When adding a new member, the email must be unique (enforced by the juser plugin). The member is then linked to a joomla account by userid.

 

[ckcoj]

I did run into another problem with the juser plugin. I will open a separate topic for that.