qiita add trailing/separating whitespace

trafficstars

got the error Error running SQL: SYNTAX_ERROR. MSG: trailing junk after numeric literal at or near "1A" LINE 6: WHERE study_id = 1AND artifact_type = '... ^ on my test instance

log says

==> bcf_qiita/Logs/qiita_21174.log <==
[E 240322 13:35:00 web:1669] Uncaught exception GET /study/description/1 (127.0.0.1)
    HTTPServerRequest(protocol='https', host='qiita.jlab.bio', method='GET', uri='/study/description/1', version='HTTP/1.0', remote_ip='127.0.0.1')
    Traceback (most recent call last):
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 256, in _execute
        cur.execute(sql, sql_args)
      File "/homes/sjanssen/bcf_qiita/envs/qiita/lib/python3.9/site-packages/psycopg2/extras.py", line 146, in execute
        return super().execute(query, vars)
    psycopg2.errors.SyntaxError: trailing junk after numeric literal at or near "1A"
    LINE 6:                      WHERE study_id = 1AND artifact_type = '...
                                                  ^
    
    
    During handling of the above exception, another exception occurred:
    
    Traceback (most recent call last):
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 199, in _raise_execution_error
        raise ValueError(
    ValueError: Error running SQL: SYNTAX_ERROR. MSG: trailing junk after numeric literal at or near "1A"
    LINE 6:                      WHERE study_id = 1AND artifact_type = '...
                                                  ^
    
    
    
    During handling of the above exception, another exception occurred:
    
    Traceback (most recent call last):
      File "/homes/sjanssen/bcf_qiita/envs/qiita/lib/python3.9/site-packages/tornado/web.py", line 1590, in _execute
        result = method(*self.path_args, **self.path_kwargs)
      File "/homes/sjanssen/bcf_qiita/envs/qiita/lib/python3.9/site-packages/tornado/web.py", line 3006, in wrapper
        return method(self, *args, **kwargs)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_pet/handlers/study_handlers/base.py", line 28, in get
        study_info = study_get_req(study, self.current_user.id)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_pet/handlers/api_proxy/studies.py", line 120, in study_get_req
        study.artifacts(artifact_type='BIOM')) != 0
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/study.py", line 1108, in artifacts
        for aid in qdb.sql_connection.TRN.execute_fetchflatten()]
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 49, in wrapper
        return func(self, *args, **kwargs)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 389, in execute_fetchflatten
        return list(chain.from_iterable(self.execute()[idx]))
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 49, in wrapper
        return func(self, *args, **kwargs)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 313, in execute
        return self._execute()
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 260, in _execute
        self._raise_execution_error(sql, sql_args, e)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 205, in _raise_execution_error
        raise ValueError("Error running SQL query: %s" % str(error))
    ValueError: Error running SQL query: Error running SQL: SYNTAX_ERROR. MSG: trailing junk after numeric literal at or near "1A"
    LINE 6:                      WHERE study_id = 1AND artifact_type = '...
                                                  ^
    
    
[E 240322 13:35:00 web:2161] 500 GET /study/description/1 (127.0.0.1) 159.63ms
[E 240322 13:35:00 web:1114] Uncaught exception in write_error
    Traceback (most recent call last):
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 256, in _execute
        cur.execute(sql, sql_args)
      File "/homes/sjanssen/bcf_qiita/envs/qiita/lib/python3.9/site-packages/psycopg2/extras.py", line 146, in execute
        return super().execute(query, vars)
    psycopg2.errors.SyntaxError: trailing junk after numeric literal at or near "1A"
    LINE 6:                      WHERE study_id = 1AND artifact_type = '...
                                                  ^
    
    
    During handling of the above exception, another exception occurred:
    
    Traceback (most recent call last):
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 199, in _raise_execution_error
        raise ValueError(
    ValueError: Error running SQL: SYNTAX_ERROR. MSG: trailing junk after numeric literal at or near "1A"
    LINE 6:                      WHERE study_id = 1AND artifact_type = '...
                                                  ^
    
    
    
    During handling of the above exception, another exception occurred:
    
    Traceback (most recent call last):
      File "/homes/sjanssen/bcf_qiita/envs/qiita/lib/python3.9/site-packages/tornado/web.py", line 1590, in _execute
        result = method(*self.path_args, **self.path_kwargs)
      File "/homes/sjanssen/bcf_qiita/envs/qiita/lib/python3.9/site-packages/tornado/web.py", line 3006, in wrapper
        return method(self, *args, **kwargs)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_pet/handlers/study_handlers/base.py", line 28, in get
        study_info = study_get_req(study, self.current_user.id)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_pet/handlers/api_proxy/studies.py", line 120, in study_get_req
        study.artifacts(artifact_type='BIOM')) != 0
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/study.py", line 1108, in artifacts
        for aid in qdb.sql_connection.TRN.execute_fetchflatten()]
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 49, in wrapper
        return func(self, *args, **kwargs)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 389, in execute_fetchflatten
        return list(chain.from_iterable(self.execute()[idx]))
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 49, in wrapper
        return func(self, *args, **kwargs)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 313, in execute
        return self._execute()
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 260, in _execute
        self._raise_execution_error(sql, sql_args, e)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 205, in _raise_execution_error
        raise ValueError("Error running SQL query: %s" % str(error))
    ValueError: Error running SQL query: Error running SQL: SYNTAX_ERROR. MSG: trailing junk after numeric literal at or near "1A"
    LINE 6:                      WHERE study_id = 1AND artifact_type = '...
                                                  ^
    
    
    
    During handling of the above exception, another exception occurred:
    
    Traceback (most recent call last):
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 256, in _execute
        cur.execute(sql, sql_args)
      File "/homes/sjanssen/bcf_qiita/envs/qiita/lib/python3.9/site-packages/psycopg2/extras.py", line 146, in execute
        return super().execute(query, vars)
    psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "pk_logging"
    DETAIL:  Key (logging_id)=(294) already exists.
    
    
    During handling of the above exception, another exception occurred:
    
    Traceback (most recent call last):
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 199, in _raise_execution_error
        raise ValueError(
    ValueError: Error running SQL: UNIQUE_VIOLATION. MSG: duplicate key value violates unique constraint "pk_logging"
    DETAIL:  Key (logging_id)=(294) already exists.
    
    
    
    During handling of the above exception, another exception occurred:
    
    Traceback (most recent call last):
      File "/homes/sjanssen/bcf_qiita/envs/qiita/lib/python3.9/site-packages/tornado/web.py", line 1112, in send_error
        self.write_error(status_code, **kwargs)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_pet/handlers/base_handlers.py", line 69, in write_error
        LogEntry.create(
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/logger.py", line 103, in create
        return cls(qdb.sql_connection.TRN.execute_fetchlast())
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 49, in wrapper
        return func(self, *args, **kwargs)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 336, in execute_fetchlast
        return self.execute()[-1][0][0]
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 49, in wrapper
        return func(self, *args, **kwargs)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 313, in execute
        return self._execute()
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 260, in _execute
        self._raise_execution_error(sql, sql_args, e)
      File "/homes/sjanssen/bcf_qiita/qiita-spots/qiita/qiita_db/sql_connection.py", line 205, in _raise_execution_error
        raise ValueError("Error running SQL query: %s" % str(error))
    ValueError: Error running SQL query: Error running SQL: UNIQUE_VIOLATION. MSG: duplicate key value violates unique constraint "pk_logging"
    DETAIL:  Key (logging_id)=(294) already exists.

Mar 22 '24 12:03 sjanssen2

Thank you @sjanssen2.

Any thoughts of why we never hit this issue before? I'm actually kind of confused.

Mar 22 '24 14:03 antgonza

A very good question. Hard to answer, though. Would an error like this be logged in the database? Can you reproduce the error on your end?

Mar 22 '24 14:03 sjanssen2

I tried a couple of examples and they work for me:

In [16]: len(Study(550).artifacts(artifact_type='BIOM'))
Out[16]: 22

In [17]: len(Study(550).artifacts(artifact_type='per_sample_FASTQ'))
Out[17]: 0

Can it be a psycopg2 version difference? We are currently using:

$ pip freeze | grep psycopg2
psycopg2==2.9.3

Apr 01 '24 13:04 antgonza

These are my psycopg2 versions. I remember I had some issues installing them through setup.py

psycopg2-binary           2.9.9                    pypi_0    pypi
psycopg2-pool             1.2                      pypi_0    pypi

Apr 02 '24 13:04 sjanssen2

Thank you. I just realized why we haven't seen it before: the artifact_type parameter should be string not int. Not 10% sure what would be the right way to handle this; any suggestions?

Apr 02 '24 14:04 antgonza

Is there an easy way to print out the actual SQL statements for a TRN? I wonder how the statements look like in your above tests. Even if the artifact_type parameter is an int like 5 the if clause should be entered the sql_where variable should be set to AND artifact_type = 5 and thus the where clause in line 1102 should read like ...WHERE study_id = 550AND artifact_type = 5AND visibility_id NOT IN ..., i.e. missing whitespaces in front of AND and after the value of artifact_type. Is it possible, that your psycopg2 lib automatically detects/splits at SQL keywords like AND and mine does not?

Apr 02 '24 14:04 sjanssen2

After checking, it's an PgSQL thing; if you use quotes on the SELECT it works fine without spaces. For example, these 2 work fine:

qiita_test=# select * from qiita.study_artifact where study_id = '1' and artifact_id = '3';
 study_id | artifact_id 
----------+-------------
        1 |           3
(1 row)

qiita_test=# select * from qiita.study_artifact where study_id = '1'and artifact_id = '3';
 study_id | artifact_id 
----------+-------------
        1 |           3
(1 row)

Apr 02 '24 14:04 antgonza

convenient ;-) However, shouldn't the study_id be an integer within the python object and as such not be quoted in the SQL syntax? I'd argue, having the additional whitespace(s) is the more secure solution here

Apr 02 '24 14:04 sjanssen2

Agree, I'll merge now.

Apr 02 '24 14:04 antgonza

qiita qiita copied to clipboard

add trailing/separating whitespace

qiita
qiita copied to clipboard