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

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