Re: [firebird-support] update all records

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

Sergio asked why this statement

  update stock set actualizar = 0 where 1=1

doesn't update all records in the stock table.   First, the clause "where
1=1" is unnecessary.  The update statement does not requires a where clause
and in the absence of a where clause, it affects all records in the table.

, liviusliv...@poczta.onet.pl [firebird-support] responded


> one of possible reason is that records are not visible to transaction with
> this update statement.
> e.g.
> 1. you start transaction (tr1)
> 2. someone start transaction 2(tr2)
> 3. tr2 insert some records
> 4. tr1 run update
> 5. tr2 commit
> 6. tr1 commit
>
all records inserted by tr2 are not updated if tr1 is e.g. snapshot
> or during tr1 update there are inserts.
>

Actually, that's not true.  If tr2 inserts records before tr1 attempts its
update, tr1 will discover an update conflict when it finds one of tr2's new
records.  If tr1 is a no-wait transaction, it will report an error
immediately otherwise it will wait for tr2's commit.  Then, if tr1 is a
snapshot transaction, when tr2 commits, tr1 will report an error.  If tr1
is a read-committed transaction, it will silently update tr2's new records
without having looked at them.*

If, however, the sequence is that both transactions start, then tr1 does
its update and tr2 stores its new records after the update completes, both
transactions will succeed and the records inserted by tr2 will not be
updated.  Which would also be the case if tr1 ran to completion before tr2
started.

What's slightly more likely is that tr1 and tr2 are concurrent and the
sequence of actions is that tr1 modifies all the stock records then tr2
reads all the stock records and sees the old versions (assuming that tr2 is
not a "no record version" transaction).  Even if tr2 is a read-committed
transaction, it will read the next older version of records if the most
recent is not committed.

So, Sergio, a bit more information would help us give you a better answer.
Are there multiple transactions running?  Why do you think some records
weren't updated?  Do you know what transaction options you're using?

Cheers,

Ann

* If the inserts and update are running at the same time, the most likely
case is that tr2's inserts will be physically the last entries in the
table.  Tr1's updates will be made in storage order, so the last thing
records it tries to modify will be the records tr2 created.  If the table
has had lots of records deleted, it's possible (I think) for tr2 to store
its records on partially empty pages in the first part of the table and
avoid a collision with tr1 if tr1 has already modified the records if found
on those pages.  That's very similar to having tr1 perform its update
before tr2 stores its records.

Re: [firebird-support] update all records

[liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

1=1 => true

“where true” is always true
this is oposite to 1=0 => false

and this statements are equal:
update stock set actualizar = 0 where 1=1;
update stock set actualizar = 0;

regards,
Karol Bieniaszewski




From: mailto:firebird-support@yahoogroups.com 
Sent: Thursday, May 5, 2016 9:55 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] update all records

  

Hello, 


I don't understand your SQL :
"Where 1=1"   first "1" is a field name ?


if "Where actualizar=1" it may works ?
The condition is on records, and must use fields values...no ?


If you don't use condition, all records should be updated to the same value 0...


"Update stock set actualizar=0"  should update all records with actualizar =0 
to whole stock table... 


With best regards,
Mikey



Le Jeudi 5 mai 2016 19h19, "kristinwens...@yahoo.com [firebird-support]" 
 a écrit :




  
Thanks



On Wednesday, May 4, 2016 6:45 PM, "shg_siste...@yahoo.com.ar 
[firebird-support]"  wrote:




  
hello! I've been googling but found nothing about it.


Why this sentence does not update all the records in the table? (FB 2.5)


update stock set actualizar = 0 where 1=1



Thanks!!


Sergio

Re: [firebird-support] update all records

[Michel LE CLEZIO mlcvi...@yahoo.fr [firebird-support]]

Hello, 
I don't understand your SQL :"Where 1=1"   first "1" is a field name ?
if "Where actualizar=1" it may works ?The condition is on records, and must use 
fields values...no ?
If you don't use condition, all records should be updated to the same value 0...
"Update stock set actualizar=0"  should update all records with actualizar =0 
to whole stock table... 
With best regards,Mikey 

Le Jeudi 5 mai 2016 19h19, "kristinwens...@yahoo.com [firebird-support]" 
 a écrit :
 

     Thanks 

On Wednesday, May 4, 2016 6:45 PM, "shg_siste...@yahoo.com.ar 
[firebird-support]"  wrote:
 

     hello! I've been googling but found nothing about it.
Why this sentence does not update all the records in the table? (FB 2.5)
update stock set actualizar = 0 where 1=1

Thanks!!
Sergio  

 #yiv9733264172 #yiv9733264172 -- #yiv9733264172ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9733264172 
#yiv9733264172ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9733264172 
#yiv9733264172ygrp-mkp #yiv9733264172hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv9733264172 #yiv9733264172ygrp-mkp #yiv9733264172ads 
{margin-bottom:10px;}#yiv9733264172 #yiv9733264172ygrp-mkp .yiv9733264172ad 
{padding:0 0;}#yiv9733264172 #yiv9733264172ygrp-mkp .yiv9733264172ad p 
{margin:0;}#yiv9733264172 #yiv9733264172ygrp-mkp .yiv9733264172ad a 
{color:#ff;text-decoration:none;}#yiv9733264172 #yiv9733264172ygrp-sponsor 
#yiv9733264172ygrp-lc {font-family:Arial;}#yiv9733264172 
#yiv9733264172ygrp-sponsor #yiv9733264172ygrp-lc #yiv9733264172hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9733264172 
#yiv9733264172ygrp-sponsor #yiv9733264172ygrp-lc .yiv9733264172ad 
{margin-bottom:10px;padding:0 0;}#yiv9733264172 #yiv9733264172actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9733264172 
#yiv9733264172activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9733264172
 #yiv9733264172activity span {font-weight:700;}#yiv9733264172 
#yiv9733264172activity span:first-child 
{text-transform:uppercase;}#yiv9733264172 #yiv9733264172activity span a 
{color:#5085b6;text-decoration:none;}#yiv9733264172 #yiv9733264172activity span 
span {color:#ff7900;}#yiv9733264172 #yiv9733264172activity span 
.yiv9733264172underline {text-decoration:underline;}#yiv9733264172 
.yiv9733264172attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv9733264172 .yiv9733264172attach div a 
{text-decoration:none;}#yiv9733264172 .yiv9733264172attach img 
{border:none;padding-right:5px;}#yiv9733264172 .yiv9733264172attach label 
{display:block;margin-bottom:5px;}#yiv9733264172 .yiv9733264172attach label a 
{text-decoration:none;}#yiv9733264172 blockquote {margin:0 0 0 
4px;}#yiv9733264172 .yiv9733264172bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv9733264172 
.yiv9733264172bold a {text-decoration:none;}#yiv9733264172 dd.yiv9733264172last 
p a {font-family:Verdana;font-weight:700;}#yiv9733264172 dd.yiv9733264172last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9733264172 
dd.yiv9733264172last p span.yiv9733264172yshortcuts 
{margin-right:0;}#yiv9733264172 div.yiv9733264172attach-table div div a 
{text-decoration:none;}#yiv9733264172 div.yiv9733264172attach-table 
{width:400px;}#yiv9733264172 div.yiv9733264172file-title a, #yiv9733264172 
div.yiv9733264172file-title a:active, #yiv9733264172 
div.yiv9733264172file-title a:hover, #yiv9733264172 div.yiv9733264172file-title 
a:visited {text-decoration:none;}#yiv9733264172 div.yiv9733264172photo-title a, 
#yiv9733264172 div.yiv9733264172photo-title a:active, #yiv9733264172 
div.yiv9733264172photo-title a:hover, #yiv9733264172 
div.yiv9733264172photo-title a:visited {text-decoration:none;}#yiv9733264172 
div#yiv9733264172ygrp-mlmsg #yiv9733264172ygrp-msg p a 
span.yiv9733264172yshortcuts 
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9733264172 
.yiv9733264172green {color:#628c2a;}#yiv9733264172 .yiv9733264172MsoNormal 
{margin:0 0 0 0;}#yiv9733264172 o {font-size:0;}#yiv9733264172 
#yiv9733264172photos div {float:left;width:72px;}#yiv9733264172 
#yiv9733264172photos div div {border:1px solid 
#66;height:62px;overflow:hidden;width:62px;}#yiv9733264172 
#yiv9733264172photos div label 
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9733264172
 #yiv9733264172reco-category {font-size:77%;}#yiv9733264172 
#yiv9733264172reco-desc {font-size:77%;}#yiv9733264172 .yiv9733264172replbq 
{margin:4px;}#yiv9733264172 #yiv9733264172ygrp-actbar div a:first-child 
{margin-right:2px;padding-right:5px;}#yiv9733264172 #yiv9733264172ygrp-mlmsg 
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9733264172 
#yiv9733264172ygrp-mlmsg table 

Re: [firebird-support] update all records

[liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

one of possible reason is that records are not visible to transaction with this 
update statement.
e.g. 
1. you start transansction (tr1)
2. someone start transaction 2(tr2)
3. tr2 insert some records
4. tr1 run update
5. tr2 commit
6. tr1 commit

all records inserted by tr2 are not updated if tr1 is e.g. snapshot
or during tr1 update there are inserts.


regards,
Karol Bieniaszewski

From: mailto:firebird-support@yahoogroups.com 
Sent: Wednesday, May 4, 2016 8:45 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] update all records

  

hello! I've been googling but found nothing about it.




Why this sentence does not update all the records in the table? (FB 2.5)




update stock set actualizar = 0 where 1=1





Thanks!!




Sergio

Re: [firebird-support] How to discover which architecture is installed on Windows

[shouldbe q931 shouldbeq...@gmail.com [firebird-support]]

On Wed, May 4, 2016 at 5:44 AM, Helen Borrie hele...@iinet.net.au
[firebird-support]  wrote:

>
>
> Hello shouldbe,
>
> Tuesday, May 3, 2016, 11:35:31 PM, you wrote:
>
> > Hi,
>
> > I've inherited a Firebird database on Windows without any
> > documentation, and I'm trying to document how it is setup if it is
> > ever needed to be rebuilt.
>
> The database and the server are two separate things. You can access a
> database from any model (Superserver, Classic or embedded, for v.2.1).
>
> > Windows reports that the product version is 2.1.2.18118
>
> That's the build number, i.e., Firebird 2.1, sub-release 2 (very old),
> build 18118.
>
> > Perfmon reports that fbserver is using 16 threads, which following
> >
> http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/qsg2-classic-or-super.html
> > makes me presume it is "Superserver", but is there any other way of
> > confirming ?
>
> Yes, probably SS. But the most conclusive way is to inspect the
> executable details in the Services applet. For SS, the executable is
> fbserver.exe; for Classic, fb_inet_server.exe.
>
> Not having seen either before, I didn't even think that the filename would
be the decider, its fbserver,exe, so must be SS


> The installation might also have the Guardian running (fbguard.exe) as
> a service. That's an option that will restart the Firebird SS service
> if it is stopped for some reason. It's unnecessary for SS running as
> a service (Windows services can be set up to do it anyway) but useful
> if, for some reason, SS is running as an application. Guardian should
> NOT be used with Classic.
>
> yes, it has fbguard, from the link I posted it looked as if SS could be
run with guard, but only as a service, which is the case.


> If it is an embedded installation, that's different, as the server and
> client components are merged into one DLL and there won't be a service
> installed. Its "native" name is fbembed.dll but it is often renamed
> to fbclient.dll or gds32.dll and would be located in the same
> directory as the client application. The (possibly renamed)
> fbembed.dll is used in lieu of the client-only library, fbclient.dll.
>
> Also, if you are using v.2.1, it is STRONGLY recommended that you
> upgrade to the newest sub-release (2.1.7, last-ever for 2.1!) It is
> still v.2.1, so you don't need to upgrade the database on-disk
> structure, but it has about a zillion bugs fixed across the long, long
> life of v.2.1.
>
That's very useful to know, I'll add that to the list.

Hats off for deciding to document. It's no fun to get a system
> landing on you without it! Did you know that you can use isql
> -extract to extract a complete script of your metadata?
>
> HB
>
Without documentation life can become difficult very easily...

Hopefully I'll be able to figure out the rest.

Many Thanks!

Re: [firebird-support] update all records

[kristinwens...@yahoo.com [firebird-support]]

Thanks 

On Wednesday, May 4, 2016 6:45 PM, "shg_siste...@yahoo.com.ar 
[firebird-support]"  wrote:
 

     hello! I've been googling but found nothing about it.
Why this sentence does not update all the records in the table? (FB 2.5)
update stock set actualizar = 0 where 1=1

Thanks!!
Sergio  #yiv5869094069 #yiv5869094069 -- #yiv5869094069ygrp-mkp {border:1px 
solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5869094069 
#yiv5869094069ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5869094069 
#yiv5869094069ygrp-mkp #yiv5869094069hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv5869094069 #yiv5869094069ygrp-mkp #yiv5869094069ads 
{margin-bottom:10px;}#yiv5869094069 #yiv5869094069ygrp-mkp .yiv5869094069ad 
{padding:0 0;}#yiv5869094069 #yiv5869094069ygrp-mkp .yiv5869094069ad p 
{margin:0;}#yiv5869094069 #yiv5869094069ygrp-mkp .yiv5869094069ad a 
{color:#ff;text-decoration:none;}#yiv5869094069 #yiv5869094069ygrp-sponsor 
#yiv5869094069ygrp-lc {font-family:Arial;}#yiv5869094069 
#yiv5869094069ygrp-sponsor #yiv5869094069ygrp-lc #yiv5869094069hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5869094069 
#yiv5869094069ygrp-sponsor #yiv5869094069ygrp-lc .yiv5869094069ad 
{margin-bottom:10px;padding:0 0;}#yiv5869094069 #yiv5869094069actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5869094069 
#yiv5869094069activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5869094069
 #yiv5869094069activity span {font-weight:700;}#yiv5869094069 
#yiv5869094069activity span:first-child 
{text-transform:uppercase;}#yiv5869094069 #yiv5869094069activity span a 
{color:#5085b6;text-decoration:none;}#yiv5869094069 #yiv5869094069activity span 
span {color:#ff7900;}#yiv5869094069 #yiv5869094069activity span 
.yiv5869094069underline {text-decoration:underline;}#yiv5869094069 
.yiv5869094069attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv5869094069 .yiv5869094069attach div a 
{text-decoration:none;}#yiv5869094069 .yiv5869094069attach img 
{border:none;padding-right:5px;}#yiv5869094069 .yiv5869094069attach label 
{display:block;margin-bottom:5px;}#yiv5869094069 .yiv5869094069attach label a 
{text-decoration:none;}#yiv5869094069 blockquote {margin:0 0 0 
4px;}#yiv5869094069 .yiv5869094069bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv5869094069 
.yiv5869094069bold a {text-decoration:none;}#yiv5869094069 dd.yiv5869094069last 
p a {font-family:Verdana;font-weight:700;}#yiv5869094069 dd.yiv5869094069last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5869094069 
dd.yiv5869094069last p span.yiv5869094069yshortcuts 
{margin-right:0;}#yiv5869094069 div.yiv5869094069attach-table div div a 
{text-decoration:none;}#yiv5869094069 div.yiv5869094069attach-table 
{width:400px;}#yiv5869094069 div.yiv5869094069file-title a, #yiv5869094069 
div.yiv5869094069file-title a:active, #yiv5869094069 
div.yiv5869094069file-title a:hover, #yiv5869094069 div.yiv5869094069file-title 
a:visited {text-decoration:none;}#yiv5869094069 div.yiv5869094069photo-title a, 
#yiv5869094069 div.yiv5869094069photo-title a:active, #yiv5869094069 
div.yiv5869094069photo-title a:hover, #yiv5869094069 
div.yiv5869094069photo-title a:visited {text-decoration:none;}#yiv5869094069 
div#yiv5869094069ygrp-mlmsg #yiv5869094069ygrp-msg p a 
span.yiv5869094069yshortcuts 
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5869094069 
.yiv5869094069green {color:#628c2a;}#yiv5869094069 .yiv5869094069MsoNormal 
{margin:0 0 0 0;}#yiv5869094069 o {font-size:0;}#yiv5869094069 
#yiv5869094069photos div {float:left;width:72px;}#yiv5869094069 
#yiv5869094069photos div div {border:1px solid 
#66;height:62px;overflow:hidden;width:62px;}#yiv5869094069 
#yiv5869094069photos div label 
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5869094069
 #yiv5869094069reco-category {font-size:77%;}#yiv5869094069 
#yiv5869094069reco-desc {font-size:77%;}#yiv5869094069 .yiv5869094069replbq 
{margin:4px;}#yiv5869094069 #yiv5869094069ygrp-actbar div a:first-child 
{margin-right:2px;padding-right:5px;}#yiv5869094069 #yiv5869094069ygrp-mlmsg 
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5869094069 
#yiv5869094069ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5869094069 
#yiv5869094069ygrp-mlmsg select, #yiv5869094069 input, #yiv5869094069 textarea 
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv5869094069 
#yiv5869094069ygrp-mlmsg pre, #yiv5869094069 code {font:115% 
monospace;}#yiv5869094069 #yiv5869094069ygrp-mlmsg * 
{line-height:1.22em;}#yiv5869094069 #yiv5869094069ygrp-mlmsg #yiv5869094069logo 
{padding-bottom:10px;}#yiv5869094069 #yiv5869094069ygrp-msg p a 
{font-family:Verdana;}#yiv5869094069 #yiv5869094069ygrp-msg 
p#yiv5869094069attach-count span 

[firebird-support] update all records

[shg_siste...@yahoo.com.ar [firebird-support]]

hello! I've been googling but found nothing about it.
 

 Why this sentence does not update all the records in the table? (FB 2.5)
 

 update stock set actualizar = 0 where 1=1

 

 Thanks!!
 

 Sergio

Re: [firebird-support] Fast Slow Stored procedure

[millsjac...@yahoo.com [firebird-support]]

Hi Thomas

I did a trace as you suggested.
The slow SP shows these 3 consecutive statements showing a 20 min gap of no 
activity

2016-05-04T14:25:30.5020 (3268:061B51F8) EXECUTE_PROCEDURE_START
C:\MAGUSDB\FBDB\SAMPLE1.FDB (ATT_45, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Magus\Magus C++\Latest Developement IBO Test\MagusManager.exe:8304
(TRA_382, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

Procedure CLEAR_BUILD_DXS:
param0 = integer, "0"


2016-05-04T14:45:39.7880 (3268:061B51F8) PREPARE_STATEMENT
C:\MAGUSDB\FBDB\SAMPLE1.FDB (ATT_45, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Magus\Magus C++\Latest Developement IBO Test\MagusManager.exe:8304
(TRA_382, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

Statement 605:
---
Insert into TEMP_IMPORT_DXS (DXS_NO, ABS_NO, DB,  DW, VALUE1, USED,
SPACE, UOP, EOL)
select DXS_NO, ABS_NO, DB, DW, VALUE1, USED, SPACE, UOP, EOL from 
IMPORT_DXS
^^^

The fast SP there is a only a small gap

2016-05-05T07:24:05.3290 (3268:061B51F8) EXECUTE_PROCEDURE_START
C:\MAGUSDB\FBDB\SAMPLE1.FDB (ATT_52, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Magus\Magus C++\Latest Developement IBO Test\MagusManager.exe:5636
(TRA_417, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

Procedure CLEAR_BUILD_DXS:
param0 = integer, "0"


2016-05-05T07:24:13.4540 (3268:061B51F8) PREPARE_STATEMENT
C:\MAGUSDB\FBDB\SAMPLE1.FDB (ATT_52, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Magus\Magus C++\Latest Developement IBO Test\MagusManager.exe:5636
(TRA_417, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

Statement 605:
---
Insert into TEMP_IMPORT_DXS (DXS_NO, ABS_NO, DB,  DW, VALUE1, USED,
SPACE, UOP, EOL)
select DXS_NO, ABS_NO, DB, DW, VALUE1, USED, SPACE, UOP, EOL from 
IMPORT_DXS
^^^

Can you make any suggestion as to what happened for 20 min

Jack
 

[firebird-support] Re: Fast Slow Stored procedure

[millsjac...@yahoo.com [firebird-support]]

Hi Carlos
Thanks for the response.
I don't think so I have dome a backup & restore and still get slow running 
sometimes

Jack