search

merenlab / anvio

An analysis and visualization platform for 'omics data
http://merenlab.org/software/anvio
GNU General Public License v3.0
439 stars 145 forks source link

Update Profile Database from v16 to v20 #502

Closed jbird9 closed 7 years ago

jbird9 commented 7 years ago

I recently tried to update a profile database form version 16 to 20 using the update scripts. In the end, an error was thrown:

sqlite3.OperationalError: no such table: mean_coverage_Q1Q3_splits

This seems to suggest that it wants to find that table in the sqlite table. However, the table is called mean_coverage_Q2Q3_splits in my v20 database. The same table is called mean_coverage_Q1Q3_splits in v16. I tested changing the name of the tables but then it just wants to find Q2Q3 later.

The problem seem to occur at the first conversion from 16 to 17.

-JTB

meren commented 7 years ago

Hi Jordan,

Which version of anvi'o are you using? I remember an error we had fixed awhile back regarding this :/

When exactly do you get the error? Can you copy-paste the full command and terminal output?

Thanks,

jbird9 commented 7 years ago

I am using version 2.3.1

Anvio/CONTIGSDB/MT_59E5_59E13_59E25_63E6$ anvi-script-upgrade-profile-db-v16-to-v17 /mnt/d/Dropbox_Lloyd/Dropbox\ (Lloyd\ Lab)/IODP\ Baltic\ 347/SAGs_Anvio/CONTIGSDB/MT_59E5_59E13_59E25_63E6/PROFILE.db

Here is what happens when I interactive v20 after changing the table name as mentioned above.

(anvio-2.3.1) redbird@redbird:/mnt/d/Dropbox_Lloyd/Dropbox (Lloyd Lab)/IODP Baltic 347/SAGs_Anvio/CONTIGSDB/MT_59E5_59E13_59E25_63E6$ anvi-interactive -c /mnt/d/Dropbox_Lloyd/Dropbox\ (Lloyd\ Lab)/IODP\ Baltic\ 347/SAGs_Anvio/CONTIGSDB/contigs.db -p /mnt/d/Dropbox_Lloyd/Dropbox\ (Lloyd\ Lab)/IODP\ Baltic\ 347/SAGs_Anvio/CONTIGSDB/MT_59E5_59E13_59E25_63E6/PROFILE.test.db Auxiliary Data ...............................: Found: /mnt/d/Dropbox_Lloyd/Dropbox (Lloyd Lab)/IODP Baltic 347/SAGs_Anvio/CONTIGSDB/contigs.h5 (v. 1) Contigs DB ...................................: Initialized: /mnt/d/Dropbox_Lloyd/Dropbox (Lloyd Lab)/IODP Baltic 347/SAGs_Anvio/CONTIGSDB/contigs.db (v. 8) [25 Apr 17 13:01:26 Initializing the profile database superclass] Loading split names Traceback (most recent call last): File "/home/redbird/LINUX_PROGRAMS/virtual-envs/anvio-2.3.1/bin/anvi-interactive", line 111, in d = interactive.InputHandler(args) File "/home/redbird/LINUX_PROGRAMS/virtual-envs/anvio-2.3.1/lib/python3.5/site-packages/anvio/interactive.py", line 140, in init self.load_full_mode(args) File "/home/redbird/LINUX_PROGRAMS/virtual-envs/anvio-2.3.1/lib/python3.5/site-packages/anvio/interactive.py", line 509, in load_full_mode ProfileSuperclass.init(self, args) File "/home/redbird/LINUX_PROGRAMS/virtual-envs/anvio-2.3.1/lib/python3.5/site-packages/anvio/dbops.py", line 1119, in init self.split_names = get_split_names_in_profile_db(self.profile_db_path) File "/home/redbird/LINUX_PROGRAMS/virtual-envs/anvio-2.3.1/lib/python3.5/site-packages/anvio/dbops.py", line 3415, in get_split_names_in_profile_db split_names = set(profile_db.db.get_single_column_from_table('mean_coverage_Q2Q3_splits', 'contig')) File "/home/redbird/LINUX_PROGRAMS/virtual-envs/anvio-2.3.1/lib/python3.5/site-packages/anvio/db.py", line 172, in get_single_column_from_table response = self._exec('''SELECT %s FROM %s''' % (column, table)) File "/home/redbird/LINUX_PROGRAMS/virtual-envs/anvio-2.3.1/lib/python3.5/site-packages/anvio/db.py", line 151, in _exec ret_val = self.cursor.execute(sql_query) sqlite3.OperationalError: no such table: mean_coverage_Q2Q3_splits

Here is the first conversion of v16 to v17

WARNING

This script will try to upgrade your profile database from v16 to v17. It happens to be that this upgrade will only rename every occurrence of portion_covered to detection. We don't even know why we called it portion_covered in the first place :/ You can just press ENTER to continue. If you want to cancel the upgrade and think more about it, press CTRL+C now. If you want to avoid this message the next time, use '--just-do-it'.

Press ENTER to continue...

Profile db type ..............................: Merged Full profile .................................: True [25 Apr 17 13:06:39 Trying to upgrade the profile database] ... Traceback (most recent call last): File "/home/redbird/LINUX_PROGRAMS/virtual-envs/anvio-2.3.1/bin/anvi-script-upgrade-profile-db-v16-to-v17", line 100, in update_profile_db(args.profile_db, just_do_it = args.just_do_it) File "/home/redbird/LINUX_PROGRAMS/virtual-envs/anvio-2.3.1/bin/anvi-script-upgrade-profile-db-v16-to-v17", line 63, in update_profile_db profile_db._exec('INSERT INTO %s VALUES ("detection", "detection_splits")' % t.views_table_name) File "/home/redbird/LINUX_PROGRAMS/virtual-envs/anvio-2.3.1/lib/python3.5/site-packages/anvio/db.py", line 151, in _exec ret_val = self.cursor.execute(sql_query) sqlite3.OperationalError: unable to open database file (anvio-2.3.1) redbird@redbird:/mnt/d/Dropbox_Lloyd/Dropbox (Lloyd Lab)/IODP Baltic 347/SAGs_Anvio/CONTIGSDB/MT_59E5_59E13_59E25_63E6$ anvi-script-upgrade-profile-db-v16-to-v17 /mnt/d/D ropbox_Lloyd/Dropbox\ (Lloyd\ Lab)/IODP\ Baltic\ 347/SAGs_Anvio/CONTIGSDB/MT_59E5_59E13_59E25_63E6/PROFILE.db

WARNING

This script will try to upgrade your profile database from v16 to v17. It happens to be that this upgrade will only rename every occurrence of portion_covered to detection. We don't even know why we called it portion_covered in the first place :/ You can just press ENTER to continue. If you want to cancel the upgrade and think more about it, press CTRL+C now. If you want to avoid this message the next time, use '--just-do-it'.

Press ENTER to continue...

Profile db type ..............................: Merged Full profile .................................: False

It was not my decision to put spaces in the file that I can't change the name of. :/

meren commented 7 years ago

Hi Jordan,

I apologize for responding this this late. I just realized you in fact responded to my question :/

Unfortunately this error makes no sense to me. It is always a challenge to update db versions from one to another without running into any problem, but, I've never run into this error, and can't find a way to reproduce it.

Do you have your original db somewhere? If you can share it with me maybe I can try to upgrade it and find out what is happening.

Best,

jbird9 commented 7 years ago

Hi Meren,

I have attached the original db. Thanks so much for getting back to me on this and caring so much about fixing every issue.

I hope you had a good time teaching the workshop last week. A couple of my labmates were in attendance so hopefully you will see some more Lloyd Lab/Anvio papers coming along shortly.

Thanks,

Jordan ​ contigs.old.db.gz https://drive.google.com/file/d/0B8it0IQmOcJTU1ZzaVdVWEdmV0E/view?usp=drive_web ​

On Tue, May 2, 2017 at 11:33 AM, A. Murat Eren notifications@github.com wrote:

Hi Jordan,

I apologize for responding this this late. I just realized you in fact responded to my question :/

Unfortunately this error makes no sense to me. It is always a challenge to update db versions from one to another without running into any problem, but, I've never run into this error, and can't find a way to reproduce it.

Do you have your original db somewhere? If you can share it with me maybe I can try to upgrade it and find out what is happening.

Best,

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/merenlab/anvio/issues/502#issuecomment-298671262, or mute the thread https://github.com/notifications/unsubscribe-auth/ADa5FBpBT9WOXoei4TlBGpY7k66cW2H7ks5r10y1gaJpZM4NHx5N .

-- Jordan Bird Ph D Student at The University of Tennessee jbird9@utk.edu or jordantobybird@gmail.com 870-718-9053

meren commented 7 years ago

Hi Jordan, thank you very much for your response, but I think you sent the wrong DB :) Can you please send the profile database?

You can send it directly to my private e-mail (a.murat.eren at gmail) if you would like to make sure there are no public links to it from anywhere.

Best,

jbird9 commented 7 years ago

Oops, here it is.

​​​ PROFILE.old.db.gz https://drive.google.com/file/d/0B8it0IQmOcJTdEhrRmpVcXB5VUk/view?usp=drive_web ​

On Tue, May 2, 2017 at 12:17 PM, A. Murat Eren notifications@github.com wrote:

Hi Jordan, thank you very much for your response, but I think you sent the wrong DB :) Can you please send the profile database?

You can send it directly to my private e-mail (a.murat.eren at gmail) if you would like to make sure there are no public links to it from anywhere.

Best,

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/merenlab/anvio/issues/502#issuecomment-298684306, or mute the thread https://github.com/notifications/unsubscribe-auth/ADa5FBhnvF2jCBOH0-7DepHNK2yBDEfYks5r11cLgaJpZM4NHx5N .

-- Jordan Bird Ph D Student at The University of Tennessee jbird9@utk.edu or jordantobybird@gmail.com 870-718-9053

meren commented 7 years ago

Hi Jordan,

I did everything you probably did,

gzip -d PROFILE.old.db.gz
cp PROFILE.old.db PROFILE.db
anvi-script-upgrade-profile-db-v16-to-v17 PROFILE.db --just-do-it
anvi-script-upgrade-profile-db-v17-to-v18 PROFILE.db --just-do-it
anvi-script-upgrade-profile-db-v18-to-v19 PROFILE.db --just-do-it
anvi-script-upgrade-profile-db-v19-to-v20 PROFILE.db --just-do-it
gzip -d contigs.old.db.gz
anvi-script-upgrade-contigs-db-v6-to-v7 contigs.old.db --just-do-it
anvi-script-upgrade-contigs-db-v7-to-v8 contigs.old.db --just-do-it
anvi-interactive -c contigs.old.db -p PROFILE.db

and as a result I got this:

image

I am using the master branch, but it should be almost identical to the official version since we didn't change anything related since the release:

$ anvi-interactive -v
Anvi'o version ...............................: 2.3.1-master
Profile DB version ...........................: 20
Contigs DB version ...........................: 8
Pan DB version ...............................: 5
Samples information DB version ...............: 2
Genome data storage version ..................: 1
Auxiliary data storage version ...............: 3
Anvi'server users data storage version .......: 1

I think you have some older anvi'o files lying around on your disk :/

jbird9 commented 7 years ago

No that really is the same file I was working with :/. And your methods seem identical to mine. I will have to get back to this a little later. When I get out of the meeting.

On May 2, 2017 12:34 PM, "A. Murat Eren" notifications@github.com wrote:

Hi Jordan,

I did everything you probably did,

gzip -d PROFILE.old.db.gz cp PROFILE.old.db PROFILE.db anvi-script-upgrade-profile-db-v16-to-v17 PROFILE.db --just-do-it anvi-script-upgrade-profile-db-v17-to-v18 PROFILE.db --just-do-it anvi-script-upgrade-profile-db-v18-to-v19 PROFILE.db --just-do-it anvi-script-upgrade-profile-db-v19-to-v20 PROFILE.db --just-do-it gzip -d contigs.old.db.gz anvi-script-upgrade-contigs-db-v6-to-v7 contigs.old.db --just-do-it anvi-script-upgrade-contigs-db-v7-to-v8 contigs.old.db --just-do-it anvi-interactive -c contigs.old.db -p PROFILE.db

and as a result I got this:

[image: image] https://cloud.githubusercontent.com/assets/197307/25628166/dc2566fa-2f2a-11e7-943d-7ea2194e6dda.png

I am using the master branch, but it should be almost identical to the official version since we didn't change anything related since the release:

$ anvi-interactive -v Anvi'o version ...............................: 2.3.1-master Profile DB version ...........................: 20 Contigs DB version ...........................: 8 Pan DB version ...............................: 5 Samples information DB version ...............: 2 Genome data storage version ..................: 1 Auxiliary data storage version ...............: 3 Anvi'server users data storage version .......: 1

I think you have some older anvi'o files lying around on your disk :/

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/merenlab/anvio/issues/502#issuecomment-298689170, or mute the thread https://github.com/notifications/unsubscribe-auth/ADa5FLnYGZSoqY45GdalFYARS6ueNVwfks5r11sdgaJpZM4NHx5N .

jbird9 commented 7 years ago

Ok...I am pretty sure that this is a BASH on Windows specific problem. Which is not something you need to spend time on fixing. However, I did solve a long time problem that I had with installing the anvio 2.3.1 on the newest update of Ubuntu 16.04 with python 3.6. Turns on that all over the internet people are screaming about problems with python 3.6 and apparently one of the those problem cause 3.6 not to be able to import sqlite3. I downloaded python3.5 separately, used alias to make it my python flavor of choice and it can run anvio just fine and all the upgrades mentioned above worked just fine too. The only reason I am posting about this is because I am concerned that other Ubuntu 16.04 users that have python3.6 will have the same problem.

meren commented 7 years ago

Thank you very much for this note, Jordan. I hope Google will help others to find it in case it happens to other people.