Cannot modify table if part of a view

Details for the issue

This may be linked to #1650.
Tried to rename fields in a table, but it failed as it tried to do something with the view, and failed.

What did you do?

Tried to modify a table. In fact, just opening the 'Edit table definition' window and clicking 'OK' causes the error (which obviously tries to rebuild the table)

What did you expect to see?

No error

What did you see instead?

An error.

Useful extra information

The info below often helps, please fill it out if you're able to. :)

So the table schema is:

CREATE TABLE "categories" (
	"ca_id"	INTEGER,
	"ca_category"	INTEGER,
	"ca_parentCategory"	INTEGER,
	PRIMARY KEY("ca_id")
);

And I have a view:

CREATE VIEW "view_transactions" AS select * from transactions as t
left join accounts as a on tr_accountId=ac_id
left join categories on tr_categoryId=ca_id

I was aiming to remove the 'ca_' prefixes from the fields, but any table modification results in the above error. Deleting the view works as a workaround, but obviously, a long-term solution would be handy. 😉

This was from the SQL log.

PRAGMA foreign_keys
PRAGMA foreign_keys = '0';
PRAGMA database_list;
SELECT type,name,sql,tbl_name FROM "main".sqlite_master;
PRAGMA "main".TABLE_INFO("view_transactions");
SELECT type,name,sql,tbl_name FROM sqlite_temp_master;
CREATE TABLE "sqlb_temp_table_4" (
	"ca_id"	INTEGER,
	"ca_category"	INTEGER,
	"ca_parentCategory"	INTEGER,
	PRIMARY KEY("ca_id")
);
INSERT INTO "main"."sqlb_temp_table_4" ("ca_category","ca_id","ca_parentCategory") SELECT "ca_category","ca_id","ca_parentCategory" FROM "main"."categories";
PRAGMA defer_foreign_keys
PRAGMA defer_foreign_keys = '1';
DROP TABLE "main"."categories";
ALTER TABLE "main"."sqlb_temp_table_4" RENAME TO "categories"
PRAGMA database_list;

Error occurred at the second from last line - at the 'ALTER TABLE' command.

What operating system are you using?

Windows: ( version: 10_ )
Linux: ( distro: ___ )
Mac OS: ( version: ___ )
Other: ___

What is your DB4S version?

Nightly (24 Dec)
3.11.0-alpha1 or 3.11.0-beta*
3.10.1
Other: ___

Did you also

Try out the latest nightly version: https://github.com/sqlitebrowser/sqlitebrowser#nightly-builds
Search for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20

@chrisjlocke

Heh Heh Heh. Good catch @chrisjlocke. 😄

For me this just works 😕 I get the same SQL log as you but without any errors. Can you maybe double check which SQLite version is used in the nightlies?

Do not recreate table schema if table did not change

Can you maybe double check which SQLite version is used in the nightlies?

The Windows and macOS nightlies are using 3.26.0 at the moment. It's the only "safe" version. 😄

@chrisjlocke

Ok, this version should definitely work 🙁

@chrisjlocke You said "In fact, just opening the 'Edit table definition' window and clicking 'OK' causes the error (which obviously tries to rebuild the table)". Can you check if this is still happening in the latest nightly build? I fixed this problem and the new code is similar to the code which should have prevented #1677 from ever happening. So it's interesting to see if the new code works as expected or if it has the same problem, i.e. if just clicking 'OK' rebuilds the table or not 😄

As a data point (related to my previous comment), it turns out SQLite 3.25.3 is safe too. That release also has the patch for the Magellan vulnerability. We're still using 3.26.0 in our nightlies though.

Using the latest nightly (27 Dec) if the table structure dialog is opened and I click on 'OK', the log simply displays:

PRAGMA foreign_keys
PRAGMA foreign_keys = '0';
PRAGMA "main".foreign_key_check
PRAGMA foreign_keys = '1';

So the table is no longer rebuilt.
However, renaming a field still generates the same error:

Again, worth checking if this issue persists after the fix for #1725 😉

While renaming a field works, the error occurs if the primary key switch is turned off. Can do a screencast if required.

Basic steps:
Create table with primary key. Save table. Modify table. Remove primary key toggle for primary key field. Click OK. Receive below error.

Adding a field works. Removing a field generates the same error.

Yeah, maybe a screencast would help. I didn't manage to reproduce it yet 😄

Yay! I can play with the cheesy music again! 🎶

So 'view1' is simply a 'select * ffrom table11'. Removing the primary key on table11 causes the error.

https://screencast-o-matic.com/watch/cqnobX39di

Some observations:

Initially I tried this with SQLCipher 3.4.1 which worked fine. Now I'm using SQLite 3.26.0 and get the same error.
While playing around with this I could also trigger an error message which referred to a view which doesn't mention the modified table at all but is selecting from a non-existing table instead.

So there is definitely some weirdness going on here.

Ok, I just followed the instructions from the SQLite website step by step using the command line tool and... 🥁 ... I got the exact same error message:

SQLite version 3.26.0 2018-12-12 11:57:35
Enter ".help" for usage hints.
sqlite> pragma foreign_keys=off;
sqlite> begin transaction;
sqlite> create table new_table11(Field1 INTEGER, Field2 INTEGER, Field3 INTEGER, Field4 INTEGER);
sqlite> insert into new_table11 select * from table11;
sqlite> drop table table11;
sqlite> alter table new_table11 rename to table11;
Error: error in view view1: no such table: main.table11

So maybe it's not an issue in our code after all?

There's a new SQLite release (3.27.1) out that mentions fixing an ALTER TABLE bug (item 11) with views:

https://www.sqlite.org/releaselog/3_27_1.html

Maybe this is due to that?

Hmmm, maybe point 17 in that same SQLite release notes list too.

There is no WITH clause and no redundant UNIQUE clauses involved in our case but the release notes definitely make it sound like there are some remaining issues in the SQLite code.

This sounds like somebody else is having the same issue (he's doing it wrong initially but later writes he gets the same error when doing it the right way). The SQLite developers sound sceptical though 😉

Here is something interesting. In this issue drh is telling us a bit about the improved ALTER TABLE functionality of SQLite >= 3.25.0:

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.

To me 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 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, to me 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. Seems like an unusual long time for the SQLite devs to fix a bug.

Maybe lets ask them?

So this issue is triggered when using SQLite >= 3.25.0? It works fine with older versions of SQLite?

@karim

@karim I haven't done any conclusive testing yet. The SQLCipher version I tried is based on SQLite 3.15 - so there are many SQLite versions I haven't tried yet. 3.25.0 mainly comes into mind because of the improved ALTER TABLE support it added.

@justinclift I wanted to post a quick comment on that issue but turns out you're not allowed to do that as an anonymous user. I'll see if I get around to write to the mailing list soon-ish 😄

@MKleusberg

@MKleusberg From memory, @chrisjlocke is already subscribed to that mailing list. Maybe Chris can do it? 😄

I'm simply asking if this is affecting some specific versions and we have no way of fixing so we can add them to the list known issues.

Have posted to the SQLite mailing list...

...to which Dan has replied.

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

@chrisjlocke

Thanks @chrisjlocke. 😄

Navigation Menu

Repository navigation

Cannot modify table if part of a view #1686

Details for the issue

What did you do?

What did you expect to see?

What did you see instead?

Useful extra information

What operating system are you using?

What is your DB4S version?

Did you also

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Cannot modify table if part of a view #1686

Description

Details for the issue

What did you do?

What did you expect to see?

What did you see instead?

Useful extra information

What operating system are you using?

What is your DB4S version?

Did you also

Activity

justinclift commented on Dec 25, 2018

MKleusberg commented on Dec 25, 2018

justinclift commented on Dec 25, 2018

MKleusberg commented on Dec 26, 2018

justinclift commented on Dec 26, 2018

chrisjlocke commented on Dec 27, 2018

MKleusberg commented on Feb 15, 2019

chrisjlocke commented on Feb 15, 2019

MKleusberg commented on Feb 15, 2019

chrisjlocke commented on Feb 15, 2019

chrisjlocke commented on Feb 15, 2019

MKleusberg commented on Feb 15, 2019

MKleusberg commented on Feb 15, 2019

justinclift commented on Feb 15, 2019

justinclift commented on Feb 15, 2019

MKleusberg commented on Feb 15, 2019

MKleusberg commented on Feb 15, 2019

MKleusberg commented on Feb 16, 2019

justinclift commented on Feb 16, 2019

karim commented on Feb 21, 2019

MKleusberg commented on Feb 21, 2019

justinclift commented on Feb 21, 2019

karim commented on Feb 22, 2019

chrisjlocke commented on Feb 22, 2019

chrisjlocke commented on Feb 22, 2019

justinclift commented on Feb 22, 2019

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions