Hi Freerk, Can you be more specific about the problems that you're seeing? Also, please continue to cc galaxy-dev as you're more likely to get help with your issues since it's going to a larger audience. Thanks, J. On Mar 11, 2010, at 10:42 AM, Dijk, F van wrote:
Hi Jeremy,
Thanks for the fast replay. The first issue is solved, a new one occured during the second point. When I migrate the sqlite database to SQL and try to import it into the MySQL database some errors occur. MySQL is complaining about the syntax used in the SQLdump. We are using the following MySQL version: mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (x86_64) using readline 5.2 I also read the info on the link you provided in your answer, but this won't solve this problem I think. So, do you have a solution?
Sincerely,
Freerk van Dijk
Van: Jeremy Goecks [mailto:jeremy.goecks@emory.edu] Verzonden: donderdag 11 maart 2010 15:25 Aan: Dijk, F van CC: galaxy-dev@bx.psu.edu Onderwerp: Re: [galaxy-dev] Database migration problem
Hi Freerk,
When we change the databaseconnection to "mysql:///galaxy?unix_socket=/var/run/mysqld/mysqld.sock" in the universe_wsgi.ini file and run Galaxy we receive an error like:
"Access denied for user 'root'@'localhost' (using password: NO)") None None
So is there an option somewhere which we also have to change to make the connection work?
You need to specify a username and password to access your database. Try this URL:
mysql://user:password@localhost/galaxy?unix_socket=/var/lib/mysql/mysql.sock (exchanging 'user' and 'password' according to your settings)
And question two: Do we need to migrate the content from the sqlite database to the MySQL database by hand or does this happen automatically?
You need to do the migration manually. Kanwei provided a nice solution to this problem a while back:
http://lists.bx.psu.edu/pipermail/galaxy-dev/2010-February/001996.html
-- 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. --
Good luck and let us know if you have any more problems.
J.
De inhoud van dit bericht is vertrouwelijk en alleen bestemd voor de geadresseerde(n). Anderen dan de geadresseerde(n) mogen geen gebruik maken van dit bericht, het niet openbaar maken of op enige wijze verspreiden of vermenigvuldigen. Het UMCG kan niet aansprakelijk gesteld worden voor een incomplete aankomst of vertraging van dit verzonden bericht.
The contents of this message are confidential and only intended for the eyes of the addressee(s). Others than the addressee(s) are not allowed to use this message, to make it public or to distribute or multiply this message in any way. The UMCG cannot be held responsible for incomplete reception or delay of this transferred message.
Hi, The syntax generated in the output generates errors. They look like this: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANSACTION' at line 1 and ERROR 1059 (42000) at line 77132: Identifier name 'ix_history_dataset_association_display_at_authorization_history_dataset _association_id' is too long I hope this can be solved. Sincerely, Freerk ________________________________ Van: Jeremy Goecks [mailto:jeremy.goecks@emory.edu] Verzonden: donderdag 11 maart 2010 16:50 Aan: Dijk, F van; galaxy-dev Onderwerp: Re: [galaxy-dev] Database migration problem Hi Freerk, Can you be more specific about the problems that you're seeing? Also, please continue to cc galaxy-dev as you're more likely to get help with your issues since it's going to a larger audience. Thanks, J. On Mar 11, 2010, at 10:42 AM, Dijk, F van wrote: Hi Jeremy, Thanks for the fast replay. The first issue is solved, a new one occured during the second point. When I migrate the sqlite database to SQL and try to import it into the MySQL database some errors occur. MySQL is complaining about the syntax used in the SQLdump. We are using the following MySQL version: mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (x86_64) using readline 5.2 I also read the info on the link you provided in your answer, but this won't solve this problem I think. So, do you have a solution? Sincerely, Freerk van Dijk ________________________________ Van: Jeremy Goecks [mailto:jeremy.goecks@emory.edu] Verzonden: donderdag 11 maart 2010 15:25 Aan: Dijk, F van CC: galaxy-dev@bx.psu.edu Onderwerp: Re: [galaxy-dev] Database migration problem Hi Freerk, When we change the databaseconnection to "mysql:///galaxy?unix_socket=/var/run/mysqld/mysqld.sock" in the universe_wsgi.ini file and run Galaxy we receive an error like: "Access denied for user 'root'@'localhost' (using password: NO)") None None So is there an option somewhere which we also have to change to make the connection work? You need to specify a username and password to access your database. Try this URL: mysql://user:password@localhost/galaxy?unix_socket=/var/lib/mysql/mysql. sock (exchanging 'user' and 'password' according to your settings) And question two: Do we need to migrate the content from the sqlite database to the MySQL database by hand or does this happen automatically? You need to do the migration manually. Kanwei provided a nice solution to this problem a while back: http://lists.bx.psu.edu/pipermail/galaxy-dev/2010-February/001996.html -- 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. -- Good luck and let us know if you have any more problems. J. ________________________________ De inhoud van dit bericht is vertrouwelijk en alleen bestemd voor de geadresseerde(n). Anderen dan de geadresseerde(n) mogen geen gebruik maken van dit bericht, het niet openbaar maken of op enige wijze verspreiden of vermenigvuldigen. Het UMCG kan niet aansprakelijk gesteld worden voor een incomplete aankomst of vertraging van dit verzonden bericht. The contents of this message are confidential and only intended for the eyes of the addressee(s). Others than the addressee(s) are not allowed to use this message, to make it public or to distribute or multiply this message in any way. The UMCG cannot be held responsible for incomplete reception or delay of this transferred message. De inhoud van dit bericht is vertrouwelijk en alleen bestemd voor de geadresseerde(n). Anderen dan de geadresseerde(n) mogen geen gebruik maken van dit bericht, het niet openbaar maken of op enige wijze verspreiden of vermenigvuldigen. Het UMCG kan niet aansprakelijk gesteld worden voor een incomplete aankomst of vertraging van dit verzonden bericht. The contents of this message are confidential and only intended for the eyes of the addressee(s). Others than the addressee(s) are not allowed to use this message, to make it public or to distribute or multiply this message in any way. The UMCG cannot be held responsible for incomplete reception or delay of this transferred message.
Hi Freerk, You have a couple options: (a) Use PostgreSQL as your database. We've found that Postgres is better behaved and more flexible than MySQL. For instance, using Postgres will solve ERROR 1059 for you; I don't know if it will solve ERROR 1064. (b) edit the generated SQL yourself to make it compatible with MySQL. E.g. for errors of type ERROR 1059, you can shorten the index name to make it compatible with MySQL. I'm not sure what the problem is ERROR 1064, but you should be able to look at the MySQL syntax and change a couple characters here and there to address this and similar errors. The MySQL syntax for transaction is here: http://dev.mysql.com/doc/refman/5.1/en/commit.html Hope this helps. Let us know if have more questions. J. On Mar 11, 2010, at 11:26 AM, Dijk, F van wrote:
Hi,
The syntax generated in the output generates errors. They look like this: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANSACTION' at line 1 and ERROR 1059 (42000) at line 77132: Identifier name 'ix_history_dataset_association_display_at_authorization_history_dataset_association_id' is too long
I hope this can be solved.
Sincerely,
Freerk
Van: Jeremy Goecks [mailto:jeremy.goecks@emory.edu] Verzonden: donderdag 11 maart 2010 16:50 Aan: Dijk, F van; galaxy-dev Onderwerp: Re: [galaxy-dev] Database migration problem
Hi Freerk,
Can you be more specific about the problems that you're seeing? Also, please continue to cc galaxy-dev as you're more likely to get help with your issues since it's going to a larger audience.
Thanks, J.
On Mar 11, 2010, at 10:42 AM, Dijk, F van wrote:
Hi Jeremy,
Thanks for the fast replay. The first issue is solved, a new one occured during the second point. When I migrate the sqlite database to SQL and try to import it into the MySQL database some errors occur. MySQL is complaining about the syntax used in the SQLdump. We are using the following MySQL version: mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (x86_64) using readline 5.2 I also read the info on the link you provided in your answer, but this won't solve this problem I think. So, do you have a solution?
Sincerely,
Freerk van Dijk
Van: Jeremy Goecks [mailto:jeremy.goecks@emory.edu] Verzonden: donderdag 11 maart 2010 15:25 Aan: Dijk, F van CC: galaxy-dev@bx.psu.edu Onderwerp: Re: [galaxy-dev] Database migration problem
Hi Freerk,
When we change the databaseconnection to "mysql:///galaxy?unix_socket=/var/run/mysqld/mysqld.sock" in the universe_wsgi.ini file and run Galaxy we receive an error like:
"Access denied for user 'root'@'localhost' (using password: NO)") None None
So is there an option somewhere which we also have to change to make the connection work?
You need to specify a username and password to access your database. Try this URL:
mysql://user:password@localhost/galaxy?unix_socket=/var/lib/mysql/mysql.sock (exchanging 'user' and 'password' according to your settings)
And question two: Do we need to migrate the content from the sqlite database to the MySQL database by hand or does this happen automatically?
You need to do the migration manually. Kanwei provided a nice solution to this problem a while back:
http://lists.bx.psu.edu/pipermail/galaxy-dev/2010-February/001996.html
-- 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. --
Good luck and let us know if you have any more problems.
J.
De inhoud van dit bericht is vertrouwelijk en alleen bestemd voor de geadresseerde(n). Anderen dan de geadresseerde(n) mogen geen gebruik maken van dit bericht, het niet openbaar maken of op enige wijze verspreiden of vermenigvuldigen. Het UMCG kan niet aansprakelijk gesteld worden voor een incomplete aankomst of vertraging van dit verzonden bericht.
The contents of this message are confidential and only intended for the eyes of the addressee(s). Others than the addressee(s) are not allowed to use this message, to make it public or to distribute or multiply this message in any way. The UMCG cannot be held responsible for incomplete reception or delay of this transferred message.
De inhoud van dit bericht is vertrouwelijk en alleen bestemd voor de geadresseerde(n). Anderen dan de geadresseerde(n) mogen geen gebruik maken van dit bericht, het niet openbaar maken of op enige wijze verspreiden of vermenigvuldigen. Het UMCG kan niet aansprakelijk gesteld worden voor een incomplete aankomst of vertraging van dit verzonden bericht.
The contents of this message are confidential and only intended for the eyes of the addressee(s). Others than the addressee(s) are not allowed to use this message, to make it public or to distribute or multiply this message in any way. The UMCG cannot be held responsible for incomplete reception or delay of this transferred message.
Hi, We had some issues using MySQL instead of sqlite as well. This is how it was solved on our end (Fresh installation): These steps are from a fresh Hg clone of galaxy (The latest version included the python egg needed for successful migration): 1) >sh setup.sh 2) Create a new MySQL database, in our case: galaxy-new 3) Create a MySQL user with sufficient privileges to talk to the created MySQL db, in our case: galaxy 4) In the galaxy root directory, edit the universe_wsgi.ini: Uncomment the #database_connection line and edit it to this format: *database_connection = mysql://username:password@host/database_name* So in our case, it was: *mysql://galaxy@localhost/galaxy-new* If you have a password set for your MySQL user it would be: mysql://galaxy:yourpassword@localhost/galaxy-new 5) Save the file, and: > sh run.sh - From here the migration proceeded automatically. Rgds, Roy Weckiewicz. On Thu, Mar 11, 2010 at 10:26 AM, Dijk, F van <f.van.dijk@medgen.umcg.nl>wrote:
Hi,
The syntax generated in the output generates errors. They look like this: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANSACTION' at line 1 and ERROR 1059 (42000) at line 77132: Identifier name 'ix_history_dataset_association_display_at_authorization_history_dataset_association_id' is too long
I hope this can be solved.
Sincerely,
Freerk
------------------------------ *Van:* Jeremy Goecks [mailto:jeremy.goecks@emory.edu] *Verzonden:* donderdag 11 maart 2010 16:50 *Aan:* Dijk, F van; galaxy-dev
*Onderwerp:* Re: [galaxy-dev] Database migration problem
Hi Freerk,
Can you be more specific about the problems that you're seeing? Also, please continue to cc galaxy-dev as you're more likely to get help with your issues since it's going to a larger audience.
Thanks, J.
On Mar 11, 2010, at 10:42 AM, Dijk, F van wrote:
Hi Jeremy,
Thanks for the fast replay. The first issue is solved, a new one occured during the second point. When I migrate the sqlite database to SQL and try to import it into the MySQL database some errors occur. MySQL is complaining about the syntax used in the SQLdump. We are using the following MySQL version: mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (x86_64) using readline 5.2 I also read the info on the link you provided in your answer, but this won't solve this problem I think. So, do you have a solution?
Sincerely,
Freerk van Dijk
------------------------------ *Van:* Jeremy Goecks [mailto:jeremy.goecks@emory.edu] *Verzonden:* donderdag 11 maart 2010 15:25 *Aan:* Dijk, F van *CC:* galaxy-dev@bx.psu.edu *Onderwerp:* Re: [galaxy-dev] Database migration problem
Hi Freerk,
When we change the databaseconnection to " mysql:///galaxy?unix_socket=/var/run/mysqld/mysqld.sock" in the universe_wsgi.ini file and run Galaxy we receive an error like:
"Access denied for user 'root'@'localhost' (using password: NO)") None None
So is there an option somewhere which we also have to change to make the connection work?
You need to specify a username and password to access your database. Try this URL:
mysql://user:password@localhost/galaxy?unix_socket=/var/lib/mysql/mysql.sock (exchanging 'user' and 'password' according to your settings)
And question two: Do we need to migrate the content from the sqlite database to the MySQL database by hand or does this happen automatically?
You need to do the migration manually. Kanwei provided a nice solution to this problem a while back:
http://lists.bx.psu.edu/pipermail/galaxy-dev/2010-February/001996.html
--
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.
--
Good luck and let us know if you have any more problems.
J.
------------------------------ De inhoud van dit bericht is vertrouwelijk en alleen bestemd voor de geadresseerde(n). Anderen dan de geadresseerde(n) mogen geen gebruik maken van dit bericht, het niet openbaar maken of op enige wijze verspreiden of vermenigvuldigen. Het UMCG kan niet aansprakelijk gesteld worden voor een incomplete aankomst of vertraging van dit verzonden bericht.
The contents of this message are confidential and only intended for the eyes of the addressee(s). Others than the addressee(s) are not allowed to use this message, to make it public or to distribute or multiply this message in any way. The UMCG cannot be held responsible for incomplete reception or delay of this transferred message.
------------------------------ De inhoud van dit bericht is vertrouwelijk en alleen bestemd voor de geadresseerde(n). Anderen dan de geadresseerde(n) mogen geen gebruik maken van dit bericht, het niet openbaar maken of op enige wijze verspreiden of vermenigvuldigen. Het UMCG kan niet aansprakelijk gesteld worden voor een incomplete aankomst of vertraging van dit verzonden bericht.
The contents of this message are confidential and only intended for the eyes of the addressee(s). Others than the addressee(s) are not allowed to use this message, to make it public or to distribute or multiply this message in any way. The UMCG cannot be held responsible for incomplete reception or delay of this transferred message.
_______________________________________________ galaxy-dev mailing list galaxy-dev@lists.bx.psu.edu http://lists.bx.psu.edu/listinfo/galaxy-dev
-- Roy Weckiewicz Texas A&M University
Forgot to mention that this method was done on OSX (latest) - parameters might differ depending on your system On Sat, Mar 13, 2010 at 9:16 AM, Roy Weckiewicz <roystefan@gmail.com> wrote:
Hi,
We had some issues using MySQL instead of sqlite as well. This is how it was solved on our end (Fresh installation):
These steps are from a fresh Hg clone of galaxy (The latest version included the python egg needed for successful migration):
1) >sh setup.sh 2) Create a new MySQL database, in our case: galaxy-new 3) Create a MySQL user with sufficient privileges to talk to the created MySQL db, in our case: galaxy 4) In the galaxy root directory, edit the universe_wsgi.ini: Uncomment the #database_connection line and edit it to this format:
*database_connection = mysql://username:password@host/database_name*
So in our case, it was:
*mysql://galaxy@localhost/galaxy-new*
If you have a password set for your MySQL user it would be: mysql://galaxy:yourpassword@localhost/galaxy-new
5) Save the file, and: > sh run.sh - From here the migration proceeded automatically.
Rgds,
Roy Weckiewicz.
On Thu, Mar 11, 2010 at 10:26 AM, Dijk, F van <f.van.dijk@medgen.umcg.nl>wrote:
Hi,
The syntax generated in the output generates errors. They look like this: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANSACTION' at line 1 and ERROR 1059 (42000) at line 77132: Identifier name 'ix_history_dataset_association_display_at_authorization_history_dataset_association_id' is too long
I hope this can be solved.
Sincerely,
Freerk
------------------------------ *Van:* Jeremy Goecks [mailto:jeremy.goecks@emory.edu] *Verzonden:* donderdag 11 maart 2010 16:50 *Aan:* Dijk, F van; galaxy-dev
*Onderwerp:* Re: [galaxy-dev] Database migration problem
Hi Freerk,
Can you be more specific about the problems that you're seeing? Also, please continue to cc galaxy-dev as you're more likely to get help with your issues since it's going to a larger audience.
Thanks, J.
On Mar 11, 2010, at 10:42 AM, Dijk, F van wrote:
Hi Jeremy,
Thanks for the fast replay. The first issue is solved, a new one occured during the second point. When I migrate the sqlite database to SQL and try to import it into the MySQL database some errors occur. MySQL is complaining about the syntax used in the SQLdump. We are using the following MySQL version: mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (x86_64) using readline 5.2 I also read the info on the link you provided in your answer, but this won't solve this problem I think. So, do you have a solution?
Sincerely,
Freerk van Dijk
------------------------------ *Van:* Jeremy Goecks [mailto:jeremy.goecks@emory.edu] *Verzonden:* donderdag 11 maart 2010 15:25 *Aan:* Dijk, F van *CC:* galaxy-dev@bx.psu.edu *Onderwerp:* Re: [galaxy-dev] Database migration problem
Hi Freerk,
When we change the databaseconnection to " mysql:///galaxy?unix_socket=/var/run/mysqld/mysqld.sock" in the universe_wsgi.ini file and run Galaxy we receive an error like:
"Access denied for user 'root'@'localhost' (using password: NO)") None None
So is there an option somewhere which we also have to change to make the connection work?
You need to specify a username and password to access your database. Try this URL:
mysql://user:password@localhost/galaxy?unix_socket=/var/lib/mysql/mysql.sock (exchanging 'user' and 'password' according to your settings)
And question two: Do we need to migrate the content from the sqlite database to the MySQL database by hand or does this happen automatically?
You need to do the migration manually. Kanwei provided a nice solution to this problem a while back:
http://lists.bx.psu.edu/pipermail/galaxy-dev/2010-February/001996.html
--
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.
--
Good luck and let us know if you have any more problems.
J.
------------------------------ De inhoud van dit bericht is vertrouwelijk en alleen bestemd voor de geadresseerde(n). Anderen dan de geadresseerde(n) mogen geen gebruik maken van dit bericht, het niet openbaar maken of op enige wijze verspreiden of vermenigvuldigen. Het UMCG kan niet aansprakelijk gesteld worden voor een incomplete aankomst of vertraging van dit verzonden bericht.
The contents of this message are confidential and only intended for the eyes of the addressee(s). Others than the addressee(s) are not allowed to use this message, to make it public or to distribute or multiply this message in any way. The UMCG cannot be held responsible for incomplete reception or delay of this transferred message.
------------------------------ De inhoud van dit bericht is vertrouwelijk en alleen bestemd voor de geadresseerde(n). Anderen dan de geadresseerde(n) mogen geen gebruik maken van dit bericht, het niet openbaar maken of op enige wijze verspreiden of vermenigvuldigen. Het UMCG kan niet aansprakelijk gesteld worden voor een incomplete aankomst of vertraging van dit verzonden bericht.
The contents of this message are confidential and only intended for the eyes of the addressee(s). Others than the addressee(s) are not allowed to use this message, to make it public or to distribute or multiply this message in any way. The UMCG cannot be held responsible for incomplete reception or delay of this transferred message.
_______________________________________________ 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
participants (3)
-
Dijk, F van -
Jeremy Goecks -
Roy Weckiewicz