To keep the list in the loop, I'm sending this response that I wrote to Jeremy, with some of our system's identifying information stripped out. 

dave

-- 
David O'Connor
http://labs.pathology.wisc.edu/oconnor
ph: 608-301-5710

Forwarded message:

From: David O'Connor <david.h.oconnor@gmail.com>
To: Jeremy Goecks <jeremy.goecks@emory.edu>
Cc: Simon Lank <Simon.Lank@gmail.com>, galaxy-dev@bx.psu.edu
Date: Wednesday, April 18, 2012 10:01:28 AM
Subject: Re: [galaxy-dev] Potential database corruption with local galaxy instance

Hi Jeremy,

Thanks for the troubleshooting help. I've done what you've suggested and embedded the responses below:
SELECT * FROM migrate_version;

What is the version? Also, for completeness, what database are you using?
repository_id: Galaxy
repository_path: lib/galaxy/model/migrate
version: 93 

Something else you can try is downgrading your database revision and then upgrading to see if you can find the problem. From a command line in your galaxy directory, do this:

% sh manage_db.sh downgrade 92
% sh manage_db.sh upgrade
The downgrade to 92 and upgrade created job.params.  

You may want to back up your database before trying anything to ensure that you can rollback if something goes wrong.

J.
Unfortunately, we are still getting errors about duplicate key values. The debug output when I try to export a history to a file is shown below my signature. Is there anything that was updated recently that would change primary keys? Thanks again for your help, I'm not really sure why we are suddenly encountering these problems after running a stable local Galaxy instance for nearly two years. 

Thanks,

dave


Server Error

URL: <snip>
Module paste.exceptions.errormiddleware:143 in __call__
>>  app_iter = self.application(environ, start_response)
Module paste.debug.prints:98 in __call__
>>  environ, self.app)
Module paste.wsgilib:539 in intercept_output
>>  app_iter = application(environ, replacement_start_response)
Module paste.recursive:80 in __call__
>>  return self.application(environ, start_response)
Module paste.httpexceptions:632 in __call__
>>  return self.application(environ, start_response)
Module galaxy.web.framework.base:160 in __call__
>>  body = method( trans, **kwargs )
Module galaxy.web.controllers.history:679 in export_archive
>>  history_exp_tool.execute( trans, incoming = params, set_output_hid = True )
Module galaxy.tools:1661 in execute
>>  return self.tool_action.execute( self, trans, incoming=incoming, set_output_hid=set_output_hid, history=history,**kwargs )
Module galaxy.tools.actions.history_imp_exp:121 in execute
>>  trans.sa_session.flush()
Module sqlalchemy.orm.scoping:127 in do
>>  return getattr(self.registry(), name)(*args, **kwargs)
Module sqlalchemy.orm.session:1356 in flush
>>  self._flush(objects)
Module sqlalchemy.orm.session:1434 in _flush
>>  flush_context.execute()
Module sqlalchemy.orm.unitofwork:261 in execute
>>  UOWExecutor().execute(self, tasks)
Module sqlalchemy.orm.unitofwork:753 in execute
>>  self.execute_save_steps(trans, task)
Module sqlalchemy.orm.unitofwork:768 in execute_save_steps
>>  self.save_objects(trans, task)
Module sqlalchemy.orm.unitofwork:759 in save_objects
>>  task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
Module sqlalchemy.orm.mapper:1424 in _save_obj
>>  c = connection.execute(statement.values(value_params), params)
Module sqlalchemy.engine.base:824 in execute
>>  return Connection.executors[c](self, object, multiparams, params)
Module sqlalchemy.engine.base:874 in _execute_clauseelement
>>  return self.__execute_context(context)
Module sqlalchemy.engine.base:896 in __execute_context
>>  self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context)
Module sqlalchemy.engine.base:950 in _cursor_execute
>>  self._handle_dbapi_exception(e, statement, parameters, cursor, context)
Module sqlalchemy.engine.base:931 in _handle_dbapi_exception
>>  raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
IntegrityError: (IntegrityError) duplicate key value violates unique constraint "job_export_history_archive_pkey" 'INSERT INTO job_export_history_archive (id, job_id, history_id, dataset_id, compressed, history_attrs_filename, datasets_attrs_filename, jobs_attrs_filename) VALUES (%(id)s, %(job_id)s, %(history_id)s, %(dataset_id)s, %(compressed)s, %(history_attrs_filename)s, %(datasets_attrs_filename)s, %(jobs_attrs_filename)s)' {'job_id': 142241L, 'jobs_attrs_filename': [path to galaxy]/galaxy_dist/database/tmp/tmpxvS9aW/tmpbAz3Bx', 'history_id': 2320, 'datasets_attrs_filename': '[path to galaxy]/galaxy_dist/database/tmp/tmpxvS9aW/tmpsYUMXS', 'history_attrs_filename': '[path to galaxy]/galaxy_dist/database/tmp/tmpxvS9aW/tmplIz1m6', 'dataset_id': 185986L, 'id': 2L, 'compressed': True}
extra data

CGI Variables
CONTENT_LENGTH'0'
HTTP_ACCEPT'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8'
HTTP_ACCEPT_CHARSET'ISO-8859-1,utf-8;q=0.7,*;q=0.3'
HTTP_ACCEPT_ENCODING'gzip,deflate,sdch'
HTTP_ACCEPT_LANGUAGE'en-US,en;q=0.8'
HTTP_CONNECTION'keep-alive'
HTTP_COOKIE'__utma=66120872.977517160.1310045015.1325861530.1330104947.7;__utmz=66120872.1311045198.4.4.utmcsr=news.wisc.edu|utmccn=(referral)|utmcmd=referral|utmcct=/19571; __unam=a09e0f7-1312ec609b3-18b6bbaa-11; _saml_idp=aHR0cHM6Ly9sb2dpbi53aXNjLmVkdS9pZHAvc2hpYmJvbGV0aA%3D%3D+;galaxysession=c6ca0ddb55be603a4f09296d70d12dd2ed57e5d59af9de061cbc7c473ecbb378aecb2e3d2b895568'
HTTP_HOST'
<snip>
'
HTTP_REFERER'<snip>'
HTTP_USER_AGENT'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_3) AppleWebKit/535.19 (KHTML, like Gecko) Chrome/18.0.1025.162 Safari/535.19'
PATH_INFO'/history/export_archive'
REMOTE_ADDR'192.168.1.47'
REQUEST_METHOD'GET'
SERVER_NAME'0.0.0.0'
SERVER_PORT'8080'
SERVER_PROTOCOL'HTTP/1.1'

Configuration
__file__'[path to galaxy]/galaxy_dist/universe_wsgi.ini'
admin_users'
<snip>'
allow_library_path_paste'True'
allow_user_creation'False'
allow_user_deletion'True'
database_connection'
<snip>
'
database_engine_option_max_overflow'100'
database_engine_option_pool_size'100'
datatypes_config_file'datatypes_conf.xml'
debug'True'
enable_api'True'
enable_job_recovery'False'
enable_pages'True'
enable_tracks'True'
file_path'database/files'
here'
[path to galaxy]
/galaxy_dist'
library_import_dir'<Msnip>'
local_job_queue_workers'25'
new_file_path'database/tmp'
require_login'True'
smtp_server'<snip>'
static_cache_time'360'
static_dir'[path to galaxy]/galaxy_dist/static/'
static_enabled'True'
static_favicon_dir'[path to galaxy]/galaxy_dist/static/favicon.ico'
static_images_dir'
[path to galaxy]
/galaxy_dist/static/images'
static_scripts_dir'[path to galaxy]/galaxy_dist/static/scripts/'
static_style_dir'
[path to galaxy]
/galaxy_dist/static/june_2007_style/blue'
tool_config_file'tool_conf.xml'
tool_data_path'tool-data'
tool_path'tools'
use_interactive'False'
user_library_import_dir'[path to galaxy]/galaxy_dist/database/directory_upload'

WSGI Variables
application<paste.debug.prints.PrintDebugMiddleware object at 0x4cecfd0>
paste.cookies(<SimpleCookie: __unam='a09e0f7-1312ec609b3-18b6bbaa-11' __utma='66120872.977517160.1310045015.1325861530.1330104947.7' __utmz='66120872.1311045198.4.4.utmcsr=news.wisc.edu|utmccn=(referral)|utmcmd=referral|utmcct=/19571' _saml_idp='aHR0cHM6Ly9sb2dpbi53aXNjLmVkdS9pZHAvc2hpYmJvbGV0aA%3D%3D+' galaxysession='c6ca0ddb55be603a4f09296d70d12dd2ed57e5d59af9de061cbc7c473ecbb378aecb2e3d2b895568'>, '__utma=66120872.977517160.1310045015.1325861530.1330104947.7;__utmz=66120872.1311045198.4.4.utmcsr=news.wisc.edu|utmccn=(referral)|utmcmd=referral|utmcct=/19571; __unam=a09e0f7-1312ec609b3-18b6bbaa-11; _saml_idp=aHR0cHM6Ly9sb2dpbi53aXNjLmVkdS9pZHAvc2hpYmJvbGV0aA%3D%3D+;galaxysession=c6ca0ddb55be603a4f09296d70d12dd2ed57e5d59af9de061cbc7c473ecbb378aecb2e3d2b895568')
paste.expected_exceptions[<class 'paste.httpexceptions.HTTPException'>]
paste.httpexceptions<paste.httpexceptions.HTTPExceptionHandler object at 0x4cd8430>
paste.httpserver.thread_pool<paste.httpserver.ThreadPool object at 0x1205970>
paste.printdebug_listeners[<cStringIO.StringO object at 0x4bc5ac0>, <open file '<stderr>', mode 'w' at 0x350d0>]
paste.recursive.forward<paste.recursive.Forwarder from />
paste.recursive.include<paste.recursive.Includer from />
paste.recursive.include_app_iter<paste.recursive.IncluderAppIter from />
paste.recursive.script_name''
paste.remove_printdebug<function remove_printdebug at 0x4b939b0>
paste.throw_errorsTrue
webob._parsed_query_vars(MultiDict([]), '')
wsgi process'Multithreaded'


full traceback
URL: <snip>
Module paste.exceptions.errormiddleware:143 in __call__
>>  app_iter = self.application(environ, start_response)
Module paste.debug.prints:98 in __call__
>>  environ, self.app)
Module paste.wsgilib:539 in intercept_output
>>  app_iter = application(environ, replacement_start_response)
Module paste.recursive:80 in __call__
>>  return self.application(environ, start_response)
Module paste.httpexceptions:632 in __call__
>>  return self.application(environ, start_response)
Module galaxy.web.framework.base:160 in __call__
>>  body = method( trans, **kwargs )
Module galaxy.web.controllers.history:679 in export_archive
>>  history_exp_tool.execute( trans, incoming = params, set_output_hid = True )
Module galaxy.tools:1661 in execute
>>  return self.tool_action.execute( self, trans, incoming=incoming, set_output_hid=set_output_hid, history=history,**kwargs )
Module galaxy.tools.actions.history_imp_exp:121 in execute
>>  trans.sa_session.flush()
Module sqlalchemy.orm.scoping:127 in do
>>  return getattr(self.registry(), name)(*args, **kwargs)
Module sqlalchemy.orm.session:1356 in flush
>>  self._flush(objects)
Module sqlalchemy.orm.session:1434 in _flush
>>  flush_context.execute()
Module sqlalchemy.orm.unitofwork:261 in execute
>>  UOWExecutor().execute(self, tasks)
Module sqlalchemy.orm.unitofwork:753 in execute
>>  self.execute_save_steps(trans, task)
Module sqlalchemy.orm.unitofwork:768 in execute_save_steps
>>  self.save_objects(trans, task)
Module sqlalchemy.orm.unitofwork:759 in save_objects
>>  task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
Module sqlalchemy.orm.mapper:1424 in _save_obj
>>  c = connection.execute(statement.values(value_params), params)
Module sqlalchemy.engine.base:824 in execute
>>  return Connection.executors[c](self, object, multiparams, params)
Module sqlalchemy.engine.base:874 in _execute_clauseelement
>>  return self.__execute_context(context)
Module sqlalchemy.engine.base:896 in __execute_context
>>  self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context)
Module sqlalchemy.engine.base:950 in _cursor_execute
>>  self._handle_dbapi_exception(e, statement, parameters, cursor, context)
Module sqlalchemy.engine.base:931 in _handle_dbapi_exception
>>  raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
IntegrityError: (IntegrityError) duplicate key value violates unique constraint "job_export_history_archive_pkey" 'INSERT INTO job_export_history_archive (id, job_id, history_id, dataset_id, compressed, history_attrs_filename, datasets_attrs_filename, jobs_attrs_filename) VALUES (%(id)s, %(job_id)s, %(history_id)s, %(dataset_id)s, %(compressed)s, %(history_attrs_filename)s, %(datasets_attrs_filename)s, %(jobs_attrs_filename)s)' {'job_id': 142241L, 'jobs_attrs_filename': '[path to galaxy]/galaxy_dist/database/tmp/tmpxvS9aW/tmpbAz3Bx', 'history_id': 2320, 'datasets_attrs_filename':[path to galaxy]/galaxy_dist/database/tmp/tmpxvS9aW/tmpsYUMXS', 'history_attrs_filename': '[path to galaxy]/galaxy_dist/database/tmp/tmpxvS9aW/tmplIz1m6', 'dataset_id': 185986L, 'id': 2L, 'compressed': True}