Re: Data Corruption

Be sure to follow the logic of your cascade deletes and any other triggers
that might exist. Did you rebuild your indexes- the most common corruption
point in my experience. There's a big difference between a row missing from
the index and missing from the table.

Be sure to post to the mailing list also, you might get a better response.

On 6/5/07, Larry Wissink <lwissink(at)ebates(dot)com> wrote:
>
> I know this is not really the point of this group, but I'm wondering if
> someone can help me with a weird problem on our production dbs. Since
> yesterday we have had problems with rows missing from a primary table (table
> of users/email addresses). We can't figure out how the rows were deleted
> because nothing is designed to delete these rows. The primary reason I
> suspect it is a data corruption rather than bad code or malicious user is
> that when I try to re-insert the missing row, Postgres complains that it
> violates the primary key constraint.
>
>
>
> So, any suggestions on where to look for Internal errors? Any known bugs
> that could be causing this? Any suggestions on how to fix it? Fortunately
> we should be able to recover most data from backup servers, but it's still a
> big concern because obviously if it can happen in one table it could happen
> anywhere else.
>
>
>
> Ok, details.
>
> So far, no hardware problems have been identified. I can go into the
> hardware details, but I'll leave that aside for the moment.
>
>
>
> Our PG version is: 7.4.7. (Yes I know we should upgrade…)
>
>
>
> Here's what happens:
>
>
>
> Missing User:
>
> EBPRD1=# select user_id, user_username, user_email, user_password,
> user_created_date from users where user_id = 6020900;
>
> user_id | user_username | user_email | user_password | user_created_date
>
> ---------+---------------+------------+---------------+-------------------
>
> (0 rows)
>
>
>
> Try to re-insert the missing user:
>
> EBPRD1=# insert into users
>
> (user_id, user_username, user_email, user_password, user_created_date )
>
> values (6020900, 'sofifi97(at)optonline(dot)net', '
> sofifi97(at)optonline(dot)net', '********', '2003-07-05 08:37:07.620');
>
> EBPRD1-# EBPRD1-# ERROR: duplicate key violates unique constraint
> "con_user_id_pri"
>
>
>
> That constraint is of course a primary key constraint on the users table.
>
>
>
>
> I don't think I'd get that error if the row had been deleted in any normal
> method.
>
>
>
> Any suggestions or relevant postgres reference is greatly appreciated.
>
>
>
> Larry.
>
>
>
>
>
>
>
> I don't think the table structure is important, but here it is:
>
> EBPRD1=# \d users
>
> Table "public.users"
>
> Column | Type | Modifiers
>
>
>
> --------------------------+--------------------------+---------------------
>
> user_id | numeric | not null
>
> user_username | character varying(255) | not null
>
> user_email | character varying(255) | not null
>
> user_referrer_url | character varying(2048) |
>
> user_password | character varying(64) | not null
>
> user_rebate_paid | numeric(9,0) |
>
> user_rebate_due | numeric(9,0) |
>
> user_referred1_paid | numeric(9,0) |
>
> user_referred1_due | numeric(9,0) |
>
> user_referred2_due | numeric(9,0) |
>
> user_referred2_paid | numeric(9,0) |
>
> user_referred3_due | numeric(9,0) |
>
> user_referred3_paid | numeric(9,0) |
>
> user_pri_ref_user_id | numeric |
>
> user_sec_ref_user_id | numeric |
>
> user_ter_ref_user_id | numeric |
>
> user_fully_registered_c | character(1) |
>
> user_wants_promo_mail_c | character(1) |
>
> user_spare_flag_b | character(1) |
>
> user_spare_flag_c | character(1) |
>
> user_created_date | timestamp with time zone | not null
>
> user_registration_type | character(3) |
>
> user_username_number | numeric |
>
> user_username_name | character varying(255) |
>
> user_active | character(1) | default 'T'::bpchar
>
> user_email_valid | character(1) | default 'T'::bpchar
>
> user_wants_rebate_mail | character(1) |
>
> user_password_hint | character varying(96) |
>
> user_last_modified | timestamp with time zone |
>
> user_primary_account_id | numeric |
>
> user_unused_num1 | numeric |
>
> user_referrer_banner | character varying(255) |
>
> user_frn_banner_id | numeric(38,0) |
>
> user_signup_page | character varying(50) |
>
> user_showme_info_popup | character varying(1) |
>
> user_showme_promo_popup | character varying(1) |
>
> user_wants_mktg_mail | character varying(1) |
>
> user_wants_sales_mail | character varying(1) |
>
> user_wants_specials_mail | character varying(1) |
>
> user_has_flag | character varying(1) |
>
> user_registration_code | character varying(128) |
>
> Indexes:
>
> "con_user_id_pri" primary key, btree (user_id)
>
> "users_useremail_uk" unique, btree (user_email)
>
> "ix_user_last_modified" btree (user_last_modified)
>
> "user_created_date_index" btree (user_created_date)
>
> "users_pri_ref_indx" btree (user_pri_ref_user_id)
>
> "users_sec_ref_indx" btree (user_sec_ref_user_id)
>
> "users_ter_ref_indx" btree (user_ter_ref_user_id)
>
> "users_username_indx" btree (user_username)
>
> "users_username_no_indx" btree (user_username_name,
> user_username_number)
>
> Foreign-key constraints:
>
> "con_user_ref_users_ter" FOREIGN KEY (user_ter_ref_user_id) REFERENCES
> users(user_id) ON DELETE CASCADE
>
> "con_user_ref_users_sec" FOREIGN KEY (user_sec_ref_user_id) REFERENCES
> users(user_id) ON DELETE CASCADE
>
> "con_user_ref_users_pri" FOREIGN KEY (user_pri_ref_user_id) REFERENCES
> users(user_id) ON DELETE CASCADE
>
>
>
>
>
>
>
>
>