search

SURFscz / SBS

Samenwerking Beheer Systeem ↣ Collaboration Management System
Apache License 2.0
3 stars 2 forks source link

`/api/services` returns 500 if a Service has a `crm_id` that corresponds to multiple Orgs #1540

Closed baszoetekouw closed 1 month ago

baszoetekouw commented 1 month ago

Servies tab can't be loaded. Network console shows that https://sram.surf.nl/api/services/all?include_counts=true returns a 500.

Aug 15 13:59:00 sbs-pf1 gunicorn[9328]: Traceback (most recent call last):
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs/server/api/base.py", line 189, in wrapper
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     body, status = f(*args, **kwargs)
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs/server/api/service.py", line 326, in all_services
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     return _do_get_services(include_counts=include_counts)
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs/server/api/service.py", line 124, in _do_get_services
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     services = query.all()
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2673, in all
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     return self._iter().all()  # type: ignore
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2827, in _iter
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2362, in execute
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     return self._execute_internal(
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2247, in _execute_internal
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     result: Result[Any] = compile_state_cls.orm_execute_statement(
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     result = conn.execute(
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     return meth(
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     return connection._execute_clauseelement(
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     ret = self._execute_context(
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     return self._exec_single_context(
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     self._handle_dbapi_exception(
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     self.dialect.do_execute(
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     cursor.execute(statement, parameters)
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/MySQLdb/cursors.py", line 179, in execute
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     res = self._query(mogrified_query)
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/MySQLdb/cursors.py", line 331, in _query
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     self._do_get_result(db)
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/MySQLdb/cursors.py", line 136, in _do_get_result
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     self._result = result = self._get_result()
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:   File "/opt/sbs/sbs-env/lib/python3.9/site-packages/MySQLdb/cursors.py", line 363, in _get_result
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]:     return self._get_db().store_result()
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1242, 'Subquery returns more than 1 row')
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]: [SQL: SELECT (SELECT organisations.name
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]: FROM organisations
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]: WHERE services.crm_id IS NOT NULL AND organisations.crm_id = services.crm_id) AS anon_1, services.id AS services_id, services.entity_id AS services_entity_id, services.name AS services_name, services.description AS services_description, services.logo AS services_logo, services.uuid4 AS services_uuid4, services.address AS services_address, services.identity_type AS services_identity_type, services.abbreviation AS services_abbreviation, services.uri AS services_uri, services.uri_info AS services_uri_info, services.privacy_policy AS services_privacy_policy, services.accepted_user_policy AS services_accepted_user_policy, services.ldap_password AS services_ldap_password, services.ldap_identifier AS services_ldap_identifier, services.contact_email AS services_contact_email, services.support_email AS services_support_email, services.security_email AS services_security_email, services.override_access_allowed_all_connections AS services_override_access_allowed_all_connections, services.automatic_connection_allowed AS services_automatic_connection_allowed, services.access_allowed_for_all AS services_access_allowed_for_all, services.allow_restricted_orgs AS services_allow_restricted_orgs, services.non_member_users_access_allowed AS services_non_member_users_access_allowed, services.connection_setting AS services_connection_setting, services.token_enabled AS services_token_enabled, services.token_validity_days AS services_token_validity_days, services.pam_web_sso_enabled AS services_pam_web_sso_enabled, services.ldap_enabled AS services_ldap_enabled, services.scim_enabled AS services_scim_enabled, services.scim_client_enabled AS services_scim_client_enabled, services.scim_url AS services_scim_url, services.scim_bearer_token AS services_scim_bearer_token, services.sweep_scim_enabled AS services_sweep_scim_enabled, services.sweep_remove_orphans AS services_sweep_remove_orphans, services.sweep_scim_daily_rate AS services_sweep_scim_daily_rate, services.sweep_scim_last_run AS services_sweep_scim_last_run, services.redirect_urls AS services_redirect_urls, services.saml_metadata AS services_saml_metadata, services.saml_metadata_url AS services_saml_metadata_url, services.oidc_client_secret AS services_oidc_client_secret, services.providing_organisation AS services_providing_organisation, services.grants AS services_grants, services.is_public_client AS services_is_public_client, services.saml_enabled AS services_saml_enabled, services.oidc_enabled AS services_oidc_enabled, services.exported_at AS services_exported_at, services.export_successful AS services_export_successful, services.export_external_identifier AS services_export_external_identifier, services.export_external_version AS services_export_external_version, services.crm_id AS services_crm_id, services.created_by AS services_created_by, services.updated_by AS services_updated_by, services.created_at AS services_created_at
Aug 15 13:59:00 sbs-pf1 gunicorn[9328]: FROM services]
baszoetekouw commented 1 month ago

Problem is the assumption that an Organisation can be uniquely identified by a crm_id. This is not the case: multiple Orgs can have the same crm_id.

So instead of adding a crm_id to Services and matching Services to Orgs based on identical crm_id, we shoudl do it the other way around: a Service should be connected to an Org, and the crm_id of the Service is the crm_id of its corresponding Org.

FlorisFokkinga commented 1 month ago

Yes, that's the right solution. So replace the CRM ID field at the service with a drop down were a platform admin can select an organisation.

mrvanes commented 1 month ago

Checked