Missing Data

microsoft.public.sqlserver.server

7 Sep 2006 4:09 PM

Bulent Yazici

Hello Everyone,

Yesterday, I noticed that about 24 entires were missing from one of my
tables. This table has been in existence in SQL Server for about 3 years now
and only has 190 entries. It lists all the partners for our website and only
has four fields.

As usual, it has a PK of a PartnerID which is automatically incremented
whenever a new Partner has been entered into the table.

Yesterday, it was brought to my attention that when the accounts team ran
their monthly reports, they realized that the figures didn't match what we
had in our bank account compared to our systems. Upon further investigation,
I realized that the reports didn't show all purchases for the month because
some of the partners had gone missing and due to the relationships in the
view, because some partners had gone missing, it wasn't showing me all the
purchases.

I checked the Partners table, and sure enough, 24 partners had gone missing.
I immediately suspected some sort of outside interference with the database.
(The DB is the back end for one of our websites). I checked the IIS logs for
any signs of SQL Injection and found some attempts to perform SQL Injections
which all failed due to the safeguards I have in my code. I could find no
evidence of anyoen succeeding in deleting of records.

I then decided to manually re-store the missing partners. I de-assigned the
PK and the incremental properties of my PartnersID and added them in
manually. I then tried to re-store the PK and the Identity Seed (Incremental)
properties which threw up error messages saying that ID=1 already exists.
When I did a simple Select * command from Enterprise Manager and Query
Analyzer, I could only see on entry with the ID = 1. I then tried to delete
my new entry and the following error message came up:

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is '48'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.

Which has really baffled me because I can only see one entry for ID = 1.

I am the only person in the company who has access to the DB and therefore
do not think any body here could have deleted the data. I also could not find
any other evidence on IIS logs about SQL Injections, other than the first
attempts which happened in early July.

Anyone have any ideas what could have happened to the data and if it is
still there, why it's suddenly disappeared?

Appreciate any kind of pointers.

--
4 Years SQL Server Developer/Administrator

7 Sep 2006 4:18 PM

Michael Epprecht [MSFT]

Hi

The error message that SQL server raised lists "index ID 1". That is the
Internal Definition for a Clustered Index on a Table in dbo.sysindexes

The problem you are having is that data in your column (ID), with a value of
48 has duplicates.

Your column name "ID" is not what SQL Server is referring to when it raises
the message "index ID 1".

Regards

--
Mike

This posting is provided "AS IS" with no warranties, and confers no rights.

Show quoteHide quote

"Bulent Yazici" <BulentYaz***@discussions.microsoft.com> wrote in message
news:833C0AC4-E554-4317-BB23-03E7EA7C269B@microsoft.com...
> Hello Everyone,
>
> Yesterday, I noticed that about 24 entires were missing from one of my
> tables. This table has been in existence in SQL Server for about 3 years
> now
> and only has 190 entries. It lists all the partners for our website and
> only
> has four fields.
>
> As usual, it has a PK of a PartnerID which is automatically incremented
> whenever a new Partner has been entered into the table.
>
> Yesterday, it was brought to my attention that when the accounts team ran
> their monthly reports, they realized that the figures didn't match what we
> had in our bank account compared to our systems. Upon further
> investigation,
> I realized that the reports didn't show all purchases for the month
> because
> some of the partners had gone missing and due to the relationships in the
> view, because some partners had gone missing, it wasn't showing me all the
> purchases.
>
> I checked the Partners table, and sure enough, 24 partners had gone
> missing.
> I immediately suspected some sort of outside interference with the
> database.
> (The DB is the back end for one of our websites). I checked the IIS logs
> for
> any signs of SQL Injection and found some attempts to perform SQL
> Injections
> which all failed due to the safeguards I have in my code. I could find no
> evidence of anyoen succeeding in deleting of records.
>
> I then decided to manually re-store the missing partners. I de-assigned
> the
> PK and the incremental properties of my PartnersID and added them in
> manually. I then tried to re-store the PK and the Identity Seed
> (Incremental)
> properties which threw up error messages saying that ID=1 already exists.
> When I did a simple Select * command from Enterprise Manager and Query
> Analyzer, I could only see on entry with the ID = 1. I then tried to
> delete
> my new entry and the following error message came up:
>
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is '48'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
> constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
>
> Which has really baffled me because I can only see one entry for ID = 1.
>
> I am the only person in the company who has access to the DB and therefore
> do not think any body here could have deleted the data. I also could not
> find
> any other evidence on IIS logs about SQL Injections, other than the first
> attempts which happened in early July.
>
> Anyone have any ideas what could have happened to the data and if it is
> still there, why it's suddenly disappeared?
>
> Appreciate any kind of pointers.
>
>
> --
> 4 Years SQL Server Developer/Administrator

Are all your drivers up to date? click for free checkup

7 Sep 2006 4:31 PM

Bulent Yazici

Hi Michael,

