DB cleaning up tables by lost foreign key

[Nicole]

I rewrote my DB and populated it again.
There is a table tbkurszeilen having ad foreign key fk_kontrakte from tbkontrakte.
This foreign key has the setting cascade. NOW. But this seems not to be valid ex post.

How can I say
Delete from tbkurszeilen where fk_kontrakt " has no target in " at tbkontrakt.id_kontrakt" ?

In other words I want to delete all data, whose foreign key does not exist any more.

[dseligo]

Code: MySQL  [Select][+][-]

1. delete
2. from tbkurszeilen
3. where fk_kontrakte not in (select id_kontrakt from tbkontrakt)

Test first with:

Code: MySQL  [Select][+][-]

1. select *
2. from tbkurszeilen
3. where fk_kontrakte not in (select id_kontrakt from tbkontrakt)

[rvk]

In other words I want to delete all data, whose foreign key does not exist any more.

You mean you have a foreign key pointing to non existing records in the other table.
That can't happen.

Or did you mean that you don't have the foreign key constraint added yet and want to delete some because otherwise you would get an error on adding the foreign key constraint?

[Nicole]

thank you seleigo, I will try that.

rvk:
This is a most strange thing and I cannot imagine, how it happened.
I migrated my corrupted DB into a new DB.
After having copied the DDL of my tables one by one and written the data from the old DB to the new one, everything looked fine.

Yesterday I found out, that some constraints were just GONE. Even a primary key was gone in one table as pk.
No idea, how this could happen. The DLL was taken from the old tables including the fk / pk constraints.
As the primary-key value was correct as value, the queries worked never the less. So I was not aware of the data disaster.

Since two days I try to migrate the data a second time to a third newly setup database.
And one trigger was gone! 10 were ok, the 11. - gone. 

Is it possible, that Flamerobin and Lazarus interact and eat database-snipets?

[rvk]

Is it possible, that Flamerobin and Lazarus interact and eat database-snipets?

Not if you work with a server instance and connect both to that server.

You need to check if there is any corruption.
You can do that with gfix.
https://www.ibphoenix.com/resources/documents/how_to/doc_5

I've already told you that in the other topics to do this on your previous corrupted database but I'm not sure if you ever did.

As long as you don't do that, this will keep happening.

[Nicole]

thank you for the verbose link.

Sure I did try the check. It failed. I am not sure, what exactly. I started at one end and there was always anything else, which did not work. So I made the first full rebuild one month ago.

I will try with your link again. At them moment the database third built looks good.
Luckily I have my unit now, which allows to rewrite the data from one database to another.

[rvk]

If the (copy of the) new database also fails the gfix check again, then you have a serious problem.

You might want to check your harddisk for corruption.

I've never had a Firebird fail on me.
O yeah, once, but that could be solved by a backup/restore cycle.

[Nicole]

you said really "check harddisk"?!!
What an idea coming up to my mind....

I did not think about this until now.
My hard-disk is a virtual one and it is created by VMWare.
And the hard-disk is a dynamic one.
And some time ago I used a function called something like "cleanup unused disk space of VM".

The first damage of my database may have been because I use the db more than 10 years and had no idea HOW to use a db. So I did a lot with it of which I bet, you never will do with a db. And I won't do this any more today. This is "I way young" and not extremely surprising.

The second damage WAS surprising.
hm, this utility of VMWare........... hm.
And sure, I used the tool after having migrated the old database, because the migration gave a lot of mess. I trusted the VMware. Although I do not trust any other cleanup utility.  :'( hm.

No backup, no mercy.
I did not loose any data just on loosing my database for two times.
I lost a lot of time, which I would have spent in more productive ways.

[rvk]

Just make sure to run the gfix check regularly.
For instance in a planned script and mail the results when it not empty (on error).
Especially because this keeps happening (and because it could also have another cause).

[Nicole]

How often do you recommend?

When my software develops I backup daily. If it runs for a time without changes, I do it weekly.
And about all 6 weeks I change backup medium-location. This means, that I give one backup into a location, which shall not be lost in case my office is lost.

[korba812]

Over time Firebird has had many bugs that caused similar problems so your problem was not necessarily due to damage to physical structure of database file (here one of the recent: https://github.com/FirebirdSQL/firebird/issues/6654).

If I remember correctly gfix only checks physical structure of database (at level of pages and records), but does not check validity of data (constraints, not null, etc). So in order to catch such errors you need to backup database, and when restoring data is  validated with constraints. For this reason, I recommend restoring database after the backup (e.g. to the null device). If during restoration of database there is a problem with constraints then you still have a chance to correct data in (still) working database. Full restoration of database from such a backup may be difficult or impossible.

[Nicole]

This is very interesting, thanks.
It was, that the database was readable and worked "fine" (in slow motion).
"only" the constraints were gone from one day to the other.

Unfortunately Firebird refused to re-add a contraint on the existing table.
So I wrote it into a new database.
And the feeling was UGLY, because all those foreign keys I added were not approved by the database, but trusted they were written fine before. Up to now it looks as if it had worked.

Backup / restore I usually did only once a year for garbage collection.
Usually I save my db as a fbd-file.
I did not trust the backup-restore-functionality to be able to restore in every case. So I preferred it as 1:1 copy.

and: thank you for the link.
I have so many not NULL fields...

[rvk]

Unfortunately Firebird refused to re-add a contraint on the existing table.

That's probably because there are already records added which don't adhere to the constraint rule.

You could have first checked if the database itself wasn't corrupt (just deleted constraint) and then fixed the records and added the constraint back.
BTW. You probably should have gotten a clear message when adding the constraint.

Backup / restore I usually did only once a year for garbage collection.
Usually I save my db as a fbd-file.
I did not trust the backup-restore-functionality to be able to restore in every case. So I preferred it as 1:1 copy.

I hope you copy when the database is NOT in use. If you do it while it is open by a client, you can also get corruption (although the transaction mechanisme will prevent this somewhat but it's always better to be save than sorry).

Normally you could do a gbak/backup and restore cycle just to make sure the restore cycle goes correctly and then delete the restored database (and then you know the backup file is correct). Normally you want to do this once a month (and the gbak/backup itself every day).

[Nicole]

The error message came from an unique. I do not think, that the fk-contraint was corrupt, as the re-writing of the tables including the constraint worked fine. I just added the fk in an empty table in a new db and made Lazarus transporting the data from the trouble-db to he new empty db. If the fk would have been corrupt, this would not have worked. I really hope, this sentence is true and there is no bug in fb which would allow this.

So my suspect: The unique-property of one element hindered to make it a foreign key. Just guessing, as everything is re-done in the meanwhile.

Yes, I close everything on saving my db. I HOPE I do it.
If you would give me one Euro for every time, I start my software and forgot to disconnect the database, which was connected at design-time - I would be rich.