[galaxy@cluster galaxy-dist]$ hg heads changeset: 10416:97d020901403 branch: stable tag: tip user: Carl Eberhard carlfeberhard@gmail.com date: Mon Sep 09 11:20:40 2013 -0400 summary: Fix to dbkey select in library upload when 'unspecified' is not in dbkey list
changeset: 10411:c42567f43aa7 user: greg date: Mon Aug 19 13:19:56 2013 -0400 summary: Filter invalid objects when generating the list of repository_dependencies objects that are associated with a tool shed repository installed into Galaxy.
2013/9/11 hogart dr.hogart@gmail.com
So,
I decided to completely drop the old galaxy psql database (I have a dump of it) - hogart# drop database galaxy;
and recreate it again - [galaxy@cluster ~]$ creatdb
After the launching of the Galaxy it is terminated with the message with requesting of upgrade the database, from 13 to 115. But the upgrading of database scheme is also failed: [galaxy@cluster galaxy-dist]$ sh manage_db.sh upgrade /export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/url.py:105: SADeprecationWarning: The SQLAlchemy PostgreSQL dialect has been renamed from 'postgres' to 'postgresql'. The new URL format is postgresql[+driver]://<user>:<pass>@<host>/<dbname> 13 -> 14...
Migration script to add support for "Pages".
- Creates Page and PageRevision tables
- Adds username column to User table
Traceback (most recent call last): File "./scripts/manage_db.py", line 64, in <module> main( repository=repo, url=db_url ) File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/shell.py", line 207, in main ret = command_func(**kwargs) File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/api.py", line 186, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "<string>", line 2, in _migrate File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/util/__init__.py", line 159, in with_engine return f(*a, **kw) File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/api.py", line 366, in _migrate schema.runchange(ver, change, changeset.step) File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/schema.py", line 91, in runchange change.run(self.engine, step) File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/script/py.py", line 145, in run script_func(engine) File "lib/galaxy/model/migrate/versions/0014_pages.py", line 55, in upgrade col.create( User_table, index_name='ix_user_username', unique_name='username' ) File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/schema.py", line 528, in create engine._run_visitor(visitorcallable, self, connection, **kwargs) File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 2302, in _run_visitor File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 1972, in _run_visitor File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/ansisql.py", line 53, in traverse_single ret = super(AlterTableVisitor, self).traverse_single(elem) File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/sql/visitors.py", line 106, in traverse_single File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/ansisql.py", line 101, in visit_column self.execute() File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/ansisql.py", line 42, in execute return self.connection.execute(self.buffer.getvalue()) File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 1449, in execute File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 1628, in _execute_text File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 1698, in _execute_context File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 1691, in _execute_context File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/default.py", line 331, in do_execute sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "username" of relation "galaxy_user" already exists '\nALTER TABLE galaxy_user ADD username VARCHAR(255)' {}
Since, it was past release of Galaxy (release_2013.06.03), I pulled the latest one and repeated the procedure, but the result was the same:
[galaxy@cluster galaxy-dist]$ sh run.sh ... Exception: Your database has version '13' but this code expects version '115'. Please backup your database and then migrate the schema by running 'sh manage_db.sh upgrade'. [galaxy@cluster galaxy-dist]$ sh manage_db.sh upgrade /export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/url.py:105: SADeprecationWarning: The SQLAlchemy PostgreSQL dialect has been renamed from 'postgres' to 'postgresql'. The new URL format is postgresql[+driver]://<user>:<pass>@<host>/<dbname> 13 -> 14...
Migration script to add support for "Pages".
- Creates Page and PageRevision tables
- Adds username column to User table
Traceback (most recent call last): File "./scripts/manage_db.py", line 62, in <module> main( repository=repo, url=db_url ) File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/shell.py", line 207, in main ret = command_func(**kwargs) File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/api.py", line 186, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "<string>", line 2, in _migrate File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/util/__init__.py", line 159, in with_engine return f(*a, **kw) File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/api.py", line 366, in _migrate schema.runchange(ver, change, changeset.step) File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/schema.py", line 91, in runchange change.run(self.engine, step) File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/script/py.py", line 145, in run script_func(engine) File "lib/galaxy/model/migrate/versions/0014_pages.py", line 67, in upgrade col.create( User_table, index_name='ix_user_username', unique_name='username' ) File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/schema.py", line 528, in create engine._run_visitor(visitorcallable, self, connection, **kwargs) File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 2302, in _run_visitor File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 1972, in _run_visitor File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/ansisql.py", line 53, in traverse_single ret = super(AlterTableVisitor, self).traverse_single(elem) File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/sql/visitors.py", line 106, in traverse_single File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/ansisql.py", line 101, in visit_column self.execute() File "/export/apps/galaxy/galaxy-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/ansisql.py", line 42, in execute return self.connection.execute(self.buffer.getvalue()) File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 1449, in execute File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 1628, in _execute_text File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 1698, in _execute_context File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 1691, in _execute_context File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/default.py", line 331, in do_execute sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "username" of relation "galaxy_user" already exists '\nALTER TABLE galaxy_user ADD username VARCHAR(255)' {}
I will be gratefull for any help with this.
2013/9/11 hogart dr.hogart@gmail.com
Dear all,
I have succeffully installed the local instance of Galaxy, that uses the postgeSQL database, and it worked fine. After several monthes of uses, it appeared the neccessety to reinstall the whole Galaxy. For this, I have completely removed the old instance, and hg clone the new one to the same location. The psql database remains the same. After upgrading the database schema with the corresponding script, I have faced with inability of upgrading the counter of history in database. For example, after creating the new history, the addition of new dataset (by uploading via ftp, from local comp, from UCSC) result in the error:
NotSupportedError: (NotSupportedError) DECLARE CURSOR ... FOR UPDATE/SHARE is not supported DETAIL: Cursors must be READ ONLY. 'SELECT history.hid_counter \nFROM history \nWHERE history.id = %(id_1)s FOR UPDATE' {'id_1': 5}
I will be very grateful with any help with this issue.
galaxy=> \du List of roles Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+----------- galaxy | no | no | yes | no limit | hogart | yes | yes | yes | no limit | postgres | yes | yes | yes | no limit | (3 rows)
galaxy=> \l List of databases Name | Owner | Encoding -----------+----------+---------- galaxy | galaxy | LATIN9 hogart | hogart | LATIN9 postgres | postgres | LATIN9 template0 | postgres | LATIN9 template1 | postgres | LATIN9 (5 rows)
galaxy=> select * from history where history.id=5; id | create_time | update_time | user_id | n ame | hid_counter | deleted | purged | genome_build | importable | slug | published | importing
----+----------------------------+----------------------------+---------+-------
----------+-------------+---------+--------+--------------+------------+------+- ----------+----------- 5 | 2013-09-10 21:38:31.888226 | 2013-09-10 21:38:31.888237 | 1 | Unname d history | 1 | f | f | ? | f | | f | f (1 row)
psql -v 8.1.22
- sincerely,
Sergei