Hi Ben,

On 10/17/2016 10:40 PM, Ben Newberg wrote:
> First off, my apologies for hijacking this thread.

:-( I just wanted to report a bug...

> But I've seen some strange things when messing with pragma writable_schema.
> It appears all bets are off?
> example: create a table of columns (x, y, z), and fill it with values.
> then, modify sqlite_master to take out column z.
> let's say later on down the line, you either add a column using the
> pragma writable_schema, or the ALTER TABLE.
> What happens is the new column is filled with the old column's values:

Which should be quite obvious: Changing sqlite_master just changes what
SQLite thinks is in those tables or rather B-Trees. This is a sharp
knife and you must know not only what you are doing but also what SQLite
is doing internally with that information.

For example I tried to get rid of a surrogate key on a table like

create table items (id integer primary key, parent, child, order);

by dropping the id column from sqlite master, assuming that it was not
in the internal representation anyway as the id will be the row number.

This was a false assumption. Actually the row number is not written but
a NULL value is prepended to every row:


So to make this modification work I would have to write code to move the
data one column to the left. SQLite does not directly support this.
And, BTW: my modification caused segfaults when accessing the DB via
SQLite. So I ended up renaming the table and recreating it from the old
data, which takes up to 2 hours for each client instance which is quite

> -- we are still seeing column z, until we vaccum:
> sqlite> vacuum;
> Error: table vacuum_db.t has 2 columns but 3 values were supplied

Most likely this is only because sqlite_master was not reread. The 3rd
column is still there despite the vacuum, it just ended up rereading
the schema. Probably because vacuum internally copies the whole database
and probably is akin to reopening the DB.

Greetings, Torsten


Niederlassung Dresden
Torsten Landschoff
Pohlandstraße 19
01309 Dresden

Tel: +49-351-312002-10
Fax: +49-351-312002-29

Registergericht und Sitz: Ingolstadt, HRB 6384
Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz
sqlite-users mailing list

Reply via email to