Migration eventually incomplete: last_successful_update part of 0.23.0-alpha3 SQLite dump, but not of newly initialised PostgreSQL nodes table schema

[almereyda]

Bug description

When converting an SQLite dump of the current 0.23 alpha3 to be imported into a cleanly initialised PostgreSQL instance with Headschale schema, the psql client throws an error that there is one value too much in the line.

NOTICE:  relation "nodes" already exists, skipping                                                                                                                            
CREATE TABLE                                                                                                                                                                  
ERROR:  INSERT has more expressions than target columns

Manually examining the situation makes it appear as if the column last_successful_update is not needed anymore.

A migration is eventually missing to remove that column from existing databases.

Environment

• Headscale v0.23.0-alpha3

To Reproduce

1. Dump the SQLite database with a command similar to sqlite3 db.sqlite .dump > db.sql.
2. Replace all back ticks with double quotes ".
3. Add IF NOT EXISTS to all CREATE TABLE statements.
4. Try to pipe the file into an authenticated PSQL client and see the error.
5. Find the columns in the PostgreSQL database to be in a different order than in SQLite, plus missing last_successful_update.

[almereyda]

The one node created after the upgrade from 0.22.3 also has the value NULL in the field.

[kradalby]

Hi @almereyda,

I have created #1754 to remove the column from the table. As for the migration from one database engine to another, this is not something we support or have a goal to support so if you are unable to make the two SQL dialects with each other after this, then that will likely require you to do manual intervention.

[almereyda]

Thanks for the quick reaction and confirming the regression.

Yes, manual intervention is totally expected, esp. when converting SQL dialects.

So to confirm, migrating from one store to the other is totally possible. One only needs to take care of the serialisation format, by converting single tick marks into double quotes, remove the PRAGMA statement, make sure the order of columns is the same and cast some ::bool and ::bytea types for some fields.

[almereyda]

As much as I understand that the SQLite migration path is currently not supported officially, it may as well be valid to consider that it will happen in the field at some point or the other.

To complete the instructions that we find here, it is also to note that we had to restart the sequence counters from their actual value, as this was not part of the SQLite dump.

select nextval('api_keys_id_seq');
select nextval('nodes_id_seq');
select nextval('pre_auth_key_acl_tags_id_seq');
select nextval('pre_auth_keys_id_seq');
select nextval('routes_id_seq');
select nextval('users_id_seq');

This showed 1 everywhere, which led new user and node registrations to fail, as the ID would already have been taken. One would need to set the next value after the current maximum ID in a given table.

select max(id) from api_keys;
select max(id) from nodes;
select max(id) from pre_auth_key_acl_tags;
select max(id) from pre_auth_keys;
select max(id) from routes;
select max(id) from users;

ALTER SEQUENCE api_keys_id_seq RESTART WITH <output from above + 1>
ALTER SEQUENCE nodes_id_seq RESTART WITH <output from above + 1>
ALTER SEQUENCE pre_auth_key_acl_tags_id_seq RESTART WITH <output from above + 1>
ALTER SEQUENCE pre_auth_keys_id_seq RESTART WITH <output from above + 1>
ALTER SEQUENCE routes_id_seq RESTART WITH <output from above + 1>
ALTER SEQUENCE users_id_seq RESTART WITH <output from above + 1>

After restarting the counters, the API returned to nominal operation.

This is left here for the interested search engine user running into similar side-effects when importing an SQLite dump into an empty PostgreSQL database freshly initialised by Headscale.