Connector Xpress MYSQL - Delete all membership without filtering

[CKFS]

Hello,

We have configured a JDBC mysql connector.

We have 2 classes pointing to 2 different tables:

-User Acccount
-User Group



They are related by User Account (ID) and User Group (User ID):








The format of the tables is as follows:

-Uses Account:
ID: A
.
.
.
rest of attributes

-User Group:
ID: A
Group: 1
ID: A
Group: 2
ID: A
Group: 3
.
.


The relationship is 1 to N. That is, user ID=A has groups 1, 2 and 3. The problem occurs when requesting the deletion of group 1, the connector automatically deletes all associated groups from the table without filtering by the group to be deleted. How can we configure that only the selected group is deleted and not all user records in the table?

Thank you,
regards.

[CKFS]

Any ideas? When an attribute such as password is changed, I can get it through Operation Bindings but this attribute I can't get, the connector removes directly without filtering. How can I set the relationship to be 1 to N?

[Dmytro Kvyatkovsky]

Dear Carl,

I don't think the model you described will work.
You need to use indirect relationship:
1) Accounts table, with this design:
AccountID
...
2) Groups table, with this design:
GroupID
...
3) Accounts-Groups relationship table, with this design:
AccountID
GroupID

Indirect relationship (relation table is called User Groups):

[CKFS]

Hello Dmytro.

Thanks for your answer, but it continues tofail.

We have 3 classes:



1) User Account --> Accounts table, with this design:
Acoount ID
ID (this is the attribute that is related to the group relational table)
...

2) Groups --> Groups table, with this design:
Group_ID

3) User Groups--> Accounts-Groups relationship table, with this design:
User_ID
Group_ID


User Account:







Groups:





Error:

2022-11-29 09:58:55,417 593775465 [ApacheDS Worker-thread-117] (com.ca.jcs.core:com.ca.jcs.processor.RetryOpProcessorProxy:343) DEBUG - com.ca.jcs.jdbc.JDBCMetaConnector: BBDD_MYSQL_BOTON_ROJO: no retry group found matching exception text 'org.apache.directory.shared.ldap.exception.LdapConfigurationException: JCS@tehwc4185asv: error in addAttrAssocs on objectClass='eTDYNAccount', name='PINDI0131000', containerName='eTDYNAccountContainerName=Accounts' [Root exception is org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [INSERT INTO "crearb"."jmltlf_user_usergroup_map" ("user_id","group_id") SELECT obj."id",ref."id" FROM "crearb"."jmltlf_users" obj,"crearb"."jmltlf_usergroups" ref WHERE obj."username"='PINDI0131000' AND ref."id" IN ('{group_id=group_id: 12}')]; Data truncation: Truncated incorrect DOUBLE value: '{group_id=group_id: 12}'; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '{group_id=group_id: 12}'] [CAUSE: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [INSERT INTO "crearb"."jmltlf_user_usergroup_map" ("user_id","group_id") SELECT obj."id",ref."id" FROM "crearb"."jmltlf_users" obj,"crearb"."jmltlf_usergroups" ref WHERE obj."username"='PINDI0131000' AND ref."id" IN ('{group_id=group_id: 12}')]; Data truncation: Truncated incorrect DOUBLE value: '{group_id=group_id: 12}'; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '{group_id=group_id: 12}' [CAUSE: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '{group_id=group_id: 12}']]'
2022-11-29 09:58:55,417 593775465 [ApacheDS Worker-thread-117] (com.ca.jcs.core:com.ca.jcs.processor.ConnectorProcessorProxyHandler:218) ERROR - com.ca.jcs.jdbc.JDBCMetaConnector: BBDD_MYSQL_BOTON_ROJO: class com.ca.jcs.processor.RetryOpProcessorProxy: failed call on doModify
org.apache.directory.shared.ldap.exception.LdapConfigurationException: JCS@tehwc4185asv: error in addAttrAssocs on objectClass='eTDYNAccount', name='PINDI0131000', containerName='eTDYNAccountContainerName=Accounts' [Root exception is org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [INSERT INTO "crearb"."jmltlf_user_usergroup_map" ("user_id","group_id") SELECT obj."id",ref."id" FROM "crearb"."jmltlf_users" obj,"crearb"."jmltlf_usergroups" ref WHERE obj."username"='PINDI0131000' AND ref."id" IN ('{group_id=group_id: 12}')]; Data truncation: Truncated incorrect DOUBLE value: '{group_id=group_id: 12}'; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '{group_id=group_id: 12}']


