[firebird-support] Digest Number 9396

['Tiberiu Horvath' [e-mail] [firebird-support]]

Support for Users of Firebird ReleasesHi all,

I have a FireBird 3.0 server on a Linux machine, at a customer who appends
arround 3000 records / day in some tables. I have a strange problem that
keeps me from performing normal backup / restore operations. In the database
there are some huge domain names in System Domains (I use IBExpert). For
example : "RDB$1297080260861" (but some other domain names are normal
"RDB$100")

These domains are used by my normal tables and

DROP DOMAIN RDB$1297080260861

gets me to an error, given the fact that the domain is used by one of my
tables.

Also, any temporary table that I create (in runtime) creates these long
domain names.

When I backup my database gbak generates normal fbk file - no error here
But when I restore this file, at the end, it generates an error - see
screenshot :
Loading Image...

gbak: ERROR: arithetic exception, numeric overflow, or string truncation
gbak: ERROR: numeric value is out of range


I am sure this is connected to the system domain name.

my questions :

1. How can I modify a System Domain name ?
2. For a given Field in a Table , how can I modify the Domain that it
uses - replace with another ?
3. Is there a way to modify the generator that generates these name ?
Probably it is a way to fix this also ...


I have a fbk file and I have the initial FDB file. If somebody could help me
with this I could send the file - I deleted all the data.

In my program I have a procedure that exports the containts of every table
in the database and them imports in a new , empty database - I did this a
few months ago. But in this case, after a few days, the problem repeated.




Thank you ,

Tiberiu

[Dalton Calford [e-mail] [firebird-support]]

That type of error generally is caused by a utility or process that has changed the definition of the column.


You have data in your column in that table that no longer fits in the new definition.


When you manually change, or use a utility to smack the system tables, vs creating a whole new column and migrating the data over, you can cause these types of errors.


Now, you need to find the offending rows and update them to get the data to fit into the new column definition.


I have done this in the past by creating a new table and writing a stored procedure that inserts the data from the old table into the new, catching the errors and logging them so that I can find which rows need to be manipulated.


I also have as a standard part of the backup process, a full restore of the database as in the past, it was easy to create circular dependencies and broken structures that prevent the data from being restored using normal backup procedures.



________________________________
From: firebird-***@yahoogroups.com <firebird-***@yahoogroups.com> on behalf of 'Tiberiu Horvath' ***@yahoo.com [firebird-support] <firebird-***@yahoogroups.com>
Sent: November 16, 2016 11:04:08 AM
To: firebird-***@yahoogroups.com
Subject: Re: [firebird-support] Digest Number 9396



Support for Users of Firebird ReleasesHi all,

I have a FireBird 3.0 server on a Linux machine, at a customer who appends
arround 3000 records / day in some tables. I have a strange problem that
keeps me from performing normal backup / restore operations. In the database
there are some huge domain names in System Domains (I use IBExpert). For
example : "RDB$1297080260861" (but some other domain names are normal
"RDB$100")

These domains are used by my normal tables and

DROP DOMAIN RDB$1297080260861

gets me to an error, given the fact that the domain is used by one of my
tables.

Also, any temporary table that I create (in runtime) creates these long
domain names.

When I backup my database gbak generates normal fbk file - no error here
But when I restore this file, at the end, it generates an error - see
screenshot :
https://www.dropbox.com/s/dvfoan0xln5rug4/Screenshot%202016-11-16%2017.49.51.png?dl=0

gbak: ERROR: arithetic exception, numeric overflow, or string truncation
gbak: ERROR: numeric value is out of range

I am sure this is connected to the system domain name.

my questions :

1. How can I modify a System Domain name ?
2. For a given Field in a Table , how can I modify the Domain that it
uses - replace with another ?
3. Is there a way to modify the generator that generates these name ?
Probably it is a way to fix this also ...

I have a fbk file and I have the initial FDB file. If somebody could help me
with this I could send the file - I deleted all the data.

In my program I have a procedure that exports the containts of every table
in the database and them imports in a new , empty database - I did this a
few months ago. But in this case, after a few days, the problem repeated.

Thank you ,

Tiberiu

[[e-mail] [firebird-support]]

Thank you for your answer,

"You have data in your column in that table that no longer fits in the new definition"


