On Fri, Aug 28, 2009 at 9:18 AM, Shawn Green <Shawn.Green@stripped> 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"
>> 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
>> to the VARCHAR columns. Anyway, it's been running for a day and a half,
>> I finally had to kill it.
>> So will this old "trick" still work? I've done this many times on 4.1
>> great success:
>> In mysql 5.0 - I create two new empty tables, one identical to the
>> and one identical but with no indexes. I name these tables with "_ion"
>> "_ioff" suffixes.
>> I then do a "insert into table_ioff select * from source" which inserts
>> 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
>> 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).
> 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.
> 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.
> 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
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.