Announcement

Collapse
No announcement yet.

Error when mass deleting email records

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Error when mass deleting email records

    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?

    Code:
    [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!

  • #2
    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

    Comment


    • #3
      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.

      Comment


      • yuri
        yuri commented
        Editing a comment
        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
        yuri commented
        Editing a comment
        You can find bad emails (with SQL or tools like phpmyadmin), by searching for "\xAE Core" in body_plain column. Then remove it manually.

    • #4
      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)

      Code:
      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
      Attached Files

      Comment


      • Jakub Grufik
        Jakub Grufik commented
        Editing a comment
        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
        Jakub Grufik commented
        Editing a comment
        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 [] []

    • #5
      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?

      Comment


      • yuri
        yuri commented
        Editing a comment
        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
        Jakub Grufik commented
        Editing a comment
        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

    • #6
      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:
      Code:
      [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

      Comment


      • Jakub Grufik
        Jakub Grufik commented
        Editing a comment
        hey man, I am filtering only emails created before 1.1.2022 and then trying to proceed with remove. Thats it.

      • rabii
        rabii commented
        Editing a comment
        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
        Jakub Grufik commented
        Editing a comment
        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.

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

      Comment

      Working...
      X