MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:mos Date:June 16 2005 4:53am
Subject:Re: Table full
View as plain text  
At 04:35 PM 6/15/2005, you wrote:
>Mathias,
>
>Here's the query:
>
>UPDATE customer_indicator INNER JOIN
>customer_listing_pref
>ON customer_listing_pref.customer_id =
>customer_indicator.customer_id
>AND customer_listing_pref.store_id =
>customer_indicator.store_id
>AND customer_listing_pref.store_id = @OLD_STORE_ID
>LEFT JOIN contact_log ON contact_log.customer_id =
>customer_indicator.customer_id AND
>contact_log.store_id = @OLD_STORE_ID
>LEFT JOIN sent ON sent.pref_id =
>customer_listing_pref.pref_id
>SET customer_indicator.employee_id = @NEW_EMPLOYEE_ID,
>customer_indicator.store_id = @NEW_STORE_ID,
>customer_listing_pref.store_id = @NEW_STORE_ID,
>sent.store_id = @NEW_STORE_ID,
>contact_log.store_id = @NEW_STORE_ID
>WHERE customer_indicator.employee_id =
>@OLD_employee_id
>AND customer_indicator.store_id = @OLD_STORE_ID
>AND customer_indicator.customer_id BETWEEN 20000 AND
>23000;
>
>+------------------------+----------+
>| Variable_name          | Value    |
>+------------------------+----------+
>| lower_case_table_names | 0        |
>| max_heap_table_size    | 16777216 |
>| max_tmp_tables         | 32       |
>| table_cache            | 64       |
>| table_type             | MYISAM   |
>| tmp_table_size         | 33554432 |
>+------------------------+----------+
>
>I don't explicitly create any tables for this
>operation. I'm just trying to run the query. If I make
>the range in the BETWEEN condition of the WHERE claus
>sufficiently small the query runs. Otherwise I get the
>table is full error.
>
>So it seems that MySQL is doing some table creation
>behind the scenes. I pretty certain that I have enough
>disk space to perform the operation (I have about 10GB
>free).
>
>Thanks,
>
>Tripp
>
>
>--- mfatene@stripped wrote:
>
> > sorri it's tmp_table_size.
> >
> > mysql> show variables like '%table%';
> > +------------------------+----------+
> > | Variable_name          | Value    |
> > +------------------------+----------+
> > | innodb_file_per_table  | OFF      |
> > | innodb_table_locks     | ON       |
> > | lower_case_table_names | 1        |
> > | max_heap_table_size    | 16777216 |
> > | max_tmp_tables         | 32       |
> > | table_cache            | 256      |
> > | table_type             | InnoDB   |
> > | tmp_table_size         | 9437184  |
> > +------------------------+----------+
> > 8 rows in set (0.00 sec)
> >
> >
> > What are :
> > show create table toto;
> > the count(*) ?
> > the query ?
> >
> >
> >
> > Mathias
> > Selon Emmett Bishop <dyne_erg@stripped>:
> >
> > > Mathias,
> > >
> > > Thanks for the reply. I couldn't find a server
> > > variable named "max_temp_table_size" but I did
> > find
> > > one named "max_heap_table_size". Is that what you
> > > meant? BTW, I forgot to mention that I'm using
> > MySQL
> > > 4.0.20. Could it be that this variable that you
> > > mention is only in later versions?
> > >
> > > Basically, what I'm trying to do is a multi-table
> > > update statement. If I select too many rows I get
> > the
> > > "Table #sql-123 is full" error. If I bite off a
> > small
> > > enough chunk, the query works.
> > >
> > > Thanks again,
> > >
> > > Tripp
> > >
> > > --- mfatene@stripped wrote:
> > >
> > > > hi,
> > > > seems to be a temp table (sybase notation).
> > > > see max_temp_table_size
> > > >
> > > > Mathias
> > > > Selon Emmett Bishop <dyne_erg@stripped>:
> > > >
> > > > > Howdy all, I have a question about a SQL
> > statement
> > > > > that I'm trying to execute. When I execute the
> > > > > statement I get the following error: The table
> > > > > '#sql_bd6_3' is full.
> > > > >
> > > > > What does this mean exactly?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Tripp
> > > > >
> > > > >
> > > > >
> > > > > __________________________________
> > > > > Yahoo! Mail Mobile
> > > > > Take Yahoo! Mail with you! Check email on your
> > > > mobile phone.
> > > > > http://mobile.yahoo.com/learn/mail
> > > > >
> > > > > --
> > > > > MySQL General Mailing List
> > > > > For list archives:
> > http://lists.mysql.com/mysql
> > > > > To unsubscribe:
> > > >
> > http://lists.mysql.com/mysql?unsub=1
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > MySQL General Mailing List
> > > > For list archives: http://lists.mysql.com/mysql
> > > > To unsubscribe:
> > > >
> > >
> >
>http://lists.mysql.com/mysql?unsub=1
> > > >
> > > >
> > >
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Tired of spam?  Yahoo! Mail has the best spam
> > protection around
> > > http://mail.yahoo.com
> > >
> >
> >
> >
>


Tripp,
         This problem may occur if your table is quite large (several gb in 
size). The update may make the table too large to address using 
conventional MySQL pointers. You may need to modify the table so it has a 
"Max Rows=nnnn" option where "nnnn" is the max rows you expect the table to 
have and this forces MySQL to use a larger table pointer. When my tables 
exceeded 100 million rows, I got a similar error.  I added "Max 
Rows=1000000000" to the table definition to solve the problem.

Mike


Thread
How to write subqueries?Unknown Sender14 Jun
  • Re: How to write subqueries?SGreen14 Jun
    • RE: How to write subqueries?Rhino14 Jun
  • RE: How to write subqueries?Rhino14 Jun
  • Re: How to write subqueries?mfatene14 Jun
    • Re: How to write subqueries?Unknown Sender14 Jun
      • Re: How to write subqueries?mfatene14 Jun
        • Table fullEmmett Bishop15 Jun
          • Re: Table fullmfatene15 Jun
            • Re: Table fullEmmett Bishop15 Jun
              • Re: Table fullmfatene15 Jun
                • Re: Table fullEmmett Bishop15 Jun
                  • Re: Table fullmos16 Jun
                    • Re: Table fullEmmett Bishop16 Jun
                      • Re: Table fullmfatene16 Jun
        • Re: How to write subqueries?Unknown Sender15 Jun
    • Re: How to write subqueries?Unknown Sender14 Jun