Alternate solution,
mysqldump --add-droptable db_name table_name >
path/with/a/lot/of/room/table_name.sql
Edit your file and change the table create clause to include TYPE = MYISAM
after the column defs. Ie:
CREATE TABLE table_name (column1 def, column2 def,...) TYPE = MYISAM
Then: mysql dbname < table_name.sql
Why? This will drop your table, and completely rebuild the index from
scratch, without taking the server down entirely. You also have a handy
backup file that you can continue to work with later if necessary.
Converting table type on the fly may be faster, I don't know, but if I can
afford the down time, I would prefer to create a backup of the data before
altering table structure.
Know what I mean?
Lindsay Adams
On 3/28/01 7:11 PM, "Benjamin Pflugmann" <[EMAIL PROTECTED]> wrote:
> Hi.
>
> On Thu, Mar 29, 2001 at 10:47:54AM +0800, [EMAIL PROTECTED] wrote:
>> Thanks!
>>
>> So, is there any way to calculate the approximate disk space required for the
>> transformation
>> from ISAM to MYISAM?
>
> I would have assumed 330MB and don't see the reason why it should use
> more. Obviously I am wrong about that.
>
> I think a solution should be to tell the MySQL daemon to store
> temporary files in a different directory, where you have enough space
> left (e.g. via the TMPDIR environment variable or the --tmpdir command
> line option).
>
> As a side note, your index/data size ratios seem a bit big. There are
> usually ways to keep indexes smaller. YMMV, of course.
>
> Bye,
>
> Benjamin.
>
>> Gerald Clark wrote:
>>
>>> error 28 is "No space left on device".
>>>
>>> Hang wrote:
>>>>
> [...]
>>>> I got quite a lot of tables and all can be converted successfully but
>>>> not this table. It contains > 3,000,000 rows, 80MB data file and 250MB
>>>> index file. I got following error message when I am converting the table
>>>> format:
>>>>
>>>> mysql> alter table mytable type=MYISAM;
>>>> ERROR 3: Error writing file '/tmp/ST4kylYE' (Errcode: 28)
> [...]
>>>> I had run isamcheck before the conversion and my /tmp got > 400MB disk
>>>> space.
>>>> Any one have idea what's going wrong?
>>>>
>>>> Besides, I also have tables on other Linux machines with about 200MB
>>>> data and 2.0GB index file. Will there be any problem?
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
