Re: [Firebird-devel] Foreign key error even there should not be one (maybe, Weird problem in one database)
I've said I can provide the DB and needed data if it is kept secret :) -Tee- On Tue, Sep 20, 2016 at 3:55 PM, Vlad Khorsun wrote: > 19.09.2016 14:06, Tommi Prami wrote: > > Weird part was that I actually pumped the data to the empty database and > it behaved the same. > > > > This is more than less puzzling. > > > > And the Backup and restore did not help,. which also, I think, should > recreate the indexes and so on... > >Without reproducible example it is impossible even to confirm problem > existance. > > Regards, > Vlad > > > > -- > Firebird-Devel mailing list, web interface at > https://lists.sourceforge.net/lists/listinfo/firebird-devel > -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Foreign key error even there should not be one (maybe, Weird problem in one database)
19.09.2016 14:06, Tommi Prami wrote: > Weird part was that I actually pumped the data to the empty database and it > behaved the same. > > This is more than less puzzling. > > And the Backup and restore did not help,. which also, I think, should > recreate the indexes and so on... Without reproducible example it is impossible even to confirm problem existance. Regards, Vlad -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Foreign key error even there should not be one (maybe, Weird problem in one database)
Weird part was that I actually pumped the data to the empty database and it behaved the same. This is more than less puzzling. And the Backup and restore did not help,. which also, I think, should recreate the indexes and so on... -Tee- On Thu, Sep 15, 2016 at 10:58 AM, Thomas Steinmaurer wrote: > > Whole story here : > > > > https://plus.google.com/105626236962310493174/posts/3UZB71NjUhA > > > > Copy of message > > - - - - - - - - - - - - - - - - > > Is there a tool (or way) to debug database step by step. > > > > Now we have weird situation where delete from one database fails, on > > foreign key error, which sounds like it would be trivial thing, but seems > > so far it is not. > > > > I was just thinking that if it would be our application code I would be > > able to debug through all of the related code and see if there is the > > culprit or not. > > > > We suspect it has somethign to do with specific data on the rows. because > > some of them delete perfectly fine. This happens in this one database > only. > > I've done backup restore for it, I ran gfix stuff on it. I even pumped > the > > data in empty DB. > > > > it should be problem of database structure (trickers and such), data or > the > > Firebird. and most likely it problem of first two. > > > > I was just thinking that is there an tool I could see step by step all > the > > operations in the DB before the foreigh key error is raised. So I could > > step by step see the order the trickers and so fires and what they do. > > > > Or if someone at Firebird project could check if it is indeed the > Firebird > > bug. I think we could send the DB, if it he/she keeps it safe and keeps > it > > secret :) > > If you re-create the FK constraint - which re-creates an index behind the > scene - solves the problem, then some sort of data/index corruption might > be an explanation. > > > Thomas > > > -- > Firebird-Devel mailing list, web interface at > https://lists.sourceforge.net/lists/listinfo/firebird-devel > -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Foreign key error even there should not be one (maybe, Weird problem in one database)
> Whole story here : > > https://plus.google.com/105626236962310493174/posts/3UZB71NjUhA > > Copy of message > - - - - - - - - - - - - - - - - > Is there a tool (or way) to debug database step by step. > > Now we have weird situation where delete from one database fails, on > foreign key error, which sounds like it would be trivial thing, but seems > so far it is not. > > I was just thinking that if it would be our application code I would be > able to debug through all of the related code and see if there is the > culprit or not. > > We suspect it has somethign to do with specific data on the rows. because > some of them delete perfectly fine. This happens in this one database only. > I've done backup restore for it, I ran gfix stuff on it. I even pumped the > data in empty DB. > > it should be problem of database structure (trickers and such), data or the > Firebird. and most likely it problem of first two. > > I was just thinking that is there an tool I could see step by step all the > operations in the DB before the foreigh key error is raised. So I could > step by step see the order the trickers and so fires and what they do. > > Or if someone at Firebird project could check if it is indeed the Firebird > bug. I think we could send the DB, if it he/she keeps it safe and keeps it > secret :) If you re-create the FK constraint - which re-creates an index behind the scene - solves the problem, then some sort of data/index corruption might be an explanation. Thomas -- Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Foreign key error even there should not be one (maybe, Weird problem in one database)
It is cascade thingy (I am not the DB guy...) So it normally works OK...
If we drop the foreign key and create it again it starts to work. Data from
Both tables gets removed correctly.
Appears the there is small difference in the newly created foreign key.
For this foreign key following stuff in system tables are :
RDB$UNIQUE_FLAG = 0 RDB$INDEX_INACTIVE = 0 RDB$INDEX_TYPE = 0
And we create it again those are NULL
Do not know if that is an actual cause but difference we see.
-Tee-
On Thu, Sep 15, 2016 at 10:44 AM, Dimitry Sibiryakov
wrote:
> 15.09.2016 9:39, Tommi Prami wrote:
> > Foreign key references are present for the record
> > Problematic key value is ("TOSITEID" = 945)
> >
> > Data is there, it can be selected but the delete fails on this DB
>
>Delete fails exactly because data is there. You must delete referencing
> records first
> and only then - record in master table.
>
>
> --
>WBR, SD.
>
>
> --
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>
--
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Foreign key error even there should not be one (maybe, Weird problem in one database)
15.09.2016 9:39, Tommi Prami wrote:
> Foreign key references are present for the record
> Problematic key value is ("TOSITEID" = 945)
>
> Data is there, it can be selected but the delete fails on this DB
Delete fails exactly because data is there. You must delete referencing
records first
and only then - record in master table.
--
WBR, SD.
--
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Foreign key error even there should not be one (maybe, Weird problem in one database)
We use
2.5.6.27020 (32bit)
[#1: Delete - PLAN (TOSITE INDEX (PK_TOSITE))]
violation of FOREIGN KEY constraint "FK_VIENTI_TOSITE" on table "VIENTI"
Foreign key references are present for the record
Problematic key value is ("TOSITEID" = 945)
Data is there, it can be selected but the delete fails on this DB
(Hope this helps)
On Thu, Sep 15, 2016 at 10:26 AM, Alex Peshkoff wrote:
> On 15.09.2016 10:14, Tommi Prami wrote:
> > Whole story here :
> >
> > https://plus.google.com/105626236962310493174/posts/3UZB71NjUhA
> >
> > Copy of message
> > - - - - - - - - - - - - - - - -
> > Is there a tool (or way) to debug database step by step.
> >
> > Now we have weird situation where delete from one database fails, on
> > foreign key error, which sounds like it would be trivial thing, but seems
> > so far it is not.
> >
> > I was just thinking that if it would be our application code I would be
> > able to debug through all of the related code and see if there is the
> > culprit or not.
> >
> > We suspect it has somethign to do with specific data on the rows. because
> > some of them delete perfectly fine. This happens in this one database
> only.
> > I've done backup restore for it, I ran gfix stuff on it. I even pumped
> the
> > data in empty DB.
> >
> > it should be problem of database structure (trickers and such), data or
> the
> > Firebird. and most likely it problem of first two.
> >
> > I was just thinking that is there an tool I could see step by step all
> the
> > operations in the DB before the foreigh key error is raised. So I could
> > step by step see the order the trickers and so fires and what they do.
> >
> > Or if someone at Firebird project could check if it is indeed the
> Firebird
> > bug. I think we could send the DB, if it he/she keeps it safe and keeps
> it
> > secret :)
> >
> > - - - -
> >
> > This is slightly scary. We dropped the FK and created it back again and
> it
> > works like a charm. And friend checked that foreign key from the System
> > tables and it seems to be exactly same.
> >
> > So could this be the bug or issue in firebird after all?
>
> What FB version do you use?
> Modern firebird versions (like FB3 for example) produce good explanation
> what FK failed and why.
> For example (in employee.fdb):
>
> SQL> delete from country where country = 'Japan';
> Statement failed, SQLSTATE = 23000
> violation of FOREIGN KEY constraint "INTEG_11" on table "JOB"
> -Foreign key references are present for the record
> -Problematic key value is ("COUNTRY" = 'Japan')
>
>
>
> --
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>
--
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Foreign key error even there should not be one (maybe, Weird problem in one database)
On 15.09.2016 10:14, Tommi Prami wrote:
> Whole story here :
>
> https://plus.google.com/105626236962310493174/posts/3UZB71NjUhA
>
> Copy of message
> - - - - - - - - - - - - - - - -
> Is there a tool (or way) to debug database step by step.
>
> Now we have weird situation where delete from one database fails, on
> foreign key error, which sounds like it would be trivial thing, but seems
> so far it is not.
>
> I was just thinking that if it would be our application code I would be
> able to debug through all of the related code and see if there is the
> culprit or not.
>
> We suspect it has somethign to do with specific data on the rows. because
> some of them delete perfectly fine. This happens in this one database only.
> I've done backup restore for it, I ran gfix stuff on it. I even pumped the
> data in empty DB.
>
> it should be problem of database structure (trickers and such), data or the
> Firebird. and most likely it problem of first two.
>
> I was just thinking that is there an tool I could see step by step all the
> operations in the DB before the foreigh key error is raised. So I could
> step by step see the order the trickers and so fires and what they do.
>
> Or if someone at Firebird project could check if it is indeed the Firebird
> bug. I think we could send the DB, if it he/she keeps it safe and keeps it
> secret :)
>
> - - - -
>
> This is slightly scary. We dropped the FK and created it back again and it
> works like a charm. And friend checked that foreign key from the System
> tables and it seems to be exactly same.
>
> So could this be the bug or issue in firebird after all?
What FB version do you use?
Modern firebird versions (like FB3 for example) produce good explanation
what FK failed and why.
For example (in employee.fdb):
SQL> delete from country where country = 'Japan';
Statement failed, SQLSTATE = 23000
violation of FOREIGN KEY constraint "INTEG_11" on table "JOB"
-Foreign key references are present for the record
-Problematic key value is ("COUNTRY" = 'Japan')
--
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
