Re: [firebird-support] I can't drop foreign key
Thank you !!! Mark !! Resolved! One procedure referenced the Index; The procedure uses "Plan Sort(Hachu Index(FKHACHUTOPARTS))" in Select SQL. I create other Index HachuP on Hachu(partsid). And I alter the procedure ,not to use plan the FKHACHUTOPARTS to use plan with new Index HachuP. I could drop the constraint. Maybe I wrote the procedure over 10years ago, and I forgot that the procedure used PLAN with Index. Hachu table has PARTSID column, and Hachu table has many column, then I didn't wrote all column. And I forgot the important column "PARTSID",sorry. I thank for your advice very much. Nobuo Watanabe On 2016/02/17 17:12, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: On 16-2-2016 22:05, watan...@yahoo.com [firebird-support] wrote: > I am using FireBird 2.5.3 > and a Database is updated from 2.2, and the database was updated from 1.5. > > The database have about 150 tables and just one foreign key. > > The Foreign key is from "hachu"Table to "parts"Table > > This is part of definition of the tables. > ISQL display these informations. > > SQL> show table parts; > OPERATIONCODE VARCHAR(100) Nullable > PARTSID INTEGER Not Null > PRODUCTCODE VARCHAR(100) Nullable > CONSTRAINT INTEG_152: > Primary key (PARTSID) > > SQL> show table hachu; > HACHUDATE bsp; DATE Nullable > HACHUID INTEGER Not Null > HACHUNO VARCHAR(10) Nullable > HSU INTEGER Nullable > CONSTRAINT FKHACHUTOPARTS: > Foreign key (PARTSID) References PARTS (PARTSID) On Delete Set Null Interesting that this table doesn't appear to have a column PARTSID, so this constraint shouldn't be able to exist. > I tried to drop the foreign key,but I could not. > SQL> alter table hachu drop constraint fkhachutoparts; > > Statement failed, SQLSTATE = 42000 > unsuccessful metadata update > -cannot delete*-INDEX FKHACHUTOPARTS > -there are 1 dependencies > > I deleted all records of "parts" and "hachu". > I deleted all triggers of "parts" and "hachu". > > But "there are 1 dependencies" Do you have stored procedures referencing this table? > I can't also drop these tables. > > SQL> drop table hachu; > Statement failed, SQLSTATE = 42000 > unsuccessful metadata update > -cannot delete > -INDEX FKHACHUTOPARTS > -there are 1 dependencies > > SQL> drop table parts; > Statement failed, SQLSTATE = 42000 > unsuccessful metadata update > -ERASE RDB$RELATION_CONSTRAINTS failed > -action cancelled by trigger (1) to preserve data integrity > -Cannot delete PRIMARY KEY being used in FOREIGN KEY definition. > > I don't understand why i can't drop the foreign key. > But i want to drop the foreign key. Could you run gifx -v -full -n on your database and report the output? If all else fails, you could also consider creating a new database, and pumping over the data. Mark -- Mark Rotteveel -- /ﾉﾍﾞﾙｯﾄ/// 株式会社 ノベルット ソフトウェア インダストリー 代表取締役 渡辺 伸雄 〒144-0043 東京都 大田区 羽田２－１２－３ Tel:03-5705-2595 Fax:03-6423-9505 ＦＡＸ番号が変わりました！ mobile-phone:080-3430-2595 070-5582-6540 Email:watan...@noveltte.jp ///ﾉﾍﾞﾙｯﾄ/
AW: [firebird-support] Problem Firebird 2.5
Thanks Paul, I have found the issue :) Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Mittwoch, 17. Februar 2016 15:38 An: firebird-support@yahoogroups.com Betreff: [firebird-support] Problem Firebird 2.5 <> Usually caused (AFAIK) by a restore done with Firebird 2.1 or an earlier gbak, or if a Firebird 2.5 backup was restored to Firebird 2.1, backed up and then restored on Firebird 2.5 On the original database you can do DROP ROLE RDB$ADMIN; COMMIT; When you restore, gbak.exe should recreate the RDB$ADMIN role. Paul
[firebird-support] Problem Firebird 2.5
<> Usually caused (AFAIK) by a restore done with Firebird 2.1 or an earlier gbak, or if a Firebird 2.5 backup was restored to Firebird 2.1, backed up and then restored on Firebird 2.5 On the original database you can do DROP ROLE RDB$ADMIN; COMMIT; When you restore, gbak.exe should recreate the RDB$ADMIN role. Paul
AW: [firebird-support] Problem Firebird 2.5
Oh shit, thank our so much! There was an old gbak in the system32 directory which were the task planer has make a backup. -Ursprüngliche Nachricht- Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Mittwoch, 17. Februar 2016 15:25 An: firebird-support@yahoogroups.com Betreff: Re: [firebird-support] Problem Firebird 2.5 17.02.2016 15:14, 'Checkmail' check_m...@satron.de [firebird-support] wrote: > .it is not the gbak from firebird 2.1 The error shows that the backup was created with gbak 2.1 but from server 2.5. Nothing you can do but create database from scratch and pump data. > Now, gfix shows me single user maintenance in attributes > > I have no access to the database You can bring the database online with gfix to pump data from it. -- 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
Re: [firebird-support] Problem Firebird 2.5
17.02.2016 15:14, 'Checkmail' check_m...@satron.de [firebird-support] wrote: > .it is not the gbak from firebird 2.1 The error shows that the backup was created with gbak 2.1 but from server 2.5. Nothing you can do but create database from scratch and pump data. > Now, gfix shows me „single user maintenance“ in attributes > > I have no access to the database You can bring the database online with gfix to pump data from it. -- 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-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
AW: [firebird-support] Problem Firebird 2.5
.it is not the gbak from firebird 2.1 and I'm using the -user sysdba -pas masterkey switches. What can I do? Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Mittwoch, 17. Februar 2016 14:32 An: firebird-support@yahoogroups.com Betreff: [firebird-support] Problem Firebird 2.5 Hello @ll, during a restore of a firebird gbk on a fres installed system with the same firebird 2.5 cs server like the original server, I get this error: attemp to store duplicate value (visible to active transactions) inn unique index RDB$Index_39 Problematic key value is (RDB$ROLE_NAME = RDB$ADMIN) Now, gfix shows me single user maintenance in attributes I have no access to the database, what goes wrong? Thank you
[firebird-support] Problem Firebird 2.5
Hello @ll,
during a restore of a firebird gbk on a fres installed system with the same
firebird 2.5 cs server like the original server, I get this error:
attemp to store duplicate value (visible to active transactions) inn unique
index "RDB$Index_39" Problematic key value is ("RDB$ROLE_NAME" =
,RDB$ADMIN')
Now, gfix shows me "single user maintenance" in attributes
I have no access to the database, what goes wrong?
Thank you
Re: [firebird-support] I can't drop foreign key
On 16-2-2016 22:05, watan...@yahoo.com [firebird-support] wrote: > I am using FireBird 2.5.3 > and a Database is updated from 2.2, and the database was updated from 1.5. > > The database have about 150 tables and just one foreign key. > > The Foreign key is from "hachu"Table to "parts"Table > > This is part of definition of the tables. > ISQL display these informations. > > SQL> show table parts; > OPERATIONCODE VARCHAR(100) Nullable > PARTSID INTEGER Not Null > PRODUCTCODE VARCHAR(100) Nullable > CONSTRAINT INTEG_152: > Primary key (PARTSID) > > SQL> show table hachu; > HACHUDATE bsp;DATE Nullable > HACHUID INTEGER Not Null > HACHUNO VARCHAR(10) Nullable > HSU INTEGER Nullable > CONSTRAINT FKHACHUTOPARTS: >Foreign key (PARTSID)References PARTS (PARTSID) On Delete Set Null Interesting that this table doesn't appear to have a column PARTSID, so this constraint shouldn't be able to exist. > I tried to drop the foreign key,but I could not. > SQL> alter table hachu drop constraint fkhachutoparts; > > Statement failed, SQLSTATE = 42000 > unsuccessful metadata update > -cannot delete*-INDEX FKHACHUTOPARTS > -there are 1 dependencies > > I deleted all records of "parts" and "hachu". > I deleted all triggers of "parts" and "hachu". > > But "there are 1 dependencies" Do you have stored procedures referencing this table? > I can't also drop these tables. > > SQL> drop table hachu; > Statement failed, SQLSTATE = 42000 > unsuccessful metadata update > -cannot delete > -INDEX FKHACHUTOPARTS > -there are 1 dependencies > > SQL> drop table parts; > Statement failed, SQLSTATE = 42000 > unsuccessful metadata update > -ERASE RDB$RELATION_CONSTRAINTS failed > -action cancelled by trigger (1) to preserve data integrity > -Cannot delete PRIMARY KEY being used in FOREIGN KEY definition. > > I don't understand why i can't drop the foreign key. > But i want to drop the foreign key. Could you run gifx -v -full -n on your database and report the output? If all else fails, you could also consider creating a new database, and pumping over the data. Mark -- Mark Rotteveel
