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.
[Info-ingres] [ingres] [Info-ingres] Logging trace conundrum - what am I missing?
2 posts
• Page 1 of 1
- mac
- Windows XP
- ASP.NET
- sharepoint
- apple2
- Windows
- mac
- FLASH
- delphi
Jump to:
Sponsored Links
- 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
Next
[Info-ingres] Re: [ingres] [Info-ingres] Re: Logging trace conundrum - what am I missing?
by Paul Mason » Sat, 26 Mar 2005 07:19:21 GMT
Re: [Info-ingres] Re: [ingres] [Info-ingres] Re: Logging trace conundrum - what am I missing?
by 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
2 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 70 guest