[solved] "unique" violation in a table
lazarus / 16-Databases

----------------------------------------------------------------------
Nicole · 2024-01-22 18:49

I have no idea, how this can happen: For the second time, my backup
and restore tool reports an unique-requirement-violation in my
database (Firebird).

Hard to say if the tool (Firebird editor) caused it or found it.
The last time it was easy to find the very line, but very hard to
solve the violation. The moment I deleted the double data-line, BOTH
of them were deleted. So I wrote the field-values on a sheet of paper
and restored one of the line by FlameRobin.

This time it is harder. The trouble-data are hidden in ten thousands
of lines.
Has anybody an idea, how to find the problem line?
SQL does not allow to find something, which is not allowed or does it?

What is the problem is, that a foreign key and a date are the same.
My rule says, they have to be unique.

Any tricks, how to deal with?
The database works, but may be the problem has anything to do with my
performance problems which I posted some time before.

This is the DLL of the table

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE TBCOT(  DATUM date NOT NULL,  FK_COMM char(2) NOT NULL, 
SPEC_LONG bigint,  SPEC_SHORT bigint,  COMM_LONG bigint,  COMM_SHORT
bigint,  SMALL_LONG bigint,  SMALL_SHORT bigint,  SPEC_NET integer, 
COMM_NET integer,  SMALL_NET integer,  CONSTRAINT UNQ_TBCOT_COMM_U_DAT
UNIQUE (FK_COMM,DATUM));ALTER TABLE TBCOT ADD CONSTRAINT FK_TBCOT_1 
FOREIGN KEY (FK_COMM) REFERENCES TBKENNZAHLEN (COMM);ALTER TABLE TBCOT
ADD CONSTRAINT CHK_DATUMAUFLEER  check (datum > '1.1.1960');GRANT
DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBCOT TO  SYSDBA WITH
GRANT OPTION GRANTED BY SYSDBA;

----------------------------------------------------------------------
rvk · 2024-01-22 19:29

Where is your primary key? Always have a primary key in a table so you
can work with these records and correct them.

But what you encountered shouldn't happen.
It can only happen if the database is corrupt or you have disabled
certain constraints.

Normally I put the corrupted database offline, try the validation and
mend options, backup and restore without indexes and constraints (
-inactive and - no_validity). Then you can investigate what
constraints cause problems and fix it, then enable the
indexes/constraints and bring the database back online.

But I would be more worried as to how this could happen.
Were the database constraints ever disabled and the db never put
online (i.e. single mode)?

----------------------------------------------------------------------
Zvoni · 2024-01-23 08:26

Quote from: rvk on January 22, 2024, 08:29:33 pmWhere is your primary
key? Always have a primary key in a table so you can work with these
records and correct them.
He has a Primary Key: It's the combination of his ForeignKey and Date,
because he declares it as UNIQUE, which makes it automatically a
PrimaryKey
(OK, i admit: Not in the sense of DDL, but my point stands)

What i can see: Is it even the Violation of that "Unique" Constraint
(even if TS stated that he gets that violation, but we don't know the
exact Error-message?
I see TS using german Date-Notation in Constraint-Check with a Column
of Type Date against a String... *shudder*

Bottom line:
FK_COMM = 2 - DATUM=2024-01-23
FK_COMM = 2 - DATUM=2024-01-22

are valid entries

----------------------------------------------------------------------
rvk · 2024-01-23 08:42

Quote from: Zvoni on January 23, 2024, 09:26:53 amQuote from: rvk on
January 22, 2024, 08:29:33 pmWhere is your primary key? Always have a
primary key in a table so you can work with these records and correct
them.
He has a Primary Key: It's the combination of his ForeignKey and Date,
because he declares it as UNIQUE, which makes it automatically a
PrimaryKey
(OK, i admit: Not in the sense of DDL, but my point stands)Sure, but
you can have multiple UNIQUE constraints. That doesn't make it a
primary key 

Quote from: Zvoni on January 23, 2024, 09:26:53 amWhat i can see: Is
it even the Violation of that "Unique" Constraint (even if TS stated
that he gets that violation, but we don't know the exact Error-
message?
I see TS using german Date-Notation in Constraint-Check with a Column
of Type Date against a String... *shudder*
Yeah, reason enough to never use combined constraints to emulate your
"primary key" 
TS also stated that removing one of the "double" records removed both
of them. That leads me to believe that the combined constraints where
actually the same (which shouldn't happen with a working constraint in
place in a non-corrupt database).

----------------------------------------------------------------------
Nicole · 2024-01-23 10:32

I AM concerned, how this can happen!!!

other case of trouble, which "cannot happen" in a different table:
Before every entry in the database I check for not being empty / zero
/ blanc.
For over two years I have in some cases null-values in very rare
cases.
There are 3 checks for the parameter "not empty", never the less
several months later I wonder why the select-statement is lacking some
expected results, - and find a null again there.
I cannot say, how this may happen. Perhaps it is anything behind my
code.
Unfortunately this is one string of 2 digits within some months where
every day with several thousands of date.
As my error-checks were never ever triggered, I do not even guess at
which date this happens.
And if this happened I bet, I cannot reproduce.

But back to our no-primary-key table.
I had no primary key because I want to safe space. The request always
will be for the comm and date.
So the primary key would just be an additional thing.
I would not guess, that the primary key is the problem, because the
above mentioned zero-value-riddle is in a table which has a primary
key.

Not sure, if these error-messages are helpful, this is why I did not
post it.
But if they are, - here they are

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
---------------------------Error---------------------------action
cancelled by trigger (3) to preserve data integrityCannot deactivate
index used by a PRIMARY/UNIQUE constraintunknown ISC error 336330835
---------------------------OK   --------------------------- 
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
gbak:creating indexes gbak:committing metadata gbak:    activating and
creating deferred index UNQ_WARE_DATUM gbak:    activating and
creating deferred index MYUNIQUE gbak:    activating and creating
deferred index UNQ_TBCOT_COMM_U_DAT gbak:cannot commit index
UNQ_TBCOT_COMM_U_DAT

----------------------------------------------------------------------
rvk · 2024-01-23 10:56

Quote from: Nicole on January 23, 2024, 11:32:02 amI had no primary
key because I want to safe space. The request always will be for the
comm and date.
So the primary key would just be an additional thing.The missing
primary key is indeed not the problem but it's something I noticed
right away because I always have a primary key on ALL my tables. It
also makes it easier to work with foreign keys. (I have LINKNUMMER in
ALL my tables with which I connect my tables. In that case FK_COMM
wouldn't exist in that table because it's already in TBKENNZAHLEN as
COMM.)

Quote from: Nicole on January 23, 2024, 11:32:02 amNot sure, if these
error-messages are helpful, this is why I did not post it.
But if they are, - here they are

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
---------------------------Error---------------------------action
cancelled by trigger (3) to preserve data integrityCannot deactivate
index used by a PRIMARY/UNIQUE constraintunknown ISC error 336330835
---------------------------OK   --------------------------- I find the
second line very disturbing. "Cannot deactivate index" would indicate
it tried to deactivate an index. If it would have succeeded, it would
have deactivated a major piece of your database design. Can you tell
WHAT SQL statement exactly resulted in this error ???

(This seems to be a backup or restore command because otherwise I
can't imagine why a index would be needed to be (de)activated.)

Quote from: Nicole on January 23, 2024, 11:32:02 amCode: Text
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
gbak:creating indexes gbak:committing metadata gbak:    activating and
creating deferred index UNQ_WARE_DATUM gbak:    activating and
creating deferred index MYUNIQUE gbak:    activating and creating
deferred index UNQ_TBCOT_COMM_U_DAT gbak:cannot commit index
UNQ_TBCOT_COMM_U_DAT Yeah, that's normal if the constraint was not in
place (or there was a corruption) and you want to restore that
database.

We still need to know if the database is corrupted or the constraint
is deactivated in the original database. Do you still have access to
that original database?

You need to view all the restraints/indexes to see if they are
(IN)ACTIVE.

----------------------------------------------------------------------
Zvoni · 2024-01-23 11:50

Quote from: rvk on January 23, 2024, 11:56:53 am(This seems to be a
backup or restore command because otherwise I can't imagine why a
index would be needed to be (de)activated.)
And if it is a Backup/Restore thing, are ALL connections to the
Database closed?

EDIT: Something else: It always mystifies me, why people create a
Table, and then in the next step ALTER it, instead of just putting the
Constraints into the CREATE TABLE-Command itself.

Have you tried without the ALTER TABLE-Stuff, but directly within the
Create Table-Command?
It might even fail, because from the Create Table it has the UNIQUE-
Constraint, and tries to create the Index for that, but then in the
next step it wants to ALTER a Column of that Constraint

Untested:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE TBCOT(  DATUM DATE NOT NULL,  FK_COMM CHAR(2) NOT NULL, 
SPEC_LONG BIGINT,  SPEC_SHORT BIGINT,  COMM_LONG BIGINT,  COMM_SHORT
BIGINT,  SMALL_LONG BIGINT,  SMALL_SHORT BIGINT,  SPEC_NET INTEGER, 
COMM_NET INTEGER,  SMALL_NET INTEGER,  CONSTRAINT UNQ_TBCOT_COMM_U_DAT
UNIQUE (FK_COMM,DATUM),  CONSTRAINT FK_TBCOT_1 FOREIGN KEY (FK_COMM)
REFERENCES TBKENNZAHLEN (COMM),  CONSTRAINT CHK_DATUMAUFLEER CHECK
(datum > '1.1.1960')); GRANT DELETE, INSERT, REFERENCES, SELECT,
UPDATE ON TBCOT TO  SYSDBA WITH GRANT OPTION GRANTED BY SYSDBA;

----------------------------------------------------------------------
rvk · 2024-01-23 12:07

Quote from: Zvoni on January 23, 2024, 12:50:53 pmEDIT: Something
else: It always mystifies me, why people create a Table, and then in
the next step ALTER it, instead of just putting the Constraints into
the CREATE TABLE-Command itself.

Have you tried without the ALTER TABLE-Stuff, but directly within the
Create Table-Command?
It might even fail, because from the Create Table it has the UNIQUE-
Constraint, and tries to create the Index for that, but then in the
next step it wants to ALTER a Column of that Constraint
It happens that I myself also add the INDEX and CONSTRAINTS after the
CREATE 
But that's more due to the fact that I have a possible DBASE import
stage in between. After the import I can investigate missing (or
incorrect) clientrecords and add them (because in the DOS version it
was possible to delete client info without deleting order info). Those
missing records are added again and after that the constraints are
set.

But if the ALTER is directly after the CREATE it could be merged. But
it shouldn't matter because directly after CREATE the table is empty
and adding the CONSTRAINT would always work afterwards.

I suspect this is really a corrupt database or the constraints/indexes
are set to INACTIVE.
Restoring the backup will activate them and it can't do that if
invalid data is added.

----------------------------------------------------------------------
Nicole · 2024-01-23 18:10

@ rvk
My organisation is, that names hint the content.
(there are a few exceptions of very old tables)

The name "fk_comm" says to me "foreign key" at a field by the name of
comm.

This means to me:
There is a table tbkennzahlen. It has a privmary key. The name of the
primary key is id + table, so pk = id_kennzahlen.

So "fk_comm" holds the value of the primary key=id_kennzahlen

================

Unfortunetely it is not an SQL snipet shows the error, but the
restore-tool of Firebird, which is executed by the Freeware Firebird-
Editor Pro.
Backup and restore works find. The only thing is the strange error-
message.

Yes, I have backups of all my databases, at least I hope so.
Unfortunately I am not very firm with databases, so I am not sure,
what your sentences mean. I unserstand the words of it, but do not
know, how to check or use.

Theses are the sentences I do not undestand.
QuoteWe still need to know if the database is corrupted or the
constraint is deactivated in the original database. Do you still have
access to that original database?

You need to view all the restraints/indexes to see if they are
(IN)ACTIVE.

@Zvoni
Sure, I may have made errors in the configuration of backup and
restore. I used the tool just a handful of times in my lifetime an
with this editor I did it for the first time.

The table is many years old. I have no idea, how I made it. Those keys
and contraints I made by FlameRobin and usually it was a work of hours
to bring them into my DB. After an eternity of clicking and clicking
away error-messages, the constaints were "in".

@rvk 2:
It is sure, that I added the contraints AFTER the create. This is the
way, either FlameRobin (the older version) works or the only way I
understood to use it.

I am afraid, the SQL-history of the item is "lost in bytes".

To sum up:
Is there a way to find out, WHAT in the DB is corrupt? This database
is - an important part of my life. This is not overdone.

Are the checking-tools?

----------------------------------------------------------------------
rvk · 2024-01-23 18:19

Quote from: Nicole on January 23, 2024, 07:10:59 pmTheses are the
sentences I do not undestand.
QuoteWe still need to know if the database is corrupted or the
constraint is deactivated in the original database. Do you still have
access to that original database?

You need to view all the restraints/indexes to see if they are
(IN)ACTIVE.
You are restoring a backup. But the backup is made from a Firebird
database (original). You need to get your hands on that original
database and investigate it.
Is it corrupt? (You can check that with gbak/gfix).
Are the problem records (duplicates) in that DB?
If there are... Is the constraints in place and active (view all
constraints)?

Check for corruption:
gfix -v -n copy1.fdb

See https://www.ibphoenix.com/resources/documents/how_to/doc_5#recogni
sing-possible-corruption

Once you know what the problem is you can go from there.

(For example of the DB is corrupt we can go through the recovery
steps. If the DB is not corrupt but the constraints are inactive it
needs to be corrected.)

----------------------------------------------------------------------
Nicole · 2024-01-24 11:19

Dear me!
It is midday and I messed around half the day.

As any newbie like me reads this:
You must not write just the path, but must put " around.
So it is cd "Program Files" if you want to go to the gfix in the
Firebird directory.

And if you are there you can double click it fine and there is a text
running and disappearing before you can read it.
If you call it from the command prompt of the powershell, - it is "not
found" 
As reason I found after too long a time a security element of windows,
you have to write "./myexe" as well with the "

Finally it ran. And there was no output.
I hope, this means, that there were no errors found in my database.

hm, clueless.
For my next question I open a new thread.

----------------------------------------------------------------------
rvk · 2024-01-24 11:40

Quote from: Nicole on January 24, 2024, 12:19:10 pmAnd if you are
there you can double click it fine and there is a text running and
disappearing before you can read it.Woops. I was under the impression
that you knew how to work with command console in Windows 

Press start button from Windows
Type cmd
Click the "Command Prompt"
Now you are in a command prompt console

Type the text below (where you change 2_5 into the correct version
number):
cd "\Program Files\Firebird\Firebird_2_5\bin"

Now you are in the bin directory of Firebird.
Type the following
gfix -v -full -user sysdba -pass masterkey
"name_and_path_of_your_database"

If it doesn't return anything the database should be good.
BTW. You could also have typed this (without the need to do the cd
command):
"C:\Program Files\Firebird\Firebird_2_1\bin\gfix.exe" -v -full -user
sysdba -pass masterkey "name_and_path_of_your_database"

~~
Next... if the database is good... you need to check if the records
you say are duplicates, are present in your working database.
You can do that with the SELECT statements.

*) If there are no duplicate records... then I'm puzzled as to how
they are generated in the backup.

*) If there are duplicates, you need to examine the status of the
constraints.
select * from RDB$INDICES where RDB$SYSTEM_FLAG = 0
Are there any 1's in the RDB$INDEX_INACTIVE ?

----------------------------------------------------------------------
Nicole · 2024-01-24 11:59

Finally I had made the Windows thing. I used Dos 6 and Win 3.11.
Then Win XP, the command shell, but forgot those things as I have not
used them that long.
Databases are not my main-thing and I wish, it once would work
forever, so I can forget about so much admin-work taking my time.

about select:
Such a select statement exists?!
More than select * from tbtable I never tried.

I did the new one, but am clueless about what it may mean.

----------------------------------------------------------------------
rvk · 2024-01-24 12:06

Quote from: Nicole on January 24, 2024, 12:59:21 pmI did the new one,
but am clueless about what it may mean.
The new one has 0 in RDB$INDEX_INACTIVE (which means it is active
because inactive = 1)

You can see the values here: https://ib-
aid.com/download/docs/firebird-language-reference-2.5/fblangref-
appx04-indices.html

But I see 3 everywhere else in RDB$INDEX_INACTIVE. I myself have never
seen the 3.
I see here that 3 also means inactive indices on the constraints. So
all the constraints you see with 3 don't have indices !!!!!
(So you have a MAJOR problem in your original database)

I'm not sure if that also means that you can create duplicate records
(according to that constraint).

QuoteRDB$INDICES has RDB$INDEX_INACTIVE flag that is null or 0 if
index is active (after CREATE INDEX or ALTER INDEX ACTIVE). 1 means
that index is inactive (after ALTER INDEX INACTIVE). But there is also
value of 3 is used to indicate inactive indices on constraints. So,
you can set RDB$INDEX_INACTIVE=3 for that index, COMMIT, and than
return value to 0 and commit again - index will be rebuilt.
You need to find the records that are not according to the constraints
and correct them.
After that... you can enable the constraints/indices again.

----------------------------------------------------------------------
rvk · 2024-01-24 12:17

Quote from: Nicole on January 24, 2024, 12:59:21 pmabout select:
Such a select statement exists?!
More than select * from tbtable I never tried.
You could try something like this:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
FK_COMM, DATUM, COUNT(*) FROM TBCOT GROUP BY FK_COMM, DATUMHAVING
COUNT(*) >= 2

----------------------------------------------------------------------
Nicole · 2024-01-24 12:50

hmm...
This SQL statement does not react.
I hit "run" and nothing happens. No error-message, no result.

This is not very encouraging....

about this unique:
These are date + item.
The data go back to about 1981 or so.
The difference if there is an additional primary key - matters.

----------------------------------------------------------------------
rvk · 2024-01-24 12:56

Quote from: Nicole on January 24, 2024, 01:50:30 pmhmm...
This SQL statement does not react.
I hit "run" and nothing happens. No error-message, no result.
It should at least give you an empty result in FlameRobin.
(So headers FK_COMM, DATUM, COUNT without any info below it.)

It it doesn't and it hangs... then it is still searching your DB.

----------------------------------------------------------------------
rvk · 2024-01-24 14:10

I just checked. Setting the RDB$INDEX_INACTIVE to 3 just deactivates
the constraint.

So you have a database full of deactivated constraints.
You might want to check how long this is going on (when did this
happen).

With the previous mentioned SQL you can check, per constraint, if you
have duplicates.

You can also just try to activate the constraint again and see if it
throws an error.

ALTER INDEX UNQ1_NAME_U_JAHR ACTIVE

If it does, you need to investigate that constraint (with the SQL
mentioned above) to see the problem records.

You can also do this with a EXECUTE BLOCK for all but I would
recommend doing it per constraint.
https://www.firebirdfaq.org/faq274/

After you did it for all, you can try a backup/recover cycle to see if
it works.

----------------------------------------------------------------------
Nicole · 2024-01-24 17:10

worked finally, sobb.
See screenshot.

I do not like the results...
Perhaps I take a break and think over, what to do and how.

----------------------------------------------------------------------
rvk · 2024-01-24 17:59

Quote from: Nicole on January 24, 2024, 06:10:40 pmI do not like the
results...
Perhaps I take a break and think over, what to do and how.
Well yes, there are 9 records with 1 duplicate (so total of 18
records).

You need to do a select on those records/
Let me fiddle a bit... 

This:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
A.CNT, B.* FROM  (SELECT COUNT(*) AS CNT, FK_COMM, DATUM      FROM
TBCOT      GROUP BY FK_COMM, DATUM      HAVING COUNT(*) > 1) AINNER
JOIN RELATIE B ON B.FK_COMM=A.FK_COMM AND B.DATUM=A.DATUMORDER BY
FK_COMM, DATUM
(Hope I didn't make any typos) This should give you all records that
are and have duplicates.
You need to determine what you want to do with those records.
The problem is that you don't have a PRIMARY KEY which is UNIQUE so
when you DELETE on FK_COMM, DATUM it will delete BOTH records.

If you have some other fields which makes the records unique you can
delete the ones you want.
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i DELETE FROM
TBCOT WHERE FK_COMM='BP' AND DATUM='31.01.2023' AND
SPEC_LONG='something unique'
If there isn't anything unique in the other fields then there is no
other option than to delete all those records and add them again
(once).

After you have done that, and there is no more result with the above
SQL, you can activate the constraint/indices again.
(you can also test it by backup up the database and restoring it
again)

~~
Did you find out what happened to the database more than a year ago
and why all those constraints/indices are deactivated??
You are lucky it's just limited to those 9 records that are duplicated
(all on 31.01.2023).

----------------------------------------------------------------------
Nicole · 2024-01-24 18:23

Thank you for the attempt.
I am afraid, the evil twins did not know, they are two of them.
There was no way around deleting those lines and inserting them again.
Manually, value by value.

Whatever I did, it was done twice.
Deleting by selecting one of the bad twins, deleted both.
Changing the date or the COMM resulted into a copy of evil twins with
the new value.
So I had some evil twins more added instead of changed one of the
existing.
At least they were deleted easily.

Thank you so very much for this SQL code which was the only way to
find them.
I was that surprised, there was a way to make them visible at all!

And the SQL-code is saved and other unique keys checked by it as well.
In the best case my DB is clean now.

----------------------------------------------------------------------
rvk · 2024-01-24 18:28

Quote from: Nicole on January 24, 2024, 07:23:57 pmAnd the SQL-code is
saved and other unique keys checked by it as well.
In the best case my DB is clean now.
Yes, and a backup and restore is the best way to find that out.
After the restore you can do the line below again to see if all those
3's are gone (they need to be NULL or 0 for ACTIVE).

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0
If all the constraints are set again the DB should be fine (at least
you can restore it again   ).

----------------------------------------------------------------------
Nicole · 2024-01-24 19:41

I am afraid, there are a lot of 3s still.
Never heard of such things before.
What does "3" mean?

This does not say anything to me. What are "Active"?
What does TDB$SYSTEM_Flag mean and most of all:
What can I do?

----------------------------------------------------------------------
rvk · 2024-01-24 19:51

Quote from: Nicole on January 24, 2024, 08:41:37 pmI am afraid, there
are a lot of 3s still.
Never heard of such things before.
What does "3" mean?

I mentioned the 3's here:

Quote from: rvk on January 24, 2024, 01:06:46 pmQuote from: Nicole on
January 24, 2024, 12:59:21 pmI did the new one, but am clueless about
what it may mean.
The new one has 0 in RDB$INDEX_INACTIVE (which means it is active
because inactive = 1)

You can see the values here: https://ib-
aid.com/download/docs/firebird-language-reference-2.5/fblangref-
appx04-indices.html

But I see 3 everywhere else in RDB$INDEX_INACTIVE. I myself have never
seen the 3.
I see here that 3 also means inactive indices on the constraints. So
all the constraints you see with 3 don't have indices !!!!!
(So you have a MAJOR problem in your original database)

I'm not sure if that also means that you can create duplicate records
(according to that constraint).

QuoteRDB$INDICES has RDB$INDEX_INACTIVE flag that is null or 0 if
index is active (after CREATE INDEX or ALTER INDEX ACTIVE). 1 means
that index is inactive (after ALTER INDEX INACTIVE). But there is also
value of 3 is used to indicate inactive indices on constraints. So,
you can set RDB$INDEX_INACTIVE=3 for that index, COMMIT, and than
return value to 0 and commit again - index will be rebuilt.
You need to find the records that are not according to the constraints
and correct them.
After that... you can enable the constraints/indices again.

The 3's mean the same as 1 in RDB$INDEX_INACTIVE. It means the
constraint and index is inactive. That means it's not there. It
doesn't do its work.

For the constraints it means it doesn't do the check and for the index
it means it doesn't keep the index current. It also means that if you
had some index, your database doesn't use it and your whole database
is slow as hell on some queries.

Are those 3's still there if you do the backup/recover cycle??
I thought the inactive ones would be activated again.
Did you get any errors during restore??

Maybe that's the reason they are there in the first place, that you
did a restore and got errors on those indexes and they were never
activated.

If the 3's remain in the restored database and you didn't get any
errors you need to activate them all (manually with ALTER or with the
EXECUTE BLOCK from the page I mentioned earlier). But first check if
you don't get any errors during recover.

----------------------------------------------------------------------
Nicole · 2024-01-25 10:01

Thank you for the verbose explanation. This time I understood half of
it.
Exactly this was my original problem: My software was slooooowww. And
as I logged its lines and units, I found the reason, - the insert
statements into the database. They took one or two seconds each, an
eternity for the job they do.

My next step was to reduce the data. I had them back 30 years. I asked
myself: Do I need that?
And reduced it to 3 years and outsourced the original database.
However the speed of the inserts did not increase.

Today in the morning I thought: I have to re-write it.
But how?!
Unfortunately backup out of Firebird with the backup tool - does not
work any more. There is an error, which is meaningless. I do not know
the number, but I am quite sure, it is not helpful. I hope, all the
data are there. I did not miss anything.

My plan - or dream? - would be this:
I print all my tables and check the SQL and ideas.
Then I setup a new database (with the default setting of UTF8!) and
play the tables as SQL inside.

This was the easy part.
What I am not sure: How to get the data of the old database into the
new one?
The fields will be the same, so there SHOULD work a select * / insert
*
IF this is possible as such.
Is it?
And if yes, will I be able to manage this.

----------------------------------------------------------------------
alpine · 2024-01-25 10:09

QuoteHowever the speed of the inserts did not increase.Make sure all
of your tables have a PK! Even a surrogate one.
This has a direct impact over how the DB engine works and for the DB
internal structure.

----------------------------------------------------------------------
rvk · 2024-01-25 10:35

Quote from: Nicole on January 25, 2024, 11:01:29 amHowever the speed
of the inserts did not increase.Until you re-activate the
constaints/indices the database will remain slow.
You NEED to activate them, otherwise this can happen again.

Look at all the indices with
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0and examine the ones with 1
or 3 in RDB$INDEX_INACTIVE.

You can start by activating one or two constraints. Look at the name
and type
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ALTER INDEX
UNQ1_NAME_U_JAHR ACTIVE(where UNQ1_NAME_U_JAHR is the chosen index)

If it doesn't give an error then it is activated.
You can do that with all indexes (there is a script where you can do
that for all indexes but first try a few manually).

Once they are all activated the database should be a lot snappier.
But it is still advised to do a backup/recover cycle after that (for
garbage cleanup etc).

Quote from: Nicole on January 25, 2024, 11:01:29 amUnfortunately
backup out of Firebird with the backup tool - does not work any more.
There is an error, which is meaningless. I do not know the number, but
I am quite sure, it is not helpful. I hope, all the data are there. I
did not miss anything.You mean the backup doesn't work anymore.
Important stuff (seems to me  ). You need regular backups.

Quote from: Nicole on January 25, 2024, 11:01:29 amWhat I am not sure:
How to get the data of the old database into the new one?
The fields will be the same, so there SHOULD work a select * / insert
*
IF this is possible as such.
Is it?
And if yes, will I be able to manage this. 
FlameRobin can export all records to a text file as INSERT statements.
But that will only work for regular text fields, not for BLOB fields.
And when executing it for reading the records back in you need to make
sure the character set is correct.
Not easy if it contains lots of different things.

----------------------------------------------------------------------
Nicole · 2024-01-25 11:54

Pls be so kind, to check my new question
https://forum.lazarus.freepascal.org/index.php?board=16.0