no, i just install FB 2.1.3 and nothing change ! again the server crash and
closing the client not shunt down the fb_inet_server.exe :(
no other choice than restart the server... this become a serious problem for me
....

in the fb log i have this :

AKSERVER Wed Nov 25 20:36:59 2009
	 INET/inet_error: read errno = 10054


AKSERVER Wed Nov 25 20:36:59 2009
	 INET/inet_error: read errno = 10054


AKSERVER Wed Nov 25 20:36:59 2009
	 INET/inet_error: read errno = 10054

....

following by


AKSERVER Wed Nov 25 20:44:57 2009
	 Fatal lock manager error: inconsistent lock table version number; found 3,
expected 144, errno: 183


AKSERVER Wed Nov 25 20:44:57 2009
	 Database: C:\PROGRAM FILES\FIREBIRD\SECURITY2.FDB
	 lock manager error
	 inconsistent lock table version number; found 3, expected 144


AKSERVER Wed Nov 25 20:44:57 2009
	 lock manager error


AKSERVER Wed Nov 25 20:44:57 2009
	 inconsistent lock table version number; found 3, expected 144


AKSERVER Wed Nov 25 20:44:57 2009
	 Fatal lock manager error: inconsistent lock table version number; found 3,
expected 144, errno: 183


AKSERVER Wed Nov 25 20:44:57 2009
	 Database: C:\PROGRAM FILES\FIREBIRD\SECURITY2.FDB
	 lock manager error
	 inconsistent lock table version number; found 3, expected 144


AKSERVER Wed Nov 25 20:44:57 2009
	 lock manager error


any idea ??

thanks you by advance for your help !
stephane

--- In firebird-support@yahoogroups.com, Hannes Streicher <hstreicher@...>
wrote:
>
> Guten Tag svanderclock,
>
> am Dienstag, 24. November 2009 um 09:42 schrieben Sie:
>
>
> > let write all the circonstance when the server crash :
>
> > 1/ WinDows 2008 Server & FB classic server 2.1.2
>
> when using firebird 2.1.2  on a windows 2003 server i had a lot of
> problems with the dead connection detection , when a client crashed or
> was not shut down properly firebird 2.1.2 did not detect it.
> (not even over the weekend when all clients were shut turned of)
> it was still reported as connected and the transaction remained open
>
> it went away after upgrading to 2.1.3
> try it
>
> > 3/ Firebird bug ?
> possibly
>
>
> --
> Mit freundlichen Grüssen
> Hannes Streicher                            mailto:HStreicher@...
>

no, it's work perfectly, and return me an integer :)

--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:
>
> >
> > You can't cast directly, however you can do the following:
> >
> >   select cast(current_timestamp - cast('12/30/1899' as timestamp) as
> > integer)
> >   from rdb$database
> >
> > Subtracting 12/30/1899 will subtract 0 and not alter the date/time you
> > already have, however it will force the result to be returned as
> > numeric(18,9) which can then be cast as int.
> >
>
> But surely this will give the date only (when you cast).
> What does the op want? the time? or the date?
> Alan
>

>
> You can't cast directly, however you can do the following:
>
>   select cast(current_timestamp - cast('12/30/1899' as timestamp) as
> integer)
>   from rdb$database
>
> Subtracting 12/30/1899 will subtract 0 and not alter the date/time you
> already have, however it will force the result to be returned as
> numeric(18,9) which can then be cast as int.
>

But surely this will give the date only (when you cast).
What does the op want? the time? or the date?
Alan

PenWin wrote:
>>>
>> If you are deleting in one of the tables regularly, then you might
>> suffer from garbage collection at SELECT time, depending on the
>> architecture (SuperServer, ClassicServer) you use.
>>
> There are no deletes on either of the tables. If hiding from the user is
> required, it is done by setting history.deleted to 1.
>

Ah.  That's almost the same cost as a deletion - the old version of the
history record has to be garbage collected.  If there's an index on the
deleted field in history, the index entry must also be removed.  Since
there are likely to be LOTS of entries in that index with the same
the index deletion will be expensive ... less so in 2.0x than 1.5x.

Good luck,

Ann

Hi PenWin,

Just to rule out:

I once noticed the same behaviour (sudden slowness of a query), but it
turned out to be someone on the network was busy copying allmost an
entire harddisk to the server... IOW, a network congestion problem and a
very busy server....

regards,
John





PenWin wrote:
> Hi!
>
> Under Firebird 1.5.5 running under Windows Vista I have a query which
> exhibits great variance in execution times - sometimes it takes 1.5
> seconds, sometimes much longer (I have seen more than 9 minutes). The
> query tries to find records in the data table which are linked to
> obsolete records in the history table. Something like:
>
> Data ( DATAKEY integer, DATA1, DATA2, ..., CODE integer )
> History ( HISTKEY integer, HISTDATA1, HISTDATA2, ..., CODE integer,
> VALIDFROM timestamp, VALIDTO timestamp, DELETED integer )
>
> The query:
>
> SELECT d.datakey, MAX(h.histkey) histkey
> FROM data d
> LEFT JOIN history h ON h.code=d.code AND h.deleted=0 AND
> h.validfrom<='now' AND h.validto>'now'
> WHERE COALESCE(d.code,0)<>COALESCE(h.code,0)
> GROUP BY d.datakey
>
> Alternative version, with almost the same behavior (I do the COALESCEd
> compare in application rather than the query itself):
>
> SELECT d.datakey, (SELECT MAX(h.histkey) FROM history h WHERE
> h.code=d.code AND h.deleted=0 AND h.validfrom<='now' AND
> h.validto>'now') histkey
> FROM data d
>
> "Data" has some 70000 rows. "History" has about 10 rows for each "Data"
> row, with indexes on code, on validfrom and on validto (three distinct
> indexes). The execution plan (for the former query) looks fine to me:
>
> PLAN SORT (JOIN (Data NATURAL,History INDEX (codeidx,validfromidx)))
>
> What could be the problem?
>
>
> Thanks, Pepak
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>


--
John vd Waeter
www.jvdw.nl
www.gps-carpool.net
www.shotinthedark.nl
www.pdaforms.nl
www.dbapocket.nl

john@...

The only thing that I notice probably could be optimized, is that I doubt it
will ever be useful to use an index for validfrom (due to your description). So,
change to

SELECT d.datakey, MAX(h.histkey) histkey
FROM data d
LEFT JOIN history h ON h.code=d.code AND h.deleted=0 AND
h.validfrom+0<='now' AND h.validto+0>'now'
WHERE COALESCE(d.code,0)<>COALESCE(h.code,0)
GROUP BY d.datakey

Other than that, well, if you run an identical query (note the word identical,
it is not identical if it e.g. uses different parameters) twice on the same
database with vastly different execution time, then I'd expect either cache
(quicker on second run), different plan or something external to the query (e.g.
sweep) to be the reason.

One thing I noted on Firebird 1.5 not all too long ago, was that indexed fields
with lots of nulls made one of my queries very slow. However, that was not slow
sometimes, it was always slow.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
On Behalf Of PenWin
Sent: 25. november 2009 13:19
To: Firebird Support
Subject: [firebird-support] Sudden increase in execution times

Hi!

Under Firebird 1.5.5 running under Windows Vista I have a query which
exhibits great variance in execution times - sometimes it takes 1.5
seconds, sometimes much longer (I have seen more than 9 minutes). The
query tries to find records in the data table which are linked to
obsolete records in the history table. Something like:

Data ( DATAKEY integer, DATA1, DATA2, ..., CODE integer )
History ( HISTKEY integer, HISTDATA1, HISTDATA2, ..., CODE integer,
VALIDFROM timestamp, VALIDTO timestamp, DELETED integer )

The query:

SELECT d.datakey, MAX(h.histkey) histkey
FROM data d
LEFT JOIN history h ON h.code=d.code AND h.deleted=0 AND
h.validfrom<='now' AND h.validto>'now'
WHERE COALESCE(d.code,0)<>COALESCE(h.code,0)
GROUP BY d.datakey

Alternative version, with almost the same behavior (I do the COALESCEd
compare in application rather than the query itself):

SELECT d.datakey, (SELECT MAX(h.histkey) FROM history h WHERE
h.code=d.code AND h.deleted=0 AND h.validfrom<='now' AND
h.validto>'now') histkey
FROM data d

"Data" has some 70000 rows. "History" has about 10 rows for each "Data"
row, with indexes on code, on validfrom and on validto (three distinct
indexes). The execution plan (for the former query) looks fine to me:

PLAN SORT (JOIN (Data NATURAL,History INDEX (codeidx,validfromidx)))

What could be the problem?


Thanks, Pepak

You can't cast directly, however you can do the following:

   select cast(current_timestamp - cast('12/30/1899' as timestamp) as
integer)
   from rdb$database

Subtracting 12/30/1899 will subtract 0 and not alter the date/time you
already have, however it will force the result to be returned as
numeric(18,9) which can then be cast as int.


[Non-text portions of this message have been removed]

Hi,

|how to cast a timestamp field to Integer ? i can eventually truncate the
time of the TimeStamp.

you can cast (timestamp_field as date) and than you can subtract from some
"arbitrary" base date -- like the "zero" date that, *AFAIR* is 12/30/1899
but you need to confirm that.


Regards,
Anderson

so i can not use global tempory table unfortunatly :( but the idea of the global
tempory table (with use write off, another file, etc..) was pretty good for what
i need !

is it any other option instead of global tempory table? store the table in other
file ?

stephane

--- In firebird-support@yahoogroups.com, Dimitry Sibiryakov <sd@...> wrote:
>
> > not really, because if i understand weel, the Global Tempory Tables is only
connected to one connection !? it's mean that client B can not see in the 
Global Tempory Tables the row put by client A .. right ?
>
>    Right.
>
>    SY, SD.
>

> not really, because if i understand weel, the Global Tempory Tables is only
connected to one connection !? it's mean that client B can not see in the 
Global Tempory Tables the row put by client A .. right ?

    Right.

    SY, SD.

Hello,

how to cast a timestamp field to Integer ? i can eventually truncate the time of
the TimeStamp.

thanks you by advance
stephane

thanks helene..

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 08:31 PM 21/11/2009, you wrote:
>
> >hmmm. finally i do like this:
> >1/ gfix -v -f => ok i receive error report
> >2/ gfix -m -i => receive error report
> >3/ again gfix -v -f => ok i receive error report
> >4/ backup the data => OK
> >5/ restore the data => OK with error : some foreign key are not active
because of foreign key violation
> >6/ ISQL => locate and delete all the data in error (only one record in the
master database missing, so i delete all the record connected to this reccord)
> >7/ activate the foreign key
> >8/ backup the data
> >9/ restore the data => OK
> >10/ run gfix -v -f => OK
> >
> >now everything seam to work weel ... did i forget something ? now can i m be
sure now that the integrity of the database is OK? all the corruption is really
go away ?
>
> Sounds good to me. :-)
>
> ./heLen
>
>
>
> >>
> >> We have an expression in English - "shutting the stable door after >the
horse has bolted".  In the hope that this is not such a case, >please study this
paper, which shows at least how important it is to >do the right things, in the
right order, and only on a COPY of your >database:
> >>
> >> http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_db_corr
> >
> >yes thanks for the link. the first think i do is :
> >gfix -write sync database.gdb
> >
> >because in fact the probleme is not only power lost, but it's can also be
server crash ! if the FB_inet crash (like out of memory) then if data are not
flush the database can be corrupted (i thing it's what happen to me). in fact
sometime the fb_inet simply "hang" accepting incomming connection but not answer
to them (making the client freezing) ... why ? i don't really know... at the
beginning i was thing the the probleme was on the client side, but now i m not
sure anymore. As it is an web app, more and more connection arrive and are
"trap" like this... stil no more memory availale on the server (monday i will
buy 64GO) ... killing the client not change anything and the only way i found
was to restart the server ! after you know the story ....
> >
> >stephane
> >
> >
> >
> >
> >
> >
> >------------------------------------
> >
> >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> >Visit http://www.firebirdsql.org and click the Resources item
> >on the main (top) menu.  Try Knowledgebase and FAQ links !
> >
> >Also search the knowledgebases at http://www.ibphoenix.com
> >
> >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >Yahoo! Groups Links
> >
> >
> >
>

not really, because if i understand weel, the Global Tempory Tables is only
connected to one connection !? it's mean that client B can not see in the 
Global Tempory Tables the row put by client A .. right ?

thanks by advance
stephane

--- In firebird-support@yahoogroups.com, Dimitry Sibiryakov <sd@...> wrote:
>
> > is it better to put these tables in another database (and in this way
maintain another pool of connection to this database) or keep these table in the
main database ?
>
>    Did you consider using of Global Temporary Tables?
>
>    SY, SD.
>

>> Under Firebird 1.5.5 running under Windows Vista I have a query which
>> exhibits great variance in execution times - sometimes it takes 1.5
>> seconds, sometimes much longer (I have seen more than 9 minutes). The
>> query tries to find records in the data table which are linked to
>> obsolete records in the history table. Something like:
>>
>> "Data" has some 70000 rows. "History" has about 10 rows for each "Data"
>> row, with indexes on code, on validfrom and on validto (three distinct
>> indexes). The execution plan (for the former query) looks fine to me:
>>
>> PLAN SORT (JOIN (Data NATURAL,History INDEX (codeidx,validfromidx)))
>>
>> What could be the problem?
>>
>
> If you are deleting in one of the tables regularly, then you might
> suffer from garbage collection at SELECT time, depending on the
> architecture (SuperServer, ClassicServer) you use.
>
There are no deletes on either of the tables. If hiding from the user is
required, it is done by setting history.deleted to 1.

Architecture is SuperServer.

Pepak

Hi!

> Under Firebird 1.5.5 running under Windows Vista I have a query which
> exhibits great variance in execution times - sometimes it takes 1.5
> seconds, sometimes much longer (I have seen more than 9 minutes). The
> query tries to find records in the data table which are linked to
> obsolete records in the history table. Something like:
>
> Data ( DATAKEY integer, DATA1, DATA2, ..., CODE integer )
> History ( HISTKEY integer, HISTDATA1, HISTDATA2, ..., CODE integer,
> VALIDFROM timestamp, VALIDTO timestamp, DELETED integer )
>
> The query:
>
> SELECT d.datakey, MAX(h.histkey) histkey
> FROM data d
> LEFT JOIN history h ON h.code=d.code AND h.deleted=0 AND
> h.validfrom<='now' AND h.validto>'now'
> WHERE COALESCE(d.code,0)<>COALESCE(h.code,0)
> GROUP BY d.datakey
>
> Alternative version, with almost the same behavior (I do the COALESCEd
> compare in application rather than the query itself):
>
> SELECT d.datakey, (SELECT MAX(h.histkey) FROM history h WHERE
> h.code=d.code AND h.deleted=0 AND h.validfrom<='now' AND
> h.validto>'now') histkey
> FROM data d
>
> "Data" has some 70000 rows. "History" has about 10 rows for each "Data"
> row, with indexes on code, on validfrom and on validto (three distinct
> indexes). The execution plan (for the former query) looks fine to me:
>
> PLAN SORT (JOIN (Data NATURAL,History INDEX (codeidx,validfromidx)))
>
> What could be the problem?

If you are deleting in one of the tables regularly, then you might
suffer from garbage collection at SELECT time, depending on the
architecture (SuperServer, ClassicServer) you use.



--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com
My blog:
http://blog.upscene.com/thomas/

Hi!

Under Firebird 1.5.5 running under Windows Vista I have a query which
exhibits great variance in execution times - sometimes it takes 1.5
seconds, sometimes much longer (I have seen more than 9 minutes). The
query tries to find records in the data table which are linked to
obsolete records in the history table. Something like:

Data ( DATAKEY integer, DATA1, DATA2, ..., CODE integer )
History ( HISTKEY integer, HISTDATA1, HISTDATA2, ..., CODE integer,
VALIDFROM timestamp, VALIDTO timestamp, DELETED integer )

The query:

SELECT d.datakey, MAX(h.histkey) histkey
FROM data d
LEFT JOIN history h ON h.code=d.code AND h.deleted=0 AND
h.validfrom<='now' AND h.validto>'now'
WHERE COALESCE(d.code,0)<>COALESCE(h.code,0)
GROUP BY d.datakey

Alternative version, with almost the same behavior (I do the COALESCEd
compare in application rather than the query itself):

SELECT d.datakey, (SELECT MAX(h.histkey) FROM history h WHERE
h.code=d.code AND h.deleted=0 AND h.validfrom<='now' AND
h.validto>'now') histkey
FROM data d

"Data" has some 70000 rows. "History" has about 10 rows for each "Data"
row, with indexes on code, on validfrom and on validto (three distinct
indexes). The execution plan (for the former query) looks fine to me:

PLAN SORT (JOIN (Data NATURAL,History INDEX (codeidx,validfromidx)))

What could be the problem?


Thanks, Pepak

>>     I'm always design client-server applications with rich GUI in this way.
>> Users even have no grants to SELECT from any table. Applications calls
>> only SPs.
>>
>
> So how do you know what field a person has updated, then what to do to update
the database back again.
>
> for example
>
> SELECT FIELD1...FIELD200
> FROM PROC_PRODUCT( :THEPRODUCT )
>
> User changes FIELD121.
>
> How do you then move this data back to the database. without updating all
fields that were received to start with.

     I pass all the record fields back to the updating procedure and see nothing
bad here.

Regards,
Vlad

>> >I have an FB2.1.3
>> >
>> > CREATE GLOBAL TEMPORARY TABLE CCIMPORTRAW (
>> >    ID           PKID NOT NULL /* PKID =3D INTEGER DEFAULT 0 NOT NULL
>> */,
>> > ... other fields
>> > ) ON COMMIT DELETE ROWS;
>> >
>> > The DB is on a web which has it's own authentication method (a user
>> table in
>> > the DB) so it uses a single user in FB to make connections.
>> >
>> > When 2 people (the same user as far as FB is concerned) make a
>> submission to
>> > this table at exactly the same moment, the server crashes with this
>> error.
>> >
>> > internal gds software consistency check (pointer page vanished from
>> > relation list in locate_space (254), file: dpm.cpp line: 3060)
>>
>>     Are they shared the same Firebird attachment ?
> Vlad,
> Using
> SELECT CURRENT_CONECTION FROM RBD$DATABASE
> I can say that it is not reliably using the same OR differnet connections.
> Sometimes the 2 pages are connecting using different and somtimes the same
> connection number, but the error occurs in either case.

   All i can say with this info : make sure it never used same connection in
different
threads simultaneously. To be more helpful i need reproducible test case...

Regards,
Vlad

> is it better to put these tables in another database (and in this way maintain
another pool of connection to this database) or keep these table in the main
database ?

    Did you consider using of Global Temporary Tables?

    SY, SD.

hello,

We have 3 tables that are mostly write (and read) intensive (compare to the
other table). these tables are not really important in case of data lost (the
first table is UserSession, the second IPlog, and the last TMPData)

is it better to put these tables in another database (and in this way maintain
another pool of connection to this database) or keep these table in the main
database ?

thanks by advance for your advise !

stephane

Hi,

I'm facing the same error in one of our clients.

we have already changed software (linux distribution - from Fedora to CentOS),
server model (from SuperServer to Classic), changed hardware (both the server
machine and network elements), executed a database backup/restore, divided the
database into multiple files, but the error does not go away.

Any ideas on how to get more information? The firebird.log is basically useless
since it logs no usefull information on this error.


Regards,
Rodrigo

--- In firebird-support@yahoogroups.com, "ramiro.barreca" <rbarreca@...> wrote:
>
> Any help for this topic?
> Thanks in advance.
>
> Ramiro
>
> --- In firebird-support@yahoogroups.com, "ramiro.barreca"
> <rbarreca@> wrote:
> >
> > We have FB 2.1.1 on Open Suse 11
> >
> > There some errors in the firebird.log file that says:
> > "I/O Error for file '/opt/......fdb'
> > Error While trying to write to file
> > Invalid argument"
> >
> > Nothing else.
> > There is a lot of them and after several ones, there is an
> > "...fbserver terminated abnormally (-1)" randomlly (almost 10 times a
> > day).
> > As an information that can help, there are some "read errno = 104"
> > entries in the log, also.
> >
> > Any help to (at least) detect the source of the problem?
> >
> > Thanks in advance.
> >
>

I am using EMBEDDED Firebird 2.1.2 and have a table with the following
columns:

"PATH" VARCHAR (256) NOT NULL *COLLATE UNICODE_CI*,
"MAJOR_VERSION" INTEGER NOT NULL,
"MINOR_VERSION" INTEGER NOT NULL,
"MACHINE_ID" CHAR (38)

When I try to create a unique index on ("MACHINE_ID", "PATH",
"MAJOR_VERSION", "MINOR_VERSION") Firebird throws the following exception:

org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544351. unsuccessful
metadata update
key size exceeds implementation restriction for index

However, the same index creation succeeds if I *remove* *COLLATE UNICODE_CI
*from "PATH" column definition.

The database is configured to use 16K pages.

What is the problem? Will this be fixed by upgrading to Firebird 2.1.3
(embedded)?

Thanks,

Alec


[Non-text portions of this message have been removed]

Matthias Hanft wrote:
> Dimitry Sibiryakov schrieb:
>  >
>  > select blz, konto, auszugrn, neugutdatum, count(*) from auszuege
>  > group by blz, konto, auszugrn, neugutdatum
>  > having count(*)>1
>
> Oh, "having" was the magic word I was trying to find - thank you
> very much! I already knew that it exists, but I guess I had used
> it just 2 or 3 times within the last 20 years... :-) (never needed
> it apparently - until now)