The data in a table that uses one of those domains has only one record, I do not alter that record at all (some document type name default defined).


I create many temporary tables in runtime (with random names) and drop those when I leave / close the database. Can this be the problem ?
but I use this structure / program at many other customers ... No problem there.


Anyway, if GBAK could backup those domains (with huge names), why , the same GBAK, could not restore those with the same name ?






Tiberiu

[Dalton Calford [e-mail] [firebird-support]]

Interesting, only one record.


The RDB$ domains are auto generated by the system - RDB$1297080260861 says that it is the 1,297,080,260,861 domain created by the system.

If this is not the only auto-generating domain you have been using, it is possible that you have hit the maximum auto-increment.


I personally never use auto-generated domains, always used named/created domains.


What version of firebird are you using? Using earlier versions, you can perform clean up on the system tables, while newer versions limit or prevent modification of the system tables entirely.


How big is the database in size - can it be zipped up into a smaller, more reasonable size for a third party to repair in case you are not comfortable with smaking system tables?


Another method, may be, using a tool like flamerobin to extract all the metadata, then create a new clean database with pristine structures, then, pump the data across.


I would personally, re-evaluate the process that is creating and dropping meta data and perhaps use the power of firebird to it's full.


best regards


Dalton



________________________________
From: firebird-***@yahoogroups.com <firebird-***@yahoogroups.com> on behalf of ***@yahoo.com [firebird-support] <firebird-***@yahoogroups.com>
Sent: November 18, 2016 5:43:56 AM
To: firebird-***@yahoogroups.com
Subject: Re: [firebird-support] Digest Number 9396



Thank you for your answer,

"You have data in your column in that table that no longer fits in the new definition"

The data in a table that uses one of those domains has only one record, I do not alter that record at all (some document type name default defined).

I create many temporary tables in runtime (with random names) and drop those when I leave / close the database. Can this be the problem ?
but I use this structure / program at many other customers ... No problem there.

Anyway, if GBAK could backup those domains (with huge names), why , the same GBAK, could not restore those with the same name ?



Tiberiu

[[e-mail] [firebird-support]]

I use only

CREATE TABLE <TN> and DROP TABLE <TN> metadata commands in runtime. I populate the tables and that's all. I don't create domains, not at all.


I use FB 3.0 on a Linux server (I don't maintain that server) .


The database file is very big - 1+ GB in size, my customers append arround 3000 records each day.


To try to fix my domain name problem I do as you suggested : I do the restore of the new database file with the correct structure and datapump the records. I just did that yesterday (I have some 6 million records in a few tables) but this process is very time consuming - around 4 hours of work.


I have one possible explanation : last time I upgraded from FB 2.55 to FB 3.0, I did the process on a Windows machine : backup FB 2.55 and restore FB 3.0 on the machine. I copied , then, the FDB file in Linux (Samba) , didn't performed a normal restore on Linux (because it is very time-consuming and my customer couldn't wait). Maybe something bad happened then with those domain names. Yesterday I did this :
1. Exported the records (in some XML files)
2. Restored an empty database file (full restore - not copy the FDB file)
3. Datapumped the records (from the XML files) .


I will see the result and keep you informed.




Thank you ,


Tiberiu

[Dalton Calford [e-mail] [firebird-support]]

