<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"\@MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:"Segoe UI Symbol";
panose-1:2 11 5 2 4 2 4 2 2 3;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">The sc930 shows a commit is being executed and that there are no errors in the update.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Laframboise, André (BAC/LAC) [mailto:andre.laframboise@canada.ca]
<br>
<b>Sent:</b> 17 May 2018 14:59<br>
<b>To:</b> Emma McGrattan; Martin Bowes; info-ingres@lists.planetingres.org<br>
<b>Subject:</b> Re: [Info-ingres] Weird problem in Ingres 10<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-CA" style="color:#1F497D;mso-fareast-language:EN-US">I was thinking of auditdb but I don’t think rollbacks are tracked in the journal files. Only committed transactions are flushed out.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA" style="color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="FR-CA" style="color:black">André Laframboise<br>
<br>
Conseiller Principal Base de Données, Direction générale de l'Innovation et du Dirigeant principal de l'information<br>
</span><span lang="EN-CA" style="color:black"><a href="http://www.bac-lac.gc.ca/fra/Pages/bac-web.aspx"><span lang="FR-CA">Bibliothèque et Archives Canada</span></a></span><span lang="FR-CA" style="color:black"> / Gouvernement du Canada
<br>
</span><span lang="EN-CA" style="color:black"><a href="mailto:andre.laframboise@canada.ca"><span lang="FR-CA">andre.laframboise@canada.ca</span></a></span><span lang="FR-CA" style="color:black"> / Tél. : 613-298-1346<br>
<br>
Senior Database Advisor, Innovation and Chief Information Officer Branch<br>
</span><span lang="EN-CA" style="color:black"><a href="http://www.bac-lac.gc.ca/eng/Pages/lac-web.aspx"><span lang="FR-CA">Library and Archives Canada</span></a></span><span lang="FR-CA" style="color:black"> / Government of Canada<br>
</span><span lang="EN-CA" style="color:black"><a href="mailto:andre.laframboise@canada.ca"><span lang="FR-CA">andre.laframboise@canada.ca</span></a></span><span lang="FR-CA" style="color:black"> / Tel: 613-298-1346<br>
<br>
</span><span lang="EN-CA"><a href="http://www.bac-lac.gc.ca/eng/events/Pages/events.aspx"><span style="border:solid windowtext 1.0pt;padding:0cm;text-decoration:none"><img border="0" width="320" height="112" id="_x0000_i1025" src="cid:image001.jpg@01D3EDEF.F8EF10E0" alt="Image removed by sender. http://www.bac-lac.gc.ca/eng/events/PublishingImages/banner.jpg"></span></a></span><span lang="FR-CA" style="color:black"><br>
</span><span lang="FR-CA" style="color:#1F497D;mso-fareast-language:EN-US"><o:p></o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US">From:</span></b><span lang="EN-US"> info-ingres-bounces@lists.planetingres.org <info-ingres-bounces@lists.planetingres.org>
<b>On Behalf Of </b>Emma McGrattan<br>
<b>Sent:</b> Thursday, May 17, 2018 9:53 AM<br>
<b>To:</b> Martin Bowes <martin.bowes@ndph.ox.ac.uk>; info-ingres@lists.planetingres.org<br>
<b>Subject:</b> Re: [Info-ingres] Weird problem in Ingres 10<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-CA"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Hey, Marty! Actian support is hiring! </span>
<span lang="EN-US" style="font-family:"Segoe UI Symbol","sans-serif"">😉</span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> <o:p></o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US">From:</span></b><span lang="EN-US"> <a href="mailto:info-ingres-bounces@lists.planetingres.org">
info-ingres-bounces@lists.planetingres.org</a> <<a href="mailto:info-ingres-bounces@lists.planetingres.org">info-ingres-bounces@lists.planetingres.org</a>>
<b>On Behalf Of </b>Martin Bowes<br>
<b>Sent:</b> Thursday, May 17, 2018 9:49 AM<br>
<b>To:</b> <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
<b>Subject:</b> Re: [Info-ingres] Weird problem in Ingres 10<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Hi Mark,<br>
<br>
For the successful updates to have occurred and now have zero rows when you check for them... Something else is screwing with your data.<br>
<br>
Do the auditdb and lets track what happens to these records.<br>
<br>
Marty<br>
<br>
-----Original Message-----<br>
From: Mark [<a href="mailto:i@dontgetlotsofspamanymore.net">mailto:i@dontgetlotsofspamanymore.net</a>]
<br>
Sent: 17 May 2018 14:44<br>
To: <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
Subject: Re: [Info-ingres] Weird problem in Ingres 10<br>
<br>
On Thu, 17 May 2018 13:24:56 +0000, Martin Bowes<br>
<<a href="mailto:martin.bowes@ndph.ox.ac.uk">martin.bowes@ndph.ox.ac.uk</a>> wrote:<br>
<br>
>Hi Mark,<br>
><br>
>That trace shows three successive update attempts each with a different message_id. The first one updates no rows the others update a single row.<br>
<br>
Yes, but ... they all should update exactly one row.<br>
<br>
>So can you do a:<br>
>select count(*) from db_msg where message_id = ' 100055700000XDBW' and queue <> ' log_20150325' /* rowcount = 0 */<br>
<br>
0 [This was 1 when the update first failed. It was subsequently<br>
updated succesfully].<br>
<br>
>select count(*) from db_msg where message_id = ' 400055700000XDBW' and queue <> ' log_20150325' /* rowcount = 1 */<br>
<br>
0<br>
<br>
>select count(*) from db_msg where message_id = ' 500055700000XDBW' and queue <> ' log_20150325' /* rowcount = 1 */<br>
<br>
0<br>
<br>
>Marty<br>
><br>
>-----Original Message-----<br>
>From: Mark [<a href="mailto:i@dontgetlotsofspamanymore.net">mailto:i@dontgetlotsofspamanymore.net</a>]
<br>
>Sent: 17 May 2018 14:17<br>
>To: <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
>Subject: Re: [Info-ingres] Weird problem in Ingres 10<br>
><br>
>On Thu, 17 May 2018 13:03:16 +0000, Martin Bowes<br>
><<a href="mailto:martin.bowes@ndph.ox.ac.uk">martin.bowes@ndph.ox.ac.uk</a>> wrote:<br>
><br>
>>Sorry, my bad that should have been a line starting: SESSION BEGINS<br>
>><br>
>>Can you send the update queries and parameters (if any) from the good and bad cases. Those won't show any private data, so it should be cool.<br>
><br>
>Cheers. I have emailed you the logs.<br>
><br>
>>Marty<br>
>><br>
>>-----Original Message-----<br>
>>From: Mark [<a href="mailto:i@dontgetlotsofspamanymore.net">mailto:i@dontgetlotsofspamanymore.net</a>]
<br>
>>Sent: 17 May 2018 13:51<br>
>>To: <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
>>Subject: Re: [Info-ingres] Weird problem in Ingres 10<br>
>><br>
>>On Thu, 17 May 2018 12:35:38 +0000, Martin Bowes<br>
>><<a href="mailto:martin.bowes@ndph.ox.ac.uk">martin.bowes@ndph.ox.ac.uk</a>> wrote:<br>
>><br>
>>>Going out on a limb here...<br>
>>><br>
>>>Are we sure the two sessions are working on the same table owned by the same user in the same database as each other?<br>
>><br>
>>I am only using a single session for this.<br>
>><br>
>>>In the sc930 trace file can you find the connection information immediately prior to the update code. You are looking for a line like:<br>
>>>BEG 27/03/2018 13:26:07.598545, Database: iidbdb, User: ingres<br>
>><br>
>>There's nothing like this in the trace files.<br>
>><br>
>>>You could also check your database and see if there are any duplicates of the table with the query:<br>
>>>Select table_owner from iitables where table_name='YOURTABLE';<br>
>>><br>
>>>Do that for each table involved in the update.<br>
>><br>
>>Only 1 row (& only 1 table in the update).<br>
>><br>
>>>Marty<br>
>>><br>
>>>-----Original Message-----<br>
>>>From: Mark [<a href="mailto:i@dontgetlotsofspamanymore.net">mailto:i@dontgetlotsofspamanymore.net</a>]
<br>
>>>Sent: 17 May 2018 13:21<br>
>>>To: <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
>>>Subject: Re: [Info-ingres] Weird problem in Ingres 10<br>
>>><br>
>>>On Thu, 17 May 2018 12:06:48 +0000, Martin Bowes<br>
>>><<a href="mailto:martin.bowes@ndph.ox.ac.uk">martin.bowes@ndph.ox.ac.uk</a>> wrote:<br>
>>><br>
>>>>No error code and with a rowcount of zero means the update is successful, but had no work to do.<br>
>>>><br>
>>>>I presume there is a where clause in the update and that it looks OK.<br>
>>><br>
>>>There is a where clause and it does match a record so there is "work<br>
>>>to do".<br>
>>><br>
>>>>Can you compare the code which worked and the code which didn't work for discrepancies?<br>
>>><br>
>>>Yes. They are exactly the same! And it only fails the first time it<br>
>>>is executed, all other times work fine.<br>
>>><br>
>>>>Marty<br>
>>>><br>
>>>>-----Original Message-----<br>
>>>>From: Mark [<a href="mailto:i@dontgetlotsofspamanymore.net">mailto:i@dontgetlotsofspamanymore.net</a>]
<br>
>>>>Sent: 17 May 2018 12:57<br>
>>>>To: <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
>>>>Subject: Re: [Info-ingres] Weird problem in Ingres 10<br>
>>>><br>
>>>>On Thu, 17 May 2018 11:21:37 +0000, Martin Bowes<br>
>>>><<a href="mailto:martin.bowes@ndph.ox.ac.uk">martin.bowes@ndph.ox.ac.uk</a>> wrote:<br>
>>>><br>
>>>>>Twice...<br>
>>>>><br>
>>>>>Once where it updates the data and once where it puts it back the way it was?<br>
>>>><br>
>>>>No. The data is the same for each.<br>
>>>><br>
>>>>>What do the Rowcount figures say on each statement?<br>
>>>><br>
>>>>0.<br>
>>>><br>
>>>>>Marty<br>
>>>>><br>
>>>>>-----Original Message-----<br>
>>>>>From: Mark [<a href="mailto:i@dontgetlotsofspamanymore.net">mailto:i@dontgetlotsofspamanymore.net</a>]
<br>
>>>>>Sent: 17 May 2018 12:12<br>
>>>>>To: <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
>>>>>Subject: Re: [Info-ingres] Weird problem in Ingres 10<br>
>>>>><br>
>>>>>On Wed, 16 May 2018 12:40:48 -0400, Karl and Betty Schendel<br>
>>>>><<a href="mailto:schendel@kbcomputer.com">schendel@kbcomputer.com</a>> wrote:<br>
>>>>><br>
>>>>>>SC930 tracing is server wide and runs until you stop it with set notrace sc930.<br>
>>>>>>So you can connect to any database, do the set trace record '/some/ingres-writable/dir'<br>
>>>>>>and the set trace point sc930, go to the trace record directory you gave it and<br>
>>>>>>ensure that there's at least something there (should be one or more sessNNNNN files),<br>
>>>>>>and then you can run your esql program until the fault happens.<br>
>>>>>><br>
>>>>>>You don't actually need to put the sc930 into the esql program unless you want to<br>
>>>>>>do it that way.<br>
>>>>>><br>
>>>>>>Once you capture what you need to capture, set notrace point sc930 turns it off.<br>
>>>>>><br>
>>>>>>You'll need the trace point privilege, simplest is to do it as the installation owner<br>
>>>>>>(user ingres, traditionally).<br>
>>>>><br>
>>>>>Thanks again. We now have traces but they don't help me. The traces<br>
>>>>>include the SQL statement and the data but, pretty much, nothing else.<br>
>>>>><br>
>>>>>The only notable thing I observed is that the data is traced twice for<br>
>>>>>the failed transaction and only once for the successful ones.<br>
>>>>><br>
>>>>>There are no error messages.<br>
>>>>><br>
>>>>>>Karl<br>
>>>>>><br>
>>>>>>> On May 16, 2018, at 11:12 AM, Mark <<a href="mailto:i@dontgetlotsofspamanymore.net">i@dontgetlotsofspamanymore.net</a>> wrote:<br>
>>>>>>> <br>
>>>>>>> On Wed, 16 May 2018 12:11:08 +0000, Martin Bowes<br>
>>>>>>> <<a href="mailto:martin.bowes@ndph.ox.ac.uk">martin.bowes@ndph.ox.ac.uk</a>> wrote:<br>
>>>>>>> <br>
>>>>>>>> Just to expand on Karl's sc930...You may already know this...<br>
>>>>>>>> <br>
>>>>>>>> To turn it on, make a recording directory...mkdir /full/path/to/directory'<br>
>>>>>>>> <br>
>>>>>>>> And then...<br>
>>>>>>>> sql iidbdb << SQL_END<br>
>>>>>>>> set trace record '/full/path/to/directory';<br>
>>>>>>>> set trace point sc930 1;<br>
>>>>>>>> \p\g<br>
>>>>>>>> \q<br>
>>>>>>>> SQL_END<br>
>>>>>>> <br>
>>>>>>> Do I need to run this on the server account? I have implemented it<br>
>>>>>>> currently in the embedded SQL of my program.<br>
>>>>>>> <br>
>>>>>>>> FYI. The digit after the sc930 indicates a tracing level, 1 should be sufficient.<br>
>>>>>>>> <br>
>>>>>>>> Run the errant query.<br>
>>>>>>>> <br>
>>>>>>>> And turn off the sc930.<br>
>>>>>>>> sql iidbdb << SQL_END<br>
>>>>>>>> set trace point sc930 0;<br>
>>>>>>>> \p\g<br>
>>>>>>>> \q<br>
>>>>>>>> SQL_END<br>
>>>>>>>> <br>
>>>>>>>> You can now access the recording directory and start scanning the files for any sign of life from your query.<br>
>>>>>>>> <br>
>>>>>>>> Marty<br>
>>>>>>>> <br>
>>>>>>>> -----Original Message-----<br>
>>>>>>>> From: Karl and Betty Schendel [<a href="mailto:schendel@kbcomputer.com">mailto:schendel@kbcomputer.com</a>]
<br>
>>>>>>>> Sent: 16 May 2018 12:54<br>
>>>>>>>> To: Ingres and related product discussion forum<br>
>>>>>>>> Subject: Re: [Info-ingres] Weird problem in Ingres 10<br>
>>>>>>>> <br>
>>>>>>>> On May 16, 2018, at 7:44 AM, Mark <<a href="mailto:i@dontgetlotsofspamanymore.net">i@dontgetlotsofspamanymore.net</a>> wrote:<br>
>>>>>>>>> <br>
>>>>>>>>> On Tue, 15 May 2018 12:27:09 -0400, Karl and Betty Schendel<br>
>>>>>>>>> <<a href="mailto:schendel@kbcomputer.com">schendel@kbcomputer.com</a>> wrote:<br>
>>>>>>>>> <br>
>>>>>>>>>> It's certainly not something I have heard of or seen before. Do you have any rules<br>
>>>>>>>>>> defined on the relevant tables? Try enabling LOG_TRACE if the problem is<br>
>>>>>>>>>> sufficiently predictable, or do a logdump after the problem occurs if it's not;<br>
>>>>>>>>>> the idea being to try to see whether you actually got any PUT (insert) or<br>
>>>>>>>>>> REP (replace) log records that were then rolled back, or whether the insert / update<br>
>>>>>>>>>> was never executed at all.<br>
>>>>>>>>> <br>
>>>>>>>>> After enabling log_trace all I got was:<br>
>>>>>>>>> <br>
>>>>>>>>> LOG: SAVEPOINT Size written/reserved: 0/ 0 Flags:<br>
>>>>>>>>> -------------------------------------------------------------------<br>
>>>>>>>> <br>
>>>>>>>> So the insert/update isn't ever being executed. Either it's failing with some sort of<br>
>>>>>>>> silent error, which would seem odd, or it's not reaching the backend at all, or<br>
>>>>>>>> it's being pre-empted by a before rule. I think the next step would be to enable<br>
>>>>>>>> sc930 tracing and see if the backend is getting the insert-update, and what<br>
>>>>>>>> end-of-query status it's recording. There should be KB articles on enabling<br>
>>>>>>>> SC930 tracing.<br>
>>>>>>>> <br>
>>>>>>>> Karl<br>
>>>>>>>> <br>
>>>>>>>> _______________________________________________<br>
>>>>>>>> Info-ingres mailing list<br>
>>>>>>>> <a href="mailto:Info-ingres@lists.planetingres.org">Info-ingres@lists.planetingres.org</a><br>
>>>>>>>> <a href="http://lists.planetingres.org/mailman/listinfo/info-ingres">http://lists.planetingres.org/mailman/listinfo/info-ingres</a><br>
>>>>>>> <br>
>>>>>>> -- <br>
>>>>>>> <insert witty sig here><br>
>>>>>>> _______________________________________________<br>
>>>>>>> Info-ingres mailing list<br>
>>>>>>> <a href="mailto:Info-ingres@lists.planetingres.org">Info-ingres@lists.planetingres.org</a><br>
>>>>>>> <a href="http://lists.planetingres.org/mailman/listinfo/info-ingres">http://lists.planetingres.org/mailman/listinfo/info-ingres</a><br>
<br>
-- <br>
<insert witty sig here><br>
_______________________________________________<br>
Info-ingres mailing list<br>
<a href="mailto:Info-ingres@lists.planetingres.org">Info-ingres@lists.planetingres.org</a><br>
<a href="http://lists.planetingres.org/mailman/listinfo/info-ingres">http://lists.planetingres.org/mailman/listinfo/info-ingres</a><br>
_______________________________________________<br>
Info-ingres mailing list<br>
<a href="mailto:Info-ingres@lists.planetingres.org">Info-ingres@lists.planetingres.org</a><br>
<a href="http://lists.planetingres.org/mailman/listinfo/info-ingres">http://lists.planetingres.org/mailman/listinfo/info-ingres</a><o:p></o:p></span></p>
</div>
</body>
</html>