-
Notifications
You must be signed in to change notification settings - Fork 419
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
sqlalchemy.exc.IntegrityError #2178
Comments
idk what happens here, but i would suggest you to check the machinery tags, if there is bad char or similar thing |
I have the same problem (using Azure with scaleset pool tags), @HUSMUS9999 could you please print the content of the following tables :
For what i understand there is a problem in the submission related code where the tag id get's a +1, this causes the tags ids to rise (as you can see in your prints) with the same value as the tasks causing the obvious relation problem. |
Following up to the previous comment, these are my DB tables related to this issue. I'll give some context here : my
machines :
tags :
machines_tags :
tasks_tags :
tasks :
So tasks_tags and tasks are obviously empty due to the error reported by OP. At this point when submitting a new task cape executes this insert :
where in my test the parameter were set to :
As you can see even with a clean DB the insert tries to use two sets of task_id and tag_id :
Incrementing At this point I don't actually know where this action is performed inside the code neither why so I'll have to keep digging until it makes sense. @doomedraven @cccs-mog @tbeadle @cccs-kevin Since you guys have clearly more experience with the new logics of the machinery module could you please correct my hypothesis in case I got something wrong and follow up with the right logic when adding a task in the relation table tasks_tags ? |
why do you comment out |
i tried to use tags instead of pool_tags in a previous test, wasn't successfull tho (my bad I had to read the code). Anyway it's presence doesn't change anything i made some previous tests without it and i still get that issue. The configurations i tried are the following :
And the final one that i saw in the previous post, none of these configurations prevented the tag_id to be incremented like I pointed out before |
So your problem is with azure right? |
No, the problem is not directly related to azure.
but of course this is not a solution to the problem, I'm trying to understand if during the for loop the tag id gets wrongly incremented. Could someone follow up with the actual expected output of this section ? |
This may or may not help, but here is my VMSS configuration in
Setting |
I'll try it right away |
Well, it didn't work as expected : This is the dump of what return self.add() has when submitting a file :
As you can see it adds x86 following this logic in database.py
this results in the sqlalchemy.exc.IntegrityError error that OP posted, this is the error log from the webserver :
|
the problem is not related to machinery i guess even when trying to perform static analysis the same problem happen its not also related to cloud hosting im using local hosting |
When if that happens in local you need to provide versions of db, sqlachemy etc as I have no issues |
Could you elaborate on how this could be an issue relate to the db and sqlalchemy version, from my previous reply if you read the error log it seems like a logical issue inside the code.
Since OP has the same issue I think that it's higly unlikely that it could be related to the psql or sqlalchemy version. |
At the moment i removed the comments from the previous reply and commented these lines instead :
Now the tasks_tags relation works correctly. Any idea on how to fix that part of code without removing the section like I did ? |
@leoiancu21 Thanks you for helping it worked for me |
My pleasure, does your analysis work completely now ? I'm still stuck in the reporting procedure where the analysis gets stuck but i don't know if this is related to this issue |
@HUSMUS9999 not exactly but it could be I have to debug a bit in order to understand it , I don't think that this could be related tho. |
alright thanks @leoiancu21 do i have to close this issue ? |
Unfortunately it's not fixed, we just used a workaround so i would leave it open so if someone finds a way to solve the problem can reply with a commit and close it |
bcz i don't have issue with tags, and i don't have to modify nothing at all. and my amount of cape servers is huge |
i have the same software version, just checked it @tbeadle i just saw in log, maybe you can help here a bit more(i have fly so my mind is pretty off)
|
@HUSMUS9999 Could you please put the following in custom/conf/cuckoo.conf:
restart cape-web and try the submission again. I'm interested in SQL statements issued. They should be in /var/log/django/access.log. The So far, I have not been able to reproduce the problem. |
I have the same problem. I changed the following settings and then checked the cape-web logs.
As you can see in the log below, the tag was deleted immediately after the new tag was inserted.
I also commented out the following process in CAPEv2/lib/cuckoo/core/database.py and it now works correctly. # There should be a better way to clean up orphans. This runs after every flush, which is crazy.
# @event.listens_for(self.session, "after_flush")
# def delete_tag_orphans(session, ctx):
# session.query(Tag).filter(~Tag.tasks.any()).filter(~Tag.machines.any()).delete(synchronize_session=False) I suspect there is a problem with the above process for removing unused tags. |
This appears to be due to a change in behavior between the version of SQLAlchemy that is required via pyproject.toml/poetry.lock or requirements.txt (1.4.50) and the version that you're running (2.0+). I was able to reproduce the error by running |
Thank you. This problem was solved by downgrading from SQLAlchemy 2.0.31 to 1.4.50. It was caused by running the following command according to the documentation. sudo -u cape poetry run pip install -r extra/optional_dependencies.txt The above command installed flask-sqlalchemy and upgraded SQLAlchemy 1.4.50 to 2.0.31. |
ok commented that out, thanks Tommy for help |
After installation success when i try to submit a binary for analysis this happen
IntegrityError
sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "tasks_tags" violates foreign key constraint "tasks_tags_tag_id_fkey"
DETAIL: Key (tag_id)=(14) is not present in table "tags".
[SQL: INSERT INTO tasks_tags (task_id, tag_id) VALUES (%(task_id)s, %(tag_id)s)]
[parameters: {'task_id': 14, 'tag_id': 14}]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last)
The debugger caught an exception in your WSGI application. You can now look at the traceback which led to the error.
To switch between the interactive traceback and the plaintext one, you can click on the "Traceback" headline. From the text traceback you can also create a paste of it. For code execution mouse-over the frame you want to debug and click on the console icon on the right side.
You can execute arbitrary Python code in the stack frames and there are some extra helpers available for introspection:
The text was updated successfully, but these errors were encountered: