Is it possible to disable foreign key checks using MySQL as target endpoint

[desmondchew]

I am replicating from MySQL source into MySQL 8.0 target endpoint.

We have to disable foreign_key_checks=0 at target to avoid FK related issues during CDC. Is there a fix where we can workaround this? Otherwise is there an option or parameter we could set at Qlik replicate so that we can disable the foreign_key_checks?

I am using the target endpoint as a replica server for MySQL.

Thank you.
Desmond

[Dana_Baldwin]

Hi @desmondchew 

The FK constraints are defined and enforced in the target database, there is no setting in Qlik Replicate that can ignore or bypass that. The change would need to be in the target database itself to remove the FK constraints.

FYI, if the same FK constraints are defined on the source database, you can run the task in transactional apply mode so that the transactions occur on the target in the same order as they did on the source, which will prevent FK constraint issues. The downside of this approach is it will run slower than it would in batch apply mode. This setting, Change Processing Mode, is at the top of this screen: Task Settings > Change Processing > Change Process Tuning.

Hope this helps!

Dana

[Heinvandenheuvel]

Target databases really shouldn't have FK's enabled during CDC operation. 

The FK constraints surely are checked at the source?

>> I am using the target endpoint as a replica server for MySQL.

If you want to use the target as a (DR) failover database then surely there are procedures the run before activating it. Just enable the forein cause as part of the failover procedure scripts, or run it shortly thereafter.

If you do want to disable this just for the CDC session you could try a setting the  Advanced --> internal parameter --> afterConnectScript to set foreign_key_checks for the CDC session. Or better still, if that is an option for MySQL (I don't know) set is through additionalConnectionProperties

Hein.