xme commented 3 years ago

Prerequisites

Please answer the following questions for yourself before submitting an issue.

[x] I am running the latest version
[x] I checked the documentation and found no answer
[x] I checked to make sure that this issue has not already been filed
[x] I'm reporting the issue to the correct repository (for multi-repository projects)
[x] I'm have read all configs with all optional parts

Expected Behavior

Following my previous case, I moved the DB to postgresql... Server installed and a export data from sqlite using pgloader. Now, cuckoo.py fails when stating with a DB constraint error.

Current Behavior

During handling of the above exception, another exception occurred:

  File "cuckoo.py", line 87, in cuckoo_main
    sched.start()
  File "/opt/CAPEv2/lib/cuckoo/core/scheduler.py", line 697, in start
    self.initialize()
  File "/opt/CAPEv2/lib/cuckoo/core/scheduler.py", line 645, in initialize
    machinery.initialize(machinery_name)
  File "/opt/CAPEv2/lib/cuckoo/common/abstracts.py", line 376, in initialize
    super(LibVirtMachinery, self).initialize(module)
  File "/opt/CAPEv2/lib/cuckoo/common/abstracts.py", line 133, in initialize
    self._initialize(module_name)
  File "/opt/CAPEv2/lib/cuckoo/common/abstracts.py", line 203, in _initialize
    resultserver_port=port,
  File "/opt/CAPEv2/lib/cuckoo/common/utils.py", line 847, in inner
    return f(self, *args, **kwargs)
  File "/opt/CAPEv2/lib/cuckoo/core/database.py", line 599, in add_machine
    machine.tags.append(self._get_or_create(session, Tag, name=tag))
  File "/opt/CAPEv2/lib/cuckoo/core/database.py", line 524, in _get_or_create
    instance = session.query(model).filter_by(**kwargs).first()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py", line 3287, in first
    ret = list(self[0:1])
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py", line 3065, in __getitem__
    return list(res)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py", line 3388, in __iter__
    self.session._autoflush()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 1597, in _autoflush
    util.raise_from_cause(e)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 1586, in _autoflush
    self.flush()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 2479, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 2617, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 2577, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
    uow,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    insert,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/persistence.py", line 1137, in _emit_insert_statements
    statement, params
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 982, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1250, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 588, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(psycopg2.errors.NotNullViolation) null value in column "id" of relation "machines" violates not-null constraint
DETAIL:  Failing row contains (null, win7x64, win7x64, 192.168.122.107, windows, virbr0, Snapshot1, f, null, null, null, 192.168.122.1, 2042).

[SQL: INSERT INTO machines (name, label, ip, platform, interface, snapshot, locked, locked_changed_on, status, status_changed_on, resultserver_ip, resultserver_port) VALUES (%(name)s, %(label)s, %(ip)s, %(platform)s, %(interface)s, %(snapshot)s, %(locked)s, %(locked_changed_on)s, %(status)s, %(status_changed_on)s, %(resultserver_ip)s, %(resultserver_port)s) RETURNING machines.id]
[parameters: {'name': 'win7x64', 'label': 'win7x64', 'ip': '192.168.122.107', 'platform': 'windows', 'interface': 'virbr0', 'snapshot': 'Snapshot1', 'locked': False, 'locked_changed_on': None, 'status': None, 'status_changed_on': None, 'resultserver_ip': '192.168.122.1', 'resultserver_port': 2042}]
(Background on this error at: http://sqlalche.me/e/gkpj)

doomedraven commented 3 years ago

Hello, it looks like it didn't port the data correctly

(psycopg2.errors.NotNullViolation) null value in column "id" of relation "machines" violates not-null constraint

xme commented 3 years ago

Ok, I re-created the DB, migrated data from sqlite to psql, migrated to the new schema. My « machines » table is filled with data. I start cuckoo.py, the content of the table is dropped (no more rows) and the same error occur!? The ‘id’ column is also created with a ‘not null’ constraint in sqlite…

doomedraven commented 3 years ago

well i can't really help here as i never did a migration between dbs. what i probably can suggest is that you drop psql db, start cuckoo.py to create everything and then just import tasks table as that is used to show tasks, the rest i don't think is usefull and most of them is auto populated on start

xme commented 3 years ago

That’s what I did, reset all data on my psql, created db. It was populated by CAPE, upgraded to last schema and imported data… Case closed! Happy! :)

kevoreilly commented 3 years ago

Happy days

kevoreilly / CAPEv2

DB constraint failed? #528

Prerequisites

Expected Behavior

Current Behavior