Exceptions on any changes to user access to collection

[maxpatiiuk]

Gothenburg museum reported a bug:
"When unchecking the last collection for a user and saving, there is an error:"

specify7_1         | The above exception was the direct cause of the following exception:
specify7_1         |
specify7_1         | Traceback (most recent call last):
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/core/handlers/exception.py", line 34, in inner
specify7_1         |     response = get_response(request)
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/core/handlers/base.py", line 115, in _get_response
specify7_1         |     response = self.process_exception_by_middleware(e, request)
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/core/handlers/base.py", line 113, in _get_response
specify7_1         |     response = wrapped_callback(request, *callback_args, **callback_kwargs)
specify7_1         |   File "/opt/specify7/specifyweb/specify/views.py", line 20, in wrapped
specify7_1         |     return view(request, *args, **kwargs)
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/views/decorators/http.py", line 40, in inner
specify7_1         |     return func(request, *args, **kwargs)
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/views/decorators/cache.py", line 44, in _wrapped_view_func
specify7_1         |     response = view_func(request, *args, **kwargs)
specify7_1         |   File "/opt/specify7/specifyweb/context/views.py", line 103, in user_collection_access
specify7_1         |     set_users_collections(cursor, user, collections)
specify7_1         |   File "/opt/specify7/specifyweb/context/views.py", line 59, in set_users_collections
specify7_1         |     [[user.id](http://user.id/), collectionids])
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/db/backends/utils.py", line 67, in execute
specify7_1         |     return self._execute_with_wrappers(sql, params, many=False, executor=self._execute
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
specify7_1         |     return executor(sql, params, many, context)
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
specify7_1         |     return self.cursor.execute(sql, params)
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
specify7_1         |     raise dj_exc_value.with_traceback(traceback) from exc_value
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
specify7_1         |     return self.cursor.execute(sql, params)
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute
specify7_1         |     return self.cursor.execute(query, args)
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
specify7_1         |     self.errorhandler(self, exc, value)
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
specify7_1         |     raise errorvalue
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
specify7_1         |     res = self._query(query)
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/MySQLdb/cursors.py", line 412, in _query
specify7_1         |     rowcount = self._do_query(q)
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/MySQLdb/cursors.py", line 375, in _do_query
specify7_1         |     db.query(q)
specify7_1         |   File "/opt/specify7/ve/lib/python3.6/site-packages/MySQLdb/connections.py", line 276, in query
specify7_1         |     _mysql.connection.query(self, query)
specify7_1         | django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1")

Tried to replicate their issue. I am receiving the following exception when making any Collections access changes:

IntegrityError at /context/user_collection_access/19/
(1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`specify`.`spprincipal_sppermission`, CONSTRAINT `FK9DD8B2FA99A7381A` FOREIGN KEY (`SpPrincipalID`) REFERENCES `spprincipal` (`SpPrincipalID`))')

[benanhalt]

I think this is related to #1161 which is fixed in the production branch. Can someone try and replicate this issue on the production branch and see if they get the reported SQL syntax exception instead of the FK constraint exception.

[grantfitzsimmons]

I can successfully save and modify user permissions on the production branch. I did not get the SQL syntax exception.

[maxpatiiuk]

@benanhalt The original error (sql syntax error) has just happened for me in the freshfish database.
Complete exception page is attached
error.html.zip

[maxpatiiuk]

From @grantfitzsimmons:

Error occurred fetching from https://herbrbge6802-testability.test.specifysystems.org/context/user_collection_access_for_sp6/19/

Invalid response code 500. Expected 200.

ProgrammingError at /context/user_collection_access_for_sp6/19/
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1")

https://herbrbge6802-testability.test.specifysystems.org/specify/security/user/19/

Navigate to the user hatkins and go to Set Collections under the Specify 6 Permissions heading.

Uncheck "Herbarium" and save. See SQL syntax error.

Can recreate in edge and testability

[grantfitzsimmons]

Caroline encountered this whenever trying to unset access to all collections

[CarolineDenis]

close if #3755 fixes it.

[CarolineDenis]

@bronwyncombs @carlosmbe could you test if this is fixed in #3755 please?

[bronwyncombs]

@bronwyncombs @carlosmbe could you test if this is fixed in #3755 please?

Yes, it is working on that branch!

Screen.Recording.2023-07-19.at.9.21.31.AM.mov

[CarolineDenis]

Fixed in #3755