Error during preflight check for storagenode databases: storage node preflight database error: orders: expected schema does not match actual

I tried moving all the DB files out and everything works fine. I guess that would be a last resort as I don’t want to lose everything.

According to the log, you’re missing an index on the unsent_orders table. Another user reported the same issue, but on a different table. Something might be wrong with this preflight check system. We’ll look into it

I think I have something like this problem too :

Have tried repairing databases and now is the 3rd day the node is offline… don’t know what more I can try before I jump off this beta.

2020-02-03T09:39:33.975Z INFO version running on version v0.31.12
2020-02-03T09:39:34.033Z INFO db.migration Database Version {“version”: 31}
Error: Error during preflight check for storagenode databases: storage node preflight database error: orders: expected schema does not match actual: &dbschema.Schema{
Tables: *dbschema.Table{&{Name: “order_archive_”, Columns: *dbschema.Column{&{Name: “archived_at”, Type: “TIMESTAMP”}, &{Name: “order_limit_serialized”, Type: “BLOB”}, &{Name: “order_serialized”, Type: “BLOB”}, &{Name: “satellite_id”, Type: “BLOB”}, &{Name: “serial_number”, Type: “BLOB”}, &{Name: “status”, Type: “INTEGER”}, &{Name: “uplink_cert_id”, Type: “INTEGER”, Reference: &dbschema.Reference{Table: “certificate”, Column: “cert_id”}}}}, &{Name: “unsent_order”, Columns: *dbschema.Column{&{Name: “order_limit_expiration”, Type: “TIMESTAMP”}, &{Name: “order_limit_serialized”, Type: “BLOB”}, &{Name: “order_serialized”, Type: “BLOB”}, &{Name: “satellite_id”, Type: “BLOB”}, &{Name: “serial_number”, Type: “BLOB”}, &{Name: “uplink_cert_id”, Type: “INTEGER”, Reference: &dbschema.Reference{Table: “certificate”, Column: “cert_id”}}}}},
Indexes: *dbschema.Index{
&{Name: “idx_order_archived_at”, Table: “order_archive_”, Columns: string{“archived_at”}},

  •           &{
    
  •                   Name:    "idx_orders",
    
  •                   Table:   "unsent_order",
    
  •                   Columns: []string{"satellite_id", "serial_number"},
    
  •           },
      },
    

    }

      storj.io/storj/storagenode/storagenodedb.(*DB).Preflight:317
      main.cmdRun:196
      storj.io/storj/pkg/process.cleanup.func1.2:299
      storj.io/storj/pkg/process.cleanup.func1:317
      github.com/spf13/cobra.(*Command).execute:826
      github.com/spf13/cobra.(*Command).ExecuteC:914
      github.com/spf13/cobra.(*Command).Execute:864
      storj.io/storj/pkg/process.ExecWithCustomConfig:79
      storj.io/storj/pkg/process.Exec:61
      main.main:326
      runtime.main:203

Try to rename the orders.db in the storage folder and restart the storagenode

You mean rename so it creates a new one?

when I renamed it , i got this error :

2020-02-04T20:52:29.535Z INFO db.migration.27 Add index archived_at to ordersDB
Error: Error creating tables for master database on storagenode: migrate: no such table: main.order_archive_
storj.io/storj/private/migrate.(*Migration).Run:182
storj.io/storj/storagenode/storagenodedb.(*DB).CreateTables:291
main.cmdRun:186
storj.io/storj/pkg/process.cleanup.func1.2:299
storj.io/storj/pkg/process.cleanup.func1:317
github.com/spf13/cobra.(*Command).execute:826
github.com/spf13/cobra.(*Command).ExecuteC:914
github.com/spf13/cobra.(*Command).Execute:864
storj.io/storj/pkg/process.ExecWithCustomConfig:79
storj.io/storj/pkg/process.Exec:61
main.main:326
runtime.main:203

Yes, exactly. But you will have a backup in any case

Have you restarted the storagenode after rename?

Yes, twice now. There’s something strange with the updated version…

We will try to fix it. Stop the storagenode.
Restore the orders.db from the backup (keep the backup for a while).
Open the orders.db with a sqlite3 (you can install it or use the docker version).

sqlite3 orders.db

Apply this SQL script:

drop index idx_orders;
CREATE INDEX idx_orders ON unsent_order(satellite_id, serial_number);
.exit

Then try to start the storagenode

2 Likes

Finally Its back up :grin:

Thank you Alexey

@Alexey @cameron

Should I wipe my DB and start from scratch or wait for you guys to try debug the preflight?

You shouldn’t start from scratch. If you have a backup of the orders.db, you can restore it and fix the same way as there:

No Luck.

The SQL command returns no such table: main.unsent_order

You do not have a backup?
Ok, apply this script:

CREATE TABLE unsent_order (
                                                satellite_id  BLOB NOT NULL,
                                                serial_number BLOB NOT NULL,

                                                order_limit_serialized BLOB      NOT NULL, -- serialized pb.OrderLi
mit
                                                order_serialized       BLOB      NOT NULL, -- serialized pb.Order
                                                order_limit_expiration TIMESTAMP NOT NULL, -- when is the deadline
for sending it

                                                uplink_cert_id INTEGER NOT NULL,

                                                FOREIGN KEY(uplink_cert_id) REFERENCES certificate(cert_id)
                                        );
CREATE TABLE order_archive_ (
                                                satellite_id  BLOB NOT NULL,
                                                serial_number BLOB NOT NULL,

                                                order_limit_serialized BLOB NOT NULL,
                                                order_serialized       BLOB NOT NULL,

                                                uplink_cert_id INTEGER NOT NULL,

                                                status      INTEGER   NOT NULL,
                                                archived_at TIMESTAMP NOT NULL,

                                                FOREIGN KEY(uplink_cert_id) REFERENCES certificate(cert_id)
                                        );
CREATE UNIQUE INDEX idx_orders ON unsent_order(satellite_id, serial_number);
CREATE TABLE versions (version int, commited_at text);
CREATE INDEX idx_order_archived_at ON order_archive_(archived_at);

You can ignore errors if the object is exist. It will create only missed parts.

Because there were so many things that were tried on the DB, I lost track of all the versions. I found an old version and was able to run the command (further above) successfully and it appears I’m up.

2 Likes

I’m having similar problems with my node. Integrity check on all databases seems to be fine but it still fails.

2020-02-23T14:31:03.221Z        INFO    version running on version v0.33.4
2020-02-23T14:31:03.233Z        INFO    db.migration    Database Version        {"version": 31}
Error: Error during preflight check for storagenode databases: storage node preflight database error: orders: expected schema does not match actual:   &dbschema.Schema{
        Tables: []*dbschema.Table{
                &{Name: "order_archive_", Columns: []*dbschema.Column{&{Name: "archived_at", Type: "TIMESTAMP"}, &{Name: "order_limit_serialized", Type: "BLOB"}, &{Name: "order_serialized", Type: "BLOB"}, &{Name: "satellite_id", Type: "BLOB"}, &{Name: "serial_number", Type: "BLOB"}, &{Name: "status", Type: "INTEGER"}, &{Name: "uplink_cert_id", Type: "INTEGER", Reference: &dbschema.Reference{Table: "certificate", Column: "cert_id"}}}},
+               &{
+                       Name: "test_table",
+                       Columns: []*dbschema.Column{
+                               &{Name: "id", Type: "int"},
+                               &{Name: "name", Type: "varchar(30)", IsNullable: true},
+                       },
+                       PrimaryKey: []string{"id"},
+               },
                &{Name: "unsent_order", Columns: []*dbschema.Column{&{Name: "order_limit_expiration", Type: "TIMESTAMP"}, &{Name: "order_limit_serialized", Type: "BLOB"}, &{Name: "order_serialized", Type: "BLOB"}, &{Name: "satellite_id", Type: "BLOB"}, &{Name: "serial_number", Type: "BLOB"}, &{Name: "uplink_cert_id", Type: "INTEGER", Reference: &dbschema.Reference{Table: "certificate", Column: "cert_id"}}}},
        },
        Indexes: []*dbschema.Index{&{Name: "idx_order_archived_at", Table: "order_archive_", Columns: []string{"archived_at"}}, &{Name: "idx_orders", Table: "unsent_order", Columns: []string{"satellite_id", "serial_number"}}},
  }

        storj.io/storj/storagenode/storagenodedb.(*DB).Preflight:301
        main.cmdRun:198
        storj.io/storj/pkg/process.cleanup.func1.2:307
        storj.io/storj/pkg/process.cleanup.func1:325
        github.com/spf13/cobra.(*Command).execute:826
        github.com/spf13/cobra.(*Command).ExecuteC:914
        github.com/spf13/cobra.(*Command).Execute:864
        storj.io/storj/pkg/process.ExecWithCustomConfig:84
        storj.io/storj/pkg/process.ExecCustomDebug:66
        main.main:328
        runtime.main:203

Stop your storagenode, make a backup of orders.db, then execute (you can use the native sqlite3 binary or use the docker version of sqlite3):

sqlite3 orders.db
drop table test_table;
.exit

Then start the node.

1 Like

This fixed the problem, thank you.

1 Like

A post was merged into an existing topic: Ordersdb error: database disk image is malformed