search

elemental-lf / benji

Benji Backup: A block based deduplicating backup software for Ceph RBD images, iSCSI targets, image files and block devices
https://benji-backup.me
Other
136 stars 43 forks source link

IntegrityError: (psycopg2.errors.NotNullViolation) when enforcing retention policy #128

Closed pwernicki closed 3 years ago

pwernicki commented 3 years ago

Describe the bug

When trying to delete old backups sql error appears.

To Reproduce

benji enforce latest3,hours48,days7,weeks4,months3;

ERROR: IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint DETAIL: Failing row contains (2021-09-08 06:49:37.903324+02, null, 1, 40, 1).

[SQL: INSERT INTO deleted_blocks (date, storage_id, uid_left, uid_right) VALUES (%(date)s, %(storage_id)s, %(uid_left)s, %(uid_right)s) RETURNING deleted_blocks.id] [parameters: ({'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903324), 'storage_id': 1, 'uid_left': 40, 'uid_right': 1}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903595), 'storage_id': 1, 'uid_left': 40, 'uid_right': 2}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903681), 'storage_id': 1, 'uid_left': 40, 'uid_right': 3}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903765), 'storage_id': 1, 'uid_left': 40, 'uid_right': 4}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903842), 'storage_id': 1, 'uid_left': 40, 'uid_right': 5}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903902), 'storage_id': 1, 'uid_left': 40, 'uid_right': 6}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903975), 'storage_id': 1, 'uid_left': 40, 'uid_right': 7}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 904034), 'storage_id': 1, 'uid_left': 40, 'uid_right': 8} ... displaying 10 of 1931 total bound parameter sets ... {'date':datetime.datetime(2021, 9, 8, 6, 49, 38, 43722), 'storage_id': 1, 'uid_left': 38, 'uid_right': 5070}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 38, 43781), 'storage_id': 1, 'uid_left': 38, 'uid_right': 24622})] (Background on this error at: https://sqlalche.me/e/14/gkpj) Traceback (most recent call last): File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1752, in _execute_context cursor, statement, parameters, context File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 968, in do_executemany **kwargs File "/usr/local/benji/lib/python3.7/site-packages/psycopg2/extras.py", line 1289, in execute_values cur.execute(b''.join(parts)) psycopg2.errors.NotNullViolation: null value in column "id" violates not-null constraint DETAIL: Failing row contains (2021-09-08 06:49:37.903324+02, null, 1, 40, 1).

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/usr/local/benji/lib/python3.7/site-packages/benji/scripts/benji.py", line 357, in main func(func_args) File "/usr/local/benji/lib/python3.7/site-packages/benji/commands.py", line 339, in enforce_retention_policy group_label=group_label) File "/usr/local/benji/lib/python3.7/site-packages/benji/benji.py", line 1147, in enforce_retention_policy self.rm(version.uid, force=True, keep_metadata_backup=keep_metadata_backup) File "/usr/local/benji/lib/python3.7/site-packages/benji/benji.py", line 697, in rm num_blocks = version.remove() File "/usr/local/benji/lib/python3.7/site-packages/benji/database.py", line 388, in remove Session.commit() File "", line 2, in commit File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1428, in commit self._transaction.commit(_to_root=self.future) File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 829, in commit self._prepare_impl() File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl self.session.flush() File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 3298, in flush self._flush(objects) File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 3438, in _flush transaction.rollback(_capture_exception=True) File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 72, in exit with_traceback=exctb, File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 207, in raise raise exception File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 3398, in _flush flush_context.execute() File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute rec.execute(self) File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 633, in execute uow, File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 247, in save_obj insert, File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1154, in _emit_insert_statements statement, multiparams, execution_options=execution_options File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1583, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 324, in _execute_on_connection self, multiparams, params, execution_options File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1462, in _execute_clauseelement cache_hit=cache_hit, File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1815, in _execute_context e, statement, parameters, cursor, context File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1996, in _handle_dbapi_exception sqlalchemy_exception, with_traceback=excinfo[2], from=e File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 207, in raise_ raise exception File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1752, in _execute_context cursor, statement, parameters, context File "/usr/local/benji/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 968, in do_executemany kwargs File "/usr/local/benji/lib/python3.7/site-packages/psycopg2/extras.py", line 1289, in execute_values cur.execute(b''.join(parts)) sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint DETAIL: Failing row contains (2021-09-08 06:49:37.903324+02, null, 1, 40, 1).

[SQL: INSERT INTO deleted_blocks (date, storage_id, uid_left, uid_right) VALUES (%(date)s, %(storage_id)s, %(uid_left)s, %(uid_right)s) RETURNING deleted_blocks.id] [parameters: ({'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903324), 'storage_id': 1, 'uid_left': 40, 'uid_right': 1}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903595), 'storage_id': 1, 'uid_left': 40, 'uid_right': 2}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903681), 'storage_id': 1, 'uid_left': 40, 'uid_right': 3}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903765), 'storage_id': 1, 'uid_left': 40, 'uid_right': 4}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903842), 'storage_id': 1, 'uid_left': 40, 'uid_right': 5}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903902), 'storage_id': 1, 'uid_left': 40, 'uid_right': 6}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 903975), 'storage_id': 1, 'uid_left': 40, 'uid_right': 7}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 37, 904034), 'storage_id': 1, 'uid_left': 40, 'uid_right': 8} ... displaying 10 of 1931 total bound parameter sets ... {'date':datetime.datetime(2021, 9, 8, 6, 49, 38, 43722), 'storage_id': 1, 'uid_left': 38, 'uid_right': 5070}, {'date': datetime.datetime(2021, 9, 8, 6, 49, 38, 43781), 'storage_id': 1, 'uid_left': 38, 'uid_right': 24622})] (Background on this error at: https://sqlalche.me/e/14/gkpj)

Expected behavior

benji remove old backups without any errors

Platform and versions (please complete the following information):

Distributor ID: Debian Description: Debian GNU/Linux 10 (buster) Release: 10 Codename: buster

INFO: Benji version: 0.15.0. INFO: Configuration version: 1.0.0, supported >=1,<2. INFO: Metadata version: 3.0.0, supported >=1,<4. INFO: Object metadata version: 2.0.0, supported >=1,<3.

Python 3.7.3

Package Version

alembic 1.4.2 argcomplete 1.11.1 asn1crypto 0.24.0 benji 0.15.0 blinker 1.4 borgbackup 1.1.9 Cerberus 1.3.2 certifi 2018.8.24 chardet 3.0.4 cloud-init 18.3 colorama 0.4.3 configobj 5.0.6 cryptography 2.6.1 dateparser 0.7.6 diskcache 4.1.0 greenlet 1.1.1 idna 2.6 importlib-metadata 1.7.0 Jinja2 2.10 jsonpatch 1.21 jsonpointer 1.10 jsonschema 2.6.0 llfuse 1.3.6 Mako 1.1.3 MarkupSafe 1.1.0 msgpack 0.5.6 oauthlib 2.1.0 pip 18.1 pkg-resources 0.0.0 prettytable 0.7.2 psycopg2-binary 2.8.5 pyasn1 0.4.2 pycryptodome 3.9.8 PyJWT 1.7.0 pyparsing 2.4.7 python-dateutil 2.8.1 python-editor 1.0.4 pytz 2020.1 PyYAML 3.13 rados 2.0.0 rbd 2.0.0 regex 2020.6.8 requests 2.21.0 ruamel.yaml 0.15.100 semantic-version 2.8.5 setproctitle 1.1.10 setuptools 40.8.0 six 1.12.0 sortedcontainers 2.2.2 sparsebitfield 0.2.3 SQLAlchemy 1.4.22 structlog 20.1.0 tzlocal 2.1 urllib3 1.24.1 zipp 3.1.0

pwernicki commented 3 years ago

I got this error because of moving from sqlite to postgresql.