[Info-ingres] [ingres] [Info-ingres] Logging trace conundrum - what am I missing?

DATABASE

    Sponsored Links

    Next

  • 1. [Info-ingres] dbms sequences access
    Hi Simon, > >> Also, I'm increasingly convinced the user is not taking locks on > >> the sequence for the transaction lifetime. > > Err, isn't that a positive feature? The only time it could be a > problem is that it makes it possible for a gap in the sequence, where > an insert (or similiar) fails. Correct, I misunderstood the section of the manual relating to sequence locking. The nextval command wil leave a logical 'Sequnce' lock in place. This will not prevent other users issuing nextvals of their own - which is good. But it will prevent somone coming along and altering the sequence with the alter command to say bump the start value or increment. Which is also good! Marty
  • 2. BEA's Workshop Studio
    Is anyone looking at BEA's Workshop Studio for SOA development with Ingres? What should I think of it? Stop and look? Or keep walking? Roy

[Info-ingres] Re: [ingres] [Info-ingres] Re: Logging trace conundrum - what am I missing?

Postby Paul Mason » Sat, 26 Mar 2005 07:19:21 GMT



I can't speak for anyone else but I didn't see the original message. 
I've just checked google groups and the original appears as a reply to 
the reply - which is odd.



Re: [Info-ingres] Re: [ingres] [Info-ingres] Re: Logging trace conundrum - what am I missing?

Postby Roy Hann » Sat, 26 Mar 2005 07:26:43 GMT





Me neither, and someone else mentioned that he hadn't seen a c.d.i. posting
of mine show up on info-ingres.  Gateway problems again, methinks.

Roy



Similar Threads:

1.[Info-ingres] Logging trace conundrum - what am I missing ?

"Betty & Karl Schendel" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
> At 6:56 PM +1100 3/25/05, Paul White wrote:
> >After digesting all those embedded parenthesis, I thought I'd go
> >find out what Marty was talking about.  Yes, this query seems to
> >writes no entries to the log.
> >
> >update fred set mary = mary;
> >
> >Does that mean it doesn't need to do the physical write to the table
either?
> >I've got a bunch of queries similar to Marty's updating calculated
> >fields for reporting purposes. I thought I could save time / IO by
> >updating only if the field had the wrong answer in it.
> >
> >UPDATE fred SET mary = (complicated calculation)
> >WHERE (mary <> (complicated calculation)) or (mary is null)
> >
> >I'm thinking the operation might run better if it didnt have to do
> >the calculation twice.
>
> That would depend on how many rows qualify to be updated.
> There's a certain amount of work involved in deciding that a row
> qualifies for update, materializing the replacement expressions,
> and giving the row back to DMF.  It's fairly deep down within DMF
> that it realizes that the row didn't actually change.  Unless
> nearly all the rows are going to get that far anyway, you're
> probably better off doing the calculation twice to exclude rows
> that won't change.
>
> One of my current semi-background projects is improving the performance
> of expression evaluation.  Just how complicated is this complicated
> calculation?

I seriously doubt any real application is ever going to benefit from
improving the performance of expression evaluation.  Real applications spend
almost all their time doing inter-process and network I/O.  The piddly extra
microseconds spent evaluating an expression inside the server in a
less-than-ideal way is completely irrelevant.  It's like worrying about the
streamlining of a steam-roller.

But of course if you have expertly-crafted TPC benchmark tests on your mind
(as opposed to real applications) then pay me no heed!  :-)

Roy


2.[Info-ingres] Logging trace conundrum - what am I missing ?


3.[Info-ingres] Logging trace conundrum - what am I missing?

Hi Dudes,

II2.6/0305 for linux

Someone is going to answer this one with a simple one line response 
and I'm going to kick myself, but here goes...

I have a backup database that is destroyed and recreated every night. 
The database is not backed up with ckpdb. The data source is an 
increasingly large production database. The data is transferred over 
using copydb. After which a few scripts are run on the database. But 
these are designed to make data tables for reporting - the primary 
purpose of this 'backup' database.

So at the end of this the 'backup' database has been loaded, is not 
backed up and has no journaling. Default journaling is on for this 
installation. So each table is marked as 'Journaling:  enabled after the 
next checkpoint'. Plus we expect that the database will be slowly 
growing.

Everynow and then the log file fills up. I get both a Force Abort and then  
a Log Full.

Its pretty clear that the problems are in the extra little scripts run after 
the copydb. Thats where we find the errors! Its also pretty clear that 
there is nothing else running on the system at the time the errors occur.

Now whether or not the database is journaled, whether or not a table is 
journaled, I would expect update to behave the same in regards to 
logging. That is each row in a table being updated would have a before 
and after image written to the log file.

And this is exactly what set log_trace shows when I do this on a 
temporary database that has gone through a similar cycle.
update test1 t1 from test2 t2
    set another_integer=t2.another_integer
    where t1.study_id=t2.study_id;
Executing . . .


********************************************************************
    LOG: BT             Size written/reserved:   80/  56  Flags:
    LOG: REP            Size written/reserved:  118/ 170  Flags:
    LOG: REP            Size written/reserved:  118/ 170  Flags:
    LOG: REP            Size written/reserved:  118/ 170  Flags:
    LOG: REP            Size written/reserved:  118/ 170  Flags:
    LOG: REP            Size written/reserved:  118/ 170  Flags:
    LOG: REP            Size written/reserved:  118/ 170  Flags:
    LOG: REP            Size written/reserved:  118/ 170  Flags:
    LOG: REP            Size written/reserved:  118/ 170  Flags:
    LOG: REP            Size written/reserved:  118/ 170  Flags:
    LOG: REP            Size written/reserved:  118/ 170  Flags:
    LOG: ET             Size written/reserved:   56/   0  Flags:

********************************************************************
test1 has 10 rows (each of which will qualify for an update) and each 
row is 18characters. So The above shows 10 updates - sweet!

Why is it then that when I run a set log_trace on the backup database I 
get things like:
 set log_trace;
update marty_temp t
set wk_to_today = int4(
    (int4(date_part('year',date('today')))-int4(left(absolute_wk,4)) )*52
     +(int4(date_part('week',date('today')))-int4(right(absolute_wk,2))));
commit;
Executing . . .


********************************************************************
    LOG: SAVEPOINT      Size written/reserved:    0/   0  Flags:

********************************************************************
(50595 rows)

********************************************************************
    LOG: ET             Size written/reserved:   56/   0  Flags:

********************************************************************

So I have 50,000 updates of a table with nothing logged?

Given that the table marty_temp has rows 367 char wide. I would have 
expected to see the update cost me 50595 *((367+41)*2)=40M. (and 
double that to allow for CLR's) 
And in a system with a 130M log file I could see why I'm going to get an 
FAL and then a LFL particurly as the above update is one of many 
running (with autocommit on). So I'm at a loss to explain why this code 
just ran today and the log file barely twitched!

What am I missing??

Martin Bowes.
Random Farscape Quote #16:
John - I'd like to leave this planet the way I got here. Alive and single.


 

4.[Info-ingres] Logging trace conundrum - what am I missing?

> Hi Dudes,
> 
> II2.6/0305 for linux
> 
> Someone is going to answer this one with a simple one line response
> and I'm going to kick myself, but here goes...

	Judging by the thundering silence either:
- No one cares.               
- No one believes me 
- No one knows

After a considerable amount of experimentation and whacking my head 
against a desk, and then experimenting on whacking my head against a 
desk...

The query does not allow for the nullabiliity of the column absolute_wk.
update temp_rec t
set wk_to_today = int4(
    (int4(date_part('year',date('today')))-int4(left(absolute_wk,4)) )*52
         +(int4(date_part('week',date('today')))-int4(right(absolute_wk,2))));

Executing . . .


********************************************************************
    LOG: BT             Size written/reserved:   80/  56  Flags:
    LOG: SAVEPOINT      Size written/reserved:    0/   0  Flags:

********************************************************************
(50001 rows)

And checking with logstat shows zero% log in use.

But if we change the query to allow for nullability:
update temp_rec t
set wk_to_today = int4(
    (int4(date_part('year',date('today')))-
int4(left(ifnull(absolute_wk,'200501'),4)) )*52
         +(int4(date_part('week',date('today')))-
int4(right(ifnull(absolute_wk,'200501'),2))));

Executing . . .


********************************************************************
    LOG: BT             Size written/reserved:   80/  56  Flags:
    LOG: REP            Size written/reserved:  474/ 519  Flags:
    LOG: REP            Size written/reserved:  474/ 519  Flags:
    LOG: REP            Size written/reserved:  474/ 519  Flags:
...etc...
    LOG: REP            Size written/reserved:  474/ 519  Flags: 
    LOG: REP            Size written/reserved:  474/ 519  Flags:
    LOG: REP            Size written/reserved:  474/ 519  Flags: 
    LOG: SAVEPOINT      Size written/reserved:    0/   0  Flags:

********************************************************************
(50001 rows)

And checking with logstat shows 43% of log in use - which is in 
agreement with expectations given the size of the table and number of 
rows updated.

Okay so, how does an update of 50,001 rows not log?

Did I mention that I'm an idiot.

When I started investigating the log overflow issue I set up a test table 
and just added enough data to make the query work. - that is - without 
error. 

I didn't actually put data into the nullable field. The original query now 
runs on this test table and says it did 50,001 updates. It does! It 
updates a null to a null. The logging system is way smarter than me 
and sees that there is nothing to do here! So no logging required.

When I 'fixed' the query, the ifnull gives it some data, so there are 
50,001 genuine updates! ergo 50,001 log entries.

This also explains the volatility in the log usage from day to day. Yes 
the update will 'update' every row in the table, but for most of a week - 
not every row changes. 

Well that was an interesting way to spend the last day at work. Now I'll 
get back to resizing the log.

Martin Bowes.
--
Random Duckman Quote #103:
Duckman: If it's sex you need, it's sex you shall have.
Cornfed: I'm flattered, but tradition says it has to be with a
woman...unless that's not what you meant.
Duckman: Ahhh...Ah--no...course that's not what I meant.








<?xml  version="1.0" ?><html>
<head>
<title></title>
</head>
<body>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">> Hi Dudes,</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">> </span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">> II2.6/0305 for linux</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">> </span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">> Someone is going to answer this one with a simple one line response</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">> and I'm going to kick myself, but here goes...</span></font></div>
<div align="left"><br/></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">            Judging by the thundering silence either:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">- No one cares.               </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">- No one believes me </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">- No one knows</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">After a considerable amount of experimentation and whacking my head 
against a desk, and then experimenting on whacking my head against a 
desk...</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">The query does not allow for the nullabiliity of the column absolute_wk.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">update temp_rec t</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">set wk_to_today = int4(</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    (int4(date_part('year',date('today')))-int4(left(absolute_wk,4)) )*52</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">         +(int4(date_part('week',date('today')))-int4(right(absolute_wk,2))));</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Executing . . .</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">********************************************************************</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    LOG: BT             Size written/reserved:   
80/  56  Flags:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    LOG: SAVEPOINT      Size written/reserved:    0/   
0  Flags:</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">********************************************************************</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">(50001 rows)</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">And checking with logstat shows zero% log in use.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">But if we change the query to allow for nullability:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">update temp_rec t</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">set wk_to_today = int4(</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    (int4(date_part('year',date('today')))-
int4(left(ifnull(absolute_wk,'200501'),4)) )*52</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">         +(int4(date_part('week',date('today')))-
int4(right(ifnull(absolute_wk,'200501'),2))));</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Executing . . .</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">********************************************************************</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    LOG: BT             Size written/reserved:   
80/  56  Flags:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    LOG: REP            Size written/reserved:  
474/ 519  Flags:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    LOG: REP            Size written/reserved:  
474/ 519  Flags:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    LOG: REP            Size written/reserved:  
474/ 519  Flags:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">...etc...</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    LOG: REP            Size written/reserved:  
474/ 519  Flags: </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    LOG: REP            Size written/reserved:  
474/ 519  Flags:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    LOG: REP            Size written/reserved:  
474/ 519  Flags: </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    LOG: SAVEPOINT      Size written/reserved:    0/   
0  Flags:</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">********************************************************************</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">(50001 rows)</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">And checking with logstat shows 43% of log in use - which is in 
agreement with expectations given the size of the table and number of 
rows updated.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Okay so, how does an update of 50,001 rows not log?</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Did I mention that I'm an idiot.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">When I started investigating the log overflow issue I set up a test table 
and just added enough data to make the query work. - that is - without 
error. </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">I didn't actually put data into the nullable field. The original query now 
runs on this test table and says it did 50,001 updates. It does! It 
updates a null to a null. The logging system is way smarter than me 
and sees that there is nothing to do here! So no logging required.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">When I 'fixed' the query, the ifnull gives it some data, so there are 
50,001 genuine updates! ergo 50,001 log entries.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">This also explains the volatility in the log usage from day to day. Yes 
the update will 'update' every row in the table, but for most of a week - 
not every row changes. </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Well that was an interesting way to spend the last day at work. Now I'll 
get back to resizing the log.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Martin Bowes.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">--</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Random Duckman Quote #103:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Duckman: If it's sex you need, it's sex you shall have.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Cornfed: I'm flattered, but tradition says it has to be with a</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">woman...unless that's not what you meant.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Duckman: Ahhh...Ah--no...course that's not what I meant.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><br/>
</div>
<div align="left"><br/>
</div>
<div align="left"><br/>
</div>
<div align="left"><br/>
</div>
<div align="left"><br/>
</div>
<div align="left"></div>
</body>
</html>

