Quoting Colbey <[EMAIL PROTECTED]>:
> Hrm.. interesting.. I know I've personally hit the 4gb limit before and
> had to do a fix for it.. but never hit an internal tablesize limitation..
>
> You didn't mention what version of mysql your running...
I mentioned in my original email ;p.....4.0.13-standard
> Also if the table has alot of columns and 1 of more of those columns has
> alot of data, perhaps considering looking at normalizing the table into a
> few? Could potentially speed up operations..
The table with a lot of columns (by alot I mean like 25 or so) is running great,
it just has millions of records...but there are no text fields in it, there are
fields that link to tables with id-text_field.
The problem is one of these tables (2 column table) with ID - text_field.
> Not the easy fix your looking for but a thought...
>
> Try running: SHOW TABLE STATUS FROM database LIKE 'table_name';
>
> It will check the max index/data sizes mysql is configured/allocated
> for...
Running that shows me the following:
mysql> SHOW TABLE STATUS FROM email_tracking LIKE 'email_body_old';
+----------------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
| 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 |
+----------------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
| email_body_old | MyISAM | Dynamic | 208853 | 20564 | 4294967292 |
4294967295 | 1820672 | 0
| 450438 | 2002-10-21 11:58:24 | 2003-08-31 01:01:41 | 2002-11-30
01:41:00 | | |
+----------------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
1 row in set (0.00 sec)
> On Thu, 4 Sep 2003, Keith Bussey wrote:
>
> > I do have another Database on the same machine which has a table that is a
> lot
> > bigger (about 9 GIGs) and it wasn't created with any of the special table
> > options suggested by Keith C. Ivey below.
> >
> > The difference is this table has many more fields, while the email_body one
> (the
> > one with the problem) as only 2:
> >
> > an ID autonumber field, and a text field.
> >
> > Perhaps there is some bug/limitation in Mysql whereby a field can only have
> so
> > much size ??
> >
> > --
> > Keith Bussey
> >
> > Wisol, Inc.
> > Chief Technology Manager
> > (514) 398-9994 ext.225
> >
> >
> > Quoting Colbey <[EMAIL PROTECTED]>:
> >
> > >
> > > Most likely it's the 4GB OS limitation... My suggestion is to create a
> > > new table using mysql's built in raid option... span the table over
> > > multiple files to allow of much larger table growth...
> > >
> > > migrate all the rows over to the new spanned table..
> > >
> > >
> > >
> > > On Thu, 4 Sep 2003, Keith C. Ivey wrote:
> > >
> > > > On 4 Sep 2003 at 10:53, Keith Bussey wrote:
> > > >
> > > > > Your suggestion seemed to wipe out my rows ! (s'ok I got a backup
> ;p)
> > > >
> > > > Yikes! Are you running into any file system limits? Have you dealt
> > > > with files larger than 4 GB on that server before with no problems?
> > > > If not, you may have run into a MySQL bug of some sort.
> > > >
> > > > An alternative way to get the table to have 5-byte pointers would be
> > > > to create the new table (same CREATE TABLE query as for the old
> > > > structure, except add "MAX_ROWS=700000 AVG_ROW_LENGTH=20000" to the
> > > > end) and then copy all the records into it:
> > > >
> > > > INSERT INTO email_body_NEW SELECT * FROM email_body;
> > > >
> > > > Actually, the exact figures for MAX_ROWS and AVG_ROW_LENGTH shouldn't
> > > > matter, as long as their product is between 2**32 and 2**40 - 1.
> > > >
> > > > --
> > > > Keith C. Ivey <[EMAIL PROTECTED]>
> > > > Tobacco Documents Online
> > > > http://tobaccodocuments.org
> > > >
> > > >
> > > > --
> > > > MySQL General Mailing List
> > > > For list archives: http://lists.mysql.com/mysql
> > > > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> > > >
> > >
> >
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
