List:General Discussion« Previous MessageNext Message »
From:Hank Date:August 26 2009 12:11pm
Subject:upgrading from 4.1 to 5.0 "trick"
View as plain text  
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

Thread
upgrading from 4.1 to 5.0 "trick"Hank26 Aug
  • Re: upgrading from 4.1 to 5.0 "trick"Shawn Green28 Aug
    • Re: upgrading from 4.1 to 5.0 "trick"Hank3 Sep