search

specify / specify7

Specify 7
https://www.specifysoftware.org/products/specify-7/
GNU General Public License v2.0
63 stars 36 forks source link

Querying Agent Created By causes a crash #2305

Closed grantfitzsimmons closed 11 months ago

grantfitzsimmons commented 1 year ago
image

Run the query:

AttributeError at /stored_query/ephemeral/
'Relationship' object has no attribute 'otherSideName'
image

Specify 7 Crash Report - 2022-10-07T15_07_16.669Z.txt

https://fwri-edge.test.specifysystems.org/specify/query/105/

Reported By: Eric at FWRI

grantfitzsimmons commented 1 year ago

Important:

This works properly in v7.6.1

image

https://fwri-v761.test.specifysystems.org/specify/query/105/

realVinayak commented 1 year ago

There was a bug in SQLAlchemy with cyclical queries. The previous versions v7.6.1 were doing the query wrong (no runtime errors but the columns are swapped in any cyclical query). Ben fixed the problem with cyclical queries but the side effect is that createdBy and modifiedBy relationships in the agent no longer work because those relationships are unidirectional. Schema change can be done to make the relationships bidirectional.

grantfitzsimmons commented 1 year ago

https://fwri2023-favicon-improvements.test.specifysystems.org/specify/query/105/

user: epost

AttributeError at /stored_query/ephemeral/
'Relationship' object has no attribute 'otherSideName'
AttributeError at /stored_query/ephemeral/
'Relationship' object has no attribute 'otherSideName'

Specify 7 Crash Report - 2023-01-05T01_27_42.148Z.txt

grantfitzsimmons commented 1 year ago

@maxpatiiuk Eric at FWRI has reported this same issue again to @mc0822. What is involved in fixing this?

maxpatiiuk commented 1 year ago

@realVinayak do you remember how far you have gone in fixing this?

grantfitzsimmons commented 1 year ago

Similar, if not identical, issue with AttributeError at /stored_query/ephemeral/

image 
AttributeError at /stored_query/ephemeral/

type object 'CollectionObject' has no attribute 'projects'

db, user: kim

Specify 7 Crash Report - 2023-01-26T17 23 53.475Z.txt

grantfitzsimmons commented 1 year ago

@realVinayak I am still able to recreate this. Paul has reported this issue again.

https://fwri-edge.test.specifysystems.org/specify/query/105/

image 
AttributeError at /stored_query/ephemeral/ 'Relationship' object has no attribute 'otherSideName' Request Method: POST Request URL: http://fwri-edge.test.specifysystems.org/stored_query/ephemeral/ Django Version: 3.2.15 Python Executable: /opt/specify7/ve/bin/python3.8 Python Version: 3.8.0 Python Path: ['/opt/specify7', '/opt/specify7', '/opt/specify7/ve/bin', '/usr/lib/python38.zip', '/usr/lib/python3.8', '/usr/lib/python3.8/lib-dynload', '/opt/specify7/ve/lib/python3.8/site-packages', '/opt/specify7'] Server time: Mon, 03 Apr 2023 17:28:21 -0500 Installed Applications: ('django.contrib.sessions', 'django.contrib.staticfiles', 'django.contrib.contenttypes', 'django.contrib.auth', 'specifyweb.specify', 'specifyweb.permissions', 'specifyweb.accounts', 'specifyweb.stored_queries', 'specifyweb.businessrules', 'specifyweb.express_search', 'specifyweb.context', 'specifyweb.attachment_gw', 'specifyweb.frontend', 'specifyweb.barvis', 'specifyweb.report_runner', 'specifyweb.interactions', 'specifyweb.workbench', 'specifyweb.notifications', 'specifyweb.export', 'specifyweb.raven_placeholder') Installed Middleware: ['django.middleware.gzip.GZipMiddleware', 'django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.locale.LocaleMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'specifyweb.context.middleware.ContextMiddleware', 'specifyweb.permissions.middleware.PermissionsMiddleware', 'specifyweb.middleware.general.GeneralMiddleware'] Traceback (most recent call last): File "/opt/specify7/ve/lib/python3.8/site-packages/django/core/handlers/exception.py", line 47, in inner response = get_response(request) File "/opt/specify7/ve/lib/python3.8/site-packages/django/core/handlers/base.py", line 181, in _get_response response = wrapped_callback(request, *callback_args, **callback_kwargs) File "/opt/specify7/ve/lib/python3.8/site-packages/django/views/decorators/http.py", line 40, in inner return func(request, *args, **kwargs) File "/opt/specify7/specifyweb/specify/views.py", line 29, in wrapped return view(request, *args, **kwargs) File "/opt/specify7/ve/lib/python3.8/site-packages/django/views/decorators/cache.py", line 44, in _wrapped_view_func response = view_func(request, *args, **kwargs) File "/opt/specify7/specifyweb/stored_queries/views.py", line 81, in ephemeral data = run_ephemeral_query(collection, request.specify_user, spquery) File "/opt/specify7/specifyweb/stored_queries/execution.py", line 378, in run_ephemeral_query return execute(session, collection, user, tableid, distinct, count_only, File "/opt/specify7/specifyweb/stored_queries/execution.py", line 513, in execute query, order_by_exprs = build_query(session, collection, user, tableid, field_specs, recordsetid=recordsetid, formatauditobjs=formatauditobjs, distinct=distinct) File "/opt/specify7/specifyweb/stored_queries/execution.py", line 589, in build_query query, field, predicate = fs.add_to_query(query, formatauditobjs=formatauditobjs) File "/opt/specify7/specifyweb/stored_queries/queryfield.py", line 49, in add_to_query return self.fieldspec.add_to_query(query, value=self.value, op_num=None if no_filter else self.op_num, negate=self.negate, formatter=self.format_name, formatauditobjs=formatauditobjs) File "/opt/specify7/specifyweb/stored_queries/queryfieldspec.py", line 212, in add_to_query query, orm_model, table, field = self.build_join(query, self.join_path) File "/opt/specify7/specifyweb/stored_queries/queryfieldspec.py", line 184, in build_join return query.build_join(self.root_table, model, join_path) File "/opt/specify7/specifyweb/stored_queries/query_construct.py", line 97, in build_join field = table.get_field(field.otherSideName) Exception Type: AttributeError at /stored_query/ephemeral/ Exception Value: 'Relationship' object has no attribute 'otherSideName' Request information: USER: Specifyuser object (1) GET: No GET data POST: No POST data FILES: No FILES data COOKIES: csrftoken = 'eLhqRqBmQAxdhMNfJuz3UWT1tMMFmmb5DNmjE5oOcgV2lMWqaBva34zPzl5Qxe2K' sessionid = '39wft9ktzpnkushd23gvgfe8xmeqmchw' collection = '32769' META: CONTENT_LENGTH = '1572' CONTENT_TYPE = 'application/json' CSRF_COOKIE = 'eLhqRqBmQAxdhMNfJuz3UWT1tMMFmmb5DNmjE5oOcgV2lMWqaBva34zPzl5Qxe2K' HTTP_ACCEPT = 'application/json' HTTP_ACCEPT_ENCODING = 'gzip, deflate, br' HTTP_ACCEPT_LANGUAGE = 'en-US,en;q=0.5' HTTP_CONNECTION = 'close' HTTP_COOKIE = 'csrftoken=eLhqRqBmQAxdhMNfJuz3UWT1tMMFmmb5DNmjE5oOcgV2lMWqaBva34zPzl5Qxe2K; sessionid=39wft9ktzpnkushd23gvgfe8xmeqmchw; collection=32769' HTTP_DNT = '1' HTTP_HOST = 'fwri-edge.test.specifysystems.org' HTTP_ORIGIN = 'https://fwri-edge.test.specifysystems.org' HTTP_REFERER = 'https://fwri-edge.test.specifysystems.org/specify/query/105/' HTTP_SEC_FETCH_DEST = 'empty' HTTP_SEC_FETCH_MODE = 'cors' HTTP_SEC_FETCH_SITE = 'same-origin' HTTP_SEC_GPC = '1' HTTP_USER_AGENT = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:109.0) Gecko/20100101 Firefox/112.0' HTTP_X_CSRFTOKEN = '********************' HTTP_X_FORWARDED_FOR = '129.237.90.91' HTTP_X_REAL_IP = '129.237.90.91' PATH_INFO = '/stored_query/ephemeral/' QUERY_STRING = '' RAW_URI = '/stored_query/ephemeral/' REMOTE_ADDR = '172.28.0.22' REMOTE_PORT = '57980' REQUEST_METHOD = 'POST' SCRIPT_NAME = '' SERVER_NAME = '0.0.0.0' SERVER_PORT = '8000' SERVER_PROTOCOL = 'HTTP/1.0' SERVER_SOFTWARE = 'gunicorn/20.1.0' gunicorn.socket = wsgi.errors = wsgi.file_wrapper = wsgi.input = wsgi.input_terminated = True wsgi.multiprocess = True wsgi.multithread = False wsgi.run_once = False wsgi.url_scheme = 'http' wsgi.version = '(1, 0)' Settings: Using settings module settings ABSOLUTE_URL_OVERRIDES = {} ADMINS = '()' ADMIN_MEDIA_PREFIX = '/static/admin/' ALLOWED_HOSTS = ['*'] ALLOW_SPECIFY6_PASSWORDS = '********************' ALLOW_SUPPORT_LOGIN = False ANONYMOUS_USER = None APPEND_SLASH = True AUTHENTICATION_BACKENDS = ['django.contrib.auth.backends.ModelBackend'] AUTH_LDAP_SERVER_URI = None AUTH_PASSWORD_VALIDATORS = '********************' AUTH_USER_MODEL = 'specify.Specifyuser' CACHES = {'default': {'BACKEND': 'django.core.cache.backends.locmem.LocMemCache'}} CACHE_MIDDLEWARE_ALIAS = 'default' CACHE_MIDDLEWARE_KEY_PREFIX = '********************' CACHE_MIDDLEWARE_SECONDS = 600 CELERY_BROKER_URL = 'redis://redis/0' CELERY_RESULT_BACKEND = 'redis://redis/1' CELERY_TASK_DEFAULT_QUEUE = 'fwri-edge' CSRF_COOKIE_AGE = 31449600 CSRF_COOKIE_DOMAIN = None CSRF_COOKIE_HTTPONLY = False CSRF_COOKIE_NAME = 'csrftoken' CSRF_COOKIE_PATH = '/' CSRF_COOKIE_SAMESITE = 'Lax' CSRF_COOKIE_SECURE = False CSRF_FAILURE_VIEW = 'django.views.csrf.csrf_failure' CSRF_HEADER_NAME = 'HTTP_X_CSRFTOKEN' CSRF_TRUSTED_ORIGINS = [] CSRF_USE_SESSIONS = False DATABASES = {'default': {'ENGINE': 'specifyweb.hibernateboolsbackend.backends.mysql', 'NAME': 'fwri', 'USER': 'root', 'PASSWORD': '********************', 'HOST': 'mariadb', 'PORT': '', 'OPTIONS': {}, 'TEST': {'CHARSET': None, 'COLLATION': None, 'MIGRATE': True, 'MIRROR': None, 'NAME': None}, 'ATOMIC_REQUESTS': False, 'AUTOCOMMIT': True, 'CONN_MAX_AGE': 0, 'TIME_ZONE': None}} DATABASE_HOST = 'mariadb' DATABASE_NAME = 'fwri' DATABASE_OPTIONS = {} DATABASE_PORT = '' DATABASE_ROUTERS = [] DATA_UPLOAD_MAX_MEMORY_SIZE = 419430400 DATA_UPLOAD_MAX_NUMBER_FIELDS = 1000 DATETIME_FORMAT = 'N j, Y, P' DATETIME_INPUT_FORMATS = ['%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M:%S.%f', '%Y-%m-%d %H:%M', '%m/%d/%Y %H:%M:%S', '%m/%d/%Y %H:%M:%S.%f', '%m/%d/%Y %H:%M', '%m/%d/%y %H:%M:%S', '%m/%d/%y %H:%M:%S.%f', '%m/%d/%y %H:%M'] DATE_FORMAT = 'N j, Y' DATE_INPUT_FORMATS = ['%Y-%m-%d', '%m/%d/%Y', '%m/%d/%y', '%b %d %Y', '%b %d, %Y', '%d %b %Y', '%d %b, %Y', '%B %d %Y', '%B %d, %Y', '%d %B %Y', '%d %B, %Y'] DEBUG = True DEBUG_PROPAGATE_EXCEPTIONS = False DECIMAL_SEPARATOR = '.' DEFAULT_AUTO_FIELD = 'django.db.models.AutoField' DEFAULT_CHARSET = 'utf-8' DEFAULT_EXCEPTION_REPORTER = 'django.views.debug.ExceptionReporter' DEFAULT_EXCEPTION_REPORTER_FILTER = 'django.views.debug.SafeExceptionReporterFilter' DEFAULT_FILE_STORAGE = 'django.core.files.storage.FileSystemStorage' DEFAULT_FROM_EMAIL = 'webmaster@localhost' DEFAULT_HASHING_ALGORITHM = 'sha256' DEFAULT_INDEX_TABLESPACE = '' DEFAULT_TABLESPACE = '' DEPOSITORY_DIR = '/volumes/static-files/depository' DISABLE_AUDITING = False DISALLOWED_USER_AGENTS = [] EMAIL_BACKEND = 'django.core.mail.backends.smtp.EmailBackend' EMAIL_HOST = 'localhost' EMAIL_HOST_PASSWORD = '********************' EMAIL_HOST_USER = '' EMAIL_PORT = 25 EMAIL_SSL_CERTFILE = None EMAIL_SSL_KEYFILE = '********************' EMAIL_SUBJECT_PREFIX = '[Django] ' EMAIL_TIMEOUT = None EMAIL_USE_LOCALTIME = False EMAIL_USE_SSL = False EMAIL_USE_TLS = False FILE_UPLOAD_DIRECTORY_PERMISSIONS = None FILE_UPLOAD_HANDLERS = ['django.core.files.uploadhandler.MemoryFileUploadHandler', 'django.core.files.uploadhandler.TemporaryFileUploadHandler'] FILE_UPLOAD_MAX_MEMORY_SIZE = 104857600 FILE_UPLOAD_PERMISSIONS = 420 FILE_UPLOAD_TEMP_DIR = None FIRST_DAY_OF_WEEK = 0 FIXTURE_DIRS = [] FORCE_SCRIPT_NAME = None FORMAT_MODULE_PATH = None FORM_RENDERER = 'django.forms.renderers.DjangoTemplates' IGNORABLE_404_URLS = [] INSTALLED_APPS = "('django.contrib.sessions', 'django.contrib.staticfiles', 'django.contrib.contenttypes', 'django.contrib.auth', 'specifyweb.specify', 'specifyweb.permissions', 'specifyweb.accounts', 'specifyweb.stored_queries', 'specifyweb.businessrules', 'specifyweb.express_search', 'specifyweb.context', 'specifyweb.attachment_gw', 'specifyweb.frontend', 'specifyweb.barvis', 'specifyweb.report_runner', 'specifyweb.interactions', 'specifyweb.workbench', 'specifyweb.notifications', 'specifyweb.export', 'specifyweb.raven_placeholder')" INTERNAL_IPS = [] JAVA_PATH = '/usr/bin/java' LANGUAGES = [('en-us', 'English'), ('ru-ru', 'русский'), ('uk-ua', 'українська'), ('fr-fr', 'français'), ('es-es', 'español')] LANGUAGES_BIDI = ['he', 'ar', 'ar-dz', 'fa', 'ur'] LANGUAGE_CODE = 'en-us' LANGUAGE_COOKIE_AGE = None LANGUAGE_COOKIE_DOMAIN = None LANGUAGE_COOKIE_HTTPONLY = False LANGUAGE_COOKIE_NAME = 'language' LANGUAGE_COOKIE_PATH = '/' LANGUAGE_COOKIE_SAMESITE = None LANGUAGE_COOKIE_SECURE = False LOCALE_PATHS = "('/opt/specify7/frontend/locale',)" LOGGING = {'version': 1, 'disable_existing_loggers': False, 'formatters': {'standard': {'format': '[%(asctime)s] [%(levelname)s] [%(name)s:%(lineno)s] %(message)s', 'datefmt': '%d/%b/%Y %H:%M:%S'}}, 'handlers': {'console': {'level': 'DEBUG', 'class': 'logging.StreamHandler', 'formatter': 'standard'}}, 'loggers': {'django.request': {'handlers': ['console'], 'level': 'DEBUG', 'propagate': False}, 'specifyweb': {'handlers': ['console'], 'level': 'DEBUG', 'propagate': False}}} LOGGING_CONFIG = 'logging.config.dictConfig' LOGIN_REDIRECT_URL = '/' LOGIN_URL = '/accounts/login/' LOGOUT_REDIRECT_URL = None MANAGERS = '()' MASTER_NAME = 'root' MASTER_PASSWORD = '********************' MEDIA_ROOT = '' MEDIA_URL = '/' MESSAGE_STORAGE = 'django.contrib.messages.storage.fallback.FallbackStorage' MIDDLEWARE = ['django.middleware.gzip.GZipMiddleware', 'django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.locale.LocaleMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'specifyweb.context.middleware.ContextMiddleware', 'specifyweb.permissions.middleware.PermissionsMiddleware', 'specifyweb.middleware.general.GeneralMiddleware'] MIGRATION_MODULES = {} MONTH_DAY_FORMAT = 'F j' NOTIFICATION_TTL_DAYS = 7 NUMBER_GROUPING = 0 OAUTH_LOGIN_PROVIDERS = {} PASSWORD_HASHERS = '********************' PASSWORD_RESET_TIMEOUT = '********************' PASSWORD_RESET_TIMEOUT_DAYS = '********************' PREPEND_WWW = False RAVEN_CONFIG = None REPORT_RUNNER_HOST = 'report-runner' REPORT_RUNNER_PORT = '8080' ROOT_URLCONF = 'specifyweb.urls' RO_MODE = False SA_DATABASE_URL = 'mysql://root:root@mariadb:3306/fwri?charset=utf8' SA_POOL_RECYCLE = 3600 SECRET_KEY = '********************' SECURE_BROWSER_XSS_FILTER = False SECURE_CONTENT_TYPE_NOSNIFF = True SECURE_HSTS_INCLUDE_SUBDOMAINS = False SECURE_HSTS_PRELOAD = False SECURE_HSTS_SECONDS = 0 SECURE_PROXY_SSL_HEADER = None SECURE_REDIRECT_EXEMPT = [] SECURE_REFERRER_POLICY = 'same-origin' SECURE_SSL_HOST = None SECURE_SSL_REDIRECT = False SEPARATE_WEB_ATTACHMENT_FOLDERS = None SERVER_EMAIL = 'root@localhost' SESSION_CACHE_ALIAS = 'default' SESSION_COOKIE_AGE = 1209600 SESSION_COOKIE_DOMAIN = None SESSION_COOKIE_HTTPONLY = True SESSION_COOKIE_NAME = 'sessionid' SESSION_COOKIE_PATH = '/' SESSION_COOKIE_SAMESITE = 'Lax' SESSION_COOKIE_SECURE = False SESSION_ENGINE = 'django.contrib.sessions.backends.file' SESSION_EXPIRE_AT_BROWSER_CLOSE = True SESSION_FILE_PATH = None SESSION_SAVE_EVERY_REQUEST = False SESSION_SERIALIZER = 'django.contrib.sessions.serializers.JSONSerializer' SETTINGS_MODULE = 'settings' SHORT_DATETIME_FORMAT = 'm/d/Y P' SHORT_DATE_FORMAT = 'm/d/Y' SIGNING_BACKEND = 'django.core.signing.TimestampSigner' SILENCED_SYSTEM_CHECKS = [] SITE_ID = 1 SPECIFY_CONFIG_DIR = '/opt/Specify/config' SPECIFY_THICK_CLIENT = '/opt/Specify' STATICFILES_DIRS = "(('config', '/opt/Specify/config'),)" STATICFILES_FINDERS = "('django.contrib.staticfiles.finders.FileSystemFinder', 'django.contrib.staticfiles.finders.AppDirectoriesFinder')" STATICFILES_STORAGE = 'django.contrib.staticfiles.storage.StaticFilesStorage' STATIC_ROOT = '' STATIC_URL = '/static/' STATS_URL = 'https://stats.specifycloud.org/capture' SUPPORT_LOGIN_TTL = 300 TEMPLATES = [{'BACKEND': 'django.template.backends.django.DjangoTemplates', 'DIRS': [], 'APP_DIRS': True, 'OPTIONS': {'context_processors': ['django.contrib.auth.context_processors.auth', 'django.template.context_processors.debug', 'django.template.context_processors.i18n', 'django.template.context_processors.media', 'django.template.context_processors.static', 'django.template.context_processors.tz', 'django.contrib.messages.context_processors.messages', 'django.template.context_processors.request']}}] TEST_NON_SERIALIZED_APPS = [] TEST_RUNNER = 'django.test.runner.DiscoverRunner' THICK_CLIENT_LOCATION = '/opt/Specify' THOUSAND_SEPARATOR = ',' TIME_FORMAT = 'P' TIME_INPUT_FORMATS = ['%H:%M:%S', '%H:%M:%S.%f', '%H:%M'] TIME_ZONE = 'America/Chicago' USE_I18N = True USE_L10N = True USE_THOUSAND_SEPARATOR = False USE_TZ = False USE_X_FORWARDED_HOST = False USE_X_FORWARDED_PORT = False VERSION = 'edge(debug)' WB_UPLOAD_LOG_DIR = '/home/specify/wb_upload_logs' WEBPACK_LOADER = {'MANIFEST_FILE': '/static/manifest.json'} WEB_ATTACHMENT_COLLECTION = 'sp7demofish' WEB_ATTACHMENT_KEY = '********************' WEB_ATTACHMENT_REQUIRES_KEY_FOR_GET = '********************' WEB_ATTACHMENT_URL = 'https://demo-assets.specifycloud.org/web_asset_store.xml' WSGI_APPLICATION = None X_FRAME_OPTIONS = 'DENY' YEAR_MONTH_FORMAT = 'F Y' You’re seeing this error because you have DEBUG = True in your Django settings file. Change that to False, and Django will display a standard page generated by the handler for this status code.

Specify 7 Crash Report - 2023-04-03T17 29 17.249Z.txt

realVinayak commented 1 year ago

Because I never fixed it

realVinayak commented 1 year ago

@realVinayak do you remember how far you have gone in fixing this?

Nothing. Long time ago, will need to investigate this again

melton-jason commented 1 year ago

Just from what I know about the backend, here is what I believe the issue to be.

The Relationship class is one defined in Specify and gets its attributes from the Specify 6's specify_datamodel.xml. https://github.com/specify/specify7/blob/935d304e8f2be58bb2e78a112bee04bb79d8e130/specifyweb/specify/load_datamodel.py#L206-L216

The Relationship will only have the otherSideName attribute if it exists in the xml. In the case of createdByAgent and modifiedByAgent, this is not the case:

<relationship type="many-to-one" classname="edu.ku.brc.specify.datamodel.Agent" relationshipname="createdByAgent" columnname="CreatedByAgentID" updatable="false" required="false" save="false" likemanytoone="false"/>
<relationship type="many-to-one" classname="edu.ku.brc.specify.datamodel.Agent" relationshipname="modifiedByAgent" columnname="ModifiedByAgentID" updatable="false" required="false" save="false" likemanytoone="false"/>

The issue occurs when we have a table join onto itself. In this issues case, when the base table is Agent and we are making a query onto an Agent table (which we are doing via Created by Agent) https://github.com/specify/specify7/blob/935d304e8f2be58bb2e78a112bee04bb79d8e130/specifyweb/stored_queries/query_construct.py#L90-L97

Because createdByAgent and modifiedByAgent relationships do not have otherSideName, the Attribute Error is thrown.

Specify only crashes when querying createdByAgent and modifiedByAgent in queries where Agent is the base table. However, this may happen to other tables and relationships. Using the following regular expression (<relationship(?!.*othersidename).*), I was able to find 524 occurrences where relationships do not have the othersidename attribute. We would need to look further into which tables/relationships are affected to get an entire scope of everything affected.

The solution here would to 'solve' our workaround to not rely on the otherSideName attribute.

melton-jason commented 1 year ago

Similar, if not identical, issue with AttributeError at /stored_query/ephemeral/

image 
AttributeError at /stored_query/ephemeral/

type object 'CollectionObject' has no attribute 'projects'

db, user: kim

Specify 7 Crash Report - 2023-01-26T17 23 53.475Z.txt

https://github.com/specify/specify7/issues/2305#issuecomment-1405345925

This is caused by a separate "bug". Projects to CollectionObjects is a many-to-many relationship, which is not supported in Specify 7. See #2316 and #2928

The cause of this issue is caused when joining a base table with the same table and running a query on a relationship which has no otherSideName (see https://github.com/specify/specify7/issues/2305#issuecomment-1594922611)

maxpatiiuk commented 1 year ago

While in some cases otherSideName is missing because of a mistake (there are a lot different mistakes in the datamodel because it was edited by hand :sigh:), most cases might be that way for a reason.

Can you check if otherSideName happes to be empty in the same cases when database column name is not set? I.e, every table has a createdByAgent relationship, but Agent table does not in return have a relationship back to every other table, thus otherSideName is not set to signify that this is a one-sided relationship.

Not sure if that's actually how that is as I'm not a database wizard (though Vinny might be) and what impact changing this would have on sp6 and sp7

grantfitzsimmons commented 1 year ago

Are there any updates on this error? I’ve checked both the “comment” and “issue #2305” links below and haven’t been able to ascertain where we stand. It’s been over 6 months now, and I still cannot run this query.

Paul has mentioned this issue again. We need to think about ways to solve it.

realVinayak commented 1 year ago

We all have been majorly wrong until this point.

There is no SQLAlchemy bug when doing self joins.

Slight background information into how SQLAlchemy interacts with Specify datamodel (the code for was written 10 years ago). First, we have the specify datamodel that gets loaded from the xml. We use ORM version of SQLAlchemy (alternative being the Core). The ORM versions needs to have an object relational model of the datamodel (like Django ORM) mapping classes to actual physical tables. So, once datamodel gets loaded (from XML), we make SQLAlchemy classes and tables (which is done here: https://github.com/specify/specify7/blob/production/specifyweb/stored_queries/build_models.py). In essence, this code syncs actual specify datamodel to SQLAlchemy datamodel. It defines all the relationships, all the columns for all the tables for SQLAlchemy to use. Each table in specify data model gets its corresponding class, and table definition in SQLAlchemy. They should be the same.

However, there is a very small bug in that code.

This is best seen by a manifestation of that. In the Taxon table of specify datamodel, the relationship Parent is many-to-one and the relationship Children is one-to-many. This makes sense. However, if you look at the relationship mapping that gets generated in the SQLAlchemy's version of Taxon, we see this: 

'Taxon.parent', "symbol('ONETOMANY')", "{Column('ParentID', Integer(), ForeignKey('taxon.TaxonID'), table=<taxon>)}")
'Taxon.children', "symbol('MANYTOONE')", "{Column('TaxonID', Integer(), table=<taxon>, primary_key=True, nullable=False)}"

What the above means. The first column is the relationship name. The second is the direction. The third is the "remote_side" of that relationship - which means - "what's the column on the other side of this relationship". First off, parent is set to be one-to-many and children is set to be many-to-one. Second, the "remote_side" is also flipped. If looking for parent, you will look for ParentID in your table, but will match it (aka remote side) to TaxonID on the other table.

So, what this means? Basically, when generating the SQLAlchemy orm datamodel, the code flipped relationships for self relationships - all trees. Another table with self relationship is Agent (CreatedByAgentID, ModifiedByAgentID). So, the SQLAlchemy queries having self joins will also be flipped. So, Ben thought that this was a bug in SQLAlchemy - it was instead a bug in the code that generated models for SQLAlchemy (which was still doing everything correctly - it was given flipped relationships in the first place, so it made flipped queries - which Ben patched a fix by deflipping it - which wasn't correct at all)

To the happy part now, how to fix it? The exact lines which cause the flipping of relationship in tree are here: https://github.com/specify/specify7/blob/80a0c45581b5eb94224b0131f8d07b3fa311f0b9/specifyweb/stored_queries/build_models.py#L84C1-L87. They set the remote side correctly - but they set the entire thing in the opposite relationship. So, they set the remote side in the children to be taxonid - which then makes SQLAlchemy automatically infer that remote side in parent to be parentid (causing this flip). It also has some problems with handling relationship Agent -> Created/ModifiedByAgentID as othersidename is null.

Since this code is 10 years old, and powers all SQLAlchemy, I want to be very careful when modifying it. My changes in this branch (https://github.com/specify/specify7/compare/issue-2305) fix the issue. However, I'll need to take a look if there are any edge cases / side effects that making this change do.

realVinayak commented 1 year ago

To confirm, this is what relationship spec looks like after my changes:

('Taxon.parent', "symbol('MANYTOONE')", "{Column('TaxonID', Integer(), table=<taxon>, primary_key=True, nullable=False)}")
('Taxon.children', "symbol('ONETOMANY')", "{Column('ParentID', Integer(), ForeignKey('taxon.TaxonID'), table=<taxon>)}")

vs what they were before:

'Taxon.parent', "symbol('ONETOMANY')", "{Column('ParentID', Integer(), ForeignKey('taxon.TaxonID'), table=<taxon>)}")
'Taxon.children', "symbol('MANYTOONE')", "{Column('TaxonID', Integer(), table=<taxon>, primary_key=True, nullable=False)}"