Re: [galaxy-dev] Sqlite to MYSQL conversion in galaxy

19 Feb 2010

      Good news, Roy,

Let us know if there's anything more we can do.

Thanks,
--nate

Roy Weckiewicz wrote:
...
Just created a new database again and tried it -- Seems like it worked ! 
Complete migration as far as I can tell:
Tables:  
Tables_in_galaxy-new
cloud_image
cloud_instance
cloud_provider
cloud_snapshot
cloud_store
cloud_uci
cloud_user_credentials
dataset
dataset_permissions
dataset_tag_association
default_history_permissions
default_user_permissions
event
form_definition
form_definition_current
form_values
galaxy_group
galaxy_session
galaxy_session_to_history
galaxy_user
group_role_association
history
history_annotation_association
history_dataset_association
history_dataset_association_annotation_association
history_dataset_association_display_at_authorizati...
history_dataset_association_tag_association
history_tag_association
history_user_share_association
implicitly_converted_dataset_association
job
job_external_output_metadata
job_parameter
job_to_input_dataset
job_to_output_dataset
job_to_output_library_dataset
library
library_dataset
library_dataset_dataset_association
library_dataset_dataset_association_permissions
library_dataset_dataset_info_association
library_dataset_permissions
library_folder
library_folder_info_association
library_folder_permissions
library_info_association
library_permissions
metadata_file
migrate_version
page
page_revision
page_tag_association
page_user_share_association
request
request_event
request_type
role
sample
sample_event
sample_state
stored_workflow
stored_workflow_annotation_association
stored_workflow_menu_entry
stored_workflow_tag_association
stored_workflow_user_share_connection
tag
user_action
user_address
user_group_association
user_preference
user_role_association
validation_error
visualization
visualization_revision
workflow
workflow_step
workflow_step_annotation_association
workflow_step_connection
workflow_step_tag_association
workflow_tag_association
So far so good !
Rgds,
Roy Weckiewicz.
On Fri, Feb 19, 2010 at 1:46 PM, Roy Weckiewicz <roystefan@gmail.com 
<mailto:roystefan@gmail.com>> wrote:
Hi Nate,
Sorry I had an empty database set the first time (which was giving
    me the revision error):
Part of my problem has been trying to convert the db from sqlite to
    mysql -- the closest I got using scripts was 40 tables in the database:
dataset
    dataset_permissions
    default_history_permissions
    default_user_permissions
    event
    form_definition_current
    galaxy_group
    galaxy_session
    galaxy_session_to_history
    galaxy_user
    group_role_association
    history
    history_dataset_association
    history_user_share_association
    implicitly_converted_dataset_association
    job
    job_external_output_metadata
    job_parameter
    job_to_input_dataset
    job_to_output_dataset
    library
    library_dataset
    library_dataset_dataset_association
    library_dataset_dataset_association_permissions
    library_dataset_permissions
    library_folder
    library_folder_permissions
    library_permissions
    metadata_file
    migrate_version
    role
    stored_workflow
    stored_workflow_menu_entry
    stored_workflow_user_share_connection
    user_group_association
    user_role_association
    validation_error
    workflow
    workflow_step
    workflow_step_connection
When I try to run it linked to the above database I get the
    following error:
galaxy.model.migrate.check DEBUG 2010-02-19 13:42:18,987
    MySQL_python egg successfully loaded for mysql dialect
    Traceback (most recent call last):
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/lib/galaxy/web/buildapp.py",
    line 61, in app_factory
        app = UniverseApplication( global_conf = global_conf, **kwargs )
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/lib/galaxy/app.py",
    line 28, in __init__
        create_or_verify_database( db_url,
    self.config.database_engine_options )
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/lib/galaxy/model/migrate/check.py",
    line 102, in create_or_verify_database
        % ( db_schema.version, migrate_repository.versions.latest ) )
    Exception: Your database has version '22' but this code expects
    version '39'.  Please backup your database and then migrate the
    schema by running 'sh manage_db.sh upgrade'.
So when I try to run the manage_db.sh I get:
22 -> 23...
Migration script to add columns for tracking whether pages are
    deleted and
    publicly accessible.
Traceback (most recent call last):
      File "./scripts/manage_db.py", line 45, in <module>
        main( repository='lib/galaxy/model/migrate', url=db_url )
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/shell.py",
    line 150, in main
        ret = command_func(**kwargs)
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py",
    line 221, in upgrade
        return _migrate(url, repository, version, upgrade=True, err=err,
    **opts)
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py",
    line 349, in _migrate
        schema.runchange(ver, change, changeset.step)
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/schema.py",
    line 184, in runchange
        change.run(self.engine, step)
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/script/py.py",
    line 101, in run
        func()
      File
    "lib/galaxy/model/migrate/versions/0023_page_published_and_deleted_columns.py",
    line 20, in upgrade
        Page_table = Table( "page", metadata, autoload=True )
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/schema.py",
    line 108, in __call__
        return type.__call__(self, name, metadata, *args, **kwargs)
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/schema.py",
    line 236, in __init__
        _bind_or_error(metadata).reflecttable(self,
    include_columns=include_columns)
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py",
    line 1265, in reflecttable
        self.dialect.reflecttable(conn, table, include_columns)
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/databases/mysql.py",
    line 1664, in reflecttable
        sql = self._show_create_table(connection, table, charset)
      File
    "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/databases/mysql.py",
    line 1835, in _show_create_table
        raise exc.NoSuchTableError(full_name)
    sqlalchemy.exc.NoSuchTableError: page
I guess my problem all along is not having the full table schema for
    use in mySQL.
Rgds,
Roy Weckiewicz
On Fri, Feb 19, 2010 at 1:33 PM, Nate Coraor <nate@bx.psu.edu
    <mailto:nate@bx.psu.edu>> wrote:
Roy Weckiewicz wrote:
Thanks again Nate,
I just tried the one you sent me - it seems I just have a
            database revision issue:
Traceback (most recent call last):
             File
            "/Users/rsw3284/Downloads/galaxy-0c07212afc00/lib/galaxy/web/buildapp.py",
            line 61, in app_factory
               app = UniverseApplication( global_conf = global_conf,
            **kwargs )
             File
            "/Users/rsw3284/Downloads/galaxy-0c07212afc00/lib/galaxy/app.py",
            line 28, in __init__
               create_or_verify_database( db_url,
            self.config.database_engine_options )
             File
            "/Users/rsw3284/Downloads/galaxy-0c07212afc00/lib/galaxy/model/migrate/check.py",
            line 72, in create_or_verify_database
               raise Exception( "Your database is older than hg revision
            1464:c7acaa1bb88f and will need to be updated manually" )
            Exception: Your database is older than hg revision
            1464:c7acaa1bb88f and will need to be updated manually
Hi Roy,
Is this the database you converted from sqlite?  Does it include
        the 'migrate_version' table?  Could you paste the output of
        'SHOW TABLES;'?
Thanks,
Rgds,
Roy Weckiewicz
On Fri, Feb 19, 2010 at 1:28 PM, Nate Coraor
            <nate@bx.psu.edu <mailto:nate@bx.psu.edu>
            <mailto:nate@bx.psu.edu <mailto:nate@bx.psu.edu>>> wrote:
Roy Weckiewicz wrote:
What's strange is that eventhough the file name on
            the egg is
                   MySQL*fat-ucs2.egg the error still reports the file
            in question
                   as MySQL*fat.egg -- I've tried renaming the egg to
            match the
                   original file, but it didn't seem to make a difference.
                   Furthermore, when I run.sh, the program will create a
            directory
                   in home called .python-eggs/ containing the _MySQL.so
            file,
                   among other things - Will I have to add a different
            URL to the
                   eggs.ini to keep it from re-downloading the old file?
Ah, sorry, that egg relies on some uncommitted patches.  I've
               rebuilt it using a clean checkout, could you try this one
            instead?:
http://www.bx.psu.edu/~nate/galaxy/MySQL_python-1.2.2_5.0.67_static-py2.5-ma...
Thanks,
               --nate
-- 
            Roy Weckiewicz
            Texas A&M University
-- 
    Roy Weckiewicz
    Texas A&M University
-- 
Roy Weckiewicz
Texas A&M University