Cannot update/delete N:M transient table - Xataface 2.1.2

[LHammonds]

Records can be added to the transient / joining table (tbl_FamilySkill) when creating a new record (tbl_Family) or editing an existing (tbl_Family) record by adding related Skills (placing checks in Skills checkboxes). Any attempt to remove related Skills (unchecking the boxes and then saving) will fail. It will also fail upon trying to delete the Family record. The database user has all permissions to the database including delete.

Error in the apache log (replaced backticks with single quotes to avoid github formatting issue):

[Tue Sep 08 17:06:59.162881 2020] [:error] [pid 7580] [client x.x.x.x:55618] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'limit 1' at line 1, referer: index.php?-table=tbl_family&-action=edit&-cursor=0&-skip=0&-limit=30&-mode=list&-recordid=tbl_family%3FFamily_ID%3D1&--saved=1&--msg=Record+successfully+saved
[Tue Sep 08 17:06:59.163132 2020] [:error] [pid 7580] [client x.x.x.x:55618] PHP Fatal error:  Uncaught exception 'Exception' with message 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'limit 1' at line 1' in xataface/public-api.php:821\nStack trace:\n#0 xataface/Dataface/IO.php(848): df_q('select '', NULL...', Object(mysqli))\n#1 xataface/Dataface/IO.php(1797): Dataface_IO->recordExists(Object(Dataface_Record), NULL, 'tbl_familyskill')\n#2 xataface/Dataface/IO.php(594): Dataface_IO->removeRelatedRecord(Object(Dataface_RelatedRecord), false, true)\n#3 xataface/Dataface/IO.php(702): Dataface_IO->saveTransients(Object(Dataface_Record), Array, NULL, true)\n#4 xataface/Dataface/QuickForm.php(1120): Dataface_IO->write(Object(Dataface_Record), Array, NULL, true, false)\n#5 [internal function]: Dataface_QuickForm->save(Array)\n#6 xataface/lib/HTML/QuickForm.php(1632): call_user_func(Array, Array)\n#7 xataface/actions/e in xataface/public-api.php on line 821, referer: index.php?-table=tbl_family&-action=edit&-cursor=0&-skip=0&-limit=30&-mode=list&-recordid=tbl_family%3FFamily_ID%3D1&--saved=1&--msg=Record+successfully+saved

Enabling the general log on MariaDB and capturing the query that breaks shows the same query noted in the apache log: (replaced backticks with single quotes to avoid github formatting issue)

select '', NULL from 'tbl_familyskill' where  limit 1;

I get the same issue on Ubuntu Server 18.04 (MariaDB 10.5.5, Apache 2.4.29, PHP 5.6.40, Xataface 2.1.2) and 20.04 (MariaDB 10.5.5, Apache 2.4.41, PHP 7.4.3, Xataface 2.1.2)

Here are the table definitions (replaced backticks with single quotes to avoid github formatting issue):

CREATE TABLE 'tbl_family' (
  'Family_ID' smallint(6) NOT NULL AUTO_INCREMENT COMMENT 'Index',
  'FirstName' varchar(30) COLLATE utf8_bin NOT NULL COMMENT 'First Name',
  'LastName' varchar(30) COLLATE utf8_bin NOT NULL COMMENT 'Last Name',
  'Birthday' date DEFAULT NULL COMMENT 'Birthday',
  'Filename' varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT 'Filename of uploaded image',
  'DateCreated' timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Date record created',
  'DateModified' datetime DEFAULT NULL COMMENT 'Date record last modified',
  PRIMARY KEY ('Family_ID')
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Family Members';
CREATE TABLE 'tbl_skill' (
  'Skill_ID' smallint(6) NOT NULL AUTO_INCREMENT COMMENT 'Index',
  'Name' varchar(30) COLLATE utf8_bin NOT NULL COMMENT 'Name of skill',
  'Value' int(11) DEFAULT NULL COMMENT 'Value of skill',
  'DateCreated' timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Date record created',
  'DateModified' datetime DEFAULT NULL COMMENT 'Date record last modified',
  PRIMARY KEY ('Skill_ID')
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='List of Skills';
CREATE TABLE 'tbl_familyskill' (
  'Family_ID' smallint(6) NOT NULL COMMENT 'Associated Family Member',
  'Skill_ID' smallint(6) NOT NULL COMMENT 'Associated Skill',
  KEY 'Family_ID' ('Family_ID'),
  KEY 'Skill_ID' ('Skill_ID'),
  CONSTRAINT 'tbl_familyskill_ibfk_1' FOREIGN KEY ('Family_ID') REFERENCES 'tbl_family' ('Family_ID'),
  CONSTRAINT 'tbl_familyskill_ibfk_2' FOREIGN KEY ('Skill_ID') REFERENCES 'tbl_skill' ('Skill_ID')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Family Skills';

This is in the fields.ini

[fieldgroup:Primary]
  section:order = 1
  order = 1
  label = "Primary Fields"
[fieldgroup:Secondary]
  section:order = 2
  order = 2
  label = "Secondary Fields"
[fieldgroup:Other]
  section:order = 3
  order = 3
  label = "Other Fields"
[Family_ID]
  group = Other
  widget:type = static
  widget:label = "Index"
[FirstName]
  group = Primary
  order = 1
  widget:label = "First Name"
  widget:description = "First Name"
  validators:required = 1
  widget:atts:size = 30
  widget:focus = 1
[LastName]
  group = Primary
  order = 2
  widget:label = "Last Name"
  widget:description = "Last Name"
  validators:required = 1
  widget:atts:size = 30
[Birthday]
  group = Primary
  order = 3
  widget:label = "Birthday"
  widget:type = calendar
  validators:required = 0
[Filename]
; http://xataface.com/documentation/how-to/how-to-handle-file-uploads
; NOTE: Files uploaded with the same name as existing files will overwrite
;       the existing files.
  group = Primary
  order = 4
  widget:label = "Image"
  widget:description = "Optional image of family member"
  Type = container
  widget:type = file
  allowed_extensions = png,jpg,jpeg,gif
  validators:required = 0
[FamilySkills]
  group = Primary
  order = 5
  widget:label = "Skills"
  widget:description = "Select skills related to this family member"
  widget:type = checkbox
  transient = 1
  relationship = skills
[DateCreated]
  group = Other
  date_format = %Y-%m-%d
  widget:type = static
  timestamp = insert
[DateModified]
  group = Other
  date_format = %Y-%m-%d
  timestamp = update
  widget:type = static

And this is the relationships.ini

[skills]
tbl_skill.Skill_ID = tbl_familyskill.Skill_ID
tbl_familyskill.Family_ID = "$Family_ID"

[shannah]

It doesn't look like tbl_familyskill has a primary key. Create a primary key on (Family_ID, Skill_ID) and it should resolve the issue.

[LHammonds]

omg, cannot see the tree because of the forest. It works now. Thanks for the quick rely.