*** InternalError: compressed data is corrupted

This morning, I encountered a strange error and had to spend several hours fixing it. Instead of setting up the database on the server, I had to shut everything down. All processes needed to be stopped to replace certain data. However, I didn’t stop everything; I analyzed the problem first. I deleted and rebuilt the indexes, realizing the issue wasn't with the indexes. The error indicated that one of the records was likely corrupted. I wrote a command to find the specific record causing the error during selection and deleted it. Eventually, once I removed that record, everything started working fine.

This the solution script

from django.db import connection, DatabaseError

(Pdb) cursor = connection.cursor()

(Pdb) cursor.execute("DELETE FROM spontaneousmail_spontaneousprofile WHERE id = '197507'")


And to find profile ID.

class Command(BaseCommand):

     batch_size = 1

     last_processed_id = 197506  

     while True:

         try:

             objects = SpontaneousProfile.objects.filter(id__gt=last_processed_id).order_by('id')[:batch_size]

             print(last_processed_id)

             if not objects:

                 break

             for obj in objects:

                 obj.save()

         except:

                 import pdb;pdb.set_trace()

         last_processed_id = obj.id

other relevant information and attempts



Select * from spontaneousmail_spontaneousprofile;

ERROR: compressed data is corrupted


django.db.utils.InternalError: compressed data is corrupted


DROP INDEX spontaneousmail_spontaneousprofile_email_key; ( Error )


ALTER TABLE spontaneousmail_spontaneousprofile DROP CONSTRAINT spontaneousmail_spontaneousprofile_email_key;


CREATE UNIQUE INDEX spontaneousmail_spontaneousprofile_email_key ON public.spontaneousmail_spontaneousprofile USING btree (email);


☹️


100 kan het wel selecteren zonder probleem


profiles = SpontaneousProfile.objects.all().order_by('id')[:100]



SpontaneousProfile.objects.get(id='197507').delete()

*** InternalError: compressed data is corrupted


Record is located… But? Via python ORM could not be deleted.




spontaneousmail_spontaneousprofile_email_key | CREATE UNIQUE INDEX spontaneousmail_spontaneousprofile_email_key ON public.spontaneousmail_spontaneousprofile USING btree (email)

spontaneousmail_spontaneousprofile_pkey | CREATE UNIQUE INDEX spontaneousmail_spontaneousprofile_pkey ON public.spontaneousmail_spontaneousprofile USING btree (id)

spontaneousmail_spontaneousprofile_2ab549e2 | CREATE INDEX spontaneousmail_spontaneousprofile_2ab549e2 ON public.spontaneousmail_spontaneousprofile USING btree (last_sended)

spontaneousmail_spontaneousprofile_a013658e | CREATE INDEX spontaneousmail_spontaneousprofile_a013658e ON public.spontaneousmail_spontaneousprofile USING btree (unsubscribe)

spontaneousmail_spontaneousprofile_ba830d5e | CREATE INDEX spontaneousmail_spontaneousprofile_ba830d5e ON public.spontaneousmail_spontaneousprofile USING btree (has_unsubscribe)

spontaneousmail_spontaneousprofile_d697ea38 | CREATE INDEX spontaneousmail_spontaneousprofile_d697ea38 ON public.spontaneousmail_spontaneousprofile USING btree (job_id)

spontaneousmail_spontaneousprofile_email_0fdb7e4e_like | CREATE INDEX spontaneousmail_spontaneousprofile_email_0fdb7e4e_like ON public.spontaneousmail_spontaneousprofile USING btree (email varchar_pattern_ops)

spontaneousmail_spontaneousprofile_unsubscribe_0b7f0e63_like | CREATE INDEX spontaneousmail_spontaneousprofile_unsubscribe_0b7f0e63_like ON public.spontaneousmail_spontaneousprofile USING btree (unsubscribe varchar_pattern_ops)