Error when mass deleting email records

[Jakub Grufik]

Helo guys, I need to delete a lot of emails (80 000).

I am trying to use "select all results" checkbox and then mass action Remove. When I try to do it, i got modal window saying that it is being processed. I tried to wait for like 15 minutes and nothing happened and nothing have been removed.

When I try to select first 200 records, then click show more select another 200 and then try to use mass action Remove I am getting error 500.

Could you help me how to delete all the emails stored in the system, please?

[2023-03-14 08:37:14] ERROR: Uncaught Exception Spatie\Async\Output\ParallelError: "PHP Fatal error:  Allowed memory size of 536870912 bytes exhausted (tried to allocate 6918736 bytes) in /data/3/3/338fc1b6-ecde-4497-89be-33f005571989/esmerofve.eu/web/application/Espo/ORM/SqlExecutor.php on line 70 " at /data/3/3/338fc1b6-ecde-4497-89be-33f005571989/esmerofve.eu/web/vendor/spatie/async/src/Output/ParallelError.php line 11 {"exception":"[object] (Spatie\\Async\\Output\\ParallelError(code: 0): PHP Fatal error:  Allowed memory size of 536870912 bytes exhausted (tried to allocate 6918736 bytes) in /data/3/3/338fc1b6-ecde-4497-89be-33f005571989/esmerofve.eu/web/application/Espo/ORM/SqlExecutor.php on line 70\n at /data/3/3/338fc1b6-ecde-4497-89be-33f005571989/esmerofve.eu/web/vendor/spatie/async/src/Output/ParallelError.php:11)"} []
[2023-03-14 08:37:55] ERROR: (HY000) SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xAE Core...' for column 'body_plain' at row 1; POST /MassAction; line: 70, file: /data/3/3/338fc1b6-ecde-4497-89be-33f005571989/esmerofve.eu/web/application/Espo/ORM/SqlExecutor.php [] []
​

Thanks a lot!

[Jakub Grufik]

I tried to use workaround by putting line ini_set(“memory_limit”,”16M“); to /data/3/3/338fc1b6-ecde-4497-89be-33f005571989/esmerofve.eu/web/application/Espo/ORM/SqlExecutor.php but it did not help

[yuri]

There are two problems.

One or multiple emails causing the 2nd error. It's something related to encoding. You can remove these emails manually from DB. I think it's related to https://forum.espocrm.com/forum/gene...f-8-characters

Another is the memory limit exhausted. As it's processed in CLI, you need to tweak the parameter for PHP CLI (it must be a separate config file). It's set to around 512M. It's weird that it was not enough. Try to increase to 1024M.

[yuri]

You can monitor the job execution at Administration > Jobs. If the job is Running for quite long, it's likely memory limit issue happened. If it's Failed soon, it's the encoding issue.

[yuri]

You can find bad emails (with SQL or tools like phpmyadmin), by searching for "\xAE Core" in body_plain column. Then remove it manually.

[Jakub Grufik]

hello yuri thanks a lot for the suggestions

I tried to filter out all the emails containing 'xAE Core' in body_plain in phpmyadmin, but I got 0 results (screen1)

SELECT * FROM `email`
WHERE body_plain LIKE '%xAE Core%';​

Also could you please elaborate a bit on creating a separate config file to increase the memory limit to 1024? Where to put the script and how it should look like? I have never faced this issue so I am not sure how to proceed with the needed steps.

Thank you very much

[Jakub Grufik]

Alright, I used query
SELECT * FROM `email`
WHERE body_plain LIKE '%xAE%'; and got 106 results that I have deleted manually, now I would love to increase the memory limit to try it out.

I am going to try it now without increasing the memory limit and will see what happens

[Jakub Grufik]

Still getting error Now mass remove is not working even for the first 200 records. Will try to dig into database and table emails, but so fat I am not able to get those broken emails

[2023-03-14 11:20:08] ERROR: (HY000) SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xAE 7698...' for column 'body_plain' at row 1; POST /MassAction; line: 70, file: /data/3/3/338fc1b6-ecde-4497-89be-33f005571989/esmerofve.eu/web/application/Espo/ORM/SqlExecutor.php [] []

[Jakub Grufik]

yuri sir can I have a question please? What will happen if I delete all the records in the table 'email' manually from our database. Is it safe way how to delete all the emails from our system?

[yuri]

The problem that email attachments won't be removed by the cleanup job.

You can set deleted = 1 with SQL, and wait until cleanup job processes. It's 3 months by default. https://docs.espocrm.com/administrat...rams/#clean-up

Cleanup job should not fail on any encoding issue as it does not load record contents.

[Jakub Grufik]

thank you yuri, I am now trying to SET deleted = 1 for all the records in the table "email" but now I am getting error:
#3 - Error writing file '/tmpfs/r4-my80-2/MLfd=3124' (OS errno 28 - No space left on device)

I am now in contact with our hosting provider if they can fix that

[Jakub Grufik]

Hello guys, I would love to reopen this topic as our database and its table with emails is getting bigger and bigger.

Today I treid to delete approx 3000emails from the system by checking them via "Select all results" and then in actions->remove.

However, it failed again with same error as before:

[2023-07-26 06:35:12] ERROR: JobManager: Failed job running, job '64c0be8d5d8efd561'. Mass action job error: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xAE Clas...' for column 'body_plain' at row 1; at /data/0/f/0f6e82d6-07b5-42f8-803a-fad2758ce077/esmerozz.eu/web/application/Espo/Core/MassAction/Jobs/Process.php:102. [] []

I tried to check database for the email with body_plain containing "\xAE Clas" but there are none. So basically I am now not able to mass delete emails from the system. Is there any solution for this, please?!

Thanks a lot

[Jakub Grufik]

hey man, I am filtering only emails created before 1.1.2022 and then trying to proceed with remove. Thats it.

[rabii]

it seems this is to do with encoding or collation issues, notice here (Incorrect string value: '\xAE Clas...' for column 'body_plain' at row 1) can you check that record and try to delete it then try again. also Check that Database character set and collation: Verify that the database table and the body_plain column have the correct character set and collation settings. Make sure they support the characters used in those emails.

[Jakub Grufik]

will check, thanks a lot for the tip (I already tried to check for the "broken" records via query body_plain LIKE "%\xAE Clas%" and it found 100 records, I deleted them manualy from the database but still getting the error when trying to mass delete emails.

[Jakub Grufik]

Is anyone willing to help, please?! I am clueless at the moment.