MYISAM table over 4.5 gigs?

joey3002

does anyone know of a way to make the table go over that? my table is full but yet i have to put more in it.. and please.. no other way to store it than what i have..

thanks

joey3002

im using mysql 4.x

dream_scape

well look the thing is, MYISAM tables are single files, so their size is going to limited by your operating system and file system. Here is a list:

Operating System File-size Limit
Linux 2.2-Intel 32-bit 2GB (LFS: 4G😎
Linux 2.4+ (using ext3 filesystem) 4TB
Solaris 9/10 16TB
NetWare w/NSS filesystem 8TB
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
MacOS X w/ HFS+ 2TB

So you've probably hit your OS/file system limit. Switch the tables to InnoDB, and you can store more though, since InnoDB uses a tablespace that can be made up several files, you can store a larger table than what the OS allows for a single file. The max tablespace size for InnoDB is 64TB.

Yegg

or you can create another myisam table with the same structure, like one you already have - and concatanate them together with MERGE table.

joey3002

ALTER TABLE table_name MAX_ROWS=1000000000

i read that this will fix the issue?

Yegg

it's not hard limit, so you can store MORE than MAX_ROWS. you can check the size of your table can be by so query:

show table status from DATABASE_NAME like 'TABLE_NAME';

Max_data_length - this will show the maximum size of tables in MB what your system can creat

joey3002

when i do that i get:

Name Type Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Create_options Comment

datatable MyISAM Dynamic 7032474 236 1662405404 1099511627775 333003776 1480 10731797 2006-01-16 23:59:31 2006-01-18 07:37:23 2006-01-16 23:59:31 max_rows=1000000000

Yegg

Maybe you have AUTO_INCREMENT PRIMARY KEY Column - and it's out of REGION? So it's the reason you can't add new rows?

if nope, then what error code do U get? while trying adding new rows?? if you have 5.0 MySQL installed you can enter show errors and show warnings after you entered sql query + also, are you sure that partition where DB is located is not full? you can check it up with df -alh command in linux. also what OS do u use and what kernel installed uname -r?