Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

select count(*) =0 but select * returns 1700 rows!!

2 posts in General Discussion Last posting was on 2004-06-11 14:41:00.0Z
Mike McMahon Posted on 2004-06-10 01:24:04.0Z
From: Mike McMahon <mcmahon-at-activewire-dot-net@x.x>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20031007
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: select count(*) =0 but select * returns 1700 rows!!
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: pcp09315595pcs.towson01.md.comcast.net
Message-ID: <40c7b88e$1@forums-2-dub>
X-Original-Trace: 9 Jun 2004 18:25:34 -0700, pcp09315595pcs.towson01.md.comcast.net
Lines: 67
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 9 Jun 2004 18:21:27 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 9 Jun 2004 18:24:04 -0700
X-Trace: forums-1-dub 1086830644 10.22.108.75 (9 Jun 2004 18:24:04 -0700)
X-Original-Trace: 9 Jun 2004 18:24:04 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3155
Article PK: 6614

Somehow my ASA (8.0.1.2600) db has fallen into an inconsistent state.
I believe this is related to a single "large" transaction involving
multiple insert/update statements of 800 rows across 3 main tables.
While most of the time this operation works as expected, occasionally
things start to get weird (as seen below).

I believe I can easily "repair" things by restarting Sybase and/or
rebuild indexes, so that is not the issue. What I would like to know is
how could this have happened? Is there a known problem with doing large
quantities of insert/update/delete inside a single transaction? Oh, I
connect using JDBC if that matters. Thanks for any suggestions.

===============================================================================

select count(*), issue_date from dba.group_benefit_detail where
group_no='999999'
group by issue_date;

count(*)issue_date
1 1/1/2000
256 5/1/2004
1 1/1/1999
1775 1/1/2001 <--- this one is strange, see below
258 1/1/2004
253 7/1/2004
2336 1/1/2003
506 1/1/2002

======================================================================
select count(*) from dba.group_benefit_detail where group_no='999999'
and issue_date = '2001-01-01' ;

count(*)
0 <----- what???

======================================================================
select count(*), issue_date from dba.group_benefit_detail where
group_no='999999'
and issue_date > '2000-01-01' and issue_date < '2002-01-01'
group by issue_date;

count(*)issue_date
1775 1/1/2001 <------ similar query as above but much different
results!

=========================================================================
select * from dba.group_benefit_detail where group_no='999999'
and issue_date = '2001-01-01' ;
< 1775 rows are returned, not shown for brevity >

==========================================================================
set temporary option date_format = 'YYYY-MM-DD hh:nn:ss';

select count(*), issue_date from group_benefit_detail where
group_no='999999'
group by issue_date;

count(*),issue_date
1 '2000-01-01 00:00:00'
256 '2004-05-01 00:00:00'
1 '1999-01-01 00:00:00'
1775 '2001-01-01 00:00:00'
258 '2004-01-01 00:00:00'
253 '2004-07-01 00:00:00'
2336 '2003-01-01 00:00:00'
506 '2002-01-01 00:00:00'


Robert Waywell Posted on 2004-06-11 14:41:00.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <40c7b88e$1@forums-2-dub>
Subject: Re: select count(*) =0 but select * returns 1700 rows!!
Lines: 130
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Original-NNTP-Posting-Host: 10.25.100.136
Message-ID: <40c9c4dc$1@forums-2-dub>
X-Original-Trace: 11 Jun 2004 07:42:36 -0700, 10.25.100.136
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 11 Jun 2004 07:38:15 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 11 Jun 2004 07:41:00 -0700
X-Trace: forums-1-dub 1086964860 10.22.108.75 (11 Jun 2004 07:41:00 -0700)
X-Original-Trace: 11 Jun 2004 07:41:00 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3171
Article PK: 6632

Hi Mike,
Here is one issue that was fixed prior to the 8.0.2 release, it may or
may not apply in your case:
================(Release Build - Engineering Case #301242)================

Using 'LOAD TABLE' to load data into a table could have caused some
data to 'disappear'. For example, after loading 5000 rows of data into
an empty table the statement 'select count(*) from table' could return
a value less than 5000. Also, selecting all of the rows could return
some subset of the rows that were loaded. All of the following would
need to be true to experience this problem:

1) The table into which the data was loaded was empty

2) The table into which the data was loaded contained more than one
database page

3) The resulting table after the 'LOAD TABLE' statement contained
at least 100 database pages



Databases experiencing this problem should be rebuilt. Unload the
database using DBUNLOAD and make sure to unload the data 'ordered'.
CAUTION: If you do an 'unordered' unload of the database the missing
rows will be permanently lost.



I would recommend upgrading to 8.0.2 on a test system and see if the
problem still persists. Note that if you are seeing the effect of the bug
described above then you will also need to do an unload/reload to recover.


--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
ug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

"Mike McMahon" <mcmahon-at-activewire-dot-net@x.x> wrote in message
news:40c7b88e$1@forums-2-dub...
> Somehow my ASA (8.0.1.2600) db has fallen into an inconsistent state.
> I believe this is related to a single "large" transaction involving
> multiple insert/update statements of 800 rows across 3 main tables.
> While most of the time this operation works as expected, occasionally
> things start to get weird (as seen below).
>
> I believe I can easily "repair" things by restarting Sybase and/or
> rebuild indexes, so that is not the issue. What I would like to know is
> how could this have happened? Is there a known problem with doing large
> quantities of insert/update/delete inside a single transaction? Oh, I
> connect using JDBC if that matters. Thanks for any suggestions.
>
>

============================================================================
===
>
> select count(*), issue_date from dba.group_benefit_detail where
> group_no='999999'
> group by issue_date;
>
> count(*)issue_date
> 1 1/1/2000
> 256 5/1/2004
> 1 1/1/1999
> 1775 1/1/2001 <--- this one is strange, see below
> 258 1/1/2004
> 253 7/1/2004
> 2336 1/1/2003
> 506 1/1/2002
>
> ======================================================================
> select count(*) from dba.group_benefit_detail where group_no='999999'
> and issue_date = '2001-01-01' ;
>
> count(*)
> 0 <----- what???
>
> ======================================================================
> select count(*), issue_date from dba.group_benefit_detail where
> group_no='999999'
> and issue_date > '2000-01-01' and issue_date < '2002-01-01'
> group by issue_date;
>
> count(*)issue_date
> 1775 1/1/2001 <------ similar query as above but much different
> results!
>
> =========================================================================
> select * from dba.group_benefit_detail where group_no='999999'
> and issue_date = '2001-01-01' ;
> < 1775 rows are returned, not shown for brevity >
>
> ==========================================================================
> set temporary option date_format = 'YYYY-MM-DD hh:nn:ss';
>
> select count(*), issue_date from group_benefit_detail where
> group_no='999999'
> group by issue_date;
>
> count(*),issue_date
> 1 '2000-01-01 00:00:00'
> 256 '2004-05-01 00:00:00'
> 1 '1999-01-01 00:00:00'
> 1775 '2001-01-01 00:00:00'
> 258 '2004-01-01 00:00:00'
> 253 '2004-07-01 00:00:00'
> 2336 '2003-01-01 00:00:00'
> 506 '2002-01-01 00:00:00'
>