How to delete deleted entries in Espo in the DB?

[ThomasB]

Hi,

- I added a unique field "clientid" to the account entity.
- An account record was created with the clientid set.
- The record was then deleted via UI.
- When I tried to create a new record with the same clientid, I received a error about a duplicated clientid.

When I looked at the account table in the DB, I see that records are not really deleted but just flagged "true" in the row "deleted".

My understanding was, that the clean-up job deletes records which are flagged true as deleted?

I even ran the job manually via cli and the records are still there.

Am I missing something?

EspoCRM 7.4.6

[rabii]

you need to activate the option to delete deleted record. Administration > Setting > (check) Clean up deleted records

This way the cleanup job will do the job and clean your database. otherwise if you want it frequently you can adjust the time execution of the clean-up job under Administration > Scheduled Jobs > Clean-up => then Edit the record and change the value of Scheduling to your liking.

[ThomasB]

Thanks, I was not aware of this. I checked now the option and see what happens next.

[ThomasB]

Ok, I checked the option "Clean up deleted records" and excecuted the cleanup job via cli and UI, but the records are still there...

[ThomasB]

Ok, finally figured it out.

You also have to set the config parameter "cleanupDeletedRecordsPeriod" to a shorter time. By default its 3 months.

[rabii]

yeah that what is i told you, you had to update the scheduled time for the job.
glad you sorted it out

[ThomasB]

No, the scheduled time for the job is different to the option "cleanupDeletedRecordsPeriod".

With the scheduled time for the job, you just say when the clean-up job should be run. E.g. Evey sunday.
The "cleanupDeletedRecordsPeriod" parameter specifies how long the modified day has to be in the past. The default is 3 months.

If I created a record yesterday and deleted it afterwards its just one day old. The clean-up will not remove the record until 3 months have been passed.

This is the query from the clean-up job

SELECT account.id AS `id`, account.deleted AS `deleted` FROM `account` AS `account` WHERE account.deleted = 1 AND account.modified_at < '2023-03-12 10:15:41'

[rabii]

yeah you are right, just looked it up and it is as you said. thanks for sharing