Hi, I used to be able to stop my application program before executing rollback
when there was a problem with referential integrity and use interactive SQL
to find the invalid uncommitted rows. Since I upgraded to version 11 and
using interactive SQL from the new Sybase Central, by default I don't seem
to get the uncommitted rows returned, which makes it harder to debug. If
it's not that, then I suspect I might even be getting spurious referential
integrity errors, as I can't find any rows that seem to violate the rules.
For example it will say that "there is no primary key for foreign key so-and-so,
cannot commit" and I pause my program before rollback, but I can't select
any rows that are in violation. Does this ring any bells with anybody?

I doubt I could give a simple example to replicate it, as it happens at the
end of a very large complex transaction.

cheers,
Steve M.
www.silverlink.co.uk

What exactly do you mean by *stopping* your application?

If you mean you kill it, then any open transactions should
roll back automatically, and your reported behaviour should
have never been the case unless you application code is
handling all the RI itself (autocommitting may be part of that
for example). But I will ignore that possiblity for now . . .

Also what versions did you use before going to 11?
You may be reporting a change of behaviour that
happened in, say, version 10 or before. Or worse you
may be expecting buggy behaviour in the version and
build you used to run that has since been fixed.

As a quick test what happens when you run your
query this way?

set temporary option isolation_level=0;
select * from . . . .

?

"steve m" <nyahaha (AT) spammers (DOT) despair.net> wrote

What SQL Anywhere versions are you comparing?

Glenn

steve m wrote:

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

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

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
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

Hi, I was using 8.03 before and now the OEM release of version 11.0.0.1264,
which just arrived in the past week. I can pause my application program (Windows
exe through ODBC) and step through line by line, so after stepping through
the line that sends commit, if an error pops up, it's handy to be able to
use a separate ISQL session to examine uncommitted rows and see which rows
have invalid data before I let the program proceed to the next step, which
will handle the error and send a rollback command.

Thanks for the suggestion. I'm trying to see what happens with setting the
isolation level to 0 as you suggest. I will post again if I get somewhere,
it hasn't given me an instant result, but it may be that I have to set the
isolation level for debugging in the application. I will post again.

I wasn't complaining. Just querying. :-)

It's 11.0.0.1264 and was 8.03 before (not sure the build right now). I put
more about it in a reply above just now. Ta.

Both the underlying data store, and the locking model, changed
completely with version 10 (as you must know, a version 10 server cannot
run a version 8 database for that reason). It is unsurprising to me that
the behaviour you're seeing running at isolation level 0 in an attempt
to "see" uncommitted changes is different as a consequence.

Precisely *why* you are seeing different behaviour will depend on
precisely what both the updating transaction and the querying
transaction are doing. If you can be more precise in your description of
what you are experiencing, I may be able to give you precise reasons as
to why you are seeing these changes.

One thing to keep in mind, of course, is that SQL Anywhere makes
absolutely no guarantees of this behaviour (ie "seeing" uncommitted
updates/deletes/inserts) when running at isolation level 0.

Glenn

steve m wrote:

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

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

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
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

Ok, I'm sure it's something I'm doing but...This is now morphing more into
the area of "spurious", which I didn't really expect, but the thing is this:
I can run the exact same process and get different results as follows (but
I don't know why):

1) I run my program with the big transaction with loads of deletes and some
inserts back of the same items, by the way (it deletes a load of things and
then recreates mostly the same but with some differences) and then I get
the error "no primary key for foreign key A in table B". This keeps on happening
(dozens of times, trying different database options) till I delete the foreign
key definition and recreate it.

2) Now I run the process and get another different error (moved on, sort
of thing) "no primary key for foreign key B in table C". So by now I've got
the trick of it, so I delete the foreign key definition B in table C and
recreate it apparently identically.

3) Now my process completes without any errors. Sort of good, but...

4) After some data entries etc (possibly for many sessions, completed and
closed all ok) I come back and try to re-run this process and I'm back to
square one with the foreign key messages and the only way around being to
delete and recreate the definitions.

5) Another thing that happens is that my transaction rollback command part
of my error handling is not working. It doesn't produce any error message,
but when I think I'm ready to do some more data entries or whatever, lo and
behold, the earlier data integrity error message keeps reappearing, as it
hasn't taken the rollback seemingly.

I know it's a saga, and perhaps a little vague, but I'm hoping some bells
will ring and somebody will think of what this syndrome might be down to.
I think I've tried every vaguely relevant database option to see if it would
have any effect but none have. After each test I've gone back to the default,
just so as not to get things muddled up.

I've spent all day on this so far and will continue to think of more things
to try, but if there's an EBF or something that might have some relevance
of course I will try that. I'm kind of out of ideas now, for the moment.
(Other than the painful workaround of deleting and recreating foreign key
definitions.)

:-(.

So this is sounding much less like an issue with seeing uncommitted row
modifications with an isolation level 0 read transaction, and instead
looks like something related to row locking for your application.

The relevant option setting for your application that I can see is the
wait_for_commit connection option. You can also specify CHECK ON COMMIT
for any foreign key definition, so that that specific RI constraint
isn't verified until each and every transaction commits.

If specifying this option or altering the RI constraints themselves
don't do the trick, we're going to need a real reproducible before we
get much further.

Glenn


steve m wrote:

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

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

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
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

Ok, thanks. I will try and come up with a reproducible scenario, or if I
find the reason I'll post the result.

To clarify somewhat (I hope)...I think it might be that there are no matching
items (i.e. the rogue items I'm trying to find) and there are no wrong foreign
key elements, and in fact it is a spurious error message. However, where
it is coming from is another question. I have worked around this for the
time being by using a "brute force" method of dropping and recreating all
the foreign key definitions in the database prior to running this large process
and this prevents the error from occurring. I don't know why. My money is
on that there is an underlying bug with some sort of optimisation of foreign
keys, and once you drop the foreign keys and get back to square one, the
problem disappears. Anyway, I'm able to get by now, so I guess I'll have
to leave this aside for now. Thanks for the comments.

cheers,
Steve M