Problem with ./make_database.sh

[Smooge123]

N E X T F L O W ~ version 0.23.4
Launching /export/home/craig/orchid/workflow/annotate.nf [elated_banach] - revision: fe1e111a6e
======================== Run Info ==================================================
Database: mysql://root:orchid123@localhost:3306/feature_test
Mutations: 41
Number of chunks / process: 1

[warm up] executor > local
[b7/afe46e] Submitted process > makeTabixes (splitting data)
[36/cb64ee] Submitted process > makeBeds (splitting data)
[73/b446ec] Submitted process > updateMetadata (saving feature info)
WARN: Process makeBeds (splitting data) terminated with an error exit status (1) -- Execution is retried (1)
[76/a6ef5b] Re-submitted process > makeBeds (splitting data)
ERROR ~ Error executing process > 'makeBeds (splitting data)'

Caused by:
Process makeBeds (splitting data) terminated with an error exit status (1)

Command executed:

mysql -u$MYSQL_USER -h$MYSQL_IP -P$MYSQL_PORT -D$MYSQL_DB -NB -e "SELECT CONCAT('chr',chromosome), start-1, end, ssm_id, '' FROM ssm" > variants.bed
sort-bed variants.bed > sorted_variants.bed

Command exit status:
1

Command output:
(empty)

Command error:

*****ERROR: Unrecognized parameter: variants.bed *****
I've tried entering the command by hand and get ERROR 1049 (42000): Unknown database 'orchid_20171215'

Thanks!

[ccario83]

It looks like this issue could be caused by a couple potential problems:

1. Orchid cannot connect to the database. When you ran make_database.sh did the output of the reset.nf or populate.nf workflow indicate any error? For database-related errors, I like to use SQL Pro (for OSX) to manually check orchid's tables. It takes the same database information as in your config script, and is a great tool if you haven't come across it before!

2. There could be a problem with the sort-bed command. When you type this command in the terminal, does it work and if so, what version is being displayed?

[Smooge123]

Thanks for your reply. I've tracked this down to a missing password in annotate.nf. I tried setting the password to null and that didn't work. Did you get this to work without a password? It doesn't seem possible. Here are my modifications to workflow/annotate.nf, my machine is having hardware problems and I will test ASAP and let you know. Also there's no newline at the end of annotate.nf, is there a newer version of this file? Stve 81c81 < mysql -u$MYSQL_USER -p$MYSQL_PWD -h$MYSQL_IP -P$MYSQL_PORT -D$MYSQL_DB -NB -e "SELECT CONCAT('chr',chromosome), start-1, end, !{params.mutation_table_name}_id, '' FROM !{params.mutation_table_name}" > variants.bed ---

mysql -u$MYSQL_USER -h$MYSQL_IP -P$MYSQL_PORT -D$MYSQL_DB -NB -e "SELECT CONCAT('chr',chromosome), start-1, end, !{params.mutation_table_name}_id, '' FROM !{params.mutation_table_name}" > variants.bed

216c216 < mysql -u$MYSQL_USER -p$MYSQL_PWD -h$MYSQL_IP -P$MYSQL_PORT -D$MYSQL_DB -NB -e " ---

mysql -u$MYSQL_USER -h$MYSQL_IP -P$MYSQL_PORT -D$MYSQL_DB -NB -e "

260c260 < echo $QUERY | mysql -u$MYSQL_USER -p$MYSQL_PWD -h$MYSQL_IP -P$MYSQL_PORT -D$MYSQL_DB -NB | sort -n -k1,1 - > query_results.tsv ---

echo $QUERY | mysql -u$MYSQL_USER -h$MYSQL_IP -P$MYSQL_PORT -D$MYSQL_DB -NB | sort -n -k1,1 - > query_results.tsv

324c324 < mysql -u$MYSQL_USER -p$MYSQL_PWD -h$MYSQL_IP -P$MYSQL_PORT -D$MYSQL_DB -NB -e " ---

mysql -u$MYSQL_USER -h$MYSQL_IP -P$MYSQL_PORT -D$MYSQL_DB -NB -e "

351c351 < mysql -u$MYSQL_USER -p$MYSQL_PWD -h$MYSQL_IP -P$MYSQL_PORT -D$MYSQL_DB -NB -e " ---

mysql -u$MYSQL_USER -h$MYSQL_IP -P$MYSQL_PORT -D$MYSQL_DB -NB -e "

366c366 < } ---

}

\ No newline at end of file [ Charset UTF-8 unsupported, converting... ]

It looks like this issue could be caused by a couple potential problems: 1) Orchid cannot connect to the database. When you ran `make_database.sh` did the output of the `reset.nf` or `populate.nf` workflow indicate any error? For database-related errors, I like to use [SQL Pro](https://www.sequelpro.com/) (for OSX) to manually check orchid's tables. It takes the same database information as in your config script, and is a great tool if you haven't come across it before! 2) There could be a problem with the `sort-bed` command. When you type this command in the terminal, does it work and if so, what version is being displayed? -- You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub: #7 (comment)

-- Steven J. Backus Computer Systems Manager University of Utah E-Mail: steven.backus@utah.edu Genetic Epidemiology Alternate: backus@math.utah.edu 391 Chipeta Way -- Suite D Office: 801.587.9308 Salt Lake City, UT 84108-1266 http://www.math.utah.edu/~backus

[ccario83]

In the config file (config, which points to workflow/nextflow.config), there are several lines that will define the database connection across all orchid-db processes. It looks like this:

// Database parameters
params.database_ip             = 'localhost'           // The URL of the database which to write data
params.database_port           = 3306                  // The database port (3306 for MySQL/MemSQL, 5432 for PostgreSQL)
params.database_username       = 'orchid'              // The username to access the created database
params.database_password       = 'orchid_flower'       // The password to access the created database

Have you tried modifying these to match your mysql setup? Deeper in the config file (around line 100), you'll see that these parameters are used to generate several nextflow environment variables, which set shell environment variables used throughout the orchid-db workflow.

Let me know if properly setting these resolves the problem.

[Smooge123]

The database paramaters are set correctly in the config file. The password is not being used, thus my changes. I see no way this can work without them. If you do, please explain. Steve [ Charset UTF-8 unsupported, converting... ]

In the config file (`config`, which points to `workflow/nextflow.config`), there are several lines that will define the database connection across all orchid-db processes. It looks like this: ``` // Database parameters params.database_ip = 'localhost' // The URL of the database which to write data params.database_port = 3306 // The database port (3306 for MySQL/MemSQL, 5432 for PostgreSQL) params.database_username = 'orchid' // The username to access the created database params.database_password = 'orchid_flower' // The password to access the created database ``` Have you tried modifying these to match your mysql setup? Deeper in the config file (around line 100), you'll see that these parameters are used to generate several nextflow environment variables, which set shell environment variables used throughout the orchid-db workflow. Let me know if properly setting these resolves the problem. -- You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub: #7 (comment)

-- Steven J. Backus Computer Systems Manager University of Utah E-Mail: steven.backus@utah.edu Genetic Epidemiology Alternate: backus@math.utah.edu 391 Chipeta Way -- Suite D Office: 801.587.9308 Salt Lake City, UT 84108-1266 http://www.math.utah.edu/~backus

[ccario83]

Thanks for the clarification, I think I understand the issue better now.

There is a quark with mysql where passwords can be specified with either -p or --password= on the command line, but only the latter will work for blank (or nonexistent) passwords in non-interactive settings.

When creating a database, orchid will attempt to use a root/no password login to create a new user with the credentials specified in the config file for the specified table, so you can use any password you'd like without revealing more privileged account information. If you set up a user for orchid manually with a blank password it could potentially cause problems due to this quark.

Moving forward I'd suggest two things:

1. Try providing a blank password in the config file: params.database_password = ''
2. Specify a generic password in the config file and let orchid create the database user instead of doing so manually.

If this doesn't work or you'd really prefer a blank password, let me know. I'm thinking it may be better to switch all the mysql flags to their long forms in the code base anyway, both for readability and to avoid strange quarks like this.

[Smooge123]

When creating a database, orchid will attempt to use a root/no password login to create a new user with the credentials specified in the config file for the specified table, so you can use any password you'd like without revealing more privileged account information.

I changed the user and password to something different: params.database_username = 'neworchid' params.database_password = 'orchid123' Then I took out my changes to annotate.nf. Indeed it added the user: mysql> select User from mysql.user; +------------------+ | User | +------------------+ | neworchid | | orchid | | root | | debian-sys-maint | | mysql.session | | mysql.sys | | root | +------------------+ 7 rows in set (0.00 sec) But it gave the same error. Without the password I'm at a loss to see how it will work. Certainly it can add the user with the root password being null, but the command: mysql -u$MYSQL_USER -h$MYSQL_IP -P$MYSQL_PORT -D$MYSQL_DB -NB -e "SELECT CONCAT('chr',chromosome), start-1, end, ssm_id, '' FROM ssm" > variants.bed sort-bed variants.bed > sorted_variants.bed still fails as it uses the user it added, not the root user. Steve -- Steven J. Backus Computer Systems Manager University of Utah E-Mail: steven.backus@utah.edu Genetic Epidemiology Alternate: backus@math.utah.edu 391 Chipeta Way -- Suite D Office: 801.587.9308 Salt Lake City, UT 84108-1266 http://www.math.utah.edu/~backus

[ccario83]

Appreciate your patience! It totally makes sense to include a password flag for mysql across the entire codebase. I'll update this now and will post a fix shortly.

[Smooge123]

Thanks! Steve [ Charset UTF-8 unsupported, converting... ]

Appreciate your patience! It totally makes sense to include a password flag for mysql across the entire codebase. I'll update this now and will post a fix shortly. -- You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub: #7 (comment)

-- Steven J. Backus Computer Systems Manager University of Utah E-Mail: steven.backus@utah.edu Genetic Epidemiology Alternate: backus@math.utah.edu 391 Chipeta Way -- Suite D Office: 801.587.9308 Salt Lake City, UT 84108-1266 http://www.math.utah.edu/~backus

[ccario83]

OK, my latest commit should help a lot. Earlier I thought you'd removed the password flags from the annotation.nf script for your database situation, so I was confused until I realized they were never in the script to begin with! The complete set of flags really should have been provided to every mysql command originally. My database set up is tolerant to this bug but I think your situation will be one of the most common so this issue has been useful for making the orchid code better. Thanks for reporting it!

The 'feature test' is working for me with the new code changes. Let me know if this commit fails to resolve your issue. You may also run into snpEff, hg19, or simulator problems since I am unable to provide these code/data externalities due to size or licensing restrictions. The download.nf script is meant to help with obtaining them, but let me know if you run into trouble.

[Smooge123]

I downloaded the latest version and ran it. I had to make a link to the old software for orchid/code/external, it was missing in this new version. After making the link and running it again, the same thing happened: ERROR ~ Error executing process > 'makeBeds (splitting data)' Caused by: Process `makeBeds (splitting data)` terminated with an error exit status (1) Command executed: mysql --user=$MYSQL_USER --password=$MYSQL_PWD --host=$MYSQL_IP --port=$MYSQL_PORT --database=$MYSQL_DB -NB -e "SELECT CONCAT('chr',chromosome), start-1, end, ssm_id, '' FROM ssm" > variants.bed sort-bed variants.bed > sorted_variants.bed Command exit status: 1 Command output: (empty) Command error: mysql: [Warning] Using a password on the command line interface can be insecure. *****ERROR: Unrecognized parameter: variants.bed ***** etc. It appears there's some other difficulty rather than just the password. Let me know if I can try something else. Steve -- Steven J. Backus Computer Systems Manager University of Utah E-Mail: steven.backus@utah.edu Genetic Epidemiology Alternate: backus@math.utah.edu 391 Chipeta Way -- Suite D Office: 801.587.9308 Salt Lake City, UT 84108-1266 http://www.math.utah.edu/~backus

[ccario83]

I wonder if there is an issue with the sort-bed command. If you do sort-bed --version, what version does it respond with? It should be 2.4.26.

Another thing to try if you haven't already is to navigate into nextflow's working directory of the failing process to take a peek at the execution environment. The error message should report the working directory when nextflow crashes (looks like work/6f/fdba0ad4fd980d1161cc80e8fe1f22). Once you navigate to it you can check variants file, the environment variables, and the executed command:

head variants.bed
cat .command.env
cat .command.sh

The variants file should exist and be non-empty and the mysql environment variables in .command.env should all be defined and correspond to those in .command.sh

You can also source the environment file and run the script directly to see if error information is more informative that way:

source .command.env
sh .command.sh

[Smooge123]

I wonder if there is an issue with the `sort-bed` command. If you do `sort-bed --version`, what version does it respond with? It should be 2.4.26.

Tool: bedtools sort (aka sortBed) Version: v2.25.0 Summary: Sorts a feature file in various and useful ways. So no.

source .command.env sh .command.sh

When doing this I get the usage command for sort-bed so it's failing on the sort-bed command (doing sh -x .command.sh confirms it). So I think it may be the sort-bed being out of date issue. Let me know how to get the correct bedtools. The command: apt-get install bedtools says: bedtools is already the newest version (2.25.0-1). Thanks! Steve -- Steven J. Backus Computer Systems Manager University of Utah E-Mail: steven.backus@utah.edu Genetic Epidemiology Alternate: backus@math.utah.edu 391 Chipeta Way -- Suite D Office: 801.587.9308 Salt Lake City, UT 84108-1266 http://www.math.utah.edu/~backus

[ccario83]

Ah, there seems to be an issue with the install instructions, which I'm now re-testing in a new virtual machine. There should be an additional requirement for bedops 2.26, which provides sort-bed. Here's how you get it:

wget https://github.com/bedops/bedops/releases/download/v2.4.26/bedops_linux_x86_64-v2.4.26.tar.bz2
tar jxvf bedops_linux_x86_64-v2.4.26.tar.bz2
sudo mv bin/* /usr/local/bin

This assumes a 64-bit unix system and that /usr/local/bin is in your $PATH.

[ccario83]

There is also an issue with long Mutation IDs in the version of mysql for Ubuntu 14.04, which can be fixed by running the SQL command:

SET @@global.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

I'll provide another update tomorrow to fix any remaining issues in the completely clean install of orchid in a virtual machine.

[Smooge123]

Rightous. I'll await a new version. Thanks for being so responsive. Steve [ Charset UTF-8 unsupported, converting... ]

There is also an issue with long Mutation IDs in the version of mysql for Ubuntu 14.04, which can be fixed by running the SQL command: ```SQL SET @@global.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; ``` I'll provide another update tomorrow to fix any remaining issues in the completely clean install of orchid in a virtual machine. -- You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub: #7 (comment)

-- Steven J. Backus Computer Systems Manager University of Utah E-Mail: steven.backus@utah.edu Genetic Epidemiology Alternate: backus@math.utah.edu 391 Chipeta Way -- Suite D Office: 801.587.9308 Salt Lake City, UT 84108-1266 http://www.math.utah.edu/~backus

[ccario83]

It looks like the latest commit has fixed remaining issues, as demonstrated through a successfully created 'feature_test' database after a clean install. I also tested the bed file generation with the dnase feature found on our lab site.

The install have also been updated to reflect changes, but I think we already addressed them all in this bug history, so your installation should be ready to go.

[Smooge123]

It looks like the latest commit has fixed remaining issues...

It all went well, thanks for your help. As the manual states, "now you're ready for machine learning" and perhaps the researcher who is doing this will need help getting past that, I'll let you know if that's the case. Thanks again! Steve -- Steven J. Backus Computer Systems Manager University of Utah E-Mail: steven.backus@utah.edu Genetic Epidemiology Alternate: backus@math.utah.edu 391 Chipeta Way -- Suite D Office: 801.587.9308 Salt Lake City, UT 84108-1266 http://www.math.utah.edu/~backus

[ccario83]

Glad to hear. I'll mark this issue as closed, but please let me know if anything else comes up!