5.[Info-ingres] RES: [Info-ingres] RES: [Info-ingres] SQL Injection attacks

> -----Mensagem original-----
> De:  XXXX@XXXXX.COM  
> [mailto: XXXX@XXXXX.COM ] Em nome de Emiliano
> Enviada em: Monday, June 12, 2006 10:06 AM
> Para:  XXXX@XXXXX.COM 
> Assunto: Re: [Info-ingres] RES: [Info-ingres] SQL Injection attacks
> 
> On 2006-06-12, Leandro Pinto Fava < XXXX@XXXXX.COM > wrote:
> > Three years ago we had a case of SQL Injection against our web
portal of
> > students's info. This portal was made using ICE and reports in 1999
> > (with very bad security control). Now we have this portal made in
PHP
> > and the possibility of SQL injection is nearly null (I think :-().
We
> > had another web application (ASP) that suffered a successful SQL
> > injetcion too. The problems were corrected as well.
> 
> And (to hook into the delightful discussion I'm having with Roy), I'll
> bet you dimes to dollars that both were using query assembly.

The ASP app was, but the ICE app was not directly. Report Writer
internally should work with query assembly when passing parameters to
run a report.

> 
> The PHP function addslashes ought to protect you if you use it
> consistently. PHP ADODb has parameter binds, which are better.

Yes.

> 
> > In our case the problems were in the application layer.
> 
> HTML injection?

No, when I said application layer, I wanted to say the problem was not
in database server.

Leandro.

6. [Info-ingres] [ingres] [Info-ingres] Dates users were created in Ingres

7. [Info-ingres] RES: [Info-ingres] RES: [Info-ingres] SQL Injection attacks

8. [Info-ingres] OpenAPI connect - what am I missing?



Return to DATABASE

 

Who is online

Users browsing this forum: No registered users and 70 guest