Sqlite to MYSQL conversion in galaxy
Hello, I am trying to configure a local galaxy server using a MYSQL database instead of the standard sqlite3. I'm using OSX and have been attempting (unsuccessfully) to convert the sqlite database (universe.sql) to mysql format. Basically, I am in need of the database table schema for mysql. I noticed on your web page that you host a mysql server at: genome-mysql.cse.ucsc.edu -- would it be possible to do some sort of mysqldump or mysqlhotcopy in order to download the table schema, so that I could upload them into my local mysql installation? I've tried using Sqlite3 to dump the contents of universe.sql, but there are numerous syntax differences that need to be addressed before it will load successfully into my mysql database. Do you have any suggestions on how to either migrate the database from sqlite3 to MYSQL (via script or program), or create the mysql database from scratch and just import the correct table schema? Any help would be appreciated. MYSQL: Ver 14.14 Distrib 5.1.37, for apple-darwin9.5.0 (i386) using readline 5.1 Python 2.5.1 Rgds, Roy Weckiewicz. -- Roy Weckiewicz Texas A&M University
Hi Roy, What I would suggest is to dump the sqlite database to standard sql, and then creating a new instance of galaxy that has mysql as the configured database. When you run the setup and run script, you'll have a freshly instantiated galaxy with empty mysql tables. Then you can just import the standard sql file into the mysql database. Hope this helps, Kanwei On Thu, Feb 18, 2010 at 1:27 PM, Roy Weckiewicz <roystefan@gmail.com> wrote:
Hello, I am trying to configure a local galaxy server using a MYSQL database instead of the standard sqlite3. I'm using OSX and have been attempting (unsuccessfully) to convert the sqlite database (universe.sql) to mysql format. Basically, I am in need of the database table schema for mysql. I noticed on your web page that you host a mysql server at: genome-mysql.cse.ucsc.edu -- would it be possible to do some sort of mysqldump or mysqlhotcopy in order to download the table schema, so that I could upload them into my local mysql installation?
I've tried using Sqlite3 to dump the contents of universe.sql, but there are numerous syntax differences that need to be addressed before it will load successfully into my mysql database. Do you have any suggestions on how to either migrate the database from sqlite3 to MYSQL (via script or program), or create the mysql database from scratch and just import the correct table schema?
Any help would be appreciated.
MYSQL: Ver 14.14 Distrib 5.1.37, for apple-darwin9.5.0 (i386) using readline 5.1 Python 2.5.1
Rgds,
Roy Weckiewicz.
-- Roy Weckiewicz Texas A&M University
_______________________________________________ galaxy-dev mailing list galaxy-dev@lists.bx.psu.edu http://lists.bx.psu.edu/listinfo/galaxy-dev
Hi Kanwei, Thank you for your response. I've tried setting the database connection in the universe_wsgi.ini file to: database_connection = mysql:///galaxy?unix_socket=/private/tmp/mysql.sock (after a new download of galaxy and first running setup.sh) Is the format correct for a OSX system running OSX? -- When I try to 'sh run.sh' with this line included, I get the following errors: galaxy.model.migrate.check DEBUG 2010-02-18 18:15:06,092 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/web/buildapp.py", line 61, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/app.py", line 28, in __init__ create_or_verify_database( db_url, self.config.database_engine_options ) File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/model/migrate/check.py", line 50, in create_or_verify_database engine = create_engine( url, **engine_options ) File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/__init__.py", line 223, in create_engine return strategy.create(*args, **kwargs) File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/strategies.py", line 62, in create dbapi = dialect_cls.dbapi(**dbapi_args) File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/databases/mysql.py", line 1456, in dbapi import MySQLdb as mysql File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/MySQLdb/__init__.py", line 19, in <module> File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 7, in <module> File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 6, in __bootstrap__ ImportError: dlopen(/Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so, 2): Library not loaded: /afs/ bx.psu.edu/home/nate/work/galaxy_central/scripts/scramble/build/py2.4-macosx-10.3-fat-ucs2/MySQL_python/mysql/lib/mysql/libmysqlclient_r.15.dylib Referenced from: /Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so Reason: image not found The program fails here and no database file is ever created. If I run the program with the previously mentioned line commented, the sqlite database creation goes without a hitch, but once exported the syntax is much different from what mysql will accept. Any ideas? Rgds, Roy Weckiewicz On Thu, Feb 18, 2010 at 2:56 PM, Kanwei Li <kanwei@gmail.com> wrote:
Hi Roy,
What I would suggest is to dump the sqlite database to standard sql, and then creating a new instance of galaxy that has mysql as the configured database. When you run the setup and run script, you'll have a freshly instantiated galaxy with empty mysql tables. Then you can just import the standard sql file into the mysql database.
Hope this helps,
Kanwei
Hello, I am trying to configure a local galaxy server using a MYSQL database instead of the standard sqlite3. I'm using OSX and have been attempting (unsuccessfully) to convert the sqlite database (universe.sql) to mysql format. Basically, I am in need of the database table schema for mysql. I noticed on your web page that you host a mysql server at: genome-mysql.cse.ucsc.edu -- would it be possible to do some sort of mysqldump or mysqlhotcopy in order to download the table schema, so that I could upload them into my local mysql installation?
I've tried using Sqlite3 to dump the contents of universe.sql, but there are numerous syntax differences that need to be addressed before it will load successfully into my mysql database. Do you have any suggestions on how to either migrate the database from sqlite3 to MYSQL (via script or program), or create the mysql database from scratch and just import the correct
On Thu, Feb 18, 2010 at 1:27 PM, Roy Weckiewicz <roystefan@gmail.com> wrote: table
schema?
Any help would be appreciated.
MYSQL: Ver 14.14 Distrib 5.1.37, for apple-darwin9.5.0 (i386) using readline 5.1 Python 2.5.1
Rgds,
Roy Weckiewicz.
-- Roy Weckiewicz Texas A&M University
_______________________________________________ galaxy-dev mailing list galaxy-dev@lists.bx.psu.edu http://lists.bx.psu.edu/listinfo/galaxy-dev
-- Roy Weckiewicz Texas A&M University
Ah, I just tried it myself and it's because the mysql egg isn't being loaded properly. I believe we're still working on creating a statically linked mysql egg and it's not complete yet. I do suggest using postgres, which is very good and used for our production systems, and works on OS X. The connection strings look like: mysql://user:pass@localhost/test postgresql://user:pass@localhost/test Kanwei On Thu, Feb 18, 2010 at 7:25 PM, Roy Weckiewicz <roystefan@gmail.com> wrote:
Hi Kanwei, Thank you for your response.
I've tried setting the database connection in the universe_wsgi.ini file to: database_connection = mysql:///galaxy?unix_socket=/private/tmp/mysql.sock (after a new download of galaxy and first running setup.sh) Is the format correct for a OSX system running OSX? -- When I try to 'sh run.sh' with this line included, I get the following errors: galaxy.model.migrate.check DEBUG 2010-02-18 18:15:06,092 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/web/buildapp.py", line 61, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/app.py", line 28, in __init__ create_or_verify_database( db_url, self.config.database_engine_options ) File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/model/migrate/check.py", line 50, in create_or_verify_database engine = create_engine( url, **engine_options ) File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/__init__.py", line 223, in create_engine return strategy.create(*args, **kwargs) File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/strategies.py", line 62, in create dbapi = dialect_cls.dbapi(**dbapi_args) File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/databases/mysql.py", line 1456, in dbapi import MySQLdb as mysql File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/MySQLdb/__init__.py", line 19, in <module> File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 7, in <module> File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 6, in __bootstrap__ ImportError: dlopen(/Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so, 2): Library not loaded: /afs/bx.psu.edu/home/nate/work/galaxy_central/scripts/scramble/build/py2.4-macosx-10.3-fat-ucs2/MySQL_python/mysql/lib/mysql/libmysqlclient_r.15.dylib Referenced from: /Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so Reason: image not found The program fails here and no database file is ever created. If I run the program with the previously mentioned line commented, the sqlite database creation goes without a hitch, but once exported the syntax is much different from what mysql will accept. Any ideas?
Rgds,
Roy Weckiewicz
On Thu, Feb 18, 2010 at 2:56 PM, Kanwei Li <kanwei@gmail.com> wrote:
Hi Roy,
What I would suggest is to dump the sqlite database to standard sql, and then creating a new instance of galaxy that has mysql as the configured database. When you run the setup and run script, you'll have a freshly instantiated galaxy with empty mysql tables. Then you can just import the standard sql file into the mysql database.
Hope this helps,
Kanwei
On Thu, Feb 18, 2010 at 1:27 PM, Roy Weckiewicz <roystefan@gmail.com> wrote:
Hello, I am trying to configure a local galaxy server using a MYSQL database instead of the standard sqlite3. I'm using OSX and have been attempting (unsuccessfully) to convert the sqlite database (universe.sql) to mysql format. Basically, I am in need of the database table schema for mysql. I noticed on your web page that you host a mysql server at: genome-mysql.cse.ucsc.edu -- would it be possible to do some sort of mysqldump or mysqlhotcopy in order to download the table schema, so that I could upload them into my local mysql installation?
I've tried using Sqlite3 to dump the contents of universe.sql, but there are numerous syntax differences that need to be addressed before it will load successfully into my mysql database. Do you have any suggestions on how to either migrate the database from sqlite3 to MYSQL (via script or program), or create the mysql database from scratch and just import the correct table schema?
Any help would be appreciated.
MYSQL: Ver 14.14 Distrib 5.1.37, for apple-darwin9.5.0 (i386) using readline 5.1 Python 2.5.1
Rgds,
Roy Weckiewicz.
-- Roy Weckiewicz Texas A&M University
_______________________________________________ galaxy-dev mailing list galaxy-dev@lists.bx.psu.edu http://lists.bx.psu.edu/listinfo/galaxy-dev
-- Roy Weckiewicz Texas A&M University
I was looking for someone to test it, try placing this in eggs/py2.4-macosx-10.3-fat-ucs2: http://www.bx.psu.edu/~nate/galaxy/MySQL_python-1.2.2_5.0.67_static-py2.5-ma... --nate Kanwei Li wrote:
Ah, I just tried it myself and it's because the mysql egg isn't being loaded properly. I believe we're still working on creating a statically linked mysql egg and it's not complete yet. I do suggest using postgres, which is very good and used for our production systems, and works on OS X. The connection strings look like:
mysql://user:pass@localhost/test
postgresql://user:pass@localhost/test
Kanwei
On Thu, Feb 18, 2010 at 7:25 PM, Roy Weckiewicz <roystefan@gmail.com> wrote:
Hi Kanwei, Thank you for your response.
I've tried setting the database connection in the universe_wsgi.ini file to: database_connection = mysql:///galaxy?unix_socket=/private/tmp/mysql.sock (after a new download of galaxy and first running setup.sh) Is the format correct for a OSX system running OSX? -- When I try to 'sh run.sh' with this line included, I get the following errors: galaxy.model.migrate.check DEBUG 2010-02-18 18:15:06,092 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/web/buildapp.py", line 61, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/app.py", line 28, in __init__ create_or_verify_database( db_url, self.config.database_engine_options ) File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/model/migrate/check.py", line 50, in create_or_verify_database engine = create_engine( url, **engine_options ) File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/__init__.py", line 223, in create_engine return strategy.create(*args, **kwargs) File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/strategies.py", line 62, in create dbapi = dialect_cls.dbapi(**dbapi_args) File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/databases/mysql.py", line 1456, in dbapi import MySQLdb as mysql File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/MySQLdb/__init__.py", line 19, in <module> File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 7, in <module> File "/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 6, in __bootstrap__ ImportError: dlopen(/Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so, 2): Library not loaded: /afs/bx.psu.edu/home/nate/work/galaxy_central/scripts/scramble/build/py2.4-macosx-10.3-fat-ucs2/MySQL_python/mysql/lib/mysql/libmysqlclient_r.15.dylib Referenced from: /Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so Reason: image not found The program fails here and no database file is ever created. If I run the program with the previously mentioned line commented, the sqlite database creation goes without a hitch, but once exported the syntax is much different from what mysql will accept. Any ideas?
Rgds,
Roy Weckiewicz
On Thu, Feb 18, 2010 at 2:56 PM, Kanwei Li <kanwei@gmail.com> wrote:
Hi Roy,
What I would suggest is to dump the sqlite database to standard sql, and then creating a new instance of galaxy that has mysql as the configured database. When you run the setup and run script, you'll have a freshly instantiated galaxy with empty mysql tables. Then you can just import the standard sql file into the mysql database.
Hope this helps,
Kanwei
On Thu, Feb 18, 2010 at 1:27 PM, Roy Weckiewicz <roystefan@gmail.com> wrote:
Hello, I am trying to configure a local galaxy server using a MYSQL database instead of the standard sqlite3. I'm using OSX and have been attempting (unsuccessfully) to convert the sqlite database (universe.sql) to mysql format. Basically, I am in need of the database table schema for mysql. I noticed on your web page that you host a mysql server at: genome-mysql.cse.ucsc.edu -- would it be possible to do some sort of mysqldump or mysqlhotcopy in order to download the table schema, so that I could upload them into my local mysql installation?
I've tried using Sqlite3 to dump the contents of universe.sql, but there are numerous syntax differences that need to be addressed before it will load successfully into my mysql database. Do you have any suggestions on how to either migrate the database from sqlite3 to MYSQL (via script or program), or create the mysql database from scratch and just import the correct table schema?
Any help would be appreciated.
MYSQL: Ver 14.14 Distrib 5.1.37, for apple-darwin9.5.0 (i386) using readline 5.1 Python 2.5.1
Rgds,
Roy Weckiewicz.
-- Roy Weckiewicz Texas A&M University
_______________________________________________ galaxy-dev mailing list galaxy-dev@lists.bx.psu.edu http://lists.bx.psu.edu/listinfo/galaxy-dev
-- Roy Weckiewicz Texas A&M University
Thanks Nate, I placed the file in the eggs directory and removed the previous one, but still seem to be getting the same errors: galaxy.model.migrate.check DEBUG 2010-02-19 12:48:02,077 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 50, in create_or_verify_database engine = create_engine( url, **engine_options ) File "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/__init__.py", line 223, in create_engine return strategy.create(*args, **kwargs) File "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/strategies.py", line 62, in create dbapi = dialect_cls.dbapi(**dbapi_args) File "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/databases/mysql.py", line 1456, in dbapi import MySQLdb as mysql File "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/MySQLdb/__init__.py", line 19, in <module> File "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 7, in <module> File "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 6, in __bootstrap__ ImportError: dlopen(/Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so, 2): Library not loaded: /afs/ bx.psu.edu/home/nate/work/galaxy_central/scripts/scramble/build/py2.4-macosx-10.3-fat-ucs2/MySQL_python/mysql/lib/mysql/libmysqlclient_r.15.dylib Referenced from: /Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so Reason: image not found 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? Rgds, Roy Weckiewicz. On Thu, Feb 18, 2010 at 8:34 PM, Nate Coraor <nate@bx.psu.edu> wrote:
I was looking for someone to test it, try placing this in eggs/py2.4-macosx-10.3-fat-ucs2:
http://www.bx.psu.edu/~nate/galaxy/MySQL_python-1.2.2_5.0.67_static-py2.5-ma...
--nate
Kanwei Li wrote:
Ah, I just tried it myself and it's because the mysql egg isn't being loaded properly. I believe we're still working on creating a statically linked mysql egg and it's not complete yet. I do suggest using postgres, which is very good and used for our production systems, and works on OS X. The connection strings look like:
mysql://user:pass@localhost/test
postgresql://user:pass@localhost/test
Kanwei
On Thu, Feb 18, 2010 at 7:25 PM, Roy Weckiewicz <roystefan@gmail.com> wrote:
Hi Kanwei, Thank you for your response.
I've tried setting the database connection in the universe_wsgi.ini file to: database_connection = mysql:///galaxy?unix_socket=/private/tmp/mysql.sock (after a new download of galaxy and first running setup.sh) Is the format correct for a OSX system running OSX? -- When I try to 'sh run.sh' with this line included, I get the following errors: galaxy.model.migrate.check DEBUG 2010-02-18 18:15:06,092 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/web/buildapp.py", line 61, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/app.py", line 28, in __init__ create_or_verify_database( db_url, self.config.database_engine_options ) File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/model/migrate/check.py", line 50, in create_or_verify_database engine = create_engine( url, **engine_options ) File
"/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/__init__.py", line 223, in create_engine return strategy.create(*args, **kwargs) File
"/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/strategies.py", line 62, in create dbapi = dialect_cls.dbapi(**dbapi_args) File
"/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/databases/mysql.py", line 1456, in dbapi import MySQLdb as mysql File
"/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/MySQLdb/__init__.py", line 19, in <module> File
"/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 7, in <module> File
"/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 6, in __bootstrap__ ImportError:
dlopen(/Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so, 2): Library not loaded: /afs/ bx.psu.edu/home/nate/work/galaxy_central/scripts/scramble/build/py2.4-macosx-10.3-fat-ucs2/MySQL_python/mysql/lib/mysql/libmysqlclient_r.15.dylib Referenced from:
/Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so Reason: image not found The program fails here and no database file is ever created. If I run the program with the previously mentioned line commented, the sqlite database creation goes without a hitch, but once exported the syntax is much different from what mysql will accept. Any ideas?
Rgds,
Roy Weckiewicz
On Thu, Feb 18, 2010 at 2:56 PM, Kanwei Li <kanwei@gmail.com> wrote:
Hi Roy,
What I would suggest is to dump the sqlite database to standard sql, and then creating a new instance of galaxy that has mysql as the configured database. When you run the setup and run script, you'll have a freshly instantiated galaxy with empty mysql tables. Then you can just import the standard sql file into the mysql database.
Hope this helps,
Kanwei
On Thu, Feb 18, 2010 at 1:27 PM, Roy Weckiewicz <roystefan@gmail.com> wrote:
Hello, I am trying to configure a local galaxy server using a MYSQL database instead of the standard sqlite3. I'm using OSX and have been attempting (unsuccessfully) to convert the sqlite database (universe.sql) to mysql format. Basically, I am in need of the database table schema for mysql. I noticed on your web page that you host a mysql server at: genome-mysql.cse.ucsc.edu -- would it be possible to do some sort of mysqldump or mysqlhotcopy in order to download the table schema, so that I could upload them into my local mysql installation?
I've tried using Sqlite3 to dump the contents of universe.sql, but there are numerous syntax differences that need to be addressed before it will load successfully into my mysql database. Do you have any suggestions on how to either migrate the database from sqlite3 to MYSQL (via script or program), or create the mysql database from scratch and just import the correct table schema?
Any help would be appreciated.
MYSQL: Ver 14.14 Distrib 5.1.37, for apple-darwin9.5.0 (i386) using readline 5.1 Python 2.5.1
Rgds,
Roy Weckiewicz.
-- Roy Weckiewicz Texas A&M University
_______________________________________________ galaxy-dev mailing list galaxy-dev@lists.bx.psu.edu http://lists.bx.psu.edu/listinfo/galaxy-dev
-- Roy Weckiewicz Texas A&M University
-- Roy Weckiewicz Texas A&M University
Ok, it appears I was having a file name issue -- this is corrected now. Now I receive the following error: galaxy.model.migrate.check DEBUG 2010-02-19 13:25:06,473 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 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 Any ideas? Rgds, Roy Weckiewicz On Fri, Feb 19, 2010 at 12:59 PM, Roy Weckiewicz <roystefan@gmail.com>wrote:
Thanks Nate,
I placed the file in the eggs directory and removed the previous one, but still seem to be getting the same errors:
galaxy.model.migrate.check DEBUG 2010-02-19 12:48:02,077 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 50, in create_or_verify_database engine = create_engine( url, **engine_options ) File "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/__init__.py", line 223, in create_engine return strategy.create(*args, **kwargs) File "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/strategies.py", line 62, in create dbapi = dialect_cls.dbapi(**dbapi_args) File "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/databases/mysql.py", line 1456, in dbapi import MySQLdb as mysql File "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/MySQLdb/__init__.py", line 19, in <module> File "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 7, in <module> File "/Users/rsw3284/Downloads/galaxy-0c07212afc00/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 6, in __bootstrap__ ImportError: dlopen(/Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so, 2): Library not loaded: /afs/ bx.psu.edu/home/nate/work/galaxy_central/scripts/scramble/build/py2.4-macosx-10.3-fat-ucs2/MySQL_python/mysql/lib/mysql/libmysqlclient_r.15.dylib Referenced from: /Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so Reason: image not found
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?
Rgds,
Roy Weckiewicz.
On Thu, Feb 18, 2010 at 8:34 PM, Nate Coraor <nate@bx.psu.edu> wrote:
I was looking for someone to test it, try placing this in eggs/py2.4-macosx-10.3-fat-ucs2:
http://www.bx.psu.edu/~nate/galaxy/MySQL_python-1.2.2_5.0.67_static-py2.5-ma...
--nate
Kanwei Li wrote:
Ah, I just tried it myself and it's because the mysql egg isn't being loaded properly. I believe we're still working on creating a statically linked mysql egg and it's not complete yet. I do suggest using postgres, which is very good and used for our production systems, and works on OS X. The connection strings look like:
mysql://user:pass@localhost/test
postgresql://user:pass@localhost/test
Kanwei
On Thu, Feb 18, 2010 at 7:25 PM, Roy Weckiewicz <roystefan@gmail.com> wrote:
Hi Kanwei, Thank you for your response.
I've tried setting the database connection in the universe_wsgi.ini file to: database_connection = mysql:///galaxy?unix_socket=/private/tmp/mysql.sock (after a new download of galaxy and first running setup.sh) Is the format correct for a OSX system running OSX? -- When I try to 'sh run.sh' with this line included, I get the following errors: galaxy.model.migrate.check DEBUG 2010-02-18 18:15:06,092 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/web/buildapp.py", line 61, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/app.py", line 28, in __init__ create_or_verify_database( db_url, self.config.database_engine_options ) File "/Users/rsw3284/Downloads/galaxy-NEW/lib/galaxy/model/migrate/check.py", line 50, in create_or_verify_database engine = create_engine( url, **engine_options ) File
"/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/__init__.py", line 223, in create_engine return strategy.create(*args, **kwargs) File
"/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/strategies.py", line 62, in create dbapi = dialect_cls.dbapi(**dbapi_args) File
"/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-noplatform/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/databases/mysql.py", line 1456, in dbapi import MySQLdb as mysql File
"/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/MySQLdb/__init__.py", line 19, in <module> File
"/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 7, in <module> File
"/Users/rsw3284/Downloads/galaxy-NEW/eggs/py2.5-macosx-10.3-fat-ucs2/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg/_mysql.py", line 6, in __bootstrap__ ImportError:
dlopen(/Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so, 2): Library not loaded: /afs/ bx.psu.edu/home/nate/work/galaxy_central/scripts/scramble/build/py2.4-macosx-10.3-fat-ucs2/MySQL_python/mysql/lib/mysql/libmysqlclient_r.15.dylib Referenced from:
/Users/rsw3284/.python-eggs/MySQL_python-1.2.2_5.0.67_static-py2.5-macosx-10.3-fat.egg-tmp/_mysql.so Reason: image not found The program fails here and no database file is ever created. If I run the program with the previously mentioned line commented, the sqlite database creation goes without a hitch, but once exported the syntax is much different from what mysql will accept. Any ideas?
Rgds,
Roy Weckiewicz
On Thu, Feb 18, 2010 at 2:56 PM, Kanwei Li <kanwei@gmail.com> wrote:
Hi Roy,
What I would suggest is to dump the sqlite database to standard sql, and then creating a new instance of galaxy that has mysql as the configured database. When you run the setup and run script, you'll have a freshly instantiated galaxy with empty mysql tables. Then you can just import the standard sql file into the mysql database.
Hope this helps,
Kanwei
On Thu, Feb 18, 2010 at 1:27 PM, Roy Weckiewicz <roystefan@gmail.com> wrote:
Hello, I am trying to configure a local galaxy server using a MYSQL database instead of the standard sqlite3. I'm using OSX and have been attempting (unsuccessfully) to convert the sqlite database (universe.sql) to mysql format. Basically, I am in need of the database table schema for mysql. I noticed on your web page that you host a mysql server at: genome-mysql.cse.ucsc.edu -- would it be possible to do some sort of mysqldump or mysqlhotcopy in order to download the table schema, so that I could upload them into my local mysql installation?
I've tried using Sqlite3 to dump the contents of universe.sql, but there are numerous syntax differences that need to be addressed before it will load successfully into my mysql database. Do you have any suggestions on how to either migrate the database from sqlite3 to MYSQL (via script or program), or create the mysql database from scratch and just import the correct table schema?
Any help would be appreciated.
MYSQL: Ver 14.14 Distrib 5.1.37, for apple-darwin9.5.0 (i386) using readline 5.1 Python 2.5.1
Rgds,
Roy Weckiewicz.
-- Roy Weckiewicz Texas A&M University
_______________________________________________ galaxy-dev mailing list galaxy-dev@lists.bx.psu.edu http://lists.bx.psu.edu/listinfo/galaxy-dev
-- Roy Weckiewicz Texas A&M University
-- Roy Weckiewicz Texas A&M University
-- Roy Weckiewicz Texas A&M University
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
participants (3)
-
Kanwei Li
-
Nate Coraor
-
Roy Weckiewicz