Depending on your details you may also want to try something like

select *
from AUSZUEGE A
where exists (
      select 1
      from AUSZUEGE ASUB
      where 'compare all key fields for equality
        and 'compare some or all other field(s)
            'that is sure to diff between
            'duplicates for inequality
    )

Kjell
--
------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64


[Non-text portions of this message have been removed]

Thanks Helen, Vlad and Martijn

I'm pretty sure now Helen is right, but I'll run my tests on 2.1.3 and check the
results.

Thanks again

--- In firebird-support@yahoogroups.com, "Vlad Khorsun" <hvlad@...> wrote:
>
> >   BTW, I'm using Firebird 2.1.1
>
>     So, check 2.1.3 or current snapshot of 2.1.4
>
> Regards,
> Vlad
>

I don't see any SQL, but this error message is typical if you try to divide by
zero or put too long a string into too short a field (like trying to put 'Yes'
into a CHAR(1) field).

It can also be that a number gets too big, but that is more unusual.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-suptport@yahoogroups.com] On Behalf Of helloise.smit
Sent: 24. november 2009 12:43
To: firebird-support@yahoogroups.com
Subject: [firebird-support] arithmetic exception, numeric overflow, or string
truncation ????

i run the following sql on a fb db and get the error above?
what does it mean pls?

