在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语句

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
);

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:

Statement failed, SQLCODE = -607
unsuccessful metadata update
-STORE RDB$RELATIONS failed
-deadlock
After line 8 in file d:\myscript.sql

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 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.

Instead of dropping and creating this way, you should use the DDL statement RECREATE TABLE instead.

Note that the word deadlock in this error is actually a bit of a misnomer (there is no real deadlock).

这篇关于在Firebird脚本中创建表会导致“元数据更新失败".陷入僵局的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！