Every time you do not define a domain when creating a column in a table, firebird creates a new domain. Unless they have changed something fundamental, they never purge unused domains. So, you perform a backup and it recreates all the prior domains first, then, proceeds to create new domains. It also may have an internal counter that may not be reset to keep track of the next internal domain name to use (don't know, have not looked at the code for years).


Either way, you need to get your data out of the database and into one that is restorable. You also need to change your create table routines to use pre-defined domains. What you did not realize is that regardless of whether you want to use domains, you will be using them. The difference is whether you use a domain you define, or let the system create domains on the fly.


At least with domains you create, you can reuse them and you do not encounter the issue you are seeing.


1 GB is not very much, My smallest DB that I maintain is 20 GB.


With FB 3, you can do a few things to speed up the process, but, at this point, you may need more help than simple forum list support can give.


Where on the planet are you located? Perhaps we can get you in touch with a FB specialist in your area.


best regards


Dalton


________________________________
From: firebird-***@yahoogroups.com <firebird-***@yahoogroups.com> on behalf of ***@yahoo.com [firebird-support] <firebird-***@yahoogroups.com>
Sent: November 19, 2016 9:12:59 AM
To: firebird-***@yahoogroups.com
Subject: Re: [firebird-support] Digest Number 9396



I use only

CREATE TABLE <TN> and DROP TABLE <TN> metadata commands in runtime. I populate the tables and that's all. I don't create domains, not at all.

I use FB 3.0 on a Linux server (I don't maintain that server) .

The database file is very big - 1+ GB in size, my customers append arround 3000 records each day.

To try to fix my domain name problem I do as you suggested : I do the restore of the new database file with the correct structure and datapump the records. I just did that yesterday (I have some 6 million records in a few tables) but this process is very time consuming - around 4 hours of work.

I have one possible explanation : last time I upgraded from FB 2.55 to FB 3.0, I did the process on a Windows machine : backup FB 2.55 and restore FB 3.0 on the machine. I copied , then, the FDB file in Linux (Samba) , didn't performed a normal restore on Linux (because it is very time-consuming and my customer couldn't wait). Maybe something bad happened then with those domain names. Yesterday I did this :
1. Exported the records (in some XML files)
2. Restored an empty database file (full restore - not copy the FDB file)
3. Datapumped the records (from the XML files) .

I will see the result and keep you informed.


Thank you ,

Tiberiu

[Ann Harrison [e-mail] [firebird-support]]

Every time you do not define a domain when creating a column in a table, firebird creates a new domain ... So, you perform a backup and it recreates all the prior domains first, then, proceeds to create new domains.

I think that's not correct prior to V3 and probably not for V3 either. Gbak maintains the relationship between fields and the domains on which they're based. The problem with extremely high domain numbers is probably related to temporary tables.

Good luck,

Ann

Either way, you need to get your data out of the database and into one that is restorable. You also need to change your create table routines to use pre-defined domains. What you did not realize is that regardless of whether you want to use domains, you will be using them. The difference is whether you use a domain you define, or let the system create domains on the fly.
At least with domains you create, you can reuse them and you do not encounter the issue you are seeing.
1 GB is not very much, My smallest DB that I maintain is 20 GB.
With FB 3, you can do a few things to speed up the process, but, at this point, you may need more help than simple forum list support can give.
Where on the planet are you located? Perhaps we can get you in touch with a FB specialist in your area.
best regards
Dalton
Sent: November 19, 2016 9:12:59 AM
Subject: Re: [firebird-support] Digest Number 9396
I use only
CREATE TABLE <TN> and DROP TABLE <TN> metadata commands in runtime. I populate the tables and that's all. I don't create domains, not at all.
I use FB 3.0 on a Linux server (I don't maintain that server) .
The database file is very big - 1+ GB in size, my customers append arround 3000 records each day.
To try to fix my domain name problem I do as you suggested : I do the restore of the new database file with the correct structure and datapump the records. I just did that yesterday (I have some 6 million records in a few tables) but this process is very time consuming - around 4 hours of work.
1. Exported the records (in some XML files)
2. Restored an empty database file (full restore - not copy the FDB file)
3. Datapumped the records (from the XML files) .
I will see the result and keep you informed.
Thank you ,
Tiberiu

[[e-mail] [firebird-support]]

This is how I create my temporary tables :

create global temporary table TEMP_112233
(
id : INTEGER,
name : CHAR(20)
)
on commit preserve rows




I also do some create views :


CREATE OR ALTER VIEW CAUT_PRODUSE_VIEW_4982590
(
COD_PRODUS,
ID_PRODUS
)

AS
select
produse.cod_produs,
produse.id_produs

from produse
inner join sortiment on (sortiment.id_sortiment + 0 = produse.id_sortiment)
where

denumire_sortiment <> 'N '







I do not know about any domain definitions, this should be an internal FireBird thing.


if I have RDB$1 defined this way :


CREATE DOMAIN RDB$1 AS INTEGER


whenever I create another table with an Integer field, this domain should be used, am I wrong ?


is there a command


create table TEMP_112233
(
id : INTEGER use domain RDB$1
)



?








BTW, I am located in Romania.






Thank you,


Tiberiu

[Helen Borrie [e-mail] [firebird-support]]

Post by ***@yahoo.com [firebird-support]
create global temporary table TEMP_112233
(
id : INTEGER,
name : CHAR(20)
)
on commit preserve rows

You should create your GTT only once in your life, not every time you
want to use it! An instance of your GTT will be created as soon as
the client inserts some data into it. An instance of this GTT will
survive as long as this client remains connected and no other client
can see this instance.

Post by ***@yahoo.com [firebird-support]
CREATE OR ALTER VIEW CAUT_PRODUSE_VIEW_4982590
(
COD_PRODUS,
ID_PRODUS
)
AS
select
produse.cod_produs,
produse.id_produs
from produse
inner join sortiment on (sortiment.id_sortiment + 0 = produse.id_sortiment)
where
denumire_sortiment <> 'N'

OK, but that JOIN criterion is strange. Do you not want the optimizer
to use the index on sortiment.id_sortiment if it would be useful? This
syntax deliberately blocks use of that index.

A view is not a temporary table. Effectively, it is a "stored query"
that you define in order to get a pre-defined data set each time you
select from it or join to it.

Post by ***@yahoo.com [firebird-support]
I do not know about any domain definitions, this should be an internal FireBird thing.
CREATE DOMAIN RDB$1 AS INTEGER
whenever I create another table with an Integer field, this domain should be used, am I wrong ?

Yes: you are quite wrong. You should not create any domains with
names that start 'RDB$' because Firebird uses that prefix to create
its internal domains, which are not intended for use by humans. If
you want to use domains (a very good idea!) you should create your own
domains with useful names. Do not "borrow" Firebird's internal domains.

Post by ***@yahoo.com [firebird-support]
is there a command
create table TEMP_112233
(
id : INTEGER use domain RDB$1
)

This is what you do. Suppose you want a domain called ID_BASE of type
integer that you want to use for creating primary keys everywhere.
You define this domain ID_BASE with the attributes you need for any
primary key. You probably have some other types you would like to
have on hand, too. For example,

CREATE DOMAIN D_ID_BASE INTEGER NOT NULL

CREATE DOMAIN D_INDICATOR CHAR(3)
COLLATION ISO8859_2
CHECK VALUE = UPPER(VALUE)

CREATE DOMAIN D_SHORT_DATA VARCHAR(99)

Now, you have have 3 domains - think of them as custom data types, if
you like. Just use the domain name like a data type when you create
the columns that you want having those attributes:

create table aTable (
id D_ID_BASE,
description D_SHORT_DATA,
status D_INDICATOR,
{..other columns..},
constraint pk_atable primary key(id)
)

You can also use these 'custom data types' in stored procedures,
triggers, executable blocks and, in Fb3, stored functions, e.g.,

create procedure ...
...
...
declare aVariable D_INDICATOR;
declare bVariable TYPE OF D_SHORT_DATA; <-- gets the data type
without the other attributes

The Firebird 2.5 Language Reference has a lot of detail about
domains, GTTs and everything else about Firebird's SQL language.
You need it. ;-)

http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/Firebird_Language_Reference_25EN.pdf

or you can study it on-line in html:

http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25.html

Helen

[[e-mail] [firebird-support]]

1. I create those temporary tables for one user only, for one transaction only, not visible for other users. I keep a track (log) of those tables and delete when I exit the program (drop table). The temporray table name is generated randomly

'TEMP_TABLE_' + <random number> .



2. the use of index is deliberately blocked, I want to do it this way, my query is faster.


3. maybe I was not clear in stating my problem.


I do not declare domains. Firebird declares those domains automatically when I create new tables (with usual types: Integer, char, etc). My problem is that when I create those tables, this automatic domain declaration declares (creates automatically) "huge" domain names (saw these with IBExpert, setting system domains visible). I can backup the database containing these domain names but I cannot restore normally. One screen capture here :
Screenshot 2016-11-16 17.49.51.png https://www.dropbox.com/s/dvfoan0xln5rug4/Screenshot%202016-11-16%2017.49.51.png?dl=0

https://www.dropbox.com/s/dvfoan0xln5rug4/Screenshot%202016-11-16%2017.49.51.png?dl=0

Screenshot 2016-11-16 17.49.51.png https://www.dropbox.com/s/dvfoan0xln5rug4/Screenshot%202016-11-16%2017.49.51.png?dl=0 Shared with Dropbox



View on www.dropbox.com https://www.dropbox.com/s/dvfoan0xln5rug4/Screenshot%202016-11-16%2017.49.51.png?dl=0
Preview by Yahoo







Thank you,


Tiberiu

[Dimitry Sibiryakov [e-mail] [firebird-support]]

Post by ***@yahoo.com [firebird-support]
My problem is that when I create those tables, this automatic domain declaration declares
(creates automatically) "huge" domain names (saw these with IBExpert, setting system
domains visible).

Bad application design resulted in bad things. For this temporary table usage pattern
you'd better to use MS SQL. Firebird has no Local Temporary Tables which are obviously
needed to you.
If you want to reuse domain names, you have to create user domains explicitly. Firebird
never reuse implicit domains.
--
WBR, SD.


------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-***@yahoogroups.com
firebird-support-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[[e-mail] [firebird-support]]

So, everytime I create a table in FireBird, a new domain is created automatically in the database ?

This surely could be an explanation of my problem !


I will try to create domains and use those in DDL .


Is this a correct approach :


create domain MyIntegerDomain Integer ;



create table myTable (id MyIntegerDomain) ;


?













Tiberiu

[Dalton Calford [e-mail] [firebird-support]]

Yes,


But the new domain is not created for every table, just for every column where you are not using a pre-defined domain.


So,


Create table MYTABLE(mycolA integer, mycolB integer, mycolC integer); would auto create three separate new domains.


I wish I knew more of what you are trying to do, as Firebird has many features that would normally remove any need for regular metadata changes.




________________________________
From: firebird-***@yahoogroups.com <firebird-***@yahoogroups.com> on behalf of ***@yahoo.com [firebird-support] <firebird-***@yahoogroups.com>
Sent: November 21, 2016 7:13:46 AM
To: firebird-***@yahoogroups.com
Subject: Re: [firebird-support] Digest Number 9396



So, everytime I create a table in FireBird, a new domain is created automatically in the database ?

This surely could be an explanation of my problem !

I will try to create domains and use those in DDL .

Is this a correct approach :

create domain MyIntegerDomain Integer ;

create table myTable (id MyIntegerDomain) ;

?






Tiberiu

[[e-mail] [firebird-support]]

I understand. And I saw that when I drop the temporary table these auto generated domain names disappear but the generated names keep on "growing" .

I modified my routines so that :


I verify if I have the domain


SELECT *
FROM
RDB$FIELDS
where
RDB$FIELD_NAME = 'MYINTEGERDOMAIN'


if not, then



create my domain MYINTEGERDOMAIN


and finally I create the tables :


create myTempTable1122 (id MYINTEGERDOMAIN)




Hope this is all right now .






Thank you all for your time,


Tiberiu

[Dimitry Sibiryakov [e-mail] [firebird-support]]

Post by ***@yahoo.com [firebird-support]
Hope this is all right now .

No. The idea to create temporary tables on the fly and then drops them is completely
wrong for Firebird.
--
WBR, SD.


------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-***@yahoogroups.com
firebird-support-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[[e-mail] [firebird-support]]

And how should I do my queries ? Is there some documentation for this ?

I need to compile data from several tables, scan the records, update , delete some and then prepare the data for a report. For me the


with query as(
select
)
select * from query


is not enough.






Tiberiu

[Helen Borrie [e-mail] [firebird-support]]

Post by ***@yahoo.com [firebird-support]
And how should I do my queries ? Is there some documentation for this ?

Yes. STUDY THE LANGUAGE REFERENCE !!!

Post by ***@yahoo.com [firebird-support]
I need to compile data from several tables, scan the records,
update , delete some and then prepare the data for a report. For me the
with query as(
select
)
select * from query
is not enough.

Of course it is not enough. SELECT * is for one-armed paperhangers.

select
produse.cod_produs,
produse.id_produs
from produse
inner join sortiment on (sortiment.id_sortiment + 0 = produse.id_sortiment)
where
denumire_sortiment <> 'N

will return the data set you want: two columns and just the rows that
match the WHERE clause. It's called Data Manipulation Language (DML)
and it is the REAL nuts and bolts of a database management system.

About domains:
You do NOT need to create a domain for every data type. For example,
if a column is simply INTEGER then declare it as INTEGER when you
define the table. Sure, Firebird will create an internal domain for
*that column* in RDB$FIELDS but you do not need to know about it.

Create domains for repeating column types that have specific
attributes, such as NOT NULL or CHECK constraints. For convenience,
if you like, keep a printed list of all your domains for reference at
design time.

And you do not need to query RDB$FIELDS to check whether a domain
exists.
-- if you try to create a domain that already exists, you will simply
get a duplicate index error.
-- if you try to create a column using a domain that doesn't exist,
you will get another kind of error, something like "invalid data
type".

Avoid creating ANY objects on the fly - tables, GTTs, views, stored
procedures, etc. Don't do DDL (data definition language) operations
in user run-time. Organise your database so that everything you need
for queries is right there. Doing it your way is like every time you
go out in your car, you have to wait for engineers to come and build a
road. Learn about DML - you will be amazed.

Helen

Post by ***@yahoo.com [firebird-support]
Tiberiu

[[e-mail] [firebird-support]]

All right, thank you all for your time !

I have code that uses temporary tables in at least 30 places in my programs. This is the way I did complicated queries. Most of these procedures are written 10 years ago (Interbase, Firebird 1.0). I cannot change everything (replace every query that uses temporary tables), cannot do this without proper testing.


In the first phase I changed every field definiton of these temporary tables, defined my domains


Integer -> MyIntegerDomain


and changed the definition (DDL) of the temporary tables in my code.


I sure will change the code / queries that uses those temporary tables.










Tiberiu

[Ann Harrison [e-mail] [firebird-support]]

Do you see the same behavior if the guid field is not indexed?

Post by ***@yahoo.com [firebird-support]
All right, thank you all for your time !
I have code that uses temporary tables in at least 30 places in my programs. This is the way I did complicated queries. Most of these procedures are written 10 years ago (Interbase, Firebird 1.0). I cannot change everything (replace every query that uses temporary tables), cannot do this without proper testing.
In the first phase I changed every field definiton of these temporary tables, defined my domains
Integer -> MyIntegerDomain
and changed the definition (DDL) of the temporary tables in my code.
I sure will change the code / queries that uses those temporary tables.
Tiberiu

[[e-mail] [firebird-support]]

We have a multithreaded app written in Clarion 9 (win32), running on several
win 2012 64 bits r2, connecting to Firebird 3 (latest build) via ODBC 32
bits (latest ODBC). We are experiencing a rare propblem, that happens not
very often. The issue is as follows:

The application does not use "begin transaction and commit", so we let the
database commit automatically
after a write process, we read the table from another thread inside the app
or from another app inside the same OS session, and the data is not there.
Of course after a few seconds (or milliseconds) the data is available. Our
concern is that we have a sequence of code that assume whatever was written
5 lines above is already available for reading, but it is failing every so
often. One solution we are considering is to use "explicit" begin
transaction and commit instead of implicit, any thoughts? (in the past, when
firebird was on version 1.5 or 2) we tried to use explicit transactions and
the system would lock up every 20 minutes... probably deadly embrace, but we
believe it was not related to the application itself, we attributed at the
time to a possible bug somewhere betweeen the ODBC or the Firebird engine
because the same app running against another DB engine would not lock up.
Any suggestions?


Cheers,
Fabian

[Tim Ward [e-mail] [firebird-support]]

Post by ***@itbizolutions.com.au [firebird-support]
The application does not use "begin transaction and commit", so we let the
database commit automatically
after a write process, we read the table from another thread inside the app
or from another app inside the same OS session, and the data is not there.
Of course after a few seconds (or milliseconds) the data is available. Our
concern is that we have a sequence of code that assume whatever was written
5 lines above is already available for reading, but it is failing every so
often.

When did I last see that one? - ah yes, it was using an Access database
to communicate from one process on one machine to another on another.
You'd receive the sync event (via another channel), poll the database,
and find that the new data wasn't actually there yet. Turned out that
there was a half-second delay before data got flushed to disk, as an
optimisation in case you were just about to write something else. (Which
could, once you'd discovered what was going on, which might take a few
days or weeks, be turned off.)

I don't however expect to see exactly the same problem with Firebird 3 ...

[[e-mail] [firebird-support]]

SOLVED - Changed code to use explicit transactions and the problem went
away, so I guess implicit transactions was "keeping the transaction opened"
for a few extra milliseconds... while the other "process / thread / or ODBC
object" was trying to read the data added by the last transaction, and as it
was not commited yet, the data was not visible. Many many years ago, like 15
years ago, a friend who advised me to use Firebird told me "dont use
explicit transations with Firebird; you'll be better of leaving Firebird
manage it implicit..."; it did not sound correct then.... and it came back
to bite me now...

Cheers,
Fabian

[Lester Caine [e-mail] [firebird-support]]

Post by ***@yahoo.com [firebird-support]
I have code that uses temporary tables in at least 30 places in my
programs. This is the way I did complicated queries. Most of these
procedures are written 10 years ago (Interbase, Firebird 1.0). I cannot
change everything (replace every query that uses temporary tables),
cannot do this without proper testing.

A lot of my code is come up on 20 years old and in the IB/FB1 days it
was a case that one had to build temporary tables in order to speed up
some report processes. Today those queries are a lot more efficient
using CTE queries ad removing the need for managing temporary tables. I
totally accept that the time taken to undertake changes may seem a waste
when things are working, but it is worth the time to pick them off over
time ...

['Leyne, Sean' [e-mail] [firebird-support]]

Dmitry,

Post by Dimitry Sibiryakov ***@ibphoenix.com [firebird-support]

Post by ***@yahoo.com [firebird-support]
Hope this is all right now .

No. The idea to create temporary tables on the fly and then drops them is
completely wrong for Firebird.

"Wrong" is really not the correct word for this case.

"Sub-optimal" or "inefficient" would be better.


Sean

['Leyne, Sean' [e-mail] [firebird-support]]

Post by ***@yahoo.com [firebird-support]
I do not declare domains.

But you could! With just a little effort on your part.

Take a little time, do the following:

CREATE DOMAIN D_INTEGER AS Integer;
CREATE DOMAIN D_Varchar_20 AS VARCHAR( 20);

Then when you create your temporary tables just make 2 minor changes:

create global temporary table TEMP_112233
(
id : D_Integer,
name : D_varchar_20
)

[[e-mail] [firebird-support]]

Yes,

exactly this is what I wrote before !






Tiberiu

['Leyne, Sean' [e-mail] [firebird-support]]

Post by ***@yahoo.com [firebird-support]
1. I create those temporary tables for one user only, for one transaction only,
not visible for other users. I keep a track (log) of those tables and delete
when I exit the program (drop table).

All of that is already supported by temporary tables -- each connection creates a **separate instance** of the GTT, so connections never see other data.

The GTT data is deleted/dropped when the Transaction commits (default) or when db connection is closed.

Post by ***@yahoo.com [firebird-support]
2. the use of index is deliberately blocked, I want to do it this way, my query is faster.

"Blocked" why?


Sean

[[e-mail] [firebird-support]]

I tested the query on a big database. It was faster if I disabled the index.



Tiberiu

Post by ***@yahoo.com [firebird-support]
2. the use of index is deliberately blocked, I want to do it this way, my query is faster.

"Blocked" why?


Sean

['Leyne, Sean' [e-mail] [firebird-support]]

The performance of the query may have been a function of the query approach itself, not the presence of the index

From: firebird-***@yahoogroups.com [mailto:firebird-***@yahoogroups.com]
Sent: Monday, November 21, 2016 1:19 PM
To: firebird-***@yahoogroups.com
Subject: RE: [firebird-support] Digest Number 9396



I tested the query on a big database. It was faster if I disabled the index.


Tiberiu

Post by ***@yahoo.com [firebird-support]
2. the use of index is deliberately blocked, I want to do it this way, my query is faster.

"Blocked" why?


Sean

[[e-mail] [firebird-support]]

OK, but if gbak backups this domain name , shouldn't it restore it also ?



Tiberiu


---In firebird-***@yahoogroups.com, <***@...> wrote :

The RDB$ domains are auto generated by the system - RDB$1297080260861 says that it is the 1,297,080,260,861 domain created by the system.