i run the following sql on a fb db and get the error above?
what does it mean pls?

just make this

select count(rowthatduplicate)
from tablename
where count(rowthatduplicate) > 1
group by rowthatduplicate



On Tue, Nov 24, 2009 at 6:06 PM, Matthias Hanft <mh@...> wrote:

>
>
> Hello,
>
> Firebird 2.0.3.12981.0-r6 on Gentoo Linux. I have a table called
> AUSZUEGE and would like to create a unique index on some of the
> columns of that table. But it wouldn't let me - there are still
> some duplicate rows in that table (which I try to avoid in the
> future by creating that unique index). How can I find that dupli-
> cate rows?
>
> I try
>
> CREATE UNIQUE INDEX AUSINDEX
> on AUSZUEGE (BLZ, KONTO, AUSZUGNR, NEUGUTDATUM);
>
> (with BLZ=CHAR(8), KONTO=VARCHAR(10), AUSZUGNR=INTEGER, and
> NEUGUTDATUM=DATE)
>
> and get
> "attempt to store duplicate value (visible to active transactions)
> in unique index "AUSINDEX"."
>
> So I need to identify those duplicate values by something like
> SELECT * from AUSZUEGE where COUNT(two_consecutive_identical_rows)>1;
> but how can I say "two_consecutive_identical_rows" in SQL?
>
> I already thought of adding a temporary VARCHAR column composed
> of the string representation of the desired index fields and get
> an output ORDER BY that colum - then the two identical rows would
> already be consecutive. But how to tell FB just to show rows where
> there _are_ at least two identical consecutive rows?
>
> Thank you,
>
> Matt
>
> [Non-text portions of this message have been removed]
>
>
>


