Re: upgrading from 4.1 to 5.0 trick
On Fri, Aug 28, 2009 at 9:18 AM, Shawn Green shawn.gr...@sun.com wrote: Hank wrote: Hello All, I'm in the process of upgrading my database from 4.1 to 5.0 on CentOS. I've been testing the mysqlcheck --check-upgrade --auto-repair command, and on one of my MYISAM tables, it's taking forever to upgrade the table. It has about 114 million rows, and I'm guessing it needs to be upgraded due to the VARCHAR columns. Anyway, it's been running for a day and a half, and I finally had to kill it. So will this old trick still work? I've done this many times on 4.1 with great success: In mysql 5.0 - I create two new empty tables, one identical to the original and one identical but with no indexes. I name these tables with _ion and _ioff suffixes. I then do a insert into table_ioff select * from source which inserts just the original data into the new table, but doesn't have to rebuild any indexes. I then flush the tables. Then in the file system, I swap the table_ion.frm and table_ion.MYI files with the table_ioff ones. Flush tables again. I then just use myisamchk -r to repair the index file. It runs in about an hour. Can I do this same thing to upgrade the tables, instead of using mysqlcheck, which seems to be rebuilding the table row-by-row, instead of sorting (which myisamchk does). thanks. -Hank Hello Hank, Your technique will work within the following narrow limits of operation: * This will only work for MyISAM tables. * myisamchk is dangerous to run against any table that is in active use as it operates at the file level and has caused corruptions with live tables. Whenever possible either stop the server or prevent access from MySQL to that table with a FLUSH TABLES WITH READ LOCK before using myisamchk. http://dev.mysql.com/doc/refman/5.0/en/flush.html Alternatively, you should be able to match or improve this import then index process if you use an ALTER TABLE ... DISABLE KEYS command before the import followed by an ALTER TABLE ... ENABLE KEYS command after the import or if you use LOAD DATA INFILE ... . Also if you can import all of the data to an empty table in a single batch (statement), the indexes will be computed only once using the batch-index algorithm (it's a sort, not a merge) and that will also save processing time. http://dev.mysql.com/doc/refman/5.0/en/alter-table.html http://dev.mysql.com/doc/refman/5.0/en/load-data.html http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html The overall problem is still that the on-disk structure of the 5.0 tables has changed and that you still need to perform some kind of dump-restore or rebuild of the data as part of the conversion. Warmest regards, Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN Hello Shawn, Thanks for your reply. Yes, I have all of your conditions covered. 1. They are myisam tables 2. This is not a production system, so other people aren't accessing the tables. 3. And your last comment about dump/restore is taken care of (in my original note) since I am creating a new table (without indexes) in mysql 5.0, and then inserting all the data from the old table into the new one. Then I'm swapping the MYI/frm files, and then rebuilding the new table. I've tested this several times now, and it works like a charm. Finally, I don't like to use the ALTER TABLE DISABLE/ENABLE statements, since they operate in silent mode -- I have no idea what it's doing, or how long to expect the process to take. It would be very nice of those commands had some built-in progress meter or feedback/callback method.
Re: upgrading from 4.1 to 5.0 trick
Hank wrote: Hello All, I'm in the process of upgrading my database from 4.1 to 5.0 on CentOS. I've been testing the mysqlcheck --check-upgrade --auto-repair command, and on one of my MYISAM tables, it's taking forever to upgrade the table. It has about 114 million rows, and I'm guessing it needs to be upgraded due to the VARCHAR columns. Anyway, it's been running for a day and a half, and I finally had to kill it. So will this old trick still work? I've done this many times on 4.1 with great success: In mysql 5.0 - I create two new empty tables, one identical to the original and one identical but with no indexes. I name these tables with _ion and _ioff suffixes. I then do a insert into table_ioff select * from source which inserts just the original data into the new table, but doesn't have to rebuild any indexes. I then flush the tables. Then in the file system, I swap the table_ion.frm and table_ion.MYI files with the table_ioff ones. Flush tables again. I then just use myisamchk -r to repair the index file. It runs in about an hour. Can I do this same thing to upgrade the tables, instead of using mysqlcheck, which seems to be rebuilding the table row-by-row, instead of sorting (which myisamchk does). thanks. -Hank Hello Hank, Your technique will work within the following narrow limits of operation: * This will only work for MyISAM tables. * myisamchk is dangerous to run against any table that is in active use as it operates at the file level and has caused corruptions with live tables. Whenever possible either stop the server or prevent access from MySQL to that table with a FLUSH TABLES WITH READ LOCK before using myisamchk. http://dev.mysql.com/doc/refman/5.0/en/flush.html Alternatively, you should be able to match or improve this import then index process if you use an ALTER TABLE ... DISABLE KEYS command before the import followed by an ALTER TABLE ... ENABLE KEYS command after the import or if you use LOAD DATA INFILE ... . Also if you can import all of the data to an empty table in a single batch (statement), the indexes will be computed only once using the batch-index algorithm (it's a sort, not a merge) and that will also save processing time. http://dev.mysql.com/doc/refman/5.0/en/alter-table.html http://dev.mysql.com/doc/refman/5.0/en/load-data.html http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html The overall problem is still that the on-disk structure of the 5.0 tables has changed and that you still need to perform some kind of dump-restore or rebuild of the data as part of the conversion. Warmest regards, -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
upgrading from 4.1 to 5.0 trick
Hello All, I'm in the process of upgrading my database from 4.1 to 5.0 on CentOS. I've been testing the mysqlcheck --check-upgrade --auto-repair command, and on one of my MYISAM tables, it's taking forever to upgrade the table. It has about 114 million rows, and I'm guessing it needs to be upgraded due to the VARCHAR columns. Anyway, it's been running for a day and a half, and I finally had to kill it. So will this old trick still work? I've done this many times on 4.1 with great success: In mysql 5.0 - I create two new empty tables, one identical to the original and one identical but with no indexes. I name these tables with _ion and _ioff suffixes. I then do a insert into table_ioff select * from source which inserts just the original data into the new table, but doesn't have to rebuild any indexes. I then flush the tables. Then in the file system, I swap the table_ion.frm and table_ion.MYI files with the table_ioff ones. Flush tables again. I then just use myisamchk -r to repair the index file. It runs in about an hour. Can I do this same thing to upgrade the tables, instead of using mysqlcheck, which seems to be rebuilding the table row-by-row, instead of sorting (which myisamchk does). thanks. -Hank
Upgrading from 4.1 to 5.0
Does anyone have experience with upgrading large databases (~500GB each) from MySQL 4.1 to 5.0? The tables are in InnoDB format. We are using the Community version. I've read that it's recommended that you use mysqldump and then restore, but this is not possible for us, as we cannot have our databases down for long, nor can we have our tables locked while doing dump. I've tried doing the following steps: ibbackup --restore copy over mysql table dirs. set default char set to latin1 (or will default to utf8) in my.cnf because that's the original char set in 4.1 Upgrade only mysql database (user and privilege tables) mysqlcheck --check-upgrade --auto-repair mysql mysql_fix_privilege_tables I've written a script to compare data between the original 4.1 and the new 5.0. Looks like certain rows have different numerical data... so this is not good. I didn't want to do mysql_upgrade on all databases in this instance of MySQL because that resulted in 2 things happening: 1) Don't set default char set to latin1. Run mysql_upgrade Some rows had data truncated in certain columns. 2) Set default char set to latin1. Run mysql_upgrade Copies to TMP table. Takes forever... This is unacceptable for us. What is the recommended way to upgrade from 4.1 to 5.0? Or are we stuck using 4.1 forever? -Paul Choi Plaxo, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 4.1 to 5.0
You may want to try replication. Setup your replication server as 5.0. That server gives you a chance to play to get things right without affecting the master server. You'll still need to do a dump to get the slave up to speed. Once you get everything right, you can switch over and the slave becomes the master. Very simple in theory, a bit more complicated in practice. Brent Baisley Systems Architect On Apr 23, 2008, at 2:28 PM, Paul Choi wrote: Does anyone have experience with upgrading large databases (~500GB each) from MySQL 4.1 to 5.0? The tables are in InnoDB format. We are using the Community version. I've read that it's recommended that you use mysqldump and then restore, but this is not possible for us, as we cannot have our databases down for long, nor can we have our tables locked while doing dump. I've tried doing the following steps: ibbackup --restore copy over mysql table dirs. set default char set to latin1 (or will default to utf8) in my.cnf because that's the original char set in 4.1 Upgrade only mysql database (user and privilege tables) mysqlcheck --check-upgrade --auto-repair mysql mysql_fix_privilege_tables I've written a script to compare data between the original 4.1 and the new 5.0. Looks like certain rows have different numerical data... so this is not good. I didn't want to do mysql_upgrade on all databases in this instance of MySQL because that resulted in 2 things happening: 1) Don't set default char set to latin1. Run mysql_upgrade Some rows had data truncated in certain columns. 2) Set default char set to latin1. Run mysql_upgrade Copies to TMP table. Takes forever... This is unacceptable for us. What is the recommended way to upgrade from 4.1 to 5.0? Or are we stuck using 4.1 forever? -Paul Choi Plaxo, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading from 4.1 to 5.0 breaks things?
Hello, I am in an environment that has a bunch of unrelated mysql databases that exist on one server. I would like to upgrade to mysql 5.0 to take advantage of the newer features, but I don't want to break existing functionality. I checked out http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html , but I was wondering if anyone out there has done some real-world upgrading and discovered that it breaks compatability with commonly known products that use mysql, such as phpbb or wordpress or something like that. Anyone have any real-world experiences of upgrading? - Steve PS I tried looking in the archives, but couldn't find much in this specific topic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
