Install database connection migration

Traceback (most recent call last): File "/ramdisk/galaxy/0.6/galaxy-dist/lib/galaxy/webapps/galaxy/buildapp.py", line 51, in app_factory app = galaxy.app.UniverseApplication( global_conf=global_conf, **kwargs ) File "/ramdisk/galaxy/0.6/galaxy-dist/lib/galaxy/app.py", line 54, in __init__ self._configure_models( check_migrate_databases=True, check_migrate_tools=check_migrate_tools, config_file=config_file ) File "/ramdisk/galaxy/0.6/galaxy-dist/lib/galaxy/config.py", line 838, in _configure_models tsi_create_or_verify_database( install_db_url, self.config.install_database_engine_options, app=self ) File "/ramdisk/galaxy/0.6/galaxy-dist/lib/galaxy/model/tool_shed_install/migrate/check.py", line 81, in create_or_verify_database db_schema = schema.ControlledSchema( engine, migrate_repository ) File "/ramdisk/galaxy/0.6/galaxy-dist/eggs/sqlalchemy_migrate-0.9.6-py2.7.egg/migrate/versioning/schema.py", line 33, in __init__ self.load() File "/ramdisk/galaxy/0.6/galaxy-dist/eggs/sqlalchemy_migrate-0.9.6-py2.7.egg/migrate/versioning/schema.py", line 54, in load exceptions.DatabaseNotControlledError(str(exc)), tb) File "/ramdisk/galaxy/0.6/galaxy-dist/eggs/sqlalchemy_migrate-0.9.6-py2.7.egg/migrate/versioning/schema.py", line 50, in load data = list(result)[0] DatabaseNotControlledError: list index out of range

3.2 years ago by

Hotz, Hans-Rudolf • 1.8k

Switzerland

Hotz, Hans-Rudolf • 1.8k wrote:

Hi David

Have you tried a complete MySQL copy of your original MySQL database? instead of migrating only individual tables?

Regards, Hans-Rudolf

ADD COMMENT • link written 3.2 years ago by Hotz, Hans-Rudolf • 1.8k

HI Hans,

Thanks for your suggestion.

Yes, I tried to use the whole MySQL dump converted to sqlite and it did not work.

I also loaded the whole dump into a second MySQL DB (using another user for the MySQL) and it did not work either.

In both cases same error

File "/galaxy-dist/eggs/sqlalchemy_migrate-0.9.6-py2.7.egg/migrate/versioning/schema.py", line 50, in load
    data = list(result)[0]
DatabaseNotControlledError: list index out of range

This is the migrate_version of my database
Galaxy | lib/galaxy/model/migrate | 129

And this is the sqlalchemy_migrate version

sqlalchemy_migrate-0.9.6-py2.7.egg

Any suggestion?

Thanks

ADD REPLY • link written 3.2 years ago by david.a.morais • 110

what's the contents of ~/galaxy_dist/lib/galaxy/model/migrate/versions/ ?

do you have all files up to "0129_job_external_output_metadata_validity.py" ?

Hans-Rudolf

ADD REPLY • link written 3.2 years ago by Hotz, Hans-Rudolf • 1.8k

Hi Hans

Yes, all of them

0001_initial_tables.py
0001_initial_tables.pyc
0002_metadata_file_table.py
0002_metadata_file_table.pyc
0003_security_and_libraries.py

...
0126_password_reset.py
0126_password_reset.pyc
0127_output_collection_adjustments.py
0127_output_collection_adjustments.pyc
0128_session_timeout.py
0128_session_timeout.pyc
0129_job_external_output_metadata_validity.py
0129_job_external_output_metadata_validity.pyc

One strange thing I noticed is that when I try to start Galaxy using the dump as the main DB I get the same error.

Is there any problems with mysql dumps and Galaxy sqlalchemy_migrate?

That's is how I am dumping my DB

mysqldump -u galaxy -pxxxxx galaxy  >whole_dump.sql

and relaoding

mysql -uinstallDB -pxxxxxxxx installDB <whole_dump.sql


Do see any error here?

Thanks a lot for taking time to help.

ADD REPLY • link written 3.2 years ago by david.a.morais • 110

The dump/re-load seems right to me

I am sorry, I am running out of ideas - I hope someone from the core-team can jump in

Hans-Rudolf

ADD REPLY • link written 3.2 years ago by Hotz, Hans-Rudolf • 1.8k

Hi Hans,

The dump load/reload worked as "database_connection" but the same dump does not work as "install_database_connection"

I really appreciated your time.
Hopefully someone from the core-team will shed some light one this.

Thanks a lot

ADD REPLY • link written 3.2 years ago by david.a.morais • 110

just out of curiosity: what happens when you start from scratch (ie a fresh Galaxy download) and set "database_connection" to one empty (no tables) database and "install_database_connection" to a second empty database and let galaxy populate the two databases? Do you get extra tables for the "install_database_connection" database not present in the other?

Hans-Rudolf

ADD REPLY • link written 3.2 years ago by Hotz, Hans-Rudolf • 1.8k

Hi Hans,

I finally found what may be the problem.

First those are the tables that Galaxy create when the 'install_database_connection' is enabled.

migrate_tools
migrate_version
repository_dependency
repository_repository_dependency_association
tool_dependency
tool_shed_repository
tool_version
tool_version_association

The database itself is created with the version 17

sqlite> select * from migrate_version;
ToolShedInstall|||lib/galaxy/model/tool_shed_install/migrate|||17

and trying to update to any other version won't work (at least using managedb.sh). I try to use an empty sqlite as a 'database_connection' so I could upgrade. The Upgrade itself worked but at that point Galaxy was throwing the error that I mentioned above.

Then I decided to compare the fields in each table (mysql version 129 vs sqlite version 17).
With that I wrote a script that convert a dump from one version to the other (adding columns when needed).
I started to manually insert the tables. They were all OK except for tool_shed_repository. Some entries from that table caused Galaxy to throw errors when I tried the 'manage installed repositories' link form the admin panel.

What I noticed is that in the Mysql version some entries have in their 'blob' field a double escaped '\' character, and Galaxy throws an error if it tries to read from an sqlite with this type pf escape.

for example

 | grep \\"Version: \\" | sed -e 's/Version: //'"}

vs

 | grep \"Version: \" | sed -e 's/Version: //'"}

So there is where I am now. I will modify all entries and reload the DB.
Hopefully all tools will still work.

Thanks a lot for your comments.

I will let you know if I pull this out.

Cheers

ADD REPLY • link written 3.2 years ago by david.a.morais • 110

Hi Hans,

It finally worked.

After adding the columns to the sqlite and correcting the blob on the 'tool_shed_repository' table I was able to load the sqlite DB and to run/install the tools without problem.

Thanks a lot for your suggestions.

Cheers

ADD REPLY • link written 3.2 years ago by david.a.morais • 110

Similar posts • Search »