MySQL Forums
Forum List  »  Newbie

Re: Will increaseing table size affect current data?
Posted by: Rick James
Date: September 08, 2009 09:16PM

The "machine" does not lock up, the "table" is LOCKed for the duration of the ALTER or mysqldump. This prevents any writes to the table from invalidating the ALTER or dump. Yes, it will take a long time; it effectively copies the entire table over, then rebuilds the index(es), often in a less than optimal way.

Recommend you check to see if your killing the system left behind any tmp files (.#sql*, or something like that) in the database directory. They can be removed.

I think newer versions of MySQL have increased the default from 2**32 to 2**48.

The issue: MyISAM indexes have pointers into the data. These pointers need to be some size (2-7 bytes); the old default was 4 bytes, hence the 2**32 limit. The ALTER TABLE ... MAX_ROWS=..., AVG_ROW_SIZE=... is turned into a pointer size and the table is rebuilt with the new size.

No data will be lost. Even a crash won't lose data -- the rebuilt table is in tmp file that are renamed at the last moment.

Another approach... (This works if you have something like an auto_increment with which to carefully walk thru the table. Also, updates to the table would invalidate this.)
1. CREATE another table ('new') with identical schema, plus the MAX_ROWS stuff.
2. Loop: Repeatedly copy the 'next' 1000 rows from the old table to the new.
3. When you are down to the last 1000, LOCK the table (to avoid any surprise inserts),
4. Copy the rest of the table
5. RENAME TABLE tbl TO old, new TO tbl;
6. UNLOCK TABLES;
7. (when satisfied) DROP TABLE old;

This approach will take awhile, but will not impact operations (except briefly in steps 3-6).

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.