Skip site navigation (1) Skip section navigation (2)

Re: Data Corruption

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: Larry Wissink <lwissink(at)ebates(dot)com>
Cc: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Data Corruption
Date: 2007-06-05 22:38:16
Message-ID: Pine.LNX.4.64.0706051532500.24164@glacier.frostconsultingllc.com (view raw or flat)
Thread:
Lists: sfpug
Larry,

Just for giggles, you might try and reindex.  I've seen this sort of 
strangeness before when a unique index got corrupted.  As you already know, 
this should be a major motivation to upgrade to at least 7.4.17 as there are 
some nice fixes that avoid data corruption problems (possibly like yours). 
Check the HISTORY file of a recent release for specifics.

On Tue, 5 Jun 2007, Larry Wissink 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
>
>
>
>
>
>
>
>
>
>

-- 
Jeff Frost, Owner 	<jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954

In response to

sfpug by date

Next:From: Jon AsherDate: 2007-06-05 22:44:38
Subject: Re: Data Corruption
Previous:From: Larry WissinkDate: 2007-06-05 21:39:37
Subject: Data Corruption

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group