What could be happening?

Thank you,
regards.

[Dmytro Kvyatkovsky]

Hello Carl,

There should not be any compound attribute for user/group relationship. There's no point in using compound attributes in there.
Please attach your project file (xxxx.con), and I'll have a look.

Regards,
Dmytry

[CKFS]

Hello Dmytro,

Please find attached the "MYSQL_Boton_Rojo.con". 

If I change the attribute Groups of compound to Integer, the error appears:

2022-12-07 08:40:53,618 1280293666 [ApacheDS Worker-thread-147] (com.ca.jcs.core:com.ca.jcs.osgi.exchange.router.MessageRouter:556) ERROR - JCS@tehwc4185asv: validation failure: <errorMessages><header>Error with eTDYNAccount[="crearb"."jmltlf_users"] object named 'eTDYNAccountName=PINDI0131000,eTDYNAccountContainerName=Accounts,eTDYNDirectoryName=BBDD_MYSQL_BOTON_ROJO,eTNamespaceName=MYSQL_BOTON_ROJO,dc=imtelco,dc=etasa':</header><messages><errorMessage>attribute eTDYN-str-multi-16[=groups]: Invalid integer value '{"group_id":"10"}'</errorMessage></messages></errorMessages>
org.apache.directory.shared.ldap.exception.LdapInvalidAttributesException: validation failure: <errorMessages><header>Error with eTDYNAccount[="crearb"."jmltlf_users"] object named 'eTDYNAccountName=PINDI0131000,eTDYNAccountContainerName=Accounts,eTDYNDirectoryName=BBDD_MYSQL_BOTON_ROJO,eTNamespaceName=MYSQL_BOTON_ROJO,dc=imtelco,dc=etasa':</header><messages><errorMessage>attribute eTDYN-str-multi-16[=groups]: Invalid integer value '{"group_id":"10"}'</errorMessage></messages></errorMessages>


1) User Account --> Accounts table, with this design:
Acoount ID
ID (this is the attribute that is related to the group relational table, (integer))
...

2) Groups --> Groups table, with this design:
Group_ID (integer)

3) User Groups--> Accounts-Groups relationship table, with this design:
User_ID
Group_ID (integer)

Thank you, 
regards.

[CKFS]

Hello Dmytro,

Please find attached the "MYSQL_Boton_Rojo.con".

If I change the attribute Groups of compound to Integer, the error appears:

2022-12-07 08:40:53,618 1280293666 [ApacheDS Worker-thread-147] (com.ca.jcs.core:com.ca.jcs.osgi.exchange.router.MessageRouter:556) ERROR - JCS@tehwc4185asv: validation failure: <errorMessages><header>Error with eTDYNAccount[="crearb"."jmltlf_users"] object named 'eTDYNAccountName=PINDI0131000,eTDYNAccountContainerName=Accounts,eTDYNDirectoryName=BBDD_MYSQL_BOTON_ROJO,eTNamespaceName=MYSQL_BOTON_ROJO,dc=imtelco,dc=etasa':</header><messages><errorMessage>attribute eTDYN-str-multi-16[=groups]: Invalid integer value '{"group_id":"10"}'</errorMessage></messages></errorMessages>
org.apache.directory.shared.ldap.exception.LdapInvalidAttributesException: validation failure: <errorMessages><header>Error with eTDYNAccount[="crearb"."jmltlf_users"] object named 'eTDYNAccountName=PINDI0131000,eTDYNAccountContainerName=Accounts,eTDYNDirectoryName=BBDD_MYSQL_BOTON_ROJO,eTNamespaceName=MYSQL_BOTON_ROJO,dc=imtelco,dc=etasa':</header><messages><errorMessage>attribute eTDYN-str-multi-16[=groups]: Invalid integer value '{"group_id":"10"}'</errorMessage></messages></errorMessages>


1) User Account --> Accounts table, with this design:
Acoount ID
ID (this is the attribute that is related to the group relational table, (integer))
...

2) Groups --> Groups table, with this design:
Group_ID (integer)

3) User Groups--> Accounts-Groups relationship table, with this design:
User_ID
Group_ID (integer)

Thank you,
regards.

Attachment(s)

MYSQL_Boton_Rojo.zip   6 KB 1 version

[CKFS]

Hello  Dmytry,

Have you been able to check this?

Thank you.

[Dmytro Kvyatkovsky]

I was on a leave, will have a look

[CKFS]

Hello  Dmytro,

Have you been able to check this?

Thank you.