SQLite Forum

Bug: Database schema corruption
Login

Bug: Database schema corruption

(1) By LeonB (leonbe) on 2020-10-26 16:05:35 [link] [source]

Hi,

It seems like running 'begin; create view if not exists IF as select *;' corrupts the database schema and produces lots of syntax errors (example attached). Is this a bug or intended behavior?

Thanks!

SQLite version 3.34.0 2020-10-20 14:40:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> /*works*/
sqlite> CREATE TABLE Persons (PersonID int, LastName varchar(255));
sqlite> select a from bbb;
Error: no such table: bbb
sqlite> /*start faulty sql*/
sqlite> begin;
sqlite> create view if not exists IF as select *;        
Error: malformed database schema (IF) - near "as": syntax error
sqlite> /*persistent errors now*/        
sqlite> CREATE TABLE anotherTable (id int, name varchar(255));
Error: malformed database schema (IF) - near "as": syntax error
sqlite> select a from bbb;
Error: malformed database schema (IF) - near "as": syntax error

(2.1) By Keith Medcalf (kmedcalf) on 2020-10-26 16:48:39 edited from 2.0 in reply to 1 [link] [source]

I believe this is a demonstration of the Garbage-In-Garbage-Out principle at work.

What do you expect to happen?

(4) By Keith Medcalf (kmedcalf) on 2020-10-26 16:50:05 in reply to 2.1 [link] [source]

All i can see is that the defective create view does not appear to actually be aborted at the statement level bit it takes rather a bit of investigation to determine this. Is this of which you are complaining?

(5) By Richard Hipp (drh) on 2020-10-26 17:04:24 in reply to 4 [link] [source]

The statement:

   CREATE VIEW IF NOT EXISTS IF AS SELECT null;

Generates an entry in the sqlite_schema table that contains the following SQL:

   CREATE VIEW IF AS SELECT null;

In other words, the "IF NOT EXISTS" clause gets removed. That is unfortunate, because with the IF NOT EXISTS clause, the statement is successfully parsed and generates a new view named "IF". But without the IF NOT EXISTS clause, it is a syntax error. And this syntax error gets inserted into the sqlite_schema table, where it causes problems whenever the schema is parsed again in the future.

You can create similar problems using a statement like:

   CREATE TABLE IF NOT EXISTS IF(a,b,c);

There are probably other variations on this same issue.

(6) By Keith Medcalf (kmedcalf) on 2020-10-26 17:17:58 in reply to 5 [link] [source]

Yes, but this behaviour ONLY exists inside a transaction.

In autocommit it does not exist.

So the underlying cause of the difficulty has to do with the statement backout in an explicit transaction since it functions correctly in an implicit transaction.

SQLite version 3.34.0 2020-10-26 16:56:18
Enter ".help" for usage hints.
sqlite> create view if not exists if as select null;
Error: malformed database schema (if) - near "as": syntax error
sqlite> .schema
sqlite> create table x(x);
sqlite> insert into x values (1),(2);
sqlite> select * from x;
1
2
sqlite> create view if not exists if as select null;
Error: malformed database schema (if) - near "as": syntax error
sqlite> select * from x;
1
2
sqlite> .schema
CREATE TABLE x(x);
sqlite> begin;
sqlite> create view if not exists if as select null;
Error: malformed database schema (if) - near "as": syntax error
sqlite> .schema
Error: malformed database schema (if) - near "as": syntax error
sqlite> select * from x;
Error: malformed database schema (if) - near "as": syntax error
sqlite> rollback;
sqlite> .schema
CREATE TABLE x(x);
sqlite> select * from x;
1
2

(7) By Richard Hipp (drh) on 2020-10-26 17:57:47 in reply to 6 [link] [source]

Right. So I think the solution will be as simple as making sure a statement journal exists for all CREATE statements. Hence it probably will not be a big change. But I have not verified this.

Any way you slice it, this is a corner case.

(8) By LeonB (leonbe) on 2020-11-04 18:50:54 in reply to 5 [link] [source]

There are probably other variations on this same issue.

The issue seems to occur with 'savepoint' too, i.e.:

 savepoint abcdef;
 cReaTE vieW IF nOt eXIsTs  if  aS sElEcT * ;

(9) By Keith Medcalf (kmedcalf) on 2020-11-04 19:07:58 in reply to 8 [link] [source]

Using savepoint means that you are inside a transaction -- either it was explicity begun with a "begin" statement or was implicitly created by the savepoint command, so this should be expected.

The solution is the same and fixes the issue for all transactions no matter how that state was commenced.

(11) By LeonB (leonbe) on 2020-11-05 14:10:14 in reply to 9 [link] [source]

Makes sense, thanks!

(10) By LeonB (leonbe) on 2020-11-05 14:09:55 in reply to 5 [link] [source]

Another variation, without "if not exists if":

SQLite version 3.34.0 2020-10-20 14:40:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> AtTach cuRreNt_daTe AS  'MM'  ;
sqlite> CREatE temp TrIGGeR if nOt eXists  V  DeleTe on  V  when upper  ( * ) begiN seLect * ; enD ;
sqlite> CrEATe vIEw if nOt eXIsTs  v  as sELecT * ;
sqlite> cReaTE ViRtUal tAblE  aaaaaa  uSiNg  'kk'  ;
Error: no such module: kk
sqlite> select aaa from bbbbbbb;
Error: malformed database schema (V) - cannot create BEFORE trigger on view: V
sqlite> CREATE TABLE Persons (PersonID int, LastName varchar(255));
Error: malformed database schema (V) - cannot create BEFORE trigger on view: V

(12) By Richard Hipp (drh) on 2020-11-05 14:21:57 in reply to 10 [link] [source]

Unable to repro. I always get "no such table: V" after the line "CREatE temp TrIGGeR if nOt eXists V DeleTe on V when upper ( * ) begiN seLect * ; enD ;"

(13) By LeonB (leonbe) on 2020-11-05 15:00:16 in reply to 12 [link] [source]

Sorry, it turned out my test directory contained a database named "2020-11-05" (current_date).


Could you try to reproduce this: 

1) Setup test.db

leon@leon-desktop:~/Downloads/sqlite-snapshot-202010201440$ ./a.out test.db
SQLite version 3.34.0 2020-10-20 14:40:53
Enter ".help" for usage hints.
sqlite> CREATE TABLE v ( '''' , H);

2 ) Exit

3 ) Start sqlite


leon@leon-desktop:~/Downloads/sqlite-snapshot-202010201440$ ./a.out 
SQLite version 3.34.0 2020-10-20 14:40:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> attach "test.db" as 'MM';
sqlite> CREatE temp TrIGGeR if nOt eXists  V  DeleTe on  V  when upper  ( * ) begiN seLect * ; enD ;
sqlite> CrEATe vIEw if nOt eXIsTs  v  as sELecT * ;
sqlite> cReaTE ViRtUal tAblE  aaaaaa  uSiNg  'kk'  ;
Error: no such module: kk
sqlite> select aaa from bbbbbbbbbbb;                                                            
Error: malformed database schema (V) - cannot create BEFORE trigger on view: V
sqlite> CREATE TABLE Persons (PersonID int, LastName varchar(255));
Error: malformed database schema (V) - cannot create BEFORE trigger on view: V
sqlite>

(14) By Richard Hipp (drh) on 2020-11-05 19:20:06 in reply to 13 [source]

(3) By Richard Hipp (drh) on 2020-10-26 16:49:28 in reply to 1 [link] [source]

Thank you for the bug report. A fix will be forthcoming.

In the meantime, if you create a corrupt schema this way by accident, and want to recovery the database file, you can do so using the following command sequence:

   PRAGMA writable_schema=ON;
   DELETE FROM sqlite_master WHERE name LIKE 'if';
   PRAGMA writable_schema=OFF;