Skip to content
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.

Sign up for GitHub

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

Jump to bottom

make env --download-reference inserting into non existing table? #3372

Closed
sjanssen2 opened this issue Feb 23, 2024 · 4 comments
Closed

make env --download-reference inserting into non existing table? #3372

sjanssen2 opened this issue Feb 23, 2024 · 4 comments

Comments

@sjanssen2
Copy link
Contributor

When setting TEST_ENVIRONMENT = FALSE the command qiita-env make --no-load-ontologies --download-reference fails with errors

(qiita) qiita@vbox-stefan:~$ qiita-env make --no-load-ontologies --download-reference
Creating database
Inserting database metadata
Building SQL layout
	Applying patch 0.sql...
	Applying patch 1.sql...
	Applying patch 2.sql...
	Applying patch 3.sql...
	Applying patch 4.sql...
	Applying patch 5.sql...
	Applying patch 6.sql...
		Applying python patch 6.py...
	Applying patch 7.sql...
		Applying python patch 7.py...
	Applying patch 8.sql...
	Applying patch 9.sql...
	Applying patch 10.sql...
	Applying patch 11.sql...
	Applying patch 12.sql...
	Applying patch 13.sql...
	Applying patch 14.sql...
		Applying python patch 14.py...
	Applying patch 15.sql...
		Applying python patch 15.py...
	Applying patch 16.sql...
	Applying patch 17.sql...
	Applying patch 18.sql...
	Applying patch 19.sql...
	Applying patch 20.sql...
	Applying patch 21.sql...
	Applying patch 22.sql...
	Applying patch 23.sql...
		Applying python patch 23.py...
	Applying patch 24.sql...
	Applying patch 25.sql...
		Applying python patch 25.py...
	Applying patch 26.sql...
	Applying patch 27.sql...
	Applying patch 28.sql...
	Applying patch 29.sql...
	Applying patch 30.sql...
		Applying python patch 30.py...
	Applying patch 31.sql...
		Applying python patch 31.py...
	Applying patch 32.sql...
	Applying patch 33.sql...
	Applying patch 34.sql...
		Applying python patch 34.py...
	Applying patch 35.sql...
	Applying patch 36.sql...
		Applying python patch 36.py...
	Applying patch 37.sql...
	Applying patch 38.sql...
		Applying python patch 38.py...
	Applying patch 39.sql...
	Applying patch 40.sql...
	Applying patch 41.sql...
	Applying patch 42.sql...
	Applying patch 43.sql...
		Applying python patch 43.py...
	Applying patch 44.sql...
	Applying patch 45.sql...
		Applying python patch 45.py...
	Applying patch 46.sql...
		Applying python patch 46.py...
	Applying patch 47.sql...
		Applying python patch 47.py...
	Applying patch 48.sql...
		Applying python patch 48.py...
	Applying patch 49.sql...
	Applying patch 50.sql...
	Applying patch 51.sql...
		Applying python patch 51.py...
	Applying patch 52.sql...
	Applying patch 53.sql...
		Applying python patch 53.py...
	Applying patch 54.sql...
		Applying python patch 54.py...
None: /home/qiita/Git/qiita-spots/qiita/qiita_db/support_files/production_data/per_sample_FASTQ/2000
None: /home/qiita/Git/qiita-spots/qiita/qiita_db/support_files/production_data/Demultiplexed/2001
None: /home/qiita/Git/qiita-spots/qiita/qiita_db/support_files/production_data/Demultiplexed/2002
None: /home/qiita/Git/qiita-spots/qiita/qiita_db/support_files/production_data/Demultiplexed/2004
None: /home/qiita/Git/qiita-spots/qiita/qiita_db/support_files/production_data/Demultiplexed/2003
	Applying patch 55.sql...
		Applying python patch 55.py...
	Applying patch 56.sql...
	Applying patch 57.sql...
	Applying patch 58.sql...
		Applying python patch 58.py...
	Applying patch 59.sql...
	Applying patch 60.sql...
	Applying patch 61.sql...
		Applying python patch 61.py...
	Applying patch 62.sql...
		Applying python patch 62.py...
	Applying patch 63.sql...
	Applying patch 64.sql...
		Applying python patch 64.py...
	Applying patch 65.sql...
	Applying patch 66.sql...
		Applying python patch 66.py...
	Applying patch 67.sql...
	Applying patch 68.sql...
	Applying patch 69.sql...
	Applying patch 70.sql...
	Applying patch 71.sql...
		Applying python patch 71.py...
	Applying patch 72.sql...
	Applying patch 73.sql...
	Applying patch 74.sql...
		Applying python patch 74.py...
	Applying patch 75.sql...
		Applying python patch 75.py...
	Applying patch 76.sql...
	Applying patch 77.sql...
	Applying patch 78.sql...
	Applying patch 79.sql...
	Applying patch 80.sql...
	Applying patch 81.sql...
		Applying python patch 81.py...
	Applying patch 82.sql...
	Applying patch 83.sql...
	Applying patch 84.sql...
	Applying patch 85.sql...
	Applying patch 86.sql...
	Applying patch 87.sql...
	Applying patch 88.sql...
	Applying patch 89.sql...
Downloading reference files
SKIPPING tree: file already exists at /home/qiita/Git/qiita-spots/qiita/qiita_db/support_files/production_data/reference/gg_13_8-97_otus.tree. To download the file again, erase the existing file first
SKIPPING taxonomy: file already exists at /home/qiita/Git/qiita-spots/qiita/qiita_db/support_files/production_data/reference/gg_13_8-97_otu_taxonomy.txt. To download the file again, erase the existing file first
SKIPPING sequence: file already exists at /home/qiita/Git/qiita-spots/qiita/qiita_db/support_files/production_data/reference/gg_13_8-97_otus.fasta. To download the file again, erase the existing file first
HIER <qiita_db.reference.Reference object at 0x7fe2335f6bb0>
stefan error  Error running SQL query: Error running SQL: UNDEFINED_TABLE. MSG: relation "qiita.processed_params_sortmerna" does not exist
LINE 1: INSERT INTO qiita.processed_params_sortmerna
                    ^


Traceback (most recent call last):
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/qiita_db/sql_connection.py", line 256, in _execute
    cur.execute(sql, sql_args)
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/psycopg2/extras.py", line 146, in execute
    return super().execute(query, vars)
psycopg2.errors.UndefinedTable: relation "qiita.processed_params_sortmerna" does not exist
LINE 1: INSERT INTO qiita.processed_params_sortmerna
                    ^


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/qiita_db/sql_connection.py", line 199, in _raise_execution_error
    raise ValueError(
ValueError: Error running SQL: UNDEFINED_TABLE. MSG: relation "qiita.processed_params_sortmerna" does not exist
LINE 1: INSERT INTO qiita.processed_params_sortmerna
                    ^



During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/qiita/miniforge3/envs/qiita/bin/qiita-env", line 113, in <module>
    env()
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "/home/qiita/miniforge3/envs/qiita/bin/qiita-env", line 38, in make
    qdb.environment_manager.make_environment(
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/qiita_db/environment_manager.py", line 228, in make_environment
    _download_reference_files()
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/qiita_db/environment_manager.py", line 127, in _download_reference_files
    _insert_processed_params(ref)
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/qiita_db/environment_manager.py", line 92, in _insert_processed_params
    qdb.sql_connection.TRN.execute()
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/qiita_db/sql_connection.py", line 49, in wrapper
    return func(self, *args, **kwargs)
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/qiita_db/sql_connection.py", line 313, in execute
    return self._execute()
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/qiita_db/sql_connection.py", line 260, in _execute
    self._raise_execution_error(sql, sql_args, e)
  File "/home/qiita/miniforge3/envs/qiita/lib/python3.9/site-packages/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: UNDEFINED_TABLE. MSG: relation "qiita.processed_params_sortmerna" does not exist
LINE 1: INSERT INTO qiita.processed_params_sortmerna

I think this happens because this function:

qiita/qiita_db/environment_manager.py

Lines 84 to 92 in 57b84cf

def _insert_processed_params(ref):
with qdb.sql_connection.TRN:
sortmerna_sql = """INSERT INTO qiita.processed_params_sortmerna
(reference_id, sortmerna_e_value, sortmerna_max_pos,
similarity, sortmerna_coverage, threads)
VALUES
(%s, 1, 10000, 0.97, 0.97, 1)"""
qdb.sql_connection.TRN.add(sortmerna_sql, [ref._id])
qdb.sql_connection.TRN.execute()

does not reflect the change in database layout of patch 33:

qiita/qiita_db/support_files/patches/33.sql

Lines 352 to 371 in 57d1b14

-- Transfer the default parameters from the processed_params_sortmerna table
IF EXISTS(SELECT * FROM qiita.processed_params_sortmerna) THEN
FOR rec IN
SELECT *
FROM qiita.processed_params_sortmerna
ORDER BY parameters_id
LOOP
val := ('{"reference":' || rec.reference_id || ','
'"sortmerna_e_value":' || rec.sortmerna_e_value || ','
'"sortmerna_max_pos":' || rec.sortmerna_max_pos || ','
'"similarity":' || rec.similarity || ','
'"sortmerna_coverage":' || rec.sortmerna_coverage || ','
'"threads":' || rec.threads || '}')::json;
INSERT INTO qiita.default_parameter_set (command_id, parameter_set_name, parameter_set)
VALUES (3, rec.param_set_name, val);
END LOOP;
ELSE
INSERT INTO qiita.default_parameter_set (command_id, parameter_set_name, parameter_set)
VALUES (3, 'Defaults', '{"reference":1,"sortmerna_e_value":1,"sortmerna_max_pos":10000,"similarity":0.97,"sortmerna_coverage":0.97,"threads":1}'::json);
END IF;

@sjanssen2
Copy link
Contributor Author

shouldn't these parameters be set when the qp-target-gene plugin get registered? https://github.com/qiita-spots/qp-target-gene/blob/858436ee6d4a1fccd35faee0b156a12953b5774a/qp_target_gene/__init__.py#L138-L151

@antgonza
Copy link
Member

To be honest, I haven't started a non-test environment in a long time.

Anyway, I think the problem is that you are not patching (via qiita-env patch) the newly created system.

@antgonza
Copy link
Member

Just saw your second question; yes, there are some old patches that are just kept for reference. For example 33.sql is from a really long time ago and the command qiita-env patch takes care of those things.

@antgonza antgonza mentioned this issue Feb 23, 2024
Closed
@antgonza
Copy link
Member

Closing this as it has been superseded by #3374.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@antgonza @sjanssen2