Thanks for the quick reply. Sorry, that was just the tiredness in me
showing. Yes, you're right, it was entry with ID 48 that was throwing up the
error message and yes, it was one of the ones I've restored manually.

Having done some research on MSDN, I found a bug which was referenced. (I
seem to have lost the KB number). However, it suggested that I try to delete
the entry using SQL Analyzer, rather than Enterprise Manager. When I executed
the command, it told me that 2 entries have been deleted, whilst in fact, I
could only see one.

So, I'm still none the wiser.

--
4 Years SQL Server Developer/Administrator

Show quoteHide quote

"Michael Epprecht [MSFT]" wrote:

> Hi
>
> The error message that SQL server raised lists "index ID 1". That is the
> Internal Definition for a Clustered Index on a Table in dbo.sysindexes
>
> The problem you are having is that data in your column (ID), with a value of
> 48 has duplicates.
>
> Your column name "ID" is not what SQL Server is referring to when it raises
> the message "index ID 1".
>
> Regards
> --
> Mike
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Bulent Yazici" <BulentYaz***@discussions.microsoft.com> wrote in message
> news:833C0AC4-E554-4317-BB23-03E7EA7C269B@microsoft.com...
> > Hello Everyone,
> >
> > Yesterday, I noticed that about 24 entires were missing from one of my
> > tables. This table has been in existence in SQL Server for about 3 years
> > now
> > and only has 190 entries. It lists all the partners for our website and
> > only
> > has four fields.
> >
> > As usual, it has a PK of a PartnerID which is automatically incremented
> > whenever a new Partner has been entered into the table.
> >
> > Yesterday, it was brought to my attention that when the accounts team ran
> > their monthly reports, they realized that the figures didn't match what we
> > had in our bank account compared to our systems. Upon further
> > investigation,
> > I realized that the reports didn't show all purchases for the month
> > because
> > some of the partners had gone missing and due to the relationships in the
> > view, because some partners had gone missing, it wasn't showing me all the
> > purchases.
> >
> > I checked the Partners table, and sure enough, 24 partners had gone
> > missing.
> > I immediately suspected some sort of outside interference with the
> > database.
> > (The DB is the back end for one of our websites). I checked the IIS logs
> > for
> > any signs of SQL Injection and found some attempts to perform SQL
> > Injections
> > which all failed due to the safeguards I have in my code. I could find no
> > evidence of anyoen succeeding in deleting of records.
> >
> > I then decided to manually re-store the missing partners. I de-assigned
> > the
> > PK and the incremental properties of my PartnersID and added them in
> > manually. I then tried to re-store the PK and the Identity Seed
> > (Incremental)
> > properties which threw up error messages saying that ID=1 already exists.
> > When I did a simple Select * command from Enterprise Manager and Query
> > Analyzer, I could only see on entry with the ID = 1. I then tried to
> > delete
> > my new entry and the following error message came up:
> >
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
> > INDEX terminated because a duplicate key was found for index ID 1. Most
> > significant primary key is '48'.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
> > constraint.
> > See previous errors.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > terminated.
> >
> > Which has really baffled me because I can only see one entry for ID = 1.
> >
> > I am the only person in the company who has access to the DB and therefore
> > do not think any body here could have deleted the data. I also could not
> > find
> > any other evidence on IIS logs about SQL Injections, other than the first
> > attempts which happened in early July.
> >
> > Anyone have any ideas what could have happened to the data and if it is
> > still there, why it's suddenly disappeared?
> >
> > Appreciate any kind of pointers.
> >
> >
> > --
> > 4 Years SQL Server Developer/Administrator
>
>
>

8 Sep 2006 10:34 PM

Sgt.Sausage

"Bulent Yazici" <BulentYaz***@discussions.microsoft.com> wrote in message
news:8477A725-7DDB-4E6E-BBF1-9D4145CFB241@microsoft.com...
> Hi Michael,
>
> Thanks for the quick reply. Sorry, that was just the tiredness in me
> showing. Yes, you're right, it was entry with ID 48 that was throwing up
> the
> error message and yes, it was one of the ones I've restored manually.
>
> Having done some research on MSDN, I found a bug which was referenced. (I
> seem to have lost the KB number). However, it suggested that I try to
> delete
> the entry using SQL Analyzer, rather than Enterprise Manager.

EM has, in my experience, issues when it can't identify a PK in the
underlying table it's trying to display in the grid. In your previous post,
you indicated you removed the PK and, hence, you're looking at the
same issues I'm seeing on a regular basis. Don't know what it is or
why it happens, but I see it all the time.

Still doesn't answer wherre they went in the first place.

Have you run your consistency checks lately?

> When I executed
> the command, it told me that 2 entries have been deleted, whilst in fact,
> I
> could only see one.

They *were* there in the table, EM just couldn't display them due to
the PK issue above.

Show quoteHide quote

> So, I'm still none the wiser.
>

Thread options