[Non-text portions of this message have been removed]

Dimitry Sibiryakov schrieb:
>
> select blz, konto, auszugrn, neugutdatum, count(*) from auszuege
> group by blz, konto, auszugrn, neugutdatum
> having count(*)>1

Oh, "having" was the magic word I was trying to find - thank you
very much! I already knew that it exists, but I guess I had used
it just 2 or 3 times within the last 20 years... :-) (never needed
it apparently - until now)

-Matt


[Non-text portions of this message have been removed]

> I try
>
> CREATE UNIQUE INDEX AUSINDEX
> on AUSZUEGE (BLZ, KONTO, AUSZUGNR, NEUGUTDATUM);
>
> (with BLZ=CHAR(8), KONTO=VARCHAR(10), AUSZUGNR=INTEGER, and
> NEUGUTDATUM=DATE)
>
> and get
> "attempt to store duplicate value (visible to active transactions)
> in unique index "AUSINDEX"."
>
> So I need to identify those duplicate values by something like
> SELECT * from AUSZUEGE where COUNT(two_consecutive_identical_rows)>1;
> but how can I say "two_consecutive_identical_rows" in SQL?

select blz, konto, auszugrn, neugutdatum, count(*) from auszuege
group by blz, konto, auszugrn, neugutdatum
having count(*)>1

    SY, SD.

> Firebird 2.0.3.12981.0-r6 on Gentoo Linux. I have a table called
> AUSZUEGE and would like to create a unique index on some of the
> columns of that table. But it wouldn't let me - there are still
> some duplicate rows in that table (which I try to avoid in the
> future by creating that unique index). How can I find that dupli-
> cate rows?
>
> So I need to identify those duplicate values by something like
> SELECT * from AUSZUEGE where COUNT(two_consecutive_identical_rows)>1;
> but how can I say "two_consecutive_identical_rows" in SQL?
>
SELECT intended_key_field1, intended_key_field2, ..., COUNT(*)
FROM tablename
GROUP BY intended_key_field1, intended_key_field2, ...
HAVING COUNT(*)>1

Pepak