Sqlite3: Creating a new table from an old table is not populating the sqlite_sequence table correctly

Question

I am attempting to drop a not null constraint on a table according to this answer. However, it does not appear to create an entry in sqlite_sequence after doing so, even though I can get it to work fine while using a test table. Interestingly, if I back up my table, recreate it, INSERT two fake rows in it, and then repeat the above procedure, the sqlite_sequence table is populated correctly. But the procedure does not work correctly when I use the original data set.

For example, this works fine on a test table:

CREATE TABLE foo (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    bar VARCHAR NOT NULL
);
INSERT INTO foo (bar) VALUES ('foo');
INSERT INTO foo (bar) VALUES ('bar');

-- As expected, this shows foo | 2
SELECT * FROM sqlite_sequence WHERE name = 'foo';

BEGIN TRANSACTION;
ALTER TABLE foo RENAME TO temp_foo;
CREATE TABLE foo (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    bar VARCHAR
);
INSERT INTO foo SELECT * from temp_foo;

-- As expected, this shows foo | 2
SELECT * FROM sqlite_sequence WHERE name = 'foo';
COMMIT;

However, when I do the exact same commands on my real table, it fails to add an entry into sqlite_sequence.

sqlite> .schema post;
CREATE TABLE post (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title VARCHAR NOT NULL,
    url_name VARCHAR NOT NULL,
    description VARCHAR NOT NULL,
    category_id INTEGER NOT NULL,
    content VARCHAR,
    is_published BOOLEAN,
    creation_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_modified_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_commenting_disabled BOOLEAN NOT NULL,
    CHECK (title <> ''),
    CHECK (url_name <> ''),
    CHECK (description <> ''),
    CHECK (content <> ''),
    FOREIGN KEY(user_id) REFERENCES user (id) ON DELETE CASCADE,
    UNIQUE (url_name),
    FOREIGN KEY(category_id) REFERENCES category (id) ON DELETE CASCADE,
    CHECK (is_published IN (0, 1)),
    CHECK (is_commenting_disabled IN (0, 1))
);
sqlite>
sqlite> select * from sqlite_sequence where name = 'post';
post|114
sqlite> 
sqlite> BEGIN TRANSACTION;
sqlite>
sqlite> ALTER TABLE post RENAME TO temp_post;
sqlite>
sqlite> CREATE TABLE post (
   ...>         id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   ...>         user_id INTEGER NOT NULL,
   ...>         title VARCHAR NOT NULL,
   ...>         url_name VARCHAR NOT NULL,
   ...>         description VARCHAR,
   ...>         category_id INTEGER NOT NULL,
   ...>         content VARCHAR,
   ...>         is_published BOOLEAN,
   ...>         creation_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
   ...>         last_modified_date DATETIME DEFAULT CURRENT_TIMESTAMP,
   ...>         is_commenting_disabled BOOLEAN NOT NULL,
   ...>         CHECK (title <> ''),
   ...>         CHECK (url_name <> ''),
   ...>         CHECK (description <> ''),
   ...>         CHECK (content <> ''),
   ...>         FOREIGN KEY(user_id) REFERENCES user (id) ON DELETE CASCADE,
   ...>         UNIQUE (url_name),
   ...>         FOREIGN KEY(category_id) REFERENCES category (id) ON DELETE CASCADE,
   ...>         CHECK (is_published IN (0, 1)),
   ...>         CHECK (is_commenting_disabled IN (0, 1))
   ...> );
sqlite>
sqlite> INSERT INTO post SELECT * FROM temp_post;
sqlite>
sqlite> select * from sqlite_sequence WHERE name in ('temp_post', 'post');
temp_post|114
sqlite> COMMIT;
sqlite>
sqlite> select * from sqlite_sequence WHERE name in ('temp_post', 'post');
temp_post|114

I've done this about three times and I cannot get it to work. I ended up doing a

INSERT INTO sqlite_sequence VALUES ('post', 114);

and everything appears to be working fine

But of course, the documentation has this to say:

The content of the sqlite_sequence table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes.

As mentioned before, if I back up the post table, and I recreate it, and then add two fake rows in it, and then repeat the above procedure, the sqlite_sequence table is correct. It would appear that something is wrong with my original data set, but I am not sure how to debug the issue.

@CL. also, any idea why this works when I insert the fake rows as opposed to working for old data? — Matthew Moisen, Oct 02 '16 at 21:04

CL. · Accepted Answer · 2016-11-20T06:53:16.173

For a statement of the form:

INSERT INTO this SELECT * FROM that;

SQLite has a special transfer optimization that can copy entire rows without having to decode and encode the row values.

When the tables have a simple structure (such as foo), this optimization can always be used.
Some features, such as foreign key constraints, are incompatible with this optimization.
And in certain situations, such as when there are UNIQUE constrains, this optimization can work only if the destination table is empty.

In the third case, the code generated for the statement checks if the table is empty. If it is empty, the transfer optimization is done, and the program stops; if it is not empty, it jumps to the second part that does the reads/writes normally. However, the code that does the empty table check forgot to update the sqlite_sequence table before stopping the program.

As a workaround, move this into the second case by enabling foreign key checking.

This bug was introduced in version 3.6.16, and was fixed in version 3.15.0.

Sqlite3: Creating a new table from an old table is not populating the sqlite_sequence table correctly

1 Answers1