This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Initialization of MySQL (MariaDb) for Funannotate.

Hi,

This post is in relation to an old Question i asked which got answered. OLD-POST

So I have around 110 fungal samples (illumina assemblies) which i want to annotate using Funannotate. Now funannotate runs PASA using SQLite which runs on 1 thread, and takes days for one sample. Even if i try to run whole pipeline as it is, this takes roughly 4-5 days for one sample and i can initiate upto 10 samples on cluster at a time. (the most time taking steps are PASA in funannotate-train (where it generated training data using RNASeq evidences) and funannotate-update (where it finds UTRs).

I asked the same question on Funannotate-Issues page and one of the co-developer/author suggested runnign MySQL initially, then runnign the funannotate steps. They also shared the script which is HERE

Problem:

I actually haven't used/done any kind of databasing using mysql/mariadb ever, so i am having really hard time initilizing the database. what concept comes to my mind for setting up script is

  • Initialize MySQL/MariaDB
  • Run funannotate train (while telling it to use mySQL)
  • Run funannotate predict
  • Run funannotate Update
  • Close/stop the database

I am trying to initialize MariaDb like this (this is after discussing everything (query) with chatGPT)

####################################
### ---- Initializing MariaDB PASA
####################################
MARIAdb_SIF="/vast/$USER/tools/containers/mariaDB_12.2.2.sif"
RUNID="${currentSAMPLE}_${SLURM_JOB_ID:-$$}_${SLURM_ARRAY_TASK_ID:-0}"

PASAdb="/work/$USER/pasaDB"

MYSQL_BASE="$PASAdb/mysql_${RUNID}"
MYSQL_DATA="$MYSQL_BASE/db"
MYSQL_CONF="$MYSQL_BASE/conf"
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3307"

mkdir -p "$MYSQL_DATA" "$MYSQL_CONF"

cp "$CONDA_PREFIX/opt/pasa-2.5.3/pasa_conf/pasa.CONFIG.template" "$MYSQL_CONF/pasa_${RUNID}.conf"
export PASACONF="$MYSQL_CONF/pasa_${RUNID}.conf"

sed -i "s/^MYSQLSERVER.*$/MYSQLSERVER=${MYSQL_HOST}:${MYSQL_PORT}/" "$PASACONF"
sed -i "s/^MYSQL_RW_USER.*$/MYSQL_RW_USER=root/" "$PASACONF"
sed -i "s/^MYSQL_RW_PASSWORD.*$/MYSQL_RW_PASSWORD=rootpass/" "$PASACONF"

cat > "$MYSQL_CONF/my.cnf" <<EOF
[mysqld]
user=$USER
port=${MYSQL_PORT}
bind-address=${MYSQL_HOST}
datadir=${MYSQL_DATA}/
socket=${MYSQL_BASE}/mysql.sock
pid-file=${MYSQL_BASE}/mysql.pid
log-error=${MYSQL_BASE}/mysql.err

[client]
port=${MYSQL_PORT}
socket=${MYSQL_BASE}/mysql.sock
EOF

if [[ ! -d "$MYSQL_DATA/mysql" ]]; then
    echo "---- Initializing MariaDB database"
    singularity exec \
        -B "$MYSQL_BASE:$MYSQL_BASE" \
        -B "$MYSQL_DATA:/var/lib/mysql" \
        "$MARIAdb_SIF" \
        /usr/bin/mariadb-install-db \
            --user="$USER" \
            --datadir=${MYSQL_DATA}/ \
            --auth-root-authentication-method=normal
fi

echo "---- Starting MariaDB server"
singularity exec --writable-tmpfs \
    -B "$MYSQL_BASE:$MYSQL_BASE" \
    -B "$MYSQL_DATA:/var/lib/mysql" \
    "$MARIAdb_SIF" \
    /usr/sbin/mariadbd \
        --defaults-file="$MYSQL_CONF/my.cnf" \
        --datadir=${MYSQL_DATA}/ \
        --user="$USER" \
        > "$MYSQL_BASE/mariadb.stdout.log" 2>&1 &

MYSQLPID=$!
echo "MariaDB PID: $MYSQLPID"

sleep 10

singularity exec \
    -B "$MYSQL_BASE:$MYSQL_BASE" \
    -B "$MYSQL_DATA:/var/lib/mysql" \
    "$MARIAdb_SIF" \
    /usr/bin/mariadb-admin \
        --host="$MYSQL_HOST" \
        --port="$MYSQL_PORT" \
        ping

echo "---- Setting MariaDB root password"

singularity exec \
    -B "$MYSQL_BASE:$MYSQL_BASE" \
    -B "$MYSQL_DATA:/var/lib/mysql" \
    "$MARIAdb_SIF" \
    /usr/bin/mariadb \
        --host="$MYSQL_HOST" \
        --port="$MYSQL_PORT" \
        -u root \
        -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootpass'; FLUSH PRIVILEGES;" \
    2>/dev/null || true

singularity exec \
    -B "$MYSQL_BASE:$MYSQL_BASE" \
    -B "$MYSQL_DATA:/var/lib/mysql" \
    "$MARIAdb_SIF" \
    /usr/bin/mariadb-admin \
        --host="$MYSQL_HOST" \
        --port="$MYSQL_PORT" \
        -u root \
        -prootpass \
        ping

after that i ran funannotate-Train like this

singularity exec -B "/vast/$USER":"/vast/$USER" "$FUNANNOTATE_SIF" \
funannotate train \
    --input "$currentFASTA" \
    --out "$fannoOUT" \
    --left_norm "$LEFT_NORM" \
    --right_norm "$RIGHT_NORM" \
    --jaccard_clip \
    --no_trimmomatic \
    --species "$currentSPECIE" \
    --strain "$currentSTRAIN" \
    --cpus "$threads" \
    --pasa_db mysql \
    --no-progress

But i always get this error at the PASA step.

[Mar 24 05:05 PM]: OS: Debian GNU/Linux 10, 144 cores, ~ 4227 GB RAM. Python: 3.8.15
[Mar 24 05:05 PM]: Running 1.8.17
[Mar 24 05:05 PM]: Trimmomatic will be skipped
[Mar 24 05:05 PM]: Read normalization will be skipped
[Mar 24 05:05 PM]: Building Hisat2 genome index
[Mar 24 05:05 PM]: Aligning reads to genome using Hisat2
[Mar 24 05:07 PM]: Running genome-guided Trinity, logfile: /vast/ti78qem/genome_assemblies/SR.assemblers/FOSC_assemblies/08.funannotate_GeneAnno/ILL001/training/Trinity-gg.log
[Mar 24 05:07 PM]: Clustering of reads from BAM and preparing assembly commands
[Mar 24 05:20 PM]: Assembling 11,088 Trinity clusters using 47 CPUs
[Mar 24 05:47 PM]: 43,737 transcripts derived from Trinity
[Mar 24 05:47 PM]: Running StringTie on Hisat2 coordsorted BAM
[Mar 24 05:47 PM]: Removing poly-A sequences from trinity transcripts using seqclean
[Mar 24 05:47 PM]: Converting transcript alignments to GFF3 format
[Mar 24 05:47 PM]: Converting Trinity transcript alignments to GFF3 format
[Mar 24 05:48 PM]: Running PASA alignment step using 43,737 transcripts
[Mar 24 05:48 PM]: CMD ERROR: /venv/opt/pasa-2.4.1/Launch_PASA_pipeline.pl -c /vast/ti78qem/genome_assemblies/SR.assemblers/FOSC_assemblies/08.funannotate_GeneAnno/ILL001/training/pasa/alignAssembly.txt -r -C -R -g /vast/ti78qem/genome_assemblies/SR.assemblers/FOSC_assemblies/08.funannotate_GeneAnno/ILL001/training/genome.fasta --IMPORT_CUSTOM_ALIGNMENTS /vast/ti78qem/genome_assemblies/SR.assemblers/FOSC_assemblies/08.funannotate_GeneAnno/ILL001/training/trinity.alignments.gff3 -T -t /vast/ti78qem/genome_assemblies/SR.assemblers/FOSC_assemblies/08.funannotate_GeneAnno/ILL001/training/trinity.fasta.clean -u /vast/ti78qem/genome_assemblies/SR.assemblers/FOSC_assemblies/08.funannotate_GeneAnno/ILL001/training/trinity.fasta --stringent_alignment_overlap 30.0 --TRANSDECODER --ALT_SPLICE --MAX_INTRON_LENGTH 3000 --CPU 48 --ALIGNERS blat --trans_gtf /vast/ti78qem/genome_assemblies/SR.assemblers/FOSC_assemblies/08.funannotate_GeneAnno/ILL001/training/funannotate_train.stringtie.gtf

I feel like the whole process by which i initiate Mariadb is wrong, but what i am doing wrong i can find that. If you can help me in this regard or point me to right direction, it will be great help.

Regards

annotations fungal funannotate
• 314 views • link
written by SomeOne • SomeOne

One problem when submitting the DB job on the cluster, it may be on a different node than the clients. You would need to submit the DB job first, then get its IP and use it in all clients in case your cluster configuration allows it at all. In any case you cannot use localhost or 127.0.0.1. That's why I would integrate the MySQL server into the container.

Also, what makes you sure that using MySQL over SQLite will provide any speedup?

• 0 views • link
written by Michael Dondrup Michael Dondrup

Hi, Thank you for helpful insights.

Also, what makes you sure that using MySQL over SQLite will provide any speedup?

Actually the wiki page of PASA suggests that and also so9mehow i managed to pull it off. and the runtime of PASA alignments (which previously took ~10+ hrs in funannotate-train now took only ~40 mintes)

• 0 views • link
written by SomeOne • SomeOne

1 answer

The output isn't really giving you any reason. Problem with the bot-generated code is also that it doesn't really contain any intermediate test of the connection and that the user ends up not understanding what it is doing. Also, it should have told you that you probably don't want to do what you asked it to do here. If the only reason for creating the mysql server in the first place was to use it with PASA, you should have at least considered simply installing it in the container by modifying the container's singularity file and building a new sif.

Have you, for example, tried to simply connect to the MySQL console as the user with that password and do you see any tables? Do you have to set up a DB schema, or is that done by the PASA pipeline?

Can you run the following command (following the CMD ERROR) through singularity directly to get more output?

I see a point where your logic could be breaking:

You are running the mysql host in a different container than the pipeline/client. Are you sure that the ports are properly mapped and accessible in the containers? Are you sure granting to 'root'@'localhost' is sufficient? Because technically, you will be using a TCP connection instead of a socket for the connection between containers, see: https://stackoverflow.com/questions/47893094/mariadb-host-works-on-localhost-but-not-ip-alternative-127-0-0-1

Can you e.g.:

singularity exec -B "/vast/$USER":"/vast/$USER" "$FUNANNOTATE_SIF" \
/usr/bin/mariadb \
        --host="$MYSQL_HOST" \
        --port="$MYSQL_PORT" \
        -u root

Then: SHOW GRANTS FOR 'root'@'127.0.0.1'

The user account used for connecting via TCP/IP must be explicitly granted privileges from the remote host. A user 'root'@'localhost' is different from 'root'@'127.0.0.1' or 'root'@'%'

So you will probably first have to do a:

CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY your_pw;
• 0 views • link
written by Michael Dondrup Michael Dondrup

Log in to answer this question.

More posts like this

No similar posts found.