在Firebird脚本中创建表会导致“元数据更新失败".陷入僵局 [英] Creating table in Firebird script causes "unsuccessful metadata update" with deadlock
问题描述
我使用"isql -i scriptfile.sql"运行以下脚本:
CONNECT C:\Databasefile.fdb USER user PASSWORD password;
SET TERM !! ;
EXECUTE BLOCK AS BEGIN
IF (EXISTS(SELECT 1 FROM rdb$relations WHERE rdb$relation_name = 'MYTABLE')) THEN
EXECUTE STATEMENT 'DROP TABLE MYTABLE;';
END!!
SET TERM ; !!
CREATE TABLE MYTABLE
(
MYCOLUMN VARCHAR(14) NOT NULL
);
我第一次运行此表(当表不存在时)将按预期方式创建. 如果再次运行该脚本,则会出现以下错误:
Statement failed, SQLCODE = -607
unsuccessful metadata update
-STORE RDB$RELATIONS failed
-deadlock
After line 8 in file d:\myscript.sql
退出脚本后,MYTABLE已被删除,无法再在数据库中找到. 如果我第三次运行脚本,则会再次创建表,并且不会引发任何错误.
为什么脚本不能同时删除然后重新创建表?
不允许使用PSQL的- 解决方案
DDL,使用EXECUTE STATEMENT
也不是直接禁止的,并且通常是可能的,但是由于这些问题,这样做仍然不明智.我不确定原因,但其中部分原因与Firebird中DDL更改的应用方式有关.使用execute语句会添加其他锁iirc,该锁与同一个表名的后续DDL冲突.
您应该使用DDL语句 The very first time I run this (when the table does not already exist) the table is created as expected.
If I run the script again I get the following error: When the script exits, MYTABLE has been deleted and can no longer be found in the database.
If I run the script a third time the table is once again created and no errors are thrown. Why can't the script both delete and then recreate a table? DDL from PSQL is not allowed, using Instead of dropping and creating this way, you should use the DDL statement Note that the word deadlock in this error is actually a bit of a misnomer (there is no real deadlock). 这篇关于在Firebird脚本中创建表会导致“元数据更新失败".陷入僵局的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!CONNECT C:\Databasefile.fdb USER user PASSWORD password;
SET TERM !! ;
EXECUTE BLOCK AS BEGIN
IF (EXISTS(SELECT 1 FROM rdb$relations WHERE rdb$relation_name = 'MYTABLE')) THEN
EXECUTE STATEMENT 'DROP TABLE MYTABLE;';
END!!
SET TERM ; !!
CREATE TABLE MYTABLE
(
MYCOLUMN VARCHAR(14) NOT NULL
);
Statement failed, SQLCODE = -607
unsuccessful metadata update
-STORE RDB$RELATIONS failed
-deadlock
After line 8 in file d:\myscript.sql
EXECUTE STATEMENT
it is not directly forbidden, and usually possible, but still not wise exactly because of these kinds of problems. I am not exactly sure about the reasons, but part of it have to do with how DDL changes are applied in Firebird; the use of execute statement adds additional locks iirc which conflict with a subsequent DDL for the same table name.RECREATE TABLE
instead.