[Info-ingres] Weird problem in Ingres 10

Thu May 17 12:23:36 UTC 2018

And yet the information we have is that log_trace shows nothing.  We should be
seeing a BT/ABORT if something happened and then was rolled back.  The
implication is that the writing part of the transaction never happened.
I'm curious to see the SC930 traces.

Karl

> On May 17, 2018, at 8:15 AM, Martin Bowes <martin.bowes at ndph.ox.ac.uk> wrote:
> 
> You will find something like:
> 
> QRY 27/03/2018 13:26:07.607044, COMMIT
> 
> Or ROLLBACK
> 
> In the trace file.
> 
> Marty
> 
> -----Original Message-----
> From: Mark [mailto:i at dontgetlotsofspamanymore.net] 
> Sent: 17 May 2018 13:00
> To: info-ingres at lists.planetingres.org
> Subject: Re: [Info-ingres] Weird problem in Ingres 10
> 
> On Thu, 17 May 2018 11:29:52 +0000, Laframboise, André (BAC/LAC)
> <andre.laframboise at canada.ca> wrote:
> 
>> Maybe the trace includes the rollback statement for the failed transaction.
> 
> Maybe, but how can I tell and why would it do a rollback?  No errors
> are reported.
> 
>> André Laframboise
>> 
>> Conseiller Principal Base de Données, Direction générale de l'Innovation et du Dirigeant principal de l'information
>> Bibliothèque et Archives Canada / Gouvernement du Canada
>> andre.laframboise at canada.ca / Tél. : 613-298-1346
>> 
>> Senior Database Advisor, Innovation and Chief Information Officer Branch
>> Library and Archives Canada / Government of Canada
>> andre.laframboise at canada.ca / Tel: 613-298-1346
>> 
>> -----Original Message-----
>> From: info-ingres-bounces at lists.planetingres.org <info-ingres-bounces at lists.planetingres.org> On Behalf Of Mark
>> Sent: Thursday, May 17, 2018 7:12 AM
>> To: info-ingres at lists.planetingres.org
>> Subject: Re: [Info-ingres] Weird problem in Ingres 10
>> 
>> On Wed, 16 May 2018 12:40:48 -0400, Karl and Betty Schendel <schendel at kbcomputer.com> wrote:
>> 
>>> SC930 tracing is server wide and runs until you stop it with set notrace sc930.
>>> So you can connect to any database, do the set trace record '/some/ingres-writable/dir'
>>> and the set trace point sc930, go to the trace record directory you 
>>> gave it and ensure that there's at least something there (should be one 
>>> or more sessNNNNN files), and then you can run your esql program until the fault happens.
>>> 
>>> You don't actually need to put the sc930 into the esql program unless 
>>> you want to do it that way.
>>> 
>>> Once you capture what you need to capture, set notrace point sc930 turns it off.
>>> 
>>> You'll need the trace point privilege, simplest is to do it as the 
>>> installation owner (user ingres, traditionally).
>> 
>> Thanks again.  We now have traces but they don't help me.  The traces include the SQL statement and the data but, pretty much, nothing else.
>> 
>> The only notable thing I observed is that the data is traced twice for the failed transaction and only once for the successful ones.
>> 
>> There are no error messages.
>> 
>>> Karl
>>> 
>>>> On May 16, 2018, at 11:12 AM, Mark <i at dontgetlotsofspamanymore.net> wrote:
>>>> 
>>>> On Wed, 16 May 2018 12:11:08 +0000, Martin Bowes 
>>>> <martin.bowes at ndph.ox.ac.uk> wrote:
>>>> 
>>>>> Just to expand on Karl's sc930...You may already know this...
>>>>> 
>>>>> To turn it on, make a recording directory...mkdir /full/path/to/directory'
>>>>> 
>>>>> And then...
>>>>> sql iidbdb << SQL_END
>>>>> set trace record '/full/path/to/directory'; set trace point sc930 1; 
>>>>> \p\g \q SQL_END
>>>> 
>>>> Do I need to run this on the server account?  I have implemented it 
>>>> currently in the embedded SQL of my program.
>>>> 
>>>>> FYI. The digit after the sc930 indicates a tracing level, 1 should be sufficient.
>>>>> 
>>>>> Run the errant query.
>>>>> 
>>>>> And turn off the sc930.
>>>>> sql iidbdb << SQL_END
>>>>> set trace point sc930 0;
>>>>> \p\g
>>>>> \q
>>>>> SQL_END
>>>>> 
>>>>> You can now access the recording directory and start scanning the files for any sign of life from your query.
>>>>> 
>>>>> Marty
>>>>> 
>>>>> -----Original Message-----
>>>>> From: Karl and Betty Schendel [mailto:schendel at kbcomputer.com]
>>>>> Sent: 16 May 2018 12:54
>>>>> To: Ingres and related product discussion forum
>>>>> Subject: Re: [Info-ingres] Weird problem in Ingres 10
>>>>> 
>>>>> On May 16, 2018, at 7:44 AM, Mark <i at dontgetlotsofspamanymore.net> wrote:
>>>>>> 
>>>>>> On Tue, 15 May 2018 12:27:09 -0400, Karl and Betty Schendel 
>>>>>> <schendel at kbcomputer.com> wrote:
>>>>>> 
>>>>>>> It's certainly not something I have heard of or seen before.  Do 
>>>>>>> you have any rules defined on the relevant tables?  Try enabling 
>>>>>>> LOG_TRACE if the problem is sufficiently predictable, or do a 
>>>>>>> logdump after the problem occurs if it's not; the idea being to 
>>>>>>> try to see whether you actually got any PUT (insert) or REP 
>>>>>>> (replace) log records that were then rolled back, or whether the insert / update was never executed at all.
>>>>>> 
>>>>>> After enabling log_trace all I got was:
>>>>>> 
>>>>>>  LOG: SAVEPOINT   Size written/reserved:      0/     0  Flags:
>>>>>> -------------------------------------------------------------------
>>>>> 
>>>>> So the insert/update isn't ever being executed.  Either it's failing 
>>>>> with some sort of silent error, which would seem odd, or it's not 
>>>>> reaching the backend at all, or it's being pre-empted by a before 
>>>>> rule.  I think the next step would be to enable
>>>>> sc930 tracing and see if the backend is getting the insert-update, 
>>>>> and what end-of-query status it's recording.  There should be KB 
>>>>> articles on enabling
>>>>> SC930 tracing.
>>>>> 
>>>>> Karl
>>>>> 
>>>>> _______________________________________________
>>>>> Info-ingres mailing list
>>>>> Info-ingres at lists.planetingres.org
>>>>> http://lists.planetingres.org/mailman/listinfo/info-ingres
>>>> 
>>>> --
>>>> <insert witty sig here>
>>>> _______________________________________________
>>>> Info-ingres mailing list
>>>> Info-ingres at lists.planetingres.org
>>>> http://lists.planetingres.org/mailman/listinfo/info-ingres
> 
> -- 
> <insert witty sig here>
> _______________________________________________
> Info-ingres mailing list
> Info-ingres at lists.planetingres.org
> http://lists.planetingres.org/mailman/listinfo/info-ingres
> _______________________________________________
> Info-ingres mailing list
> Info-ingres at lists.planetingres.org
> http://lists.planetingres.org/mailman/listinfo/info-ingres