Most reported issue relating to connecting to a remote database especially from digitalocean are related to SQL_MODES and sql_require_primary_key option.
This is not an issue with the SaaS module but codeigniter and the Perfex CRM ecosystem in general
Unfurtunately, at this time of writing, Digitalocean only allow updating sql_require_primary_key through the API. Thus we advice running below curl to update the SQL mode and disable sql_require_primary_key in a single command.
You will need to replace $DIGITALOCEAN_TOKEN with your digitalocean token; you can generate one following this link: https://cloud.digitalocean.com/account/api/tokens
You also need to replace $DATABASE_CLUSTER_UID with your databse cluster uid, you can get it from the browser url bar when you visit your database cluster. Mine is https://cloud.digitalocean.com/databases/e05f6bde-007b-4e25-8b76-fe53c2eb787f/settings and thus the cluster uid is e05f6bde-007b-4e25-8b76-fe53c2eb787f please replace with yours.
curl -X PATCH \
-H "Content-Type: application/json" \
-H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
-d '{"config": {"sql_mode": "ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION","sql_require_primary_key": false}}' \
"https://api.digitalocean.com/v2/databases/$DATABASE_CLUSTER_UID/config"After running above command your SQL mode should look like this:
