Re: Updating 4 to 5
- Original Message - From: Rob Wultsch wult...@gmail.com To: Grant Peel gp...@thenetnow.com Cc: mysql@lists.mysql.com Sent: Friday, July 02, 2010 3:57 AM Subject: Re: Updating 4 to 5 On Thu, Jul 1, 2010 at 5:44 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I have serveral servers running mysql 4, and need to update to mysql 5. It would be good if mentioned what release of the various series you were using or wanting to upgrade to. I have version 5 setup on a new dev server and will be cloning that to the old servers, then restoring all the data from backups (mysql databases included). By restoring from backup for mysql a sql dump, or a filesystem backup? Once I have restored the data from backups, I will neeed to run mysqlupgrade. My question is, will the mysqlupgrade script update all the mysql tables, (grant tables etc), as well as update all the users databases, or will there be other things that need to be done? -Grant It depends. The way I generally do upgrades is the following: 1. Identify the backup point for the current server. Do a 'mysqldump --all-databases --complete-insert' from it using the mysqldump from the version of mysql I will be using after the upgrade and record the 'show slave status' while it is running. 2. Import the backup on to the new server after removing any commands that would perform ddl on the mysql schema. 3. Setup replicaton and fail over to the new server at an opportune time. So, you should keep in mind a few things: 1. Between version of MySQL the table format changes, and it is generally worthing while to take advantage of the changes. 2. mysqlupgrade runs REPAIR TABLE which acts differently in different versions of MySQL See http://www.mysqlperformanceblog.com/2010/05/14/mysql_upgrade-and-innodb-tables/. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gp...@thenetnow.com Rob, Thanks for taking the time to reply, The mysql databases would be restored from a filesystem backup. The whole server, mysql and all, are FreeBSD 6.x I usually: Run a complete backup of all filesystems, (/,/var,/home,/user) to an NFS server, Build the new server, Restore all filesystems to the new disk (that is built with mysql 5), Run the mysql upgrade script, start the mysql servers, then fix any PHP issues (as php is upgraded from 4 to 5 as well). Comments please, -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Connection Diagnostic Tool
I am setting up the Softslate web store package that uses Hibernate to connect to MySQL. Softslate fails to connect to MysQL on the c3p0 connection pooling. While I would love to solve this little problem it would be wiser if I learn to diagnose the problem. Is there a tool that can run on the Tomcat server that can help me replicate/diagnose the problem? Softslaste is running on the same box as MySQL. Thank you! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Retrieving table and field a foreign key references
For example, the following do not provide this information: - show create table address; - describe address; - select * from TABLE_CONSTRAINTS; - select * from key_column_usage; For tables not using transactional engines like InnoDB, MySQL discards foreign key specs, otherwise see Find child tables and Find parent tables at http://www.artfulsoftware.com/infotree/queries.php. PB - On 7/2/2010 6:56 PM, Kris wrote: Hello, I am having trouble finding a way to retrieve the table and field a foreign key references by querying MySQL. If you look at the following example tables, there is no way after the tables are created to learn that: - address.sid actually references state.id Is this possible ? For example, the following do not provide this information: - show create table address; - describe address; - select * from TABLE_CONSTRAINTS; - select * from key_column_usage; CREATE TABLE state ( id VARCHAR(2) PRIMARY KEY, name TEXT ); CREATE TABLE address (id INT PRIMARY KEY, address text, zipcode INT, sid VARCHAR(2), FOREIGN KEY(sid) REFERENCES state(id) ); No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.439 / Virus Database: 271.1.1/2977 - Release Date: 07/02/10 06:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Updating 4 to 5
On Sat, Jul 3, 2010 at 4:00 AM, Grant Peel gp...@thenetnow.com wrote: - Original Message - From: Rob Wultsch wult...@gmail.com To: Grant Peel gp...@thenetnow.com Cc: mysql@lists.mysql.com Sent: Friday, July 02, 2010 3:57 AM Subject: Re: Updating 4 to 5 On Thu, Jul 1, 2010 at 5:44 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I have serveral servers running mysql 4, and need to update to mysql 5. It would be good if mentioned what release of the various series you were using or wanting to upgrade to. I have version 5 setup on a new dev server and will be cloning that to the old servers, then restoring all the data from backups (mysql databases included). By restoring from backup for mysql a sql dump, or a filesystem backup? Once I have restored the data from backups, I will neeed to run mysqlupgrade. My question is, will the mysqlupgrade script update all the mysql tables, (grant tables etc), as well as update all the users databases, or will there be other things that need to be done? -Grant It depends. The way I generally do upgrades is the following: 1. Identify the backup point for the current server. Do a 'mysqldump --all-databases --complete-insert' from it using the mysqldump from the version of mysql I will be using after the upgrade and record the 'show slave status' while it is running. 2. Import the backup on to the new server after removing any commands that would perform ddl on the mysql schema. 3. Setup replicaton and fail over to the new server at an opportune time. So, you should keep in mind a few things: 1. Between version of MySQL the table format changes, and it is generally worthing while to take advantage of the changes. 2. mysqlupgrade runs REPAIR TABLE which acts differently in different versions of MySQL See http://www.mysqlperformanceblog.com/2010/05/14/mysql_upgrade-and-innodb-tables/. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=gp...@thenetnow.com Rob, Thanks for taking the time to reply, The mysql databases would be restored from a filesystem backup. The whole server, mysql and all, are FreeBSD 6.x I usually: Run a complete backup of all filesystems, (/,/var,/home,/user) to an NFS server, Build the new server, Restore all filesystems to the new disk (that is built with mysql 5), Run the mysql upgrade script, start the mysql servers, then fix any PHP issues (as php is upgraded from 4 to 5 as well). Comments please, -Grant That may be ok, it may not. Your methodology should be fine if you are only upgrading one release: 4.1 - 5.0 But it may not be ok for other upgrades: 4.0 - 5.0 (skipping 4.1) 4.0 - 5.1 (skipping 4.1 and 5.0) 4.1 - 5.1 (skipping 5.0) At this point MySQL 5.0 is near EOL, so it would probably be wise to upgrade to 5.1. It is unwise to skip upgrades so if you are going from 4.1 to 5.1, so you would need to do an intermediate upgrade to 5.0 . -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Connection Diagnostic Tool
I use c3p0 to manage MySQL connections in my JVM stack and have for years in many installations, I have never had to do anything special. If I can connect to the server through the console at the command line of the client machine using the same credentials, then the stack will just work. Are you using Tomcat's JNDI config? I have always found those to be a pain.. I manage my DataSource via Spring which I find to be much more portable. At the end of the day, if you are able to connect manually as described above but your Tomcat application cannot, it's is not a MySQL problem.. It's more likely a Tomcat/JNDI problem. If you can't connect via the command line (same client, same host, same credentials), then we have a MySQL issue we can address as such. - michael dykman On Sat, Jul 3, 2010 at 8:08 AM, michel compu...@videotron.ca wrote: I am setting up the Softslate web store package that uses Hibernate to connect to MySQL. Softslate fails to connect to MysQL on the c3p0 connection pooling. While I would love to solve this little problem it would be wiser if I learn to diagnose the problem. Is there a tool that can run on the Tomcat server that can help me replicate/diagnose the problem? Softslaste is running on the same box as MySQL. Thank you! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Retrieving table and field a foreign key references
Kris, You can use SHOW CREATE TABLE. On Fri, Jul 2, 2010 at 7:56 PM, Kris mk...@gmx.net wrote: Hello, I am having trouble finding a way to retrieve the table and field a foreign key references by querying MySQL. If you look at the following example tables, there is no way after the tables are created to learn that: - address.sid actually references state.id Is this possible ? For example, the following do not provide this information: - show create table address; - describe address; - select * from TABLE_CONSTRAINTS; - select * from key_column_usage; CREATE TABLE state ( id VARCHAR(2) PRIMARY KEY, name TEXT ); CREATE TABLE address (id INT PRIMARY KEY, address text, zipcode INT, sid VARCHAR(2), FOREIGN KEY(sid) REFERENCES state(id) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ba...@xaprb.com -- Baron Schwartz Percona Inc http://www.percona.com/ Consulting, Training, Support Services for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
