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
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". 1) Creates Page and PageRevision tables 2) 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". 1) Creates Page and PageRevision tables 2) 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
[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
Hi Sergei,
Your current Galaxy database migration revision (13) is extremely old. How old was your previous Galaxy installation, and are you certain that the dump you created was complete, and fully restored?
To solve the original 'DECLARE CURSOR ... FOR UPDATE' problem, you'd need to update your version of PostgreSQL. 8.1.x is also very old, 9.1 or 9.2 are recommended.
--nate
On Sep 11, 2013, at 7:58 AM, hogart wrote:
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
Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at: http://lists.bx.psu.edu/
To search Galaxy mailing lists use the unified search at: http://galaxyproject.org/search/mailinglists/
Hi Nate, Yes, db schema is old, but the upgrading (manage_db.sh upgrade) to the current version resulted to the error, as mentioned above. I don't remember the release number of the previous version of Galaxy, but it was installed in the March this year. A didn't restored the dump yet, now I am playinig with the newly created database. In my newbie point of view, it seems like, that dropping of the old database (drop database galaxy; as superuser) was incomplete, - is it possible? Thanks for letting me know, I will try to upgrade the PostgeSQL (the current version is default version of CentOS 5.6).
On Sep 11, 2013, at 9:16 AM, hogart wrote:
Hi Nate, Yes, db schema is old, but the upgrading (manage_db.sh upgrade) to the current version resulted to the error, as mentioned above. I don't remember the release number of the previous version of Galaxy, but it was installed in the March this year. A didn't restored the dump yet, now I am playinig with the newly created database. In my newbie point of view, it seems like, that dropping of the old database (drop database galaxy; as superuser) was incomplete, - is it possible? Thanks for letting me know, I will try to upgrade the PostgeSQL (the current version is default version of CentOS 5.6).
Sorry, I missed the bit where you were starting with an empty database and not reloading your dump of the old one.
It does seem possible that your database is not empty on startup, as the username column should not already exist at the time that this migration script runs. When you drop and then create the database, Galaxy should create all the necessary tables and run through all of the migration scripts the first time that you start it. Is it doing this? If it thinks that the database is already at revision 13, I suspect the database is not empty when it starts up. Check your database_connection string in universe_wsgi.ini and make sure it is pointed at the correct database.
--nate
Actually, the example in my previous letter wasn't correct - I have tried to run run.sh and manage_db.sh scripts several times after creation of novel db, and the copy-paste is the result of the last attempt. So, indeed, the db galaxy was created in the first running, while warning messages from the last running. But now I drop the db again, and the issue is reproduced right after creation of the fresh db:
[galaxy@cluster galaxy-dist]$ sh run.sh <~! several messages are omitted !~> migrate.versioning.script.base DEBUG 2013-09-11 18:33:49,426 Loading script lib/galaxy/model/migrate/versions/0113_update_migrate_tools_table.py... migrate.versioning.script.base DEBUG 2013-09-11 18:33:49,426 Script lib/galaxy/model/migrate/versions/0113_update_migrate_tools_table.py loaded successfully migrate.versioning.script.base DEBUG 2013-09-11 18:33:49,426 Loading script lib/galaxy/model/migrate/versions/0114_update_migrate_tools_table_again.py... migrate.versioning.script.base DEBUG 2013-09-11 18:33:49,426 Script lib/galaxy/model/migrate/versions/0114_update_migrate_tools_table_again.py loaded successfully migrate.versioning.script.base DEBUG 2013-09-11 18:33:49,426 Loading script lib/galaxy/model/migrate/versions/0115_longer_user_password_field.py... migrate.versioning.script.base DEBUG 2013-09-11 18:33:49,426 Script lib/galaxy/model/migrate/versions/0115_longer_user_password_field.py loaded successfully migrate.versioning.repository DEBUG 2013-09-11 18:33:49,427 Repository lib/galaxy/model/migrate loaded successfully migrate.versioning.repository DEBUG 2013-09-11 18:33:49,427 Config: OrderedDict([('db_settings', OrderedDict([('__name__', 'db_settings'), ('repository_id', 'Galaxy'), ('version_table', 'migrate_version'), ('required_dbs', '[]')]))]) galaxy.model.migrate.check DEBUG 2013-09-11 18:33:49,433 psycopg2 egg successfully loaded for postgres dialect /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> galaxy.model.migrate.check INFO 2013-09-11 18:33:49,447 No database, initializing galaxy.model.migrate.check INFO 2013-09-11 18:33:49,477 Migrating 0 -> 1... galaxy.model.migrate.check INFO 2013-09-11 18:33:49,712 galaxy.model.migrate.check INFO 2013-09-11 18:33:49,712 Migrating 1 -> 2... galaxy.model.migrate.check INFO 2013-09-11 18:33:50,013 galaxy.model.migrate.check INFO 2013-09-11 18:33:50,014 Migrating 2 -> 3... galaxy.model.migrate.check INFO 2013-09-11 18:33:50,803 galaxy.model.migrate.check INFO 2013-09-11 18:33:50,803 Migrating 3 -> 4... galaxy.model.migrate.check INFO 2013-09-11 18:33:51,502 galaxy.model.migrate.check INFO 2013-09-11 18:33:51,502 Migrating 4 -> 5... galaxy.model.custom_types DEBUG 2013-09-11 18:33:51,556 Fixing a discrepancy concerning deleted shared history items. galaxy.model.custom_types DEBUG 2013-09-11 18:33:51,583 0 items affected, and restored. galaxy.model.custom_types DEBUG 2013-09-11 18:33:51,583 Time elapsed: 0.0264229774475 galaxy.model.custom_types DEBUG 2013-09-11 18:33:51,583 Fixing a discrepancy concerning cleaning up deleted history items shared before HDAs. galaxy.model.custom_types DEBUG 2013-09-11 18:33:51,586 0 items affected, and restored. galaxy.model.custom_types DEBUG 2013-09-11 18:33:51,586 Time elapsed: 0.00265407562256 galaxy.model.migrate.check INFO 2013-09-11 18:33:51,589 galaxy.model.migrate.check INFO 2013-09-11 18:33:51,589 Migrating 5 -> 6... galaxy.model.migrate.check INFO 2013-09-11 18:33:52,334 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:52,334 This migration script changes certain values in the history_dataset_association.extension galaxy.model.migrate.check INFO 2013-09-11 18:33:52,334 column, specifically 'qual' is chaged to be 'qual454'. galaxy.model.migrate.check INFO 2013-09-11 18:33:52,334 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:52,334 galaxy.model.migrate.check INFO 2013-09-11 18:33:52,335 Migrating 6 -> 7... galaxy.model.migrate.check INFO 2013-09-11 18:33:53,053 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:53,053 This migration script creates the new history_user_share_association table, and adds galaxy.model.migrate.check INFO 2013-09-11 18:33:53,053 a new boolean type column to the history table. This provides support for sharing galaxy.model.migrate.check INFO 2013-09-11 18:33:53,053 histories in the same way that workflows are shared. galaxy.model.migrate.check INFO 2013-09-11 18:33:53,053 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:53,053 galaxy.model.migrate.check INFO 2013-09-11 18:33:53,054 Migrating 7 -> 8... galaxy.model.migrate.check INFO 2013-09-11 18:33:53,969 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:53,970 This migration script adds the following new tables for supporting Galaxy forms: galaxy.model.migrate.check INFO 2013-09-11 18:33:53,970 1) form_definition_current galaxy.model.migrate.check INFO 2013-09-11 18:33:53,970 2) form_definition galaxy.model.migrate.check INFO 2013-09-11 18:33:53,970 3) form_values galaxy.model.migrate.check INFO 2013-09-11 18:33:53,970 4) request_type galaxy.model.migrate.check INFO 2013-09-11 18:33:53,970 5) request galaxy.model.migrate.check INFO 2013-09-11 18:33:53,970 6) sample galaxy.model.migrate.check INFO 2013-09-11 18:33:53,970 7) sample_state galaxy.model.migrate.check INFO 2013-09-11 18:33:53,971 8) sample_event galaxy.model.migrate.check INFO 2013-09-11 18:33:53,971 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:53,971 galaxy.model.migrate.check INFO 2013-09-11 18:33:53,971 Migrating 8 -> 9... galaxy.model.migrate.check INFO 2013-09-11 18:33:54,784 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:54,784 This migration script adds a new column to 2 tables: galaxy.model.migrate.check INFO 2013-09-11 18:33:54,785 1) a new boolean type column named 'submitted' to the 'request' table galaxy.model.migrate.check INFO 2013-09-11 18:33:54,785 2) a new string type column named 'bar_code' to the 'sample' table galaxy.model.migrate.check INFO 2013-09-11 18:33:54,785 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:54,785 galaxy.model.migrate.check INFO 2013-09-11 18:33:54,785 Migrating 9 -> 10... galaxy.model.migrate.check INFO 2013-09-11 18:33:55,607 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:55,607 This migration script adds the history_dataset_association_display_at_authorization table, which galaxy.model.migrate.check INFO 2013-09-11 18:33:55,608 allows 'private' datasets to be displayed at external sites without making them public. galaxy.model.migrate.check INFO 2013-09-11 18:33:55,608 galaxy.model.migrate.check INFO 2013-09-11 18:33:55,608 If using mysql, this script will display the following error, which is corrected in the next migration galaxy.model.migrate.check INFO 2013-09-11 18:33:55,608 script: history_dataset_association_display_at_authorization table failed: (OperationalError) galaxy.model.migrate.check INFO 2013-09-11 18:33:55,608 (1059, 'Identifier name 'ix_history_dataset_association_display_at_authorization_update_time' galaxy.model.migrate.check INFO 2013-09-11 18:33:55,608 is too long. galaxy.model.migrate.check INFO 2013-09-11 18:33:55,608 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:55,608 galaxy.model.migrate.check INFO 2013-09-11 18:33:55,609 Migrating 10 -> 11... galaxy.model.migrate.check INFO 2013-09-11 18:33:55,617 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:55,617 This script fixes a problem introduced in the previous migration script ( 9->10 ). MySQL galaxy.model.migrate.check INFO 2013-09-11 18:33:55,617 has a name length limit and thus the index 'ix_hdadaa_history_dataset_association_id' has galaxy.model.migrate.check INFO 2013-09-11 18:33:55,618 to be manually created. galaxy.model.migrate.check INFO 2013-09-11 18:33:55,618 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:55,618 galaxy.model.migrate.check INFO 2013-09-11 18:33:55,618 Migrating 11 -> 12... galaxy.model.migrate.check INFO 2013-09-11 18:33:56,467 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:56,467 This script adds a new user_address table that is currently only used with sample requests, where galaxy.model.migrate.check INFO 2013-09-11 18:33:56,467 a user can select from a list of his addresses to associate with the request. This script also galaxy.model.migrate.check INFO 2013-09-11 18:33:56,467 drops the request.submitted column which was boolean and replaces it with a request.state column galaxy.model.migrate.check INFO 2013-09-11 18:33:56,467 which is a string, allowing for more flexibility with request states. galaxy.model.migrate.check INFO 2013-09-11 18:33:56,467 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:56,467 galaxy.model.migrate.check INFO 2013-09-11 18:33:56,468 Migrating 12 -> 13... galaxy.model.migrate.check INFO 2013-09-11 18:33:57,362 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:57,362 This migration script eliminates all of the tables that were used for the 1st version of the galaxy.model.migrate.check INFO 2013-09-11 18:33:57,362 library templates where template fields and contents were each stored as a separate table row galaxy.model.migrate.check INFO 2013-09-11 18:33:57,362 in various library item tables. All of these tables are dropped in this script, eliminating all galaxy.model.migrate.check INFO 2013-09-11 18:33:57,362 existing template data. A total of 14 existing tables are dropped. galaxy.model.migrate.check INFO 2013-09-11 18:33:57,362 galaxy.model.migrate.check INFO 2013-09-11 18:33:57,362 We're now basing library templates on Galaxy forms, so field contents are stored as a jsonified galaxy.model.migrate.check INFO 2013-09-11 18:33:57,362 list in the form_values table. This script introduces the following 3 new association tables: galaxy.model.migrate.check INFO 2013-09-11 18:33:57,363 1) library_info_association galaxy.model.migrate.check INFO 2013-09-11 18:33:57,363 2) library_folder_info_association galaxy.model.migrate.check INFO 2013-09-11 18:33:57,363 3) library_dataset_dataset_info_association galaxy.model.migrate.check INFO 2013-09-11 18:33:57,363 galaxy.model.migrate.check INFO 2013-09-11 18:33:57,363 If using mysql, this script will throw an (OperationalError) exception due to a long index name galaxy.model.migrate.check INFO 2013-09-11 18:33:57,363 on the library_dataset_dataset_info_association table, which is OK because the script creates galaxy.model.migrate.check INFO 2013-09-11 18:33:57,363 an index with a shortened name. galaxy.model.migrate.check INFO 2013-09-11 18:33:57,363 ======================================== galaxy.model.migrate.check INFO 2013-09-11 18:33:57,364 galaxy.model.migrate.check INFO 2013-09-11 18:33:57,364 Migrating 13 -> 14... galaxy.model.migrate.check INFO 2013-09-11 18:33:58,217 galaxy.model.migrate.check INFO 2013-09-11 18:33:58,217 Migration script to add support for "Pages". galaxy.model.migrate.check INFO 2013-09-11 18:33:58,217 1) Creates Page and PageRevision tables galaxy.model.migrate.check INFO 2013-09-11 18:33:58,217 2) Adds username column to User table galaxy.model.migrate.check INFO 2013-09-11 18:33:58,217 galaxy.model.migrate.check INFO 2013-09-11 18:33:58,217 Traceback (most recent call last): File "/export/apps/galaxy/galaxy-dist/lib/galaxy/webapps/galaxy/buildapp.py", line 35, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/export/apps/galaxy/galaxy-dist/lib/galaxy/app.py", line 52, in __init__ create_or_verify_database( db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options, app=self ) File "/export/apps/galaxy/galaxy-dist/lib/galaxy/model/migrate/check.py", line 66, in create_or_verify_database migrate_to_current_version( engine, db_schema ) File "/export/apps/galaxy/galaxy-dist/lib/galaxy/model/migrate/check.py", line 124, in migrate_to_current_version 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 533, in create engine.execute(stmt) File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 2446, in execute return connection.execute(statement, *multiparams, **params) 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 params) File "/export/apps/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs2.egg/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement compiled_sql, distilled_params 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 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 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 cursor.execute(statement, parameters) ProgrammingError: (ProgrammingError) column "username" is of type character varying but expression is of type boolean HINT: You will need to rewrite or cast the expression. 'UPDATE galaxy_user SET username=%(username)s' {'username': False}
2013/9/11 Nate Coraor nate@bx.psu.edu
On Sep 11, 2013, at 9:16 AM, hogart wrote:
Hi Nate, Yes, db schema is old, but the upgrading (manage_db.sh upgrade) to the
current version resulted to the error, as mentioned above. I don't remember the release number of the previous version of Galaxy, but it was installed in the March this year. A didn't restored the dump yet, now I am playinig with the newly created database. In my newbie point of view, it seems like, that dropping of the old database (drop database galaxy; as superuser) was incomplete, - is it possible?
Thanks for letting me know, I will try to upgrade the PostgeSQL (the
current version is default version of CentOS 5.6).
Sorry, I missed the bit where you were starting with an empty database and not reloading your dump of the old one.
It does seem possible that your database is not empty on startup, as the username column should not already exist at the time that this migration script runs. When you drop and then create the database, Galaxy should create all the necessary tables and run through all of the migration scripts the first time that you start it. Is it doing this? If it thinks that the database is already at revision 13, I suspect the database is not empty when it starts up. Check your database_connection string in universe_wsgi.ini and make sure it is pointed at the correct database.
--nate
galaxy-dev@lists.galaxyproject.org
-
hogart -
Nate Coraor