knex: truncate() on SQLite does not reset the id value in the sqlite_sequence any longer

Upon upgrading to 0.14.0, I’ve realised that my primary keys don’t get reset anymore when I use truncate() with the SQLite dialect.

It looks like the code that’s supposed to do the reseting in truncate() is actually producing an error:

https://github.com/tgriesser/knex/blob/bf8aa4db100ed34756d1d77268e3b391d5d2b528/src/dialects/sqlite3/query/compiler.js#L89-L99

If you replace noop there with (err) => { console.log(err) }, you will see the errors logged:

{ Error: SQLITE_ERROR: no such column: dummy errno: 1, code: 'SQLITE_ERROR’ }

It looks like the table-name should be quoted in that SQL statement. If I manually add quotes, then the ids get reset again:

sql: `delete from sqlite_sequence where name = "${table}"`

I’m not able to say if that’s the correct fix for this issue.

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 25 (25 by maintainers)

Commits related to this issue

Fix truncate() on sqlite3 dialect Relates to #2312: ids were not correctly reset anymore — committed to lehni/knex by lehni 7 years ago
Fix truncate() on sqlite3 dialect Relates to #2312: ids were not correctly reset anymore — committed to lehni/knex by lehni 7 years ago
Fix truncate() on sqlite3 dialect Relates to #2312: ids were not correctly reset anymore — committed to lehni/knex by lehni 7 years ago
Fix truncate() on sqlite3 dialect Relates to #2312: ids were not correctly reset anymore — committed to lehni/knex by lehni 7 years ago
Fix truncate() on sqlite3 dialect Relates to #2312: ids were not correctly reset anymore — committed to lehni/knex by lehni 7 years ago
Fix truncate() on sqlite3 dialect Relates to #2312: ids were not correctly reset anymore — committed to lehni/knex by lehni 7 years ago
Fix truncate() on sqlite3 dialect (#2348) Relates to #2312: ids were not correctly reset anymore — committed to knex/knex by lehni 7 years ago

Most upvoted comments

I’d like to suggest two measures here:

Roll out a quick fix for ~0.14.1~ 0.14.2 that restores 0.13.0 behavior, by fixing the delete statement, without fixing the issue of the 2nd SQL statement not being waited on. This wasn’t an issue for anyone so far, so it seems to be fine, perhaps simply a question of timing working out in our favor.
Find a fix for this more complex problem with a bit more time, and a proper plan

lehni on Nov 20, 2017

Hmm I’m not sure I understand you correctly. Back-ticks aren’t the right quotes for this statement. The table names are strings in the <tt>`sqlite_sequence`</tt> table’s <tt>`name`</tt> column, so running this doesn’t work:

delete from sqlite_sequence where name = `table_name`

This works as expected:

delete from sqlite_sequence where name = "table_name"

Using back-ticks makes SQLite think that <tt>`table_name`</tt> is a column name to match against within the same <tt>`sqlite_sequence`</tt> table, while we really want to match against the actual name of the table, as it’s stored in the <tt>`sqlite_sequence`</tt> table’s <tt>`name`</tt> column, e.g.:

select * from sqlite_sequence:

name	seq
knex_migrations	15
table_name	5

And regardless of this, there is no guarantee really that this query is executed before we use the table again in another query, because nothing is waiting for it t complete, therefore the reset might currently occur too late. Even though that’s rather unlikely, this issue should also be addressed with a fix that waits for the 2nd sql statement to complete too.

lehni on Nov 9, 2017