Mmh, for me it works. Have a look: Before:
MyISAM file: /mnt/File.MYI
Record format: Packed
Character set: latin1 (8)
File-version: 1
Creation time: 2005-10-25 14:44:09
Recover time: 2006-02-15 11:23:43
Status: changed
Auto increment key: 1 Last value: 48478197
Data records: 41446968 Deleted blocks: 32
Datafile parts: 41535171 Deleted data: 3456
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4
Datafile length: 4294967248 Keyfile length: 1268716544
Max datafile length: 4294967294 Max keyfile length: 4398046510079
Recordlength: 43
table description:
Key Start Len Index Type Rec/key Root
Blocksize
1 1 4 unique unsigned long 1 1024
1024
2 17 4 multip. unsigned long 23 323361792
1024
13 4 unsigned long 6
3 9 4 multip. unsigned long 30453 808448000
1024
Run:
mysql> alter table File max_rows=200000000000;
Query OK, 41446968 rows affected (28 min 47.36 sec)
Records: 41446968 Duplicates: 0 Warnings: 0
After:
brussel: /var/lib/mysql/bacula 121# myisamchk -dv File.MYI
MyISAM file: File.MYI
Record format: Packed
Character set: latin1 (8)
File-version: 1
Creation time: 2006-03-02 18:17:02
Recover time: 2006-03-02 18:45:49
Status: checked,analyzed
Auto increment key: 1 Last value: 48478197
Data records: 41446968 Deleted blocks: 0
Datafile parts: 41446968 Deleted data: 0
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 4
Datafile length: 4290117612 Keyfile length: 1469418496
Max datafile length: 281474976710654 Max keyfile length: 4398046510079
Recordlength: 43
table description:
Key Start Len Index Type Rec/key Root
Blocksize
1 1 4 unique unsigned long 1 224365568
1024
2 17 4 multip. unsigned long 23 1047458816
1024
13 4 unsigned long 6
3 9 4 multip. unsigned long 30476 1469417472
1024
On Do, 02 Mär 2006, "George R. Kasica" <[EMAIL PROTECTED]> wrote:
> Volker:
>
> I tried the sequence below and I don't see a change in my tables at
> all:
>
> BEFORE:
> =======
> # myisamchk -dv File
>
> MyISAM file: File
> Record format: Packed
> Character set: latin1 (8)
> File-version: 1
> Creation time: 2006-01-26 13:31:19
> Status: open,changed
> Auto increment key: 1 Last value: 3412187
> Data records: 3164900 Deleted blocks: 176853
> Datafile parts: 3342897 Deleted data: 18692352
> Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4
> Datafile length: 344524708 Keyfile length: 124920832
> Max datafile length: 2147483646 Max keyfile length: 2147483646
> Recordlength: 42
>
> table description:
> Key Start Len Index Type Rec/key Root
> Blocksize
> 1 1 4 unique unsigned long 1 23406592
> 1024
> 2 9 4 multip. unsigned long 0 23013376
> 1024
> 3 9 4 multip. unsigned long 0 4072448
> 1024
> 13 4 unsigned long 0
> 17 4 unsigned long 0
>
>
>
> mysql> ALTER TABLE File MAX_ROWS=1000000000 AVG_ROW_LENGTH=102;
> Query OK, 3164900 rows affected (8 min 6.42 sec)
> Records: 3164900 Duplicates: 0 Warnings: 0
>
>
> AFTER:
> ======
> # myisamchk -dv File
>
> MyISAM file: File
> Record format: Packed
> Character set: latin1 (8)
> File-version: 1
> Creation time: 2006-03-02 11:20:33
> Recover time: 2006-03-02 11:28:38
> Status: checked,analyzed
> Auto increment key: 1 Last value: 3412187
> Data records: 3164900 Deleted blocks: 0
> Datafile parts: 3164900 Deleted data: 0
> Datafile pointer (bytes): 5 Keyfile pointer (bytes): 4
> Datafile length: 325084916 Keyfile length: 118755328
> Max datafile length: 2147483646 Max keyfile length: 2147483646
> Recordlength: 42
>
> table description:
> Key Start Len Index Type Rec/key Root
> Blocksize
> 1 1 4 unique unsigned long 1 4426752
> 1024
> 2 9 4 multip. unsigned long 49452 63589376
> 1024
> 3 9 4 multip. unsigned long 49452 118754304
> 1024
> 13 4 unsigned long 16
> 17 4 unsigned long 1
>
>
> >On Thu, 2 Mar 2006 17:46:22 +0100, you wrote:
>
> >Hi,
> >
> >this for the archives: I got error messages saying "The table 'File' is
> >full". The table was 4 GB in size. It was on a ext3 filesystem.
> >Switching to xfs didn't help.
> >The solution is:
> >http://dev.mysql.com/doc/refman/5.0/en/full-table.html
> >
> >Saying:
> >You are using a MyISAM table and the space required for the table
> >exceeds what is allowed by the internal pointer size. If you don't
> >specify the MAX_ROWS table option when you create a table, MySQL uses
> >the myisam_data_pointer_size system variable. From MySQL 5.0.6 on, the
> >default value is 6 bytes, which is enough to allow 256TB of data. Before
> >MySQL 5.0.6, the default value is 4 bytes, which is enough to allow only
> >4GB of data. See Section 5.2.2, ?Server System Variables?.
> >
> >
> >You can check the maximum data/index sizes by using this statement:
> >
> >SHOW TABLE STATUS FROM database LIKE 'tbl_name';
> >
> >
> > You also can use myisamchk -dv /path/to/table-index-file.
> >
> > If the pointer size is too small, you can fix the problem by using
> > ALTER TABLE:
> >
> > ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;
> >
> > You have to specify AVG_ROW_LENGTH only for tables with BLOB or TEXT
> > columns; in this case, MySQL can't optimize the space required based
> > only on the number of rows.
> >
> >Interesting: http://jeremy.zawodny.com/blog/archives/000796.html
> ===[George R. Kasica]=== +1 262 677 0766
> President +1 206 374 6482 FAX
> Netwrx Consulting Inc. Jackson, WI USA
> http://www.netwrx1.com
> [EMAIL PROTECTED]
> ICQ #12862186
>
>
--
Volker Sauer * Alexanderstrasse 39/217 * 64283 Darmstadt
Telefon: 06151-154260 * Mobil: 0179-6901475 * ICQ#98164307
mailto:[EMAIL PROTECTED] * http://www.volker-sauer.de
PGPKey-Fingerprint: DB2611C7B12E0B2739992E4F7E354E4D5DD5D0E0
signature.asc
Description: Digital signature
