Preflight database error: piece_expiration: expected schema does not match actual

Node Operators troubleshooting
,
1

I updated my storagenode to v0.34.3 and my node fails to start throwing the below error.
Here is the log of my system. Can you help me with this?

2020-03-18T21:12:57.373Z        INFO    version running on version v0.34.6
2020-03-18T21:12:57.396Z        INFO    db.migration    Database Version        {"version": 31}
Error: Error during preflight check for storagenode databases: storage node preflight database error: piece_expiration: expected schema does not match actual:   &dbschema.Schema{
        Tables: []*dbschema.Table{
                &{Name: "piece_expirations", Columns: []*dbschema.Column{&{Name: "deletion_failed_at", Type: "TIMESTAMP", IsNullable: true}, &{Name: "piece_expiration", Type: "TIMESTAMP"}, &{Name: "piece_id", Type: "BLOB"}, &{Name: "satellite_id", Type: "BLOB"}, &{Name: "trash", Type: "INTEGER"}}, PrimaryKey: []string{"piece_id", "satellite_id"}},
+               &{
+                       Name: "test_table",
+                       Columns: []*dbschema.Column{
+                               &{Name: "id", Type: "int"},
+                               &{Name: "name", Type: "varchar(30)", IsNullable: true},
+                       },
+                       PrimaryKey: []string{"id"},
+               },
        },
        Indexes: []*dbschema.Index{&{Name: "idx_piece_expirations_deletion_failed_at", Table: "piece_expirations", Columns: []string{"deletion_failed_at"}}, &{Name: "idx_piece_expirations_piece_expiration", Table: "piece_expirations", Columns: []string{"piece_expiration"}}, &{Name: "idx_piece_expirations_trashed", Table: "piece_expirations", Columns: []string{"satellite_id", "trash"}, Partial: "trash = 1"}},
  }

        storj.io/storj/storagenode/storagenodedb.(*DB).Preflight:301
        main.cmdRun:198
        storj.io/storj/pkg/process.cleanup.func1.2:312
        storj.io/storj/pkg/process.cleanup.func1:330
        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
2020-03-18T21:13:03.410Z        INFO    Configuration loaded from:
2020-03-18T21:13:03.425Z        INFO    Operator email: 
2020-03-18T21:13:03.425Z        INFO    operator wallet: 0x26043404d6A2C7905d061796490CBFab26E50011
2

Hello @sanpriv,
Welcome to the forum!

  1. Stop the storagenode
  2. Execute either with a local installed sqlite3 or with a docker container (for ref.: https://support.storj.io/hc/en-us/articles/360029309111-How-to-fix-a-database-disk-image-is-malformed-):
sqlite3 piece_expiration.db
drop table test_table;
.exit
  1. Try to start the storagenode and look into logs
3

I had checked databases.

Here is result

[root@storj02 storage]# sqlite3 /mnt/disk1/storage/bandwidth.db "PRAGMA integrity_check;"
ok

[root@storj02 storage]# sqlite3 /mnt/disk1/storage/info.db "PRAGMA integrity_check;"
ok

[root@storj02 storage]# sqlite3 /mnt/disk1/storage/notifications.db "PRAGMA integrity_check;"
ok

[root@storj02 storage]# sqlite3 /mnt/disk1/storage/orders.db "PRAGMA integrity_check;"
ok

[root@storj02 storage]# sqlite3 /mnt/disk1/storage/piece_expiration.db "PRAGMA integrity_check;"
Error: malformed database schema (idx_piece_expirations_trashed) - near "WHERE": syntax error

[root@storj02 storage]# sqlite3 /mnt/disk1/storage/pieceinfo.db "PRAGMA integrity_check;"
Error: malformed database schema (idx_pieceinfo__expiration) - near "WHERE": syntax error

[root@storj02 storage]# sqlite3 /mnt/disk1/storage/piece_spaced_used.db "PRAGMA integrity_check;"
ok

[root@storj02 storage]# sqlite3 /mnt/disk1/storage/reputation.db "PRAGMA integrity_check;"
ok

[root@storj02 storage]# sqlite3 /mnt/disk1/storage/satellites.db "PRAGMA integrity_check;"
ok

[root@storj02 storage]# sqlite3 /mnt/disk1/storage/storage_usage.db "PRAGMA integrity_check;"
ok

[root@storj02 storage]# sqlite3 /mnt/disk1/storage/used_serial.db "PRAGMA integrity_check;"
ok
4

Please, make sure that you used the sqlite3 with version v3.25.2 or later.
And I gave your link only for reference, how to install sqlite3 locally or use a docker container to perform the fix:

5

I have wrong version sqlite installed local.
I checked bases via docker version sqlite. All bases are ok.

I tried to drop table. But table does not exist.

I tried start storagenode and have error from my first post.

What can I do more?

6

Please, post a new error.

docker logs --tail 30 storagenode
7

Here is it

[root@storj02 ~]# docker logs --tail 30 storagenode
2020-03-18T22:38:43.175Z INFO Operator email: tech@alpriva.ru
2020-03-18T22:38:43.176Z INFO operator wallet: 0x26043404d6A2C7905d061796490CBFab26E50011
2020-03-18T22:38:43.834Z INFO version running on version v0.34.6
2020-03-18T22:38:43.853Z INFO db.migration Database Version {“version”: 31}
Error: Error during preflight check for storagenode databases: storage node preflight database error: piece_expiration: expected schema does not match actual: &dbschema.Schema{
Tables: *dbschema.Table{
&{Name: “piece_expirations”, Columns: *dbschema.Column{&{Name: “deletion_failed_at”, Type: “TIMESTAMP”, IsNullable: true}, &{Name: “piece_expiration”, Type: “TIMESTAMP”}, &{Name: “piece_id”, Type: “BLOB”}, &{Name: “satellite_id”, Type: “BLOB”}, &{Name: “trash”, Type: “INTEGER”}}, PrimaryKey: string{“piece_id”, “satellite_id”}},

  •           &{

  •                   Name: "test_table",

  •                   Columns: []*dbschema.Column{

  •                           &{Name: "id", Type: "int"},

  •                           &{Name: "name", Type: "varchar(30)", IsNullable: true},

  •                   },

  •                   PrimaryKey: []string{"id"},

  •           },
  },
  Indexes: []*dbschema.Index{&{Name: "idx_piece_expirations_deletion_failed_at", Table: "piece_expirations", Columns: []string{"deletion_failed_at"}}, &{Name: "idx_piece_expirations_piece_expiration", Table: "piece_expirations", Columns: []string{"piece_expiration"}}, &{Name: "idx_piece_expirations_trashed", Table: "piece_expirations", Columns: []string{"satellite_id", "trash"}, Partial: "trash = 1"}},

    }

      storj.io/storj/storagenode/storagenodedb.(*DB).Preflight:301
  main.cmdRun:198
  storj.io/storj/pkg/process.cleanup.func1.2:312
  storj.io/storj/pkg/process.cleanup.func1:330
  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

[root@storj02 ~]#

8

the fix still the same. Make sure that you opened an actual database

  1. Stop the storagenode
  2. Execute
sqlite3 piece_expiration.db
drop table test_table;
.exit
  1. Try to start the storagenode and look into logs
9 
[root@storj02 ~]# docker logs --tail 30 storagenode

[root@storj02 ~]# docker ps -a
CONTAINER ID        IMAGE                        COMMAND             CREATED             STATUS                      PORTS               NAMES
5aeb74125a37        storjlabs/storagenode:beta   "/entrypoint"       2 hours ago         Exited (1) 45 seconds ago                       storagenode

[root@storj02 ~]# docker run --rm -it --mount type=bind,source=/mnt/disk1/storage/,destination=/storage sstc/sqlite3 sh
/data # sqlite3 piece_expiration.db

SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.

sqlite> drop table test_table;
Error: no such table: test_table
sqlite> .exit

/data # exit

[root@storj02 ~]# docker start storagenode
storagenode

[root@storj02 ~]# docker ps -a
CONTAINER ID        IMAGE                        COMMAND             CREATED             STATUS                  PORTS                                                      NAMES
5aeb74125a37        storjlabs/storagenode:beta   "/entrypoint"       2 hours ago         Up Less than a second   195.54.210.28:14002->14002/tcp, 0.0.0.0:28967->28967/tcp   storagenode

[root@storj02 ~]# docker ps -a
CONTAINER ID        IMAGE                        COMMAND             CREATED             STATUS              PORTS                                                      NAMES
5aeb74125a37        storjlabs/storagenode:beta   "/entrypoint"       2 hours ago         Up 3 seconds        195.54.210.28:14002->14002/tcp, 0.0.0.0:28967->28967/tcp   storagenode

[root@storj02 ~]# docker logs --tail 30 storagenode
2020-03-18T22:59:08.924Z        INFO    db.migration    Database Version        {"version": 31}
Error: Error during preflight check for storagenode databases: storage node preflight database error: piece_expiration: expected schema does not match actual:   &dbschema.Schema{
        Tables: []*dbschema.Table{
                &{Name: "piece_expirations", Columns: []*dbschema.Column{&{Name: "deletion_failed_at", Type: "TIMESTAMP", IsNullable: true}, &{Name: "piece_expiration", Type: "TIMESTAMP"}, &{Name: "piece_id", Type: "BLOB"}, &{Name: "satellite_id", Type: "BLOB"}, &{Name: "trash", Type: "INTEGER"}}, PrimaryKey: []string{"piece_id", "satellite_id"}},
+               &{
+                       Name: "test_table",
+                       Columns: []*dbschema.Column{
+                               &{Name: "id", Type: "int"},
+                               &{Name: "name", Type: "varchar(30)", IsNullable: true},
+                       },
+                       PrimaryKey: []string{"id"},
+               },
        },
        Indexes: []*dbschema.Index{&{Name: "idx_piece_expirations_deletion_failed_at", Table: "piece_expirations", Columns: []string{"deletion_failed_at"}}, &{Name: "idx_piece_expirations_piece_expiration", Table: "piece_expirations", Columns: []string{"piece_expiration"}}, &{Name: "idx_piece_expirations_trashed", Table: "piece_expirations", Columns: []string{"satellite_id", "trash"}, Partial: "trash = 1"}},
  }

        storj.io/storj/storagenode/storagenodedb.(*DB).Preflight:301
        main.cmdRun:198
        storj.io/storj/pkg/process.cleanup.func1.2:312
        storj.io/storj/pkg/process.cleanup.func1:330
        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
2020-03-18T22:59:16.545Z        INFO    Configuration loaded from: /app/config/config.yaml
2020-03-18T22:59:16.563Z        INFO    Operator email: tech@alpriva.ru
2020-03-18T22:59:16.563Z        INFO    operator wallet: 0x26043404d6A2C7905d061796490CBFab26E50011
10

You should connect to your actual database, not to the copy to drop this excess table. The storagenode should be stopped during process.

11

Thanks!

I had a little mistake.

Command must be “sqlite3 /storage/piece_expiration.db”

It works now!

12

Hi there Storjlings,

After updating Docker to the newest version (Linux Centos 7), one of my nodes is not coming back up.

$ sudo docker logs storagenode
2020-03-25T12:52:30.426Z INFO Configuration loaded from: /app/config/config.yaml
2020-03-25T12:52:30.436Z INFO Operator email: redacted
2020-03-25T12:52:30.436Z INFO operator wallet: redacted
2020-03-25T12:52:30.939Z INFO version running on version v0.34.6
2020-03-25T12:52:30.948Z INFO db.migration Database Version {“version”: 31}
Error: Error during preflight check for storagenode databases: storage node preflight database error: piece_expiration: expected schema does not match actual: &dbschema.Schema{
Tables: *dbschema.Table{
&{Name: “piece_expirations”, Columns: *dbschema.Column{&{Name: “deletion_failed_at”, Type: “TIMESTAMP”, IsNullable: true}, &{Name: “piece_expiration”, Type: “TIMESTAMP”}, &{Name: “piece_id”, Type: “BLOB”}, &{Name: “satellite_id”, Type: “BLOB”}, &{Name: “trash”, Type: “INTEGER”}}, PrimaryKey: string{“piece_id”, “satellite_id”}},

I have tried to drop the test table, but sqlite3 says the following:

$ sqlite3 piece_expiration.db
$ drop table test_table;
Error: malformed database schema (idx_piece_expirations_trashed) - near “WHERE”: syntax error

After trying to follow the instructions here, it tells me that dump_all.sql does not exist.

Can you help me resolve this issue? It there a way to prevent this in the future when updating docker?

XenonOrion

13

Created new thread of this post as the solution proposed here did not work for me, see this thread

14

Hi there Storjlings,

After updating Docker to the newest version (Linux Centos 7), one of my nodes is not coming back up.

$ sudo docker logs storagenode
2020-03-25T12:52:30.426Z INFO Configuration loaded from: /app/config/config.yaml
2020-03-25T12:52:30.436Z INFO Operator email: redacted
2020-03-25T12:52:30.436Z INFO operator wallet: redacted
2020-03-25T12:52:30.939Z INFO version running on version v0.34.6
2020-03-25T12:52:30.948Z INFO db.migration Database Version {“version”: 31}
Error: Error during preflight check for storagenode databases: storage node preflight database error: piece_expiration: expected schema does not match actual: &dbschema.Schema{
Tables: *dbschema.Table{
&{Name: “piece_expirations”, Columns: *dbschema.Column{&{Name: “deletion_failed_at”, Type: “TIMESTAMP”, IsNullable: true}, &{Name: “piece_expiration”, Type: “TIMESTAMP”}, &{Name: “piece_id”, Type: “BLOB”}, &{Name: “satellite_id”, Type: “BLOB”}, &{Name: “trash”, Type: “INTEGER”}}, PrimaryKey: string{“piece_id”, “satellite_id”}},
&{
Name: “test_table”,
Columns: *dbschema.Column{
&{Name: “id”, Type: “int”},
&{Name: “name”, Type: “varchar(30)”, IsNullable: true},
},
PrimaryKey: string{“id”},
},
},
Indexes: *dbschema.Index{&{Name: “idx_piece_expirations_deletion_failed_at”, Table: “piece_expirations”, Columns: string{“deletion_failed_at”}}, &{Name: “idx_piece_expirations_piece_expiration”, Table: “piece_expirations”, Columns: string{“piece_expiration”}}, &{Name: “idx_piece_expirations_trashed”, Table: “piece_expirations”, Columns: string{“satellite_id”, “trash”}, Partial: “trash = 1”}},
}
storj.io/storj/storagenode/storagenodedb.(*DB).Preflight:301
main.cmdRun:198
storj.io/storj/pkg/process.cleanup.func1.2:312
storj.io/storj/pkg/process.cleanup.func1:330
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

I have tried to drop the test table, but sqlite3 says the following:

$ sqlite3 piece_expiration.db

$ drop table test_table;
Error: malformed database schema (idx_piece_expirations_trashed) - near “WHERE”: syntax error

After trying to follow the instructions here proposed here, it tells me that dump_all.sql does not exist.

Can you help me resolve this issue? It there a way to prevent this in the future when updating docker?

XenonOrion

15

Manually creating the dump_all.sql file, and dumping the sql code, then removing the transaction with:

cat /storage/dump_all.sql | grep -v TRANSACTION | grep -v ROLLBACK >/storage/dump_all_notrans.sql

results in the following:

PRAGMA foreign_keys=OFF;
CREATE TABLE piece_expirations (
satellite_id BLOB NOT NULL,
piece_id BLOB NOT NULL,
piece_expiration TIMESTAMP NOT NULL, – date when it can be deleted
deletion_failed_at TIMESTAMP, trash INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (satellite_id, piece_id)
);
INSERT INTO “piece_expirations” VALUES(X’AF2C42003EFC826AB4361F73F9D890942146FE0EBE806786F8E7190800000000’,X’9C3FBB6849DCAFA8BA48FBBB726879F62278AA12A3CDAA810AD19982617E9121’,‘9999-12-31 22:59:59+00:00’,NULL,0);
CREATE TABLE versions (version int, commited_at text);
INSERT INTO “versions” VALUES(26,‘2020-02-14 22:25:08.734462102 +0000 UTC m=+3.635786776’);
CREATE TABLE test_table(id int NOT NULL, name varchar(30), PRIMARY KEY (id));
CREATE INDEX idx_piece_expirations_piece_expiration ON piece_expirations(piece_expiration);
CREATE INDEX idx_piece_expirations_deletion_failed_at ON piece_expirations(deletion_failed_at);
CREATE INDEX idx_piece_expirations_trashed
ON piece_expirations(satellite_id, trash)
WHERE trash = 1;
COMMIT;

when I apply this to the piecestore by running:
sqlite3 piece_expiration.db ".read dump_all_notrans.sql"

Results in:

Error: near line 15: near “WHERE”: syntax error
Error: near line 18: cannot commit - no transaction is active

Any tips?

16

You should not unload and load the database since you have a problem with schema, and this database is not malformed.
Please, use the solution:

You should use either local installed sqlite3 or docker container, described in the mentioned article (the only that part you needed from the whole article).
If you selected the local installed sqlite3, then you must use the version v3.25.2 or later.

17

I have tried that first, but results in the following:

sqlite> drop table test_table;
Error: malformed database schema (idx_piece_expirations_trashed) - near “WHERE”: syntax error

18

Please, make sure that you used the sqlite3 with version v3.25.2 or later.

2 Likes
19

Right, that is probably it. Although, I am struggling with installing a newer version of sqlite3 on Centos. Better to do though docker?

20

That was the solution for me! Thank you Alexey! Is this something I have to anticipate to happen again?

1 Like