On 22/2/62 17:17, Chris Locke wrote:
This issue was found via DB Browser for SQLite, but relates to SQLite, not
DB Browser for SQLite, so please bear with me
If a table is part of a view, then SQLite complains that "Error: error in
view view1: no such table: main.table11"
ALTER TABLE ... RENAME TO ... commands fail in new versions of SQLite if
the schema contains views or triggers that refer to tables or columns
that do not exist. That's what is happening here. To restore the legacy
behaviour, run:
PRAGMA legacy_alter_table = 1;
https://sqlite.org/pragma.html#pragma_legacy_alter_table
Dan.
The link to the full issue is here:
https://github.com/sqlitebrowser/sqlitebrowser/issues/1686
Martin has followed the instructions here (
https://www.sqlite.org/lang_altertable.html#otheralter) which raises the
above error.
(link to comment:
https://github.com/sqlitebrowser/sqlitebrowser/issues/1686#issuecomment-464136036
)
We are using SQLite 3.27.1 and 3.26 (in two different applications) and (as
per the above GitHub thread) 3.27.1 mentions fixing an ALTER TABLE bug
(item 11) with views. However, there is no WITH clause and no redundant
UNIQUE clauses involved in our case.
Its worked fine in earlier versions of SQLite, and note a comment about the
improved ALTER TABLE functionality:
"In version 3.25.0, the ALTER TABLE statement has been enhanced to run a
test-parse of the whole schema after it alters the schema, to make sure
that the edits it performed on the schema didn't break anything."
it sounds like this extra sanity check is what is causing the problem
described here which also explains why it worked for older versions of
SQLite.
In this issue (https://www.sqlite.org/src/tktview?name=31c6e64ff9) drh
describes a very similar issue to the one here. He is using a trigger
instead of a view but besides that is is pretty much the same problem.
Again some more explanations from the ticket:
"The DROP TABLE removes the table t1, which leaves a dangling reference to
t1 in the trigger. Then during the ALTER TABLE, the trigger is reparsed,
but the reparse fails due to the dangling reference."
Again, it sounds exactly like the issue we are having here. The second
ticket is still open, so no fix there yet. Last modification was 2018-10-03.
Is there any confirmation or further details on this issue at all?
Thanks,